dbt run fails when you change the materialization type

[sdebruyn]

When you first materialize a model as a view and then later on change your config to materialize that exact same model as a table, it will fail because dbt-fabric tries to drop the model as being a table while it was a view. Other adapters handle this gracefully by dropping the view without issues.

08:10:20 fabric adapter: Database error: ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Cannot use DROP TABLE with 'dbt_sdebruyn.locations__dbt_backup' because 'dbt_sdebruyn.locations__dbt_backup' is a view. Use DROP VIEW. (3705)

[prdpsvs]

@sdebruyn , I tried to repro the issue, but couldn't. Please see the below execution log. I created a model stg_customers.sql as a view, changed it as a table and then as a view.

Also, please see the attached dbt log for all three executions. dbt.log

prdpsvs@Pradeep:~/repos/jaffle-shop$ dbt run --model stg_customers.sql
00:24:24  Running with dbt=1.8.6
00:24:24  Registered adapter: fabric=1.8.8
00:24:24  Unable to do partial parsing because saved manifest not found. Starting full parse.
00:24:25  [WARNING]: Deprecated functionality
The `tests` config has been renamed to `data_tests`. Please see
https://docs.getdbt.com/docs/build/data-tests#new-data_tests-syntax for more
information.
00:24:25  Found 6 models, 3 seeds, 20 data tests, 453 macros
00:24:25  
00:24:28  Concurrency: 4 threads (target='fabric-dev')
00:24:28  
00:24:28  1 of 1 START sql view model dbo.stg_customers .................................. [RUN]
00:24:30  1 of 1 OK created sql view model dbo.stg_customers ............................. [OK in 1.35s]
00:24:30  
00:24:30  Finished running 1 view model in 0 hours 0 minutes and 4.58 seconds (4.58s).
00:24:30  
00:24:30  Completed successfully
00:24:30  
00:24:30  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1
prdpsvs@Pradeep:~/repos/jaffle-shop$ dbt run --model stg_customers.sql
00:31:38  Running with dbt=1.8.6
00:31:38  Registered adapter: fabric=1.8.8
00:31:38  [WARNING]: Deprecated functionality
The `tests` config has been renamed to `data_tests`. Please see
https://docs.getdbt.com/docs/build/data-tests#new-data_tests-syntax for more
information.
00:31:39  Found 6 models, 3 seeds, 20 data tests, 453 macros
00:31:39  
00:31:41  Concurrency: 4 threads (target='fabric-dev')
00:31:41  
00:31:41  1 of 1 START sql table model dbo.stg_customers ................................. [RUN]
00:31:47  1 of 1 OK created sql table model dbo.stg_customers ............................ [OK in 5.35s]
00:31:47  
00:31:47  Finished running 1 table model in 0 hours 0 minutes and 7.99 seconds (7.99s).
00:31:47  
00:31:47  Completed successfully
00:31:47  
00:31:47  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1
prdpsvs@Pradeep:~/repos/jaffle-shop$ dbt run --model stg_customers.sql
00:31:57  Running with dbt=1.8.6
00:31:57  Registered adapter: fabric=1.8.8
00:31:57  [WARNING]: Deprecated functionality
The `tests` config has been renamed to `data_tests`. Please see
https://docs.getdbt.com/docs/build/data-tests#new-data_tests-syntax for more
information.
00:31:57  Found 6 models, 3 seeds, 20 data tests, 453 macros
00:31:57  
00:32:00  Concurrency: 4 threads (target='fabric-dev')
00:32:00  
00:32:00  1 of 1 START sql view model dbo.stg_customers .................................. [RUN]
00:32:01  1 of 1 OK created sql view model dbo.stg_customers ............................. [OK in 1.57s]
00:32:01  
00:32:01  Finished running 1 view model in 0 hours 0 minutes and 3.91 seconds (3.91s).
00:32:01  
00:32:01  Completed successfully
00:32:01  
00:32:01  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1
prdpsvs@Pradeep:~/repos/jaffle-shop$ 

With dbt-fabric==1.8.7 version

