Snapshotting with schema changes doesn't work

[oscar-konig]

When snapshotting a table after a schema change I get ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'column'. (156) (SQLExecDirectW)") The table is created by a simple model:

select 
     1 as id 
     --,1 as id2
     ,current_timestamp as updated_at 
union all
select 
    5 as id
    --,1 as id2
    ,current_timestamp as updated_at

and snapshot:

{% snapshot test_snap %}

{{
    config(
      target_schema='snapshots',
      unique_key='id',

      strategy='timestamp',
      updated_at='updated_at'
    )
}}

select * from dbo.my_first_dbt_model

{% endsnapshot %}

I run dbt run followed by dbt snapshot to create the snapshot. Then uncomment the commented row in the original table model to modify the table schema:

select 
     1 as id 
     ,1 as id2
     ,current_timestamp as updated_at 
union all
select 
    5 as id
    ,1 as id2
    ,current_timestamp as updated_at

Running dbt run and dbt snapshot again produces the aforementioned error. Changing the project profile to connect to a BigQuery database and running the exact same models and procedure produces no errors. The same behaviour is present when using check strategy for the snapshot instead of timestamping (and removing the updated_at column).

[dataders]

@oscar-konig thanks for reporting this and double thanks for the reproducible example! This helped @alieus and I resolve the issue very quickly. The fix was that the below macro was missing. dbt's default version of this macro default__ macro uses "add column" but TSQL prefers just "add".

{% macro sqlserver__create_columns(relation, columns) %}
  {# default__ macro uses "add column"
     TSQL preferes just "add"
  #}
  {% for column in columns %}
    {% call statement() %}
      alter table {{ relation }} add "{{ column.name }}" {{ column.data_type }};
    {% endcall %}
  {% endfor %}
{% endmacro %}