Incorrect syntax near ',' after change source table of the snapshot

[FlorianVc]

When I run dbt snapshot for a existing source table that has a new column I get the error:

Database Error in snapshot test_snapshot (snapshots\test.sql)
('42S01', "[42S01] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]There is already an object named 'test_snapshot__dbt_tmp' in the database. (2714) (SQLMoreResults)")

Expectations

The existing snapshot table is extended by the new source table column.

system setup

• Windows Server 2019 Standard (10.0)
• dbt core version: installed: 1.7.10
• MS SQL plugin: sqlserver: 1.7.4

How to reproduce

1. add the initial table and snapshot
2. execute dbt run and dbt snapshot --select test_snapshot
3. change the initial table test. Add the column Status
4. execute dbt run and dbt snapshot --select test_snapshot

initial snapshot definition

snapshots\test.sql

{% snapshot test_snapshot %}

    {{
        config(
          target_schema='PSA',
          strategy='check',
          unique_key='id',
          check_cols=['Count']
        )
    }}
    select * from {{ ref('test') }}

{% endsnapshot %}

Initial Table definition

models\example\test.sql

{{ config(
    { "as_columnstore": false,
     "materialized": 'table',
    "post-hook": [
            "{{ create_clustered_index(columns = ['id'], unique=True) }}"
        ]}

    ) }}

with source_data as (

    select  cast(1 as bigint) as id, cast(1195 as bigint) as Supplier, 1 as Count
    union all
    select cast(2 as bigint) as id, cast(7000000011 as bigint) as Supplier, 1 as Count
)
select * from source_data

changed snapshot definition

snapshots\test.sql

{% snapshot test_snapshot %}

    {{
        config(
          target_schema='PSA',
          strategy='check',
          unique_key='id',
          check_cols=['Count','Status']
        )
    }}
    select * from {{ ref('test') }}

{% endsnapshot %}

changed Table definition

models\example\test.sql

{{ config(
    { "as_columnstore": false,
     "materialized": 'table',
    "post-hook": [
            "{{ create_clustered_index(columns = ['id'], unique=True) }}"
        ]}

    ) }}

with source_data as (

    select  cast(1 as bigint) as id, cast(1195 as bigint) as Supplier, 1 as Count, 'x' as Status
    union all
    select cast(2 as bigint) as id, cast(7000000011 as bigint) as Supplier, 1 as Count, 'y' as Status
)
select * from source_data

[ericmuijsvanoord]

Could it be related to SELECT *? Potentially the materialization adds additional columns. Nvarchars are also not supported if you use those. Look into log folder and run the commands listed there manually to see the issues.

[FlorianVc]

The error happens when the temp table is filled. ('SELECT * INTO [database].[schema].[snapshottabel....] FROM [snapshot_table]')

The effected exception block:

On snapshot.dbt_test.test_snapshot: /* {"app": "dbt", "dbt_version": "1.7.11", "profile_name": "dbt_test", "target_name": "dev", "node_id": "snapshot.dbt_test.test_snapshot"} */

        SELECT * INTO
    [MDH_Process_dbt].[PSA].[test_snapshot_5841]

      , CAST(NULL AS varchar(1)) AS

   FROM [MDH_Process_dbt].[PSA].[test_snapshot]

fabric adapter: Database error: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near ','. (102) (SQLExecDirectW)")

When I run the same snapshot without the new column Status, the effected sql block look like this:

    SELECT * INTO
[MDH_Process_dbt].[PSA].[test_snapshot_10884]

FROM [MDH_Process_dbt].[PSA].[test_snapshot]

Could it be related to SELECT *? Potentially the materialization adds additional columns.

No, this has no effects, I get the same error and the same sql block is effected.

    SELECT * INTO
[MDH_Process_dbt].[PSA].[test_snapshot_6372]

  , CAST(NULL AS varchar(1)) AS

FROM [MDH_Process_dbt].[PSA].[test_snapshot]

[ericmuijsvanoord]

Ahh well, you might have an old version since I fixed this error:

https://github.com/dbt-msft/dbt-sqlserver/commit/7b5e28c2a96ddd3b640151c92c43fc247adca44e

Can you use main?

[FlorianVc]

Thanks for the fast response. 👍

How can I use the main? Is the fix in the next release?

[ericmuijsvanoord]

It seems that is not in the release yet. With pip you can install from the repository, or copy the macro (snapshot.sql) to your local project https://stackoverflow.com/questions/15268953/how-to-install-python-package-from-github

[FlorianVc]

The bug fix works as I expected.

Do you now the release version in which the bug fix is included??

[ericmuijsvanoord]

A new one has to be created, I checked the last release (last week) and it did not include the fix.

[FlorianVc]

With the following dbt versions the source changes on the snapshot table work as expected. Core:

• installed: 1.8.7
• latest: 1.8.7 - Up to date!

Plugins:

• fabric: 1.8.7 - Up to date!
• sqlserver: 1.8.4 - Up to date!