prdpsvs@Pradeep:~/repos/jaffle-shop$ dbt run --model stg_customers.sql
00:51:43  Running with dbt=1.8.6
00:51:44  Registered adapter: fabric=1.8.7
00:51:44  Found 6 models, 3 seeds, 20 data tests, 453 macros
00:51:44  
00:51:46  Concurrency: 4 threads (target='fabric-dev')
00:51:46  
00:51:46  1 of 1 START sql view model dbo.stg_customers .................................. [RUN]
00:51:48  1 of 1 OK created sql view model dbo.stg_customers ............................. [OK in 1.62s]
00:51:48  
00:51:48  Finished running 1 view model in 0 hours 0 minutes and 3.98 seconds (3.98s).
00:51:48  
00:51:48  Completed successfully
00:51:48  
00:51:48  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1
prdpsvs@Pradeep:~/repos/jaffle-shop$ dbt run --model stg_customers.sql
00:51:59  Running with dbt=1.8.6
00:51:59  Registered adapter: fabric=1.8.7
00:52:00  [WARNING]: Deprecated functionality
The `tests` config has been renamed to `data_tests`. Please see
https://docs.getdbt.com/docs/build/data-tests#new-data_tests-syntax for more
information.
00:52:00  Found 6 models, 3 seeds, 20 data tests, 453 macros
00:52:00  
00:52:02  Concurrency: 4 threads (target='fabric-dev')
00:52:02  
00:52:02  1 of 1 START sql table model dbo.stg_customers ................................. [RUN]
00:52:10  1 of 1 OK created sql table model dbo.stg_customers ............................ [OK in 7.49s]
00:52:10  
00:52:10  Finished running 1 table model in 0 hours 0 minutes and 9.86 seconds (9.86s).
00:52:10  
00:52:10  Completed successfully
00:52:10  
00:52:10  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1
prdpsvs@Pradeep:~/repos/jaffle-shop$ dbt run --model stg_customers.sql
00:52:22  Running with dbt=1.8.6
00:52:22  Registered adapter: fabric=1.8.7
00:52:22  Found 6 models, 3 seeds, 20 data tests, 453 macros
00:52:22  
00:52:25  Concurrency: 4 threads (target='fabric-dev')
00:52:25  
00:52:25  1 of 1 START sql table model dbo.stg_customers ................................. [RUN]
00:52:29  1 of 1 OK created sql table model dbo.stg_customers ............................ [OK in 3.31s]
00:52:29  
00:52:29  Finished running 1 table model in 0 hours 0 minutes and 6.09 seconds (6.09s).
00:52:29  
00:52:29  Completed successfully
00:52:29  
00:52:29  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1
prdpsvs@Pradeep:~/repos/jaffle-shop$ dbt run --model stg_customers.sql
00:52:37  Running with dbt=1.8.6
00:52:38  Registered adapter: fabric=1.8.7
00:52:38  [WARNING]: Deprecated functionality
The `tests` config has been renamed to `data_tests`. Please see
https://docs.getdbt.com/docs/build/data-tests#new-data_tests-syntax for more
information.
00:52:38  Found 6 models, 3 seeds, 20 data tests, 453 macros
00:52:38  
00:52:41  Concurrency: 4 threads (target='fabric-dev')
00:52:41  
00:52:41  1 of 1 START sql view model dbo.stg_customers .................................. [RUN]
00:52:42  1 of 1 OK created sql view model dbo.stg_customers ............................. [OK in 1.47s]
00:52:42  
00:52:42  Finished running 1 view model in 0 hours 0 minutes and 3.90 seconds (3.90s).
00:52:42  
00:52:42  Completed successfully
00:52:42  
00:52:42  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1
prdpsvs@Pradeep:~/repos/jaffle-shop$ 

[TheBishop-98]

So when I have seen this issue one has to make an error within a model causing it to fail. If the user then materializes it opposite of the first time then run it does not drop the opposing dbt_backup materialization. Maybe that is what this originally getting at?

[prdpsvs]

@TheBishop-98 , good catch.

The table materialization should check for backup_relation type using adapter.get_relation(database=this.database, schema=this.schema, identifier=this.identifier) and then drop it with correct object type. It's handled correctly in view materialization but not in table materialization.

I will update it and release a new version along other changes.

[TheBishop-98]

@prdpsvs When can we expect the fix to be shipped? Some of these small issues are popping up in our production runs, and should not be.