Unique_key using multiple values?

Hello there!

Is there any way for a unique_key to accept multiple columns as values? I would need this for incrementing. For example, suppose I have a table with 10 columns col1, col2, col3, …, col10 and I’d like to set the unique key to col3, col4, col5.
Can I do this:

{{
    config(
        materialized='incremental',
        unique_key='col3', 'col4', 'col5' 
    )
}}

or perhaps this:

{{
    config(
        materialized='incremental',
        unique_key=concat('col3', 'col4', 'col5') as 'column_key'
    )
}}

Thank you in advance, hope this isn’t a dull question!

Hi @draganvstefanov!
People ask this question all the time!
Usually, it’s recommended that people create a surrogate key that is a concatenation of cols 3, 4, and 5. dbt utils makes it really easy to create a surrogate key with the surrogate key macro.
Your model will include something like:

{{ dbt_utils.surrogate_key('col3', 'col4', 'col5') }} as new_key

and then your config might look something like

{{
    config(
        materialized='incremental',
        unique_key='new_key' 
    )
}}

Hope that helps!

1 Like

Thank you for the speedy reply! It looks like it would do the trick!
Just to confirm would the surrogate key macro go before config() in the code?

The key would go in the SELECT statement that makes up your model. It would be a column you include in your model.

Here’s an example of where we use the surrogate key macro in our dbt project at GitLab (though not in an incremental model): https://gitlab.com/gitlab-data/analytics/blob/master/transform/snowflake-dbt/models/zuora/xf/zuora_mrr_totals.sql#L43

Hope that helps

2 Likes

Thank you very much :slight_smile: