Bug in incremental model: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'column'. (156) (SQLExecDirectW)")

[xg1990]

The following query failed when the source table has a column removed

{{ config(
    materialized = 'incremental',
    unique_key = 'date',
    on_schema_change = "sync_all_columns",
) }}

SELECT
    DISTINCT *
FROM
    <source table>

The full error message is

('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'column'. (156) (SQLExecDirectW)")

Same error for on_schema_change = "append_new_columns",

and if I use

{{ config(
    materialized = 'incremental',
    unique_key = 'date',
    on_schema_change = "ignore",
) }}

I'll get this error message:

('42S22', "[42S22] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name '<the removed column>'. (207) (SQLExecDirectW)")

[hernanparra]

To solve this bug I use this macro inside the macros directory:

{% macro sqlserver__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 -%}

 {% if add_columns %}
 alter {{ relation.type }} {{ relation }}
        add
        {% for column in add_columns %}
           {{ column.name }} {{ column.data_type }}{{ ',' if not loop.last }}
        {% endfor %}{# ',' if add_columns and remove_columns #}
 {% endif %}

 {% if remove_columns %}
 alter {{ relation.type }} {{ relation }}
        drop column
        {% for column in remove_columns %}
            {{ column.name }}{{ ',' if not loop.last }}
        {% endfor %}
 {% endif %}

{%- endset -%}

{% do run_query(sql) %}

{% endmacro %}

The same macro could be used to solve the bug in this package. The right place to put it is inside the columns.sql file.

[sdebruyn]

thank you, included in #376