Unique test failed: duplicate values in primary key" dbt

TL;DR

The unique test found the same primary key value on more than one row. Store the failures, list the duplicated keys, decide whether the fix belongs in the model (dedupe logic) or upstream (source data), apply it, and rerun the test.

Error

"Unique test failed: duplicate values in primary key" dbt

Steps

1. Re-run with failures stored: dbt test --select [UNIQUE TEST NAME] --store-failures. Expected: a failures table with the duplicated rows.
2. Query the failures table grouped by the key column to see which values repeat and how many times. Expected: a short list of duplicated keys.
3. Decide where the fix belongs: if the source is wrong, fix upstream; if the model fans out rows (a join multiplying records), fix the model. Expected: a clear owner for the fix.
4. For model-side fanout, deduplicate with a window function keeping the right row per key (for example row_number() partitioned by the key, keeping row 1). Expected: the model emits one row per key.
5. Re-run dbt test --select [UNIQUE TEST NAME]. Expected: the test passes.

When to use

• A unique test fails on a column that should be a primary key.
• Duplicates appeared after adding or changing a join.

When not to use

• Duplicates are legitimate in the column (then unique is the wrong test; remove it or scope it).
• The failure is a not_null test rather than unique.

Tool compatibility

• dbt Core 1.0 and later, all adapters. Window-function dedupe syntax is standard SQL.

Variant phrasings

Unique test failed on a non-key column

Same technique, but first confirm the column should actually be unique.

Duplicate values only in incremental runs

The incremental logic inserts without checking existing keys; add a merge or dedupe step.

Why it happens

unique compiles to a group-by-having query over the column. Joins that multiply rows, unioned sources with overlap, or genuinely duplicated source data all produce repeated key values.

Edge cases

• Null keys: most warehouses treat nulls as distinct, but some do not; check your warehouse's semantics.
• Case differences (ABC vs abc) count as distinct on case-sensitive warehouses but may collide elsewhere.
• Incremental models need the dedupe inside the incremental branch too, not just the initial build.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_ujPz0aWZruwB-67BdY-wRA