Skip to main content

unique_key

snapshots/<filename>.sql
{{ config(  unique_key="column_name") }}
dbt_project.yml
snapshots:  <resource-path>:    +unique_key: column_name_or_expression

Description#

A column name or expression that is unique for the results of a snapshot. dbt uses this to match records between a result set and an existing snapshot, so that changes can be captured correctly.

caution

Providing a non-unique key will result in unexpected snapshot results. dbt will not test the uniqueness of this key, consider adding a test to your project to ensure that this key is indeed unique.

Default#

This is a required parameter. No default is provided.

Examples#

Use an id column as a unique key#

snapshots/<filename>.sql
{{    config(      unique_key="id"    )}}

You can also write this in yaml. This might be a good idea if multiple snapshots share the same unique_key (though we prefer to apply this configuration in a config block, as above).

dbt_project.yml
snapshots:  <resource-path>:    +unique_key: id

Use a combination of two columns as a unique key#

This configuration accepts a valid column expression. As such, you can concatenate two columns together as a unique key if required. It's a good idea to use a separator (e.g. '-') to ensure uniqueness.

snapshots/transaction_items_snapshot.sql
{% snapshot transaction_items_snapshot %}
    {{        config(          unique_key="transaction_id||'-'||line_item_id",          ...        )    }}
select    transaction_id||'-'||line_item_id as id,    *from {{ source('erp', 'transactions') }}
{% endsnapshot %}

Though, it's probably a better idea to construct this column in your query and use that as the unique_key:

snapshots/transaction_items_snapshot.sql

{% snapshot transaction_items_snapshot %}
    {{        config(          unique_key="id",          ...        )    }}
select    transaction_id || '-' || line_item_id as id,    *from {{ source('erp', 'transactions') }}
{% endsnapshot %}