Joins That Silently Multiply Rows: Merge Pitfalls in Practice

Revenue doubled overnight — in the report, not in the bank. The cause: a duplicate key in the products table, so every order line joined to two product rows and counted twice. No error, no warning, just a number exactly wrong by an integer multiple. Joins are the most dangerous one-liners in analysis: a single JOIN can silently multiply, drop, or scramble rows, and the result always looks like a clean table.

Fanout: The Duplicate-Key Multiplier

A join matches every left row with every right row sharing the key. If the right side holds a key twice, each matching left row appears twice. Worse, duplicates on both sides multiply: 3 × 2 becomes 6 rows. The result looks plausible — same columns, more rows — which is why fanout survives to the dashboard instead of failing loudly.

The defense is a uniqueness check before every merge: GROUP BY key HAVING COUNT(*) > 1 on both sides, on the exact key columns of the join. The side you believe is unique must prove it, every run — dimension tables gain duplicates over time (re-imports, slowly-changing dimensions done hastily, near-duplicate entities). Assert row-count expectations too: a many-to-one join must return exactly the left row count. Any deviation is a failed merge until proven otherwise.

Join-Type Traps

• INNER drops silently. Orders with no matching customer vanish — including the NULL-key and orphan rows that often indicate the most interesting data problems. Start exploratory merges with LEFT joins and inspect the unmatched.
• NULL never matches NULL. In SQL, NULL = NULL is not true, so null-keyed rows drop out of inner joins and never match each other. If nulls are legitimate (anonymous orders), handle them explicitly, not hopefully.
• Type and collation mismatches. String "42" vs integer 42, trailing spaces, case differences — join keys must be normalized identically on both sides, or matches quietly go missing. Profile both key columns (see the scorecard dimensions) before trusting the match rate.
• Many-to-many by accident. Joining on a non-unique attribute (name, city, month) when you meant an entity key produces combinatorial explosions. If the result has more rows than both inputs combined, stop and re-examine the key.

A Merge Audit in Four Assertions

Make these permanent checks on every recurring join: 1. key uniqueness holds on the "one" side; 2. row count equals the expectation for the join type (LEFT preserves left count exactly); 3. unmatched rate is within its historical band — a sudden jump means a source changed, not that business changed; 4. aggregated totals reconcile — revenue summed after the join must equal revenue summed before it. Fanout announces itself in assertion 4 even when you forgot assertions 1–3. A merge without these checks is a rumor with a schema.