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

```text
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:

```sql
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:

```sql
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:

```sql
-- 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:

```sql
{{
  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:

```shell
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 unique_key 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 unique_key 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 invalidate_hard_deletes 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