In a dbt incremental model, what does the unique_key config do?

## The problem unique_key solves An incremental dbt model produces a set of candidate rows on each run and writes them into an existing table. The question dbt has to answer is: **what if a candidate row is already in the target?** Left alone, the answer is "insert it anyway," which produces duplicates any time a row is reprocessed — a rerun after a failure, a widened lookback window, a source that restates yesterday's records. `unique_key` is how you tell dbt what "already in the target" means. It is a config, not a constraint: ```sql {{ config( materialized='incremental', unique_key='order_id' ) }} ``` It also accepts a list, which is what you need when identity is composite: ```sql {{ config( materialized='incremental', unique_key=['order_id', 'line_number'] ) }} ``` ## What dbt generates with it The exact SQL depends on the incremental strategy and the adapter: - **merge** (the default on warehouses that support it, such as Snowflake and BigQuery): dbt emits a `MERGE INTO target USING candidates ON target.order_id = candidates.order_id`, with `WHEN MATCHED THEN UPDATE SET ...` and `WHEN NOT MATCHED THEN INSERT ...`. Existing rows are overwritten in place with the new version; unseen keys are appended. - **delete+insert**: dbt deletes from the target every row whose key appears in the candidate set, then inserts all candidates. The net effect resembles merge, executed as two statements. - **append**: `unique_key` is not used at all — every candidate row is inserted. - **insert_overwrite**: identity is by partition, not by key; whole partitions present in the candidate set are replaced. So `unique_key` is only meaningful for the strategies that need to match rows. Setting it while using `append` does not protect you. ## The uniqueness obligation is yours The name misleads people: it does not create a primary key, it does not add a constraint, and dbt does not verify it. It is a promise you make about the rows your model emits. If two candidate rows in the same run share the same key, the outcome depends on the warehouse: - Some engines reject a `MERGE` where one target row matches more than one source row, and the run fails with a nondeterministic-merge error. That is the *good* case — it is loud. - Others accept it and keep an arbitrary winner, so the run is green and the data is quietly wrong. The fix is to guarantee one row per key inside the model, typically by deduplicating with a window function before the write: ```sql select * from ( select *, row_number() over ( partition by order_id order by updated_at desc ) as rn from {{ ref('stg_orders') }} ) where rn = 1 ``` ## What it does not fix Three limits are worth stating explicitly. First, **it does not clean the target.** If duplicates were loaded before you added `unique_key`, they stay. Merge updates rows matching the key — if two rows in the target share it, you now have two updated duplicates. Removing existing duplicates requires a `--full-refresh` or a manual cleanup. Second, **it does not delete.** A merge configured this way inserts and updates; a row that disappeared from the source is not removed from the target. Hard deletes in the source need explicit handling — a delete flag carried through the model, a periodic full refresh, or a strategy that overwrites whole partitions. Third, **it costs money.** A merge on a wide unique key joins candidates against the whole target table, which on a very large fact table can be the expensive part of the run. Narrowing the join with partition or date predicates — dbt exposes `incremental_predicates` for exactly this — often turns a slow merge back into a fast one, and matters most on warehouses that prune by partition or cluster key. ## Choosing the key Prefer a genuine natural or surrogate identifier from the source: an order id, an event id, a hash of the business key columns. Avoid keys that are only unique within a batch, and avoid using a load timestamp as part of the key — that makes every reload a new row, which defeats the entire point. If the model has no stable identifier at all, that is a signal: either the grain is wrong, or the model belongs on an append-only strategy where duplicates are handled downstream, or it should stay a plain `table` and be rebuilt. ## Testing it Because dbt does not enforce the key, back it with a generic `unique` test (and `not_null`) in the model's YAML. That is the mechanism that turns a silent duplication into a failing run — the incremental config states your intent, and the test is what verifies it held.