snowflake error failed: duplicate values on primary key merge failure

TL;DR

Snowflake does not enforce primary keys, so duplicate values surface through merges and checks rather than constraint errors: find the duplicate keys, determine whether the source or a retried load created them, and deduplicate with a qualify on the load timestamp, then add a uniqueness check to the load so the next duplicate fails fast.

snowflake error failed: duplicate values on primary key merge failure

Use this when

• The check or run fails with this symptom: duplicate values appear for the key in a Snowflake merge or table.
• You are working on a Snowflake table or merge showing duplicate primary key values 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

• A database that enforces primary keys rejecting the insert, where the rejection itself is the signal instead.
• The error text names a different tool, stage, or root cause. Match the verbatim error first and use that skill.

Steps

1. Group the table by the key and list the duplicate groups with their load timestamps. Verify: duplicate groups and timing are listed
2. Check whether duplicates arrived in one load or accumulated across retries by comparing load history and file names. Verify: the arrival pattern is established
3. Trace one duplicate pair to the source records to see if the source itself sends two versions. Verify: the source behaviour for the key is known
4. Deduplicate keeping the latest record per key and fix the load to merge on the key instead of appending. Verify: the table holds one row per key and the load merges
5. Add a post-load uniqueness check and rerun the load to prove it stays clean. Verify: the check passes after a fresh load

Variant phrasings

snowflake duplicate values primary key merge

The verbatim Snowflake duplicate phrasing.

snowflake merge multiple rows match duplicate key

The merge-match phrasing for duplicated targets.

deduplicate snowflake table qualify latest record

The qualify phrasing for keeping the latest row per key.

Compatibility: Snowflake MERGE and insert loads with primary key expectations

Why it happens

Because Snowflake treats primary keys as informational, nothing stops a second copy at write time; the duplication is discovered later by merges that match multiple rows or by quality checks. Retried loads and sources sending corrections as inserts are the usual creators.

Edge cases / pitfalls

• Use merge-based loads as the default in Snowflake. Append-only loads need an external idempotency story.
• Uniqueness checks belong in the load pipeline for unenforced keys, not in a weekly audit.

Provenance

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