unique_key

snapshots/<filename>.yml

snapshots:
  - name: orders_snapshot
    relation: source('my_source', 'my_table')
    config:
      unique_key: id

dbt_project.yml

snapshots:
  <resource-path>:
    +unique_key: column_name_or_expression

Description

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

In Versionless and dbt v1.9 and later, snapshots are defined and configured in YAML files within your snapshots/ directory. The unique_key is specified within the config block of your snapshot YAML file.

caution

Providing a non-unique key will result in unexpected snapshot results. dbt will not test the uniqueness of this key, consider testing the source data 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/orders_snapshot.yml

snapshots:
  - name: orders_snapshot
    relation: source('jaffle_shop', 'orders')
    config:
      schema: snapshots
      unique_key: id
      strategy: timestamp
      updated_at: updated_at

You can also specify configurations in your dbt_project.yml file if multiple snapshots share the same unique_key:

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.yml

snapshots:
  - name: transaction_items_snapshot
    relation: source('erp', 'transactions')
    config:
      schema: snapshots
      unique_key: "transaction_id || '-' || line_item_id"
      strategy: timestamp
      updated_at: updated_at

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.yml

snapshots:
  - name: transaction_items_snapshot
    relation: {{ ref('transaction_items_ephemeral') }}
    config:
      schema: snapshots
      unique_key: id
      strategy: timestamp
      updated_at: updated_at

models/transaction_items_ephemeral.sql

{{ config(materialized='ephemeral') }}

select
  transaction_id || '-' || line_item_id as id,
  *
from {{ source('erp', 'transactions') }}

In this example, we create an ephemeral model transaction_items_ephemeral that creates an id column that can be used as the unique_key our snapshot configuration.