issue with snapshot when source table has a new column

[dataders]

original dbt Slack thread in #dbt-synapse

Think I found a bug in the 0.19.2 version of the dbt-synapse.
When making a snapshot with a new detected column, it will try this query:
alter table <db>.<schema>.<table> add COLUMN <name> <datatype>;
This should be:
alter table <db>.<schema>.<table> add COLUMN <name> <datatype>;
Now you'll receive this error in v0.19.2:

Database error: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server]
[SQL Server]Parse error at line: 2, column: 75:
Incorrect syntax near 'column'. (103010) (SQLExecDirectW)")

[alittlesliceoftom]

@swanderz the example that it should be is identical unless my eyes deceive me. What you meant was, the statement should be:

alter table <db>.<schema>.<table> add <name> <datatype>;

I think?

Extra notes from slack:

I just checked and there’s no alter_relation_add_remove_columns macro for dbt-synapse or dbt-sqlserver! Maybe the default add_column works for SQL Server but not for Synapse?

We’d add a sqlserver__alter_relation_add_remove_columns or synapse__alter_relation_add_remove_columns macro. You mind making a pull request? It’ll land in one of the adapters.sql and this what the default macro looks like that you can use as a template.

{% macro default__alter_relation_add_remove_columns(relation, add_columns, remove_columns) %}
  
  {% if add_columns is none %}
    {% set add_columns = [] %}
  {% endif %}
  {% if remove_columns is none %}
    {% set remove_columns = [] %}
  {% endif %}
  
  {% set sql -%}
     
     alter {{ relation.type }} {{ relation }}
       
            {% for column in add_columns %}
               add column {{ column.name }} {{ column.data_type }}{{ ',' if not loop.last }}
            {% endfor %}{{ ',' if remove_columns | length > 0 }}
            
            {% for column in remove_columns %}
                drop column {{ column.name }}{{ ',' if not loop.last }}
            {% endfor %}
  
  {%- endset -%}

  {% do run_query(sql) %}

{% endmacro %}

[dataders]

closing -- please comment if you think this bug still persists!