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.

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.

1. 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.

1. 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.

1. 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.

1. 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