uniqueness check passes in warehouse but fails in lakehouse copy of same table error

TL;DR

Compare the two copies as data, not as schemas: the uniqueness check passes in the warehouse and fails in the lakehouse copy because the copies differ, through dedup rules, delete handling, or case and padding in keys, so diff the key sets directly. Two copies of the same table are never the same table. Replication applies its own merge and delete logic, and a check that is green on one copy says nothing about the other.

uniqueness check passes in warehouse but fails in lakehouse copy of same table error

Use this when

• The check or run fails with this symptom: a uniqueness check passes in the warehouse but fails in the lakehouse copy of the same table.
• You are working on a table replicated between a warehouse and a lakehouse with uniqueness checks on both copies 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 lakehouse copy that is intentionally at a finer grain, where the uniqueness key itself differs by design.
• The error text names a different tool, stage, or root cause. Match the verbatim error first and use that skill.

Steps

1. Run the duplicate grouping query on the lakehouse copy and list the duplicated keys. Verify: the duplicated key set is concrete
2. Look up those keys in the warehouse copy. Verify: each key exists once there, confirming a copy-side difference
3. Compare how deletes and updates replicate: merge semantics, delete markers, and snapshot timing. Verify: the replication step that creates the second row is identified
4. Fix replication to apply deletes and merges the same way, or dedupe the lakehouse copy on load with the same rule as the warehouse. Verify: the lakehouse duplicate scan returns zero groups
5. Run the uniqueness check on both copies in the same job. Verify: both copies report the same result

Variant phrasings

uniqueness passes warehouse fails lakehouse copy

The shorter phrasing focused on the named failure.

duplicate keys lakehouse copy same table error

The symptom phrasing a searcher tries next.

replicated table uniqueness mismatch fix

The variant naming the failing stage or tool directly.

Compatibility: Warehouse to lakehouse replication with tools such as Fivetran, Airbyte, Debezium, or native table sharing

Why it happens

Replication pipelines handle updates and deletes differently per target: a delete applied as a marker row in the lakehouse, or an update written as a new version without merging, leaves two rows per key where the warehouse holds one, and uniqueness fails only on the copy.

Edge cases / pitfalls

• Snapshot timing alone can create apparent duplicates during a load window; compare copies at a closed watermark before concluding replication is broken.
• Case sensitivity and padding rules differ between platforms; a key unique under one collation can duplicate under another.

Provenance

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

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

Thread query: uniqueness check passes in warehouse but fails in lakehouse copy of same table error

TL;DR: Compare the two copies as data, not as schemas: the uniqueness check passes in the warehouse and fails in the lakehouse copy because the copies differ, through dedup rules, delete handling, or case and padding in keys, so diff the key sets directly. Two copies of the same table are never the same table. Replication applies its own merge and delete logic, and a check that is green on one copy says nothing about the other.