duplicate key error failed primary key constraint on merge into warehouse table

TL;DR

Duplicate keys on merge mean the source side of the merge has more than one row per key or the target already does: check both sides for duplicates on the merge key before rerunning, deduplicate the source on the latest record, and make the merge idempotent so a retry cannot insert a second copy. Rerunning an unguarded merge compounds the problem.

duplicate key error failed primary key constraint on merge into warehouse table

Use this when

• The check or run fails with this symptom: the merge fails because the key matches multiple rows or already exists in the target.
• You are working on a warehouse merge failing on duplicate keys or a primary key constraint and the failure repeats on the same data or configuration.
• You need the failing rows, rule, or stage isolated before changing thresholds or code.

Not for this skill when

• Duplicates inside a single source file with no merge involved, which needs a load-level dedupe instead.
• The error text names a different tool, stage, or root cause. Match the verbatim error first and use that skill.

Steps

1. Count duplicates on the merge key in the source staging table and in the target table separately. Verify: which side carries the duplicates is established
2. For source duplicates, identify the latest record per key using the load or update timestamp and stage only that row for the merge. Verify: the staged source has exactly one row per key
3. For target duplicates, trace how the second copy was inserted, usually a retried load without a delete or merge guard. Verify: the insertion path that created target duplicates is found
4. Clean the target duplicates keeping the latest record, and add a uniqueness check on the key in staging before the merge step. Verify: the target holds one row per key and the staging check is in place
5. Rerun the merge and then rerun it a second time to prove idempotency. Verify: both runs succeed and row counts stay stable on the retry

Variant phrasings

duplicate key error merge into warehouse table

The verbatim merge phrasing.

primary key constraint violated on merge duplicate

The constraint phrasing from warehouse error logs.

merge failed multiple rows match key snowflake bigquery

The multiple-match phrasing for Snowflake and BigQuery merges.

Compatibility: Snowflake, BigQuery, Postgres, and Redshift MERGE statements and incremental loads

Why it happens

MERGE assumes one source row per key and a unique target key. Retried loads, late-arriving corrections appended as new rows, and sources without their own uniqueness all violate that assumption, and the warehouse surfaces the violation as a constraint or duplicate-match error at merge time.

Edge cases / pitfalls

• Stage every merge through a deduplicated intermediate table. It makes the uniqueness assumption explicit and testable.
• Log merge row counts per run. A silent doubling is visible in counts before it becomes a constraint error.

Provenance

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