dbt-sqlserver
dbt-sqlserver copied to clipboard
dbt-msft
Adding columns to snapshot not working
I get an error when I add new columns to an existing snapshot (destination is available). Error is ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near ','. (102) (SQLExecDirectW)")
This is because this TSQL code is triggered:
SELECT * INTO
[dwh].[processed].[dyn_project_15793]
, CAST(NULL AS varchar(50)) AS
, CAST(NULL AS nvarchar) AS
, CAST(NULL AS int) AS
FROM [dwh_upgrade_dbt2].[processed].[dyn_project]
I added three new columns to the snapshot called new_varchar, new_int and new_nvarchar.
This highlights another issue, that is the creation of a nvarchar without length (was also in the previous adapter version). We basically get nvarchar(1) at this point.
I found the issue in snapshot.sql
{% set tempTable %}
SELECT * INTO {{tempTableName}} {{columns}} FROM [{{relation.database}}].[{{ relation.schema }}].[{{ relation.identifier }}] {{ information_schema_hints() }}
{% endset %}
Columns should be after the *, not after the into.
For example in fabric:
{% set tempTable %}
CREATE TABLE {{tempTableName}}
AS SELECT * {{columns}} FROM [{{relation.database}}].[{{ relation.schema }}].[{{ relation.identifier }}] {{ information_schema_hints() }}
{% endset %}