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.