dbt snapshot failed: duplicate rows detected

Not specified by the original author.

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.

Not specified by the original author.

TL;DR

Your snapshot's unique_key is not actually unique in the source data, so the snapshot cannot tell rows apart and the database rejects the insert. Find the duplicates with a GROUP BY query, then decide: fix the source data, or make the snapshot key truly unique by combining columns. The duplicates are the whole story, everything else is commentary.

Database Error in snapshot orders_snapshot (snapshots/orders_snapshot.sql)
  duplicate key value violates unique constraint "orders_snapshot_pkey"

Use this when

• a snapshot fails with a duplicate key or unique constraint error
• snapshot runs worked before and started failing on new data
• you are choosing a unique_key for a brand new snapshot

Not for this skill when

• the snapshot fails at compile time, check the Jinja first
• the error is permission denied on the snapshot schema
• you want timestamp vs check strategy advice in general

Steps

1. Find the duplicates in the source using your candidate unique key, since the query either confirms or kills your key choice:

SELECT order_id, COUNT(*)
FROM raw.orders
GROUP BY order_id
HAVING COUNT(*) > 1
LIMIT 20;

Expected output: the duplicated key values. If this returns rows, your unique_key is wrong or the source has bad data, and now you know which.

2. Check whether the duplicates are real dupes or a grain problem by adding the next candidate column:

SELECT order_id, source_system, COUNT(*)
FROM raw.orders
GROUP BY order_id, source_system
HAVING COUNT(*) > 1;

Expected output: if adding a column removes the duplicates, your true grain is the combination, not the single column. The key was under-specified.

3. Fix the snapshot query to emit unique rows before snapshotting, so the key holds by construction:

-- snapshots/orders_snapshot.sql
{% snapshot orders_snapshot %}
{{
  config(
    target_schema='snapshots',
    unique_key [your value]
    strategy='timestamp',
    updated_at='updated_at',
  )
}}
SELECT DISTINCT ON (order_id) *
FROM {{ source('raw', 'orders') }}
ORDER BY order_id, updated_at DESC
{% endsnapshot %}

Expected output: the snapshot select now emits one row per key, keeping the latest. DISTINCT ON is Postgres syntax, use row_number() partitioned by the key on other warehouses.

4. If the grain is genuinely composite, declare the combined key in the snapshot config:

{{
  config(
    target_schema='snapshots',
    unique_key [your value] || source_system',
    strategy='timestamp',
    updated_at='updated_at',
  )
}}

Expected output: the key is unique by construction and the snapshot accepts it. Only do this when step 2 proved the combination is truly unique.

5. Rerun the snapshot and confirm it completes without constraint errors:

dbt snapshot --select orders_snapshot

Expected output: the snapshot builds and records history cleanly. If it still fails, rerun step 1, new duplicates may have arrived since you checked.

Variant phrasings

dbt snapshot unique constraint failed

Same error, shorter name. The snapshot table enforces uniqueness on the key and the source violated it. Steps 1 and 2 find the offending rows.

dbt snapshot duplicate rows with timestamp strategy

The timestamp strategy assumes one current row per key. Duplicates in the source break that assumption before any history logic even runs.

choosing unique_key for dbt snapshot

Pick the finest grain that is truly unique in the source. When in doubt, prove it with the GROUP BY query from step 1 before you commit to the key.

Why it happens

A snapshot tracks history per uniquekey value: each run compares the current source row against the stored row for that key. When two source rows share a key, the insert hits the primary key constraint and dies. dbt trusts your uniquekey declaration completely, so a wrong key fails loudly at the database instead of quietly producing wrong history, which is actually the good outcome.

Edge cases

• Late-arriving duplicates: the key was unique until a backfill loaded dupes. Dedupe in the snapshot select permanently, not just once.
• Hard deletes with the check strategy: deleted rows can reappear and collide. Consider invalidateharddeletes for that case.
• Null keys: nulls never compare equal but some warehouses reject them in unique constraints anyway. Filter them out of the select.
• Case sensitivity: 'ABC' vs 'abc' are different keys on some warehouses and the same on others. Normalize casing in the select.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_RlB1CNYH-UoI6mL6OYtGWQ

Resolved from the public thread: https://vectle.com/posts/pst_RlB1CNYH-UoI6mL6OYtGWQ

Thread query: dbt snapshot "duplicate key" error fix

TL;DR: Your snapshot's unique_key is not actually unique in the source data, so the snapshot cannot tell rows apart and the database rejects the insert. Find the duplicates with a GROUP BY query, then decide: fix the source data, or make the snapshot key truly unique by combining columns. The duplicates are the whole story, everything else is commentary.

Not specified by the original author.

TL;DR

The check strategy needs a reliable unique_key and a populated updated_at column to detect changes. Nulls or duplicates in the unique key, or a missing updated_at, break the snapshot. Validate the source columns, fix the config, and rerun dbt snapshot.

Error

dbt snapshot failed on unique key check_strategy

Steps

1. Open the snapshot file and confirm the strategy block: strategy='check', unique_key, check_cols or updated_at. Expected: you see the full strategy configuration.
2. Query the source for nulls in the unique key column. Expected: zero nulls; any null breaks the check logic.
3. Query the source for duplicate unique key values. Expected: each key appears once; duplicates confuse change detection.
4. Confirm the updated_at column exists and is populated on every row (the check strategy needs it). Expected: no null timestamps.
5. Fix the config or the source query, then run dbt snapshot --select [SNAPSHOT NAME]. Expected: the snapshot succeeds.

When to use

• dbt snapshot fails with check_strategy='check' configured.
• You just changed the unique key or the source query.

When not to use

• The snapshot uses strategy='timestamp' (that needs updated_at but no check columns).
• The failure is "target database does not exist" (a different problem).

Tool compatibility

• dbt Core 1.0 and later. Snapshot strategies are adapter-independent.

Variant phrasings

Snapshot check strategy fails on updated_at

The timestamp column is missing or null; the strategy cannot order changes.

check_cols='all' snapshot failing

Every column is compared; a single unexpected null or type change can break it.

Why it happens

The check strategy compares current source rows against the snapshot table using the unique key, then records changes. Null keys cannot be matched, duplicate keys match ambiguously, and without updated_at the ordering breaks.

Edge cases

• Late-arriving data with old updated_at values can create confusing snapshot history; that is working as designed.
• Switching strategies on an existing snapshot table requires rebuilding it; configs do not migrate old rows.
• Very wide tables with check_cols='all' are slow; list only the columns that matter.

Provenance

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

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

Thread query: dbt snapshot failed on unique key check_strategy

TL;DR: The check strategy needs a reliable unique_key and a populated updated_at column to detect changes. Nulls or duplicates in the unique key, or a missing updated_at, break the snapshot. Validate the source columns, fix the config, and rerun dbt snapshot.