Skip to main content

unique_key

unique_key identifies records for incremental models or snapshots, ensuring changes are captured or updated correctly.

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

Description​

A column name or expression that uniquely identifies each record in the inputs of a snapshot or incremental model. dbt uses this key to match incoming records to existing records in the target table (either a snapshot or an incremental model) so that changes can be captured or updated correctly:

  • In an incremental model, dbt replaces the old row (like a merge key or upsert).
  • In a snapshot, dbt keeps history, storing multiple rows for that same unique_key as it evolves over time.

In dbt "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 parameter is optional. If you don't provide a unique_key, your adapter will default to using incremental_strategy: append.

If you leave out the unique_key parameter and use strategies like merge, insert_overwrite, delete+insert, or microbatch, the adapter will fall back to using incremental_strategy: append.

This is different for BigQuery:

  • For incremental_strategy = merge, you must provide a unique_key; leaving it out leads to ambiguous or failing behavior.
  • For insert_overwrite or microbatch, unique_key is not required because they work by partition replacement rather than row-level upserts.

Examples​

Use an id column as a unique key​

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

Was this page helpful?

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

0
Edit this page