After adding a join, your row count and revenue totals exploded. What happened and how do you detect it?

TL;DR: A one-to-many or many-to-many join multiplies left-side rows, so any SUM on the left measure double-counts while the query runs without error. Detect it with a GROUP BY key HAVING COUNT(*) > 1 uniqueness check on the assumed-unique side, then fix by pre-aggregating the many side to the join grain.

How to approach it

Name the phenomenon, join fan-out (a many-to-many or one-to-many join multiplying rows), then go straight to diagnosis discipline: check the grain of each table and verify the join key's uniqueness instead of guessing. Interviewers want the methodical debugging voice, not just the definition.

A strong answer

A join's output grain is determined by key cardinality. If you join orders (one row per order) to shipments expecting one shipment per order, but some orders shipped in three boxes, each such order row triples, and any SUM(o.amount) now counts that order's revenue three times. Worse, totals are silently wrong: the query runs fine.

Detection in 30 seconds, test the assumed-unique side:

SELECT order_id, COUNT(*) AS n
FROM shipments
GROUP BY order_id
HAVING COUNT(*) > 1
ORDER BY n DESC
LIMIT 10;

And a before/after row-count check: SELECT COUNT(*) FROM orders vs the joined result. Any increase under a LEFT JOIN means fan-out. Senior habit worth naming: when writing pipelines, assert key uniqueness (dbt unique tests, Delta constraints) so fan-out fails loudly at load time instead of silently in dashboards.

The grain mismatch in one picture: one order at amount 100 meets three shipments, so the join emits three rows and the SUM triples.

Fixes depend on intent:

1. Pre-aggregate the many side to the join grain, the usual right answer:

SELECT o.order_id, o.amount, s.shipment_count, s.last_shipped_at
FROM orders o
LEFT JOIN (
  SELECT order_id,
         COUNT(*)            AS shipment_count,
         MAX(shipped_at)     AS last_shipped_at
  FROM shipments
  GROUP BY order_id
) s ON s.order_id = o.order_id;

2. Pick one row deterministically if you need shipment attributes, not aggregates, ROW_NUMBER() ... QUALIFY rn = 1.
3. Accept the finer grain but fix the math, if the report needs shipment-level rows, de-duplicate measures with SUM(DISTINCT) only in desperation (it breaks when amounts repeat legitimately); the honest fix is computing each measure at its own grain and joining aggregates.

If both sides are many (e.g., orders↔promotions), you get a Cartesian blow-up per key, same diagnosis, and usually a sign the data model needs a bridge table or the question needs restating.

The compounding is worth executing once, because intuition says two joins add rows and the truth is they multiply. Join order 7 (amount 100) to its three shipments and its two promo codes in the same query, and the result is 3 x 2 = 6 rows with SUM(amount) = 600 (run in sqlite): a 6x overstatement from two individually innocent-looking joins. This is how the worst dashboard incidents happen: each join was added in a different sprint by a different person, each one reviewed fine in isolation, and revenue crept up by a factor nobody can explain because no single change caused it. It is also why the detection habit is per-join, not per-query: run the uniqueness check every time a join is added, because after the second fan-out the row counts are so scrambled that attribution requires unpicking the whole query.

What interviewers probe next

• "Why didn't the query error?", SQL has no grain checker; relations are just multisets. This motivates tests/constraints.
• "Performance angle", fan-out before another join compounds multiplicatively; in Spark a hot key fanning out is also a skew bomb (one straggler task). Mention exploding intermediate shuffle sizes.
• "How would you prevent this at a customer with messy data?", uniqueness expectations on ingestion, grain documentation in the semantic layer.
• Often chained into the LEFT JOIN/WHERE trap, opposite symptom (rows vanish), same root discipline: know your grains.

Common mistakes

• Blaming "duplicates in the data" without identifying which key isn't unique at which grain.
• Patching with SELECT DISTINCT on the output, hides the bug, still double-counts aggregates.
• Not knowing that LEFT JOIN can increase row count (many think it can only preserve).