unique_key
{{ config(
unique_key="column_name"
) }}
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.
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
{{
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).
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.
{% 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
:
{% snapshot transaction_items_snapshot %}
{{
config(
unique_key="id",
...
)
}}
select
transaction_id || '-' || line_item_id as id,
*
from {{ source('erp', 'transactions') }}
{% endsnapshot %}