Unique_key using multiple values?

Archive
1

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!

2

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!

4 Likes
3

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?

4

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

4 Likes
5

Thank you very much :slight_smile:

6

Is there a way to use this macro without adding the surrogate key to the result set in the select statement? For example, we’re creating a table with a surrogate key of concat(date, id), and it’s obvious that there should not be duplicate date, id combos in the table without adding the surrogate key to the table.

2 Likes
7

Just updating @emilie’s link since gitlab directory changed:

8

Hi @emilie I am trying to test the DBT utils and its failing . its a simple SQL. Can you help?

{{ config (
materialized=“incremental”,
unique_key=“uk_dept”

)}}

with VW_DEPT as (

select
 {{ dbt_utils.surrogate_key (
  'deptno',
  'dname'
 ) }} as uk_dept,
    deptno,  
    dname,  
    loc
from "DEMO_DB"."PUBLIC"."DEPT"

)
Select * from VW_DEPT

Please find the error :
Server error: Compilation Error in rpc request (from remote system)
‘dbt_utils’ is undefined

9

Quick note:
If you have an issue it’s best to use slack to communicate them. You’ll get and answer much quicker. Discourse is more about solutions.

For your issue have a look at the documentation here. Hint: You’ll need to update your packages file.

10

Updating again: