Description
openedon Oct 11, 2024
If you attempt to materialize a model in a second warehouse by setting its database
property (and you've already materialized at least one model in your target database), you receive the following error:
('42000', '[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]The specified schema name "dbt_mycustomschema" either does not exist or you do not have permission to use it. (2760) (SQLMoreResults)')
It seems that the fabric__list_schemas
macro in dbt/include/fabric/macros/adapters/metadata.sql
always runs against the target database, not the database configured for the relation. I think this could be resolved by adding a USE {{database}}
statement immediately above the select ... from sys.schemas
statement:
USE {{database}};
select name as [schema]
from sys.schemas {{ information_schema_hints() }} {{ apply_label() }}
Reproduction steps
- Create two warehouses in the same workspace:
WH_Primary
andWH_Secondary
- Configure the profiles target to connect to
WH_Primary
and the schema to bedbt_mycustomschema
- Create a model and run dbt. Confirm the model is successfully materialized in
WH_Primary
underdbt_mycustomschema
- Create a second model and configure its database to
WH_Secondary
. - Run dbt again. You'll get the error above.
This is because when the adapter attempts to materialize the second model and checks for the existence of the schema, it checks in the WH_Primary
database (in which it exists because of step 3) and skips creating it.