dbt incremental model in SQL Server

Question

From this model, and according to my understanding of the documentation of dbt:

EDIT: (removed the DISTINCT in the statement as it was unnecessary)

Test.sql

{{ config(
        as_columnstore = false,
        schema='staging',
        materialized='incremental', 
        unique_key='id',
        incremental_strategy='merge',
        merge_update_columns = ['name', 'updated_at'])
    }}

SELECT  I.id, 
        I.name, 
        MAX(I.extraction_date) created_at,
        MAX(I.extraction_date) updated_at
  FROM staging.test_data_raw I 
 WHERE I.id IS NOT NULL
 GROUP BY I.id, I.name

I expected that, if a record with a matching Id already existed, in the table then the name and the updated_at would change but the created_at would remain as it was

But, after running it several times, the created_at always changes. So my guess is that dbt is not performing a merge operation but a delete/insert.

I am running dbt with the SQL Server connector.

Is it possible that this connector does not implement the merge strategy? Or am I doing something wrong here? And if so, is there any way to solve this?

Answer 1

I am sitting here and wondering about the same thing (but with Azure DW). No positive response in 15 days, so I guess the answer must be that the incremental strategy "merge" has not yet been implemented in the dbt-sqlserver adapter. Go to https://github.com/dbt-msft/dbt-sqlserver/discussions/categories/ideas and suggest this feature!