Converting materialization from view to table fails

[glsdown]

If I build a model materialized as a view, and update to be a table instead, I get an error message:

('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Cannot use DROP TABLE with 'dbt_sources.test__dbt_backup' because 'bdc_gemma_sources.test__dbt_backup' is a view. Use DROP VIEW. (3705) (SQLMoreResults)")

Example:

# test.sql
{{
    config(
        materialized='view'
    )
}}

select
    1 as id
    , cast('a' as varchar(2048)) as my_field

Then run dbt run --select test and it will build the view. Update to:

# test.sql
{{
    config(
        materialized='table'
    )
}}

select
    1 as id
    , cast('a' as varchar(2048)) as my_field

Then run dbt run --select test, I get the error.

[ThomsenS]

I think the issue is in this script https://github.com/microsoft/dbt-fabric/blob/main/dbt/include/fabric/macros/adapters/relation.sql line 78.

EXEC('DROP {{ relation.type }} IF EXISTS {{ relation.include(database=False) }};');

Relation.type seems to be the target materialization and not the current materialization on the database.

Replacing that with a query that checks the materialization type on the database seems to work well.

EXEC('
    DECLARE @type as varchar(10);
    SET @type  = (  SELECT TABLE_TYPE 
                    FROM INFORMATION_SCHEMA.TABLES 
                    WHERE TABLE_NAME = ''{{ relation.identifier }}'' AND TABLE_SCHEMA = ''{{relation.schema}}'');
    IF (@type = ''BASE TABLE'')
    BEGIN
      DROP TABLE IF EXISTS {{ relation.include(database=False) }};
    END
    ELSE
    BEGIN
      DROP VIEW IF EXISTS {{ relation.include(database=False) }};
    END
    ')

Disclaimer, I am still new to dbt. What do you think @prdpsvs ?

[prdpsvs]

@ThomsenS , this has to be fixed in table and view materialization. The issue is that existing_relation is not loaded in table and view materializations. I have to safely build a back up for existing relation in case any errors.

Dropping the table in relation macro is an alternative but this was root symptom. @glsdown, I fixed this issue today. I will release a minor version with a few more bug fixes tomorrow