get_catalog_relations passes unqualified schema names when custom schema prefix is used

[schakschak]

dbt-sqlserver version: 1.9.1 (works correctly on 1.9.0)

dbt-core version: 1.11.11

Database: SQL Server 2022

Problem:

When using a custom schema prefix in dbt_project.yml like this:

models:
  my_project:
    staging:
      +schema: stage
    raw_vault:
      +schema: raw_vault
The generate_schema_name macro correctly produces myproject_stage and myproject_raw_vault as the actual database schemas. But in 1.9.1, the Relation objects passed to get_catalog_relations have relation.schema = 'stage' instead of relation.schema = 'myproject_stage'. The catalog query therefore finds nothing and the catalog is written empty.

Bisect

1.9.0 works. 1.9.1 broke this. The only meaningful change between those releases is the removal of dbt-fabric as an upstream dependency (PR #628). Something in that dependency previously ensured Relation objects were constructed with the fully-generated schema name.

Sources are unaffected — their schema is their actual database schema and requires no prefix, so they resolve correctly.

Workaround

Pin to dbt-sqlserver==1.9.0.

[Benjamin-Knight]

Can I ask for some more information because the asymmetry between the tables landing in the right place still but the catalog being incorrect sounds unusual as they both use the same code path.

1. After a fresh dbt run on 1.9.1, where did the tables physically land in the database? In myproject_stage or stage?
2. Does your dbt_project.yml contain dbt_sqlserver_use_default_schema_concat: true?
3. Does your project have a macros/generate_schema_name.sql override, does it delegate to
   adapter.dispatch(...) or build the name directly?

[axellpadilla]

Adding context to check if this solves your specific issue, this behavior changed through the Fabric dependency.

Important upstream reference: microsoft/dbt-fabric#266

dbt-fabric removed its custom schema override around 1.9.3, so this dependency combination started using dbt-core’s default schema behavior:

dbt-sqlserver==1.9.0
dbt-fabric>=1.9.3

That means schemas were resolved as:

<target.schema>_<custom_schema_name>

In dbt-sqlserver==1.9.1, the Fabric dependency was removed, and legacy behavior kept to avoid breaking all the other old versions, so SQL Server returned to its legacy behavior unless explicitly configured.

Please enable this setting to keep the upstream/dbt-core behavior:

vars:
  dbt_sqlserver_use_default_schema_concat: true

See the README flags section and warnings:
https://github.com/dbt-msft/dbt-sqlserver

I will also open an issue to migrate this to dbt’s standard adapter behavior-flag approach, with command warnings and a future deprecation path toward v1.11.0:

flags:
  <adapter_flag_name>: true

[schakschak]

Can I ask for some more information because the asymmetry between the tables landing in the right place still but the catalog being incorrect sounds unusual as they both use the same code path.

1. After a fresh dbt run on 1.9.1, where did the tables physically land in the database? In myproject_stage or stage?
2. Does your dbt_project.yml contain dbt_sqlserver_use_default_schema_concat: true?
3. Does your project have a macros/generate_schema_name.sql override, does it delegate to
   adapter.dispatch(...) or build the name directly?

1. they landed in stage, for example stage.table_name
2. No - and from the other comment I should probably try the flag, which i did, but unfortunately didn't work. It doesn't concatenate the target.schema + custom schema. Only uses custom schema name
3. No custom generate_schema_name macro — the project relies entirely on the default from dbt-sqlserver/dbt-core. The schema name is built by whatever dbt-sqlserver ships, meaning the changed behaviour is caused by something in 1.9.1.

[schakschak]

Ended up with overriding generate_schema_name. Didn't get the flag to work. Thanks for your comments..