dbt test failed: unique_combination_of_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.

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