dbt snapshot failed: duplicate rows detected

TL;DR

A snapshot needs a unique key per row to version history, and the source is handing it duplicates, so dbt refuses rather than versioning garbage. Find the duplicated key values in the source, then either fix the source, pick a truly unique key, or deduplicate in a staging model before the snapshot. Never silence the check; it is protecting your history table.

dbt snapshot failed: duplicate rows detected

Use this when

• dbt snapshot fails with a duplicate rows error.
• The source table has no real primary key.
• Duplicates appeared after an upstream change.

Not for this skill when

• The snapshot runs but history looks wrong. That is a strategy or timestamp problem.
• You want to snapshot an already-deduplicated model. Snapshot the staging layer instead.

Steps

1. Query the source for the snapshot key values that appear more than once. Note the duplicate keys and how many rows share them. Verify: the duplicates are quantified
2. Decide whether the source is wrong (fix upstream) or the key is wrong (pick a better one). Note the decision. Verify: the right layer owns the fix
3. If the key is the problem, choose a unique key or build a surrogate key from columns that together identify a row. Note the new key is unique in the source. Verify: uniqueness is proven
4. If the source cannot be fixed quickly, deduplicate in a staging model and snapshot the staging output. Note the snapshot reads clean input. Verify: the snapshot is insulated
5. Re-run the snapshot and confirm it completes and versions correctly. Note history rows look right. Verify: the snapshot is trusted again

Variant phrasings

dbt snapshot unique key duplicate

The config-shaped search.

dbt snapshot strategy duplicate rows

The strategy-shaped phrasing.

Compatibility: dbt Core 1.x, timestamp and check snapshot strategies. The uniqueness requirement is the same for both.

Why it happens

Snapshots implement slowly-changing dimensions: each source row gets version rows keyed by the unique key plus validity timestamps. If two source rows share a key, dbt cannot tell which one a version belongs to, so it fails loudly instead of writing ambiguous history. The error feels annoying, but a snapshot that silently versioned duplicates would corrupt every point-in-time query built on it. The check exists because the alternative is worse.

Edge cases / pitfalls

• Composite natural keys drift when any part changes. A surrogate key built from stable columns is more robust.
• check strategy snapshots on column lists can mark rows changed when only formatting shifted. Keep the check columns tight.
• Backfilling a snapshot over a period when the source had duplicates fails the same way. Clean the window before backfilling.
• Deleting duplicates in the source without telling downstream breaks snapshot continuity. Coordinate the cleanup.

Provenance

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

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

Thread query: dbt snapshot failed: duplicate rows detected

TL;DR: A snapshot needs a unique key per row to version history, and the source is handing it duplicates, so dbt refuses rather than versioning garbage. Find the duplicated key values in the source, then either fix the source, pick a truly unique key, or deduplicate in a staging model before the snapshot. Never silence the check; it is protecting your history table.