The Join That Multiplied the Revenue
A revenue report joins a 900K-row order table to a 12K-row product-dimension table on product_id. Finance reports total revenue 8% higher than the ERP system of record. The join is an inner join. Investigation shows the product dimension contains 40 product_ids that appear more than once - the result of historical re-imports where a product was re-categorized. The order table has no duplicates on order_id. The report owner proposes switching to a left join to 'make sure nothing is lost.'
1. Explain the discrepancy and evaluate the proposed left-join fix.
Read the worked answer
The 8% inflation is join fan-out: 40 duplicated product_ids in the dimension cause each matching order row to be emitted once per duplicate dimension row. Inner vs. left join does not change this - both fan out identically on matched keys. A left join would additionally retain unmatched orders, which is a separate (and possibly desirable) behavior, but it does not fix the multiplication and could even change the total in a way that looks like a fix while the underlying defect remains.
Correct sequence:
- Quantify: join order rows to a count of dimension rows per product_id and sum the fan-out.
- Decide the grain you want: one row per order. That means the dimension must be unique on product_id before the join.
- Resolve the 40 duplicates with an explicit business rule (e.g., keep the most recent re-categorization by effective date, or the record flagged current), not an arbitrary 'first row.'
Never use a join type as a de-duplication tool; it changes row counts silently.
2. How would you build a guard so this class of error cannot silently recur?
Read the worked answer
Make the join's cardinality an explicit, tested assumption.
- Pre-join uniqueness assertion: compute a count of dimension rows per join key and assert the max is 1 before joining. Route violations to an error output rather than proceeding.
- Post-join reconciliation: compare the joined row count to the driving table's row count; a difference on a many-to-one join signals fan-out and should fail the run.
- Document the grain in the workflow so the next editor knows one row per order is the contract.
- Prefer a surrogate/current-record flag in the dimension so 'which duplicate is right' is answered by data, not by whoever edits the workflow next.
The judgement being tested: the fix is a data-model and validation fix, not a join-type toggle, and the guard belongs in the workflow so the defect fails loudly.