dbt incremental models: how unique_key works

Add it to the model's config, alongside a filter that limits what each run reads.

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

select order_id, status, updated_at
from {{ ref('stg_shop__orders') }}
{% if is_incremental() %}
where updated_at >= (select coalesce(max(updated_at), '1900-01-01') from {{ this }})
{% endif %}

If a key from the new rows already exists, dbt updates or replaces that row. If it doesn't, dbt inserts it. How it does that depends on the warehouse and the strategy.

Pass a list when one column can't identify a row. dbt recommends this over concatenating columns yourself.

{{ config(materialized='incremental', unique_key=['user_id', 'session_number']) }}

Key columns shouldn't contain nulls. A null may fail to match its row and create a duplicate. Use coalesce() to replace nulls, or build one key with dbt_utils.generate_surrogate_key.

The key must also be unique in both the existing table and the new rows, or the model can fail, depending on your warehouse and strategy. Add a unique test to catch this early.

merge and delete+insert use it. merge with no key behaves like append. append never checks for duplicates, and insert_overwrite replaces whole partitions instead of matching rows.

The strategies on offer depend on your adapter. Set one with incremental_strategy in the model's config.

A key stops new duplicates, but it doesn't remove rows already loaded. Run one full refresh to rebuild the table.

dbt run --full-refresh --select fct_orders+

The trailing + also rebuilds downstream models. See dbt's incremental models docs for more.

This rule flags incremental models with no unique_key, or an empty one. Models using insert_overwrite, microbatch or append are not judged, because those strategies never take a key. Every other strategy, including the default, is.

A re-run that re-reads loaded rows appends them again, and totals drift upward until a full refresh. A >= watermark on {{ this }} re-reads the last period on every run. A strict > watermark is safe day to day, but a backfill still duplicates and late rows are skipped. We rate that shape medium rather than high.

Stop partial reloads from duplicating rows. Set unique_key in the model config, then full-refresh once to clear the duplicates already loaded. If the model is partitioned by date, set incremental_strategy: insert_overwrite or microbatch instead; they replace whole partitions and need no key.

Before:

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

select order_id, status, updated_at
from {{ ref('stg_shop__orders') }}
{% if is_incremental() %}
where updated_at >= (select max(updated_at) from {{ this }})
{% endif %}

After:

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

select order_id, status, updated_at
from {{ ref('stg_shop__orders') }}
{% if is_incremental() %}
where updated_at >= (select max(updated_at) from {{ this }})
{% endif %}

A true event log, where every row is new and a repeated row is a real repeat, has no key to merge on. Declare incremental_strategy: append so the choice is on record. This rule then stops flagging it.

manifest.json, which dbt parse writes. We tell the watermark shapes apart by reading each model's raw_code. A manifest without it puts every flagged model in the high-severity tier.