dbt incremental model full refresh failed: unique key collision

TL;DR

A full refresh rebuilds the table from the model SQL with no existing rows to merge into, so a unique key collision during full refresh means the model output itself has duplicate values of the unique_key. The source gained duplicates, a join fanned out, or the key was never unique at the grain you assumed. Profile the model output grouped by the key, fix the grain or the join, and only then rerun the full refresh.

dbt incremental model full refresh failed: unique key collision

Use this when

• dbt build with the full refresh flag fails on a duplicate or unique constraint for an incremental model.
• A normal incremental run appeared fine but the rebuild exposes collisions.
• A recent join or union was added to the model.

Not for this skill when

• The collision happens only on incremental merges against existing rows. That points at merge logic or late arriving updates.
• The unique key is fine and the error is a warehouse primary key on a different table. Read which relation the error names.

Steps

1. Run the compiled model SQL with a grouping check on the unique_key and list keys with more than one row. Note example duplicate keys. Verify: duplication in the model output is proven with examples
2. Trace one duplicate key back through the model: check each join for a one to many leg that fans rows out, and each union for overlapping sources. Note the step where the row count doubles. Verify: the fanout or overlap is located
3. Decide the true grain: either the key needs more columns to be unique, or the model must aggregate or dedupe to one row per key with an explicit rule such as latest updated timestamp wins. Note the rule in the model. Verify: the grain decision is explicit, not accidental
4. Apply the fix, add a dbt unique test on the key plus a not_null test, and run the model on a dev target. Note the tests pass on the rebuilt table. Verify: uniqueness is enforced by a test, not by hope
5. Run the full refresh in the target environment during a window where downstream models can rebuild after it, then run the downstream tests. Note the full build is green. Verify: the refreshed table and its consumers agree

Variant phrasings

dbt full refresh duplicate key incremental model

The short form of the same failure.

dbt unique_key not unique in model output

The grain problem phrasing.

Compatibility: dbt Core and dbt Cloud incremental models on warehouses that enforce uniqueness on merge or on table creation. Exact error text varies by adapter; the duplicate in the output is the common cause.

Why it happens

Incremental merges can hide a grain problem for months: when duplicate keys arrive in one batch, many adapters take one row or fail intermittently, and when they match existing rows the merge overwrites instead of erroring. A full refresh removes the hiding place. With no target table to merge into, duplicates in the result have nowhere to go, and the collision becomes deterministic. That is why a model with no recent code change can suddenly fail a refresh the day the source starts sending a second row per key.

Edge cases / pitfalls

• Do not solve this by dropping the unique test or picking an arbitrary row with a limit. The business meaning of the duplicate matters.
• A dedupe rule based on load time makes reruns non deterministic. Prefer a source timestamp or version column.
• Full refresh on a large model is expensive and downstream snapshots may capture the intermediate state. Schedule it and pause consumers if needed.
• If the source key duplicates are themselves a bug upstream, file it there too. The model dedupe is a guardrail, not absolution.

Provenance

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