unique_key

Models

Configure the unique_key in the config block of your incremental model's SQL file, in your models/properties.yml file, or in your dbt_project.yml file.

models/my_incremental_model.sql

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

models/properties.yml

models:
  - name: my_incremental_model
    description: "An incremental model example with a unique key."
    config:
      materialized: incremental
      unique_key: id

dbt_project.yml

name: jaffle_shop

models:
  jaffle_shop:
    staging:
      +unique_key: id

Snapshots

For snapshots, configure the unique_key in the your snapshot/filename.yml file or in your dbt_project.yml file.

snapshots/<filename>.yml

snapshots:
  - name: orders_snapshot
    relation: source('my_source', 'my_table')
    config:
      unique_key: order_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 or incremental model. dbt uses this to match records between a result set and an existing snapshot or incremental model, so that changes can be captured correctly.

In dbt Cloud "Latest" release track and from dbt v1.9, snapshots are defined and configured in YAML files within your snapshots/ directory. You can specify one or multiple unique_key values within your snapshot YAML file's config key.

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

Models

In this example, the id column is the unique key for an incremental model.

models/my_incremental_model.sql

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

select * from ..

Snapshots

In this example, the id column is used as a unique key for a snapshot.

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 multiple unique keys

Models

Configure multiple unique keys for an incremental model as a string representing a single column or a list of single-quoted column names that can be used together, for example, ['col1', 'col2', …].

Columns must not contain null values, otherwise the incremental model will fail to match rows and generate duplicate rows. Refer to Defining a unique key for more information.

models/my_incremental_model.sql

{{ config(
    materialized='incremental',
    unique_key=['order_id', 'location_id']
) }}

with...

Snapshots

You can configure snapshots to use multiple unique keys for primary_key columns.

snapshots/transaction_items_snapshot.yml

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