Using multiple columns in a Unique_Key for incremental loading in DBT

Question

For incremental models, the DBT documentation here says:

The unique_key should be supplied in your model definition as a string representing a simple column or a list of single quoted column names that can be used together, for example, ['col1', 'col2', …])

I've built an incremental model in DBT with this incremental definition

{{
  config(
    materialized='incremental',
    unique_key = ['Col1', 'Col2', 'Col3']
  )
}}

Which compiles into this merge statement in in Snowflake:

using DW_DEV.dbt_dgarrison_DATA_STAGING.MY_TABLE__dbt_tmp as DBT_INTERNAL_SOURCE
    on 
        DBT_INTERNAL_SOURCE.['Col1', 'Col2', 'Col3'] = DBT_INTERNAL_DEST.['Col1', 'Col2', 'Col3']
...

And this reasonably throws a SQL ERROR complaining about the brackets:

SQL compilation error: syntax error line 4 at position 32 unexpected '['. syntax error line 4 at position 45 unexpected ','. syntax error line 4 at position 98 unexpected '['. syntax error line 4 at position 111 unexpected ','.

I can't find any other good examples using multiple columns this way. (there are options involving concatenating columns, and I'm open to recommendations on the best approach to that, but I'm trying to figure out how to use the DBT recommended syntax)

What version of `dbt` are you running? – Kay May 23 '22 at 04:08 — Kay, May 23 '22 at 04:08
1.0 - I had missed the memo that 1.1 is out – David Garrison May 23 '22 at 16:49 — David Garrison, May 23 '22 at 16:49

Aleix CC · Accepted Answer · 2022-05-23T14:28:47.547

9

As part of dbt-core 1.1.0, we can now pass a list to the unique_key statement in incremental models. See the original issue here.

This means that you should be able to achieve your goal by updating dbt-core and your dbt-<adapter> version locally; or updating your dbt Cloud version accordingly, to 1.1.0, since given the error you get, it looks like unique_key is still looking for a single string instead of an array.

edited May 23 '22 at 14:28

answered May 23 '22 at 06:41

Aleix CC

1,601
1
7
18

updating dbt was enough to get the syntax to work. It still didn't quite work for my case since I need it to handle nulls, and the dbt generated code doesn't do that. – David Garrison May 24 '22 at 18:37
I am on dbt version 1.3.0 and this is not working. I use bigquery with version 1.3.0. I understand the question was for snowflake, but seems to be the same issue + linked PR indicates it should be working for dbt+bigquery. I'll post a reaction under the PR. – Hedge92 Mar 16 '23 at 10:21

Using multiple columns in a Unique_Key for incremental loading in DBT

1 Answers1