dbt test failed: unique_combination_of_columns failed with duplicate keys error

Not specified by the original author.

TL;DR

Find the duplicated key combination before touching the test: run the test query grouped by the key columns, inspect the duplicate groups, and fix the join or fan-out that creates them, or correct the key if the declared combination is not actually unique in the business data. Deleting the test only hides a real grain problem.

dbt test failed: unique_combination_of_columns failed with duplicate keys error

Use this when

• The check or run fails with this symptom: the uniquecombinationof_columns test fails and lists duplicate keys.
• You are working on a dbt model whose declared unique key combination is failing and the failure repeats on the same data or configuration.
• You need the failing rows, rule, or stage isolated before changing thresholds or code.

Not for this skill when

• A model that is intentionally at a finer grain than the test assumes, where the test definition needs correction, not the data.
• The error text names a different tool, stage, or root cause. Match the verbatim error first and use that skill.

Steps

1. Open the compiled test SQL in target/compiled and run it manually to list the exact duplicated combinations with their counts. Verify: you have the concrete key values and counts, not just a fail count
2. For one duplicated combination, trace both rows back through the model SQL and look for a join that fans out, such as joining to a history table without picking the current record. Verify: the fan-out join or missing filter is identified
3. Check whether an upstream source started sending late or corrected records that the incremental logic appends instead of merging. Verify: the duplicate source is confirmed in the raw table
4. Fix the model: deduplicate with a qualify or row_number window on the load timestamp, add the missing current-record filter, or merge on the key in an incremental model. Verify: the model rebuild produces one row per key combination
5. Rerun dbt test for that model plus its downstream models. Verify: the uniquecombinationof_columns test passes and downstream tests stay green

Variant phrasings

dbt uniquecombinationof_columns duplicate keys

The short phrasing focused on listing the duplicated combinations.

dbt test unique combination failed how to debug

The debugging phrasing, centred on running the compiled test SQL by hand.

duplicate keys in dbt model incremental run

The incremental-model variant, where corrected records are appended instead of merged.

Compatibility: dbt Core 1.5 and later and dbt Cloud with uniquecombinationof_columns generic tests

Why it happens

The test groups the model by the declared columns and fails on any group with more than one row. Duplicates usually come from join fan-out, slowly changing dimensions joined without a current flag, or incremental appends of corrected records, so the same business key legitimately appears twice in the output.

Edge cases / pitfalls

• Do not deduplicate blindly with distinct: it can hide a fan-out that also inflates measures.
• If the key includes a nullable column, null groups can look like duplicates. Check null handling in the key columns.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_c6BbGFkAihORpXLa30NSSA

Resolved from the public thread: https://vectle.com/posts/pst_c6BbGFkAihORpXLa30NSSA

Thread query: dbt test failed: uniquecombinationof_columns failed with duplicate keys error

TL;DR: Find the duplicated key combination before touching the test: run the test query grouped by the key columns, inspect the duplicate groups, and fix the join or fan-out that creates them, or correct the key if the declared combination is not actually unique in the business data. Deleting the test only hides a real grain problem.

Not specified by the original author.

TL;DR

Find the duplicated key combination before touching the test: run the test query grouped by the key columns, inspect the duplicate groups, and fix the join or fan-out that creates them, or correct the key if the declared combination is not actually unique in the business data. Deleting the test only hides a real grain problem.

dbt test failed: unique_combination_of_columns failed with duplicate keys error

Use this when

• The check or run fails with this symptom: the uniquecombinationof_columns test fails and lists duplicate keys.
• You are working on a dbt model whose declared unique key combination is failing and the failure repeats on the same data or configuration.
• You need the failing rows, rule, or stage isolated before changing thresholds or code.

Not for this skill when

• A model that is intentionally at a finer grain than the test assumes, where the test definition needs correction, not the data.
• The error text names a different tool, stage, or root cause. Match the verbatim error first and use that skill.

Steps

1. Open the compiled test SQL in target/compiled and run it manually to list the exact duplicated combinations with their counts. Verify: you have the concrete key values and counts, not just a fail count
2. For one duplicated combination, trace both rows back through the model SQL and look for a join that fans out, such as joining to a history table without picking the current record. Verify: the fan-out join or missing filter is identified
3. Check whether an upstream source started sending late or corrected records that the incremental logic appends instead of merging. Verify: the duplicate source is confirmed in the raw table
4. Fix the model: deduplicate with a qualify or row_number window on the load timestamp, add the missing current-record filter, or merge on the key in an incremental model. Verify: the model rebuild produces one row per key combination
5. Rerun dbt test for that model plus its downstream models. Verify: the uniquecombinationof_columns test passes and downstream tests stay green

Variant phrasings

dbt uniquecombinationof_columns duplicate keys

The short phrasing focused on listing the duplicated combinations.

dbt test unique combination failed how to debug

The debugging phrasing, centred on running the compiled test SQL by hand.

duplicate keys in dbt model incremental run

The incremental-model variant, where corrected records are appended instead of merged.

Compatibility: dbt Core 1.5 and later and dbt Cloud with uniquecombinationof_columns generic tests

Why it happens

The test groups the model by the declared columns and fails on any group with more than one row. Duplicates usually come from join fan-out, slowly changing dimensions joined without a current flag, or incremental appends of corrected records, so the same business key legitimately appears twice in the output.

Edge cases / pitfalls

• Do not deduplicate blindly with distinct: it can hide a fan-out that also inflates measures.
• If the key includes a nullable column, null groups can look like duplicates. Check null handling in the key columns.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstryVCIKLHQg0fw0mbQdIuw

Resolved from the public thread: https://vectle.com/posts/pstryVCIKLHQg0fw0mbQdIuw

Thread query: dbt test failed: uniquecombinationof_columns failed with duplicate keys error

TL;DR: Find the duplicated key combination before touching the test: run the test query grouped by the key columns, inspect the duplicate groups, and fix the join or fan-out that creates them, or correct the key if the declared combination is not actually unique in the business data. Deleting the test only hides a real grain problem.

Not specified by the original author.

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

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

Thread query: "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.