Incremental does not work with unique_key that is array

[johnnytang24]

Example model:

{{ 
  config
  (
    materialized='incremental'
    , unique_key=['A', 'B']
  ) 
}}
WITH CTE AS
(
  SELECT 1 AS A, 2 AS B
  UNION
  SELECT 3 AS A, 4 AS B
)
SELECT
  A
  , B
FROM
  CTE

Error: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near 'using'. (102) (SQLExecDirectW);

Code generated in run/project/model:

delete from "Test"."dbt"."t"
        using "Test"."dbt"."#t__dbt_tmp"
        where (
                "Test"."dbt"."#t__dbt_tmp".A = "Test"."dbt"."t".A
                and
                "Test"."dbt"."#t__dbt_tmp".B = "Test"."dbt"."t".B
        );

DELETE FROM ... USING is not supported by SQL Server

[johnnytang24]

This fixes it:

File: dbt/include/sqlserver/macros/materializations/models/incremental/merge.sql

{% macro sqlserver__get_delete_insert_merge_sql(target, source, unique_key, dest_columns) %}

    {%- set dest_cols_csv = get_quoted_csv(dest_columns | map(attribute="name")) -%}

    {% if unique_key %}
        {% if unique_key is sequence and unique_key is not string %}
            delete from {{ target }}
            where exists (
                SELECT NULL
                FROM
                  {{ source }}
                WHERE
                {% for key in unique_key %}
                    {{ source }}.{{ key }} = {{ target }}.{{ key }}
                    {{ "and " if not loop.last }}
                {% endfor %}
            );
        {% else %}
            delete from {{ target }}
            where (
                {{ unique_key }}) in (
                select ({{ unique_key }})
                from {{ source }}
            );

        {% endif %}
        {% endif %}

    insert into {{ target }} ({{ dest_cols_csv }})
    (
        select {{ dest_cols_csv }}
        from {{ source }}
    )

{% endmacro %}

[sdebruyn]

@johnnytang24 Thanks for immediately providing us with the fix! Could you quickly also give me the output of dbt --version? Thanks!

[johnnytang24]

Core:
  - installed: 1.1.0

[sdebruyn]

While going through the dbt docs, I could not find any documented behaviour where you can use an array as the unique key. All examples point to SQL columns or expressions. The unique_key for incremental models is not properly documented, but you can find the same property for snapshot configurations: https://docs.getdbt.com/reference/resource-configs/unique_key

There it says you can use an expression to combine multiple columns.

However... when you take a look at the core used in dbt-core it does seem to support sequences so I'd say we have to do the same here.

I'll do the fix in this project and create an issue for the dbt documentation.

[sdebruyn]

@johnnytang24 Fix is ready in pr #251, care to review?

[johnnytang24]

Looks good!