To reconcile two datasets you compute the difference in both directions — rows in A not in B, and rows in B not in A. What properties of set-difference semantics can make that result misleading, and how do you make the comparison trustworthy?

## Why bidirectional difference is the natural first instinct Set difference is **not commutative**: `A − B` is the rows of A absent from B, and `B − A` is the reverse. Neither alone answers "are these two datasets the same?", so computing both — sometimes combined as the symmetric difference `(A − B) ∪ (B − A)` — is the obvious reconciliation. If both directions come back empty, the two relations are equal *as sets*. That last qualifier is where all the trouble lives. ## Pitfall 1: duplicate collapse hides multiplicity differences The non-ALL forms of the set operators deduplicate. So if A contains a row three times and B contains it once, `A EXCEPT B` yields the empty relation. Both directions come back clean and you report "reconciled" while one side has two extra rows — precisely the kind of error a reconciliation exists to catch. Fixes: - Use `EXCEPT ALL` in both directions where the engine supports it: it returns `max(m − n, 0)` copies and so surfaces the multiplicity gap directly. - Or reconcile a **count** alongside the set diff — a simple `COUNT(*)` per side, and a per-key count comparison. - Or, better, establish that the key is unique on both sides first, which makes multiplicity a non-issue and is usually the real requirement anyway. ## Pitfall 2: whole-row, positional matching turns cosmetic differences into total mismatches Set operators compare **entire rows**, matched by column position. Any of the following makes every row differ: - **Column order** differing between the two branches (with compatible types, no error is raised). - **An extra or missing column** on one side — this one at least errors on degree mismatch. - **Type or scale differences**: `10.0` vs `10.00` under some type rules, or an implicit coercion applied to one side. - **Whitespace and case**: trailing spaces, differing case, or a differing collation. - **Timestamp precision or time zone**: microseconds truncated on one side, or one side stored in local time. The signature symptom is a diff whose output size equals the input size in both directions. When you see that, stop looking for data problems and look for a shape or normalisation problem. Mitigation: project an explicit, identically-ordered column list on both branches (never `SELECT *`), cast both sides to a canonical type, and normalise text (trim, fold case, force a collation) before comparing. ## Pitfall 3: nulls match under distinctness, and that surprises people both ways Set operators compare rows using "is not distinct from", so two rows null in the same position count as the same row. That is usually what you want for reconciliation — but it differs from a join predicate written with `=`, where null comparisons yield unknown and the rows fail to match. If you migrate a reconciliation from `EXCEPT` to a join, you must add explicit null-safe comparisons or the results will change. ## Pitfall 4: the diff says *that*, not *why* Even a perfectly correct symmetric difference returns rows. It cannot tell you that 12,000 rows appear on both sides because one column drifted, versus 12,000 genuinely missing records. On a wide table this is the difference between a five-minute fix and a day of manual comparison. ## The robust pattern: key-based comparison Replace the whole-row diff with a **full outer join on the business key**, then classify: - key present only on the left → missing from the right, - key present only on the right → extra on the right, - key present on both, some non-key column differs → a value drift, and you can report exactly which columns. This gives an actionable report, is immune to column-order accidents (you name the columns you compare), and separates "row missing" from "row changed". Practical additions: - Verify key uniqueness on both sides first; a duplicated key silently fans out the join and inflates every count. - Compare with null-safe equality per column so that null-vs-null counts as equal and null-vs-value counts as different. - For very wide tables, compare a normalised **checksum/hash of the row** per key as a fast first pass, then drill into columns only for the keys that differ. Normalise inputs before hashing — the hash inherits every whitespace and precision sensitivity described above. - Reconcile **counts and aggregate totals** per partition as a cheap independent signal; matching row counts with a mismatching sum immediately localises the problem to a value drift. ## Cost note All deduplicating set operators sort or hash both inputs, so on large datasets the reconciliation is memory-hungry and may spill. Chunking by key range or partition makes it bounded and restartable, and gives you a per-partition report rather than one giant result you cannot triage. ## The answer to give Lead with the three semantic properties — asymmetry (so you need both directions), duplicate collapse (so counts can differ while the diff is empty), and whole-row positional matching (so cosmetic drift reads as total mismatch) — mention null distinctness, then pivot to the key-based full-outer-join pattern with per-column reporting as the design you would actually ship.