materialization_view_fabric macro is not able to find correct relation when re-running a dbt model

[il-hanna]

Description

I'm running into this case where:

• a model was first materialized as view
• then changed to materialized as table
• then when I try dbt run that model again (with existing materialization type still = table) dbt returns this error

Compilation Error in model stg__transaction_lines (models/staging/stg__transaction_lines.sql)
  When searching for a relation, dbt found an approximate match. Instead of guessing 
  which relation to use, dbt will move on. Please delete "DB_WG_BI"."dbt_staging"."stg__transaction_lines", or rename it to be less ambiguous.
  Searched for: "db_wg_bi"."dbt_staging"."stg__transaction_lines"
  Found: "DB_WG_BI"."dbt_staging"."stg__transaction_lines"
  
  > in macro materialization_table_fabric (macros/materializations/models/table/table.sql)
  > called by model stg__transaction_lines (models/staging/stg__transaction_lines.sql)

Expected behavior

• earlier I was running this model alright without dbt throwing a weird notice around schema upper/lower case. But based on the error here seems like dbt-fabric materialization macro is searching for a lower cased schema (while it should have respected the casing of the original schema), drop that object, then rebuild.

[il-hanna]

update, this is due to the quoting definition. A workaround has been switching back and forth between quoting config for database, schema, and identifier - when I use dbt run vs dbt packages.

[prdpsvs]

@il-hanna , can you share what you did to resolve this issue? It would help others, and I will see how this can be resolved.

[il-hanna]

ah sure, I was using this in the dbt_project.yml file

the problem occurred when I was using database: false, while the database name in Fabric is upper-cased, if I switched it to true technically it should be ok, but then I'd run into problem with some dbt packages --> so what I did was a combination of quoting definition above + changing the database name on Fabric to be lower-cased.

For the identifier (which is for object name) keeping it as true should be ok for usual dbt run, I just had to toggle it to false when I run a local (modified) package -- which is another note for this workaround in case somebody has similar problem with some dbt package (not neat at all, but I didn't have much time to resolve it properly).

Hope that clears it up.

[prdpsvs]

Thanks @il-hanna , The issue is that both object names and data are case sensitive by default. We recently launched CI data option. CI option for metadata will soon be available. With that, you should not have this problem.

I am wait on the metadata CI (CI on objects) to address this problem