Lessons · Python · when a join multiplies rows
When a join multiplies rows
Joining one order to its three items gives three rows, each carrying the order's amount. That is correct, and it means summing amount afterwards counts the order three times.
Hone is a place to practise programming. This is one of its lessons, written out in full and free to read without an account.
What it is for
Revenue that doubles after a join, a dashboard that disagrees with finance: fan-out is the usual cause, and it never raises an error.
How to think about it
Before summing after a join, ask what one row means now. Aggregate the many side first, or sum the one side before joining, or drop duplicates on the one side's key. Check the row count before and after every merge.
Worked example
orders = {"o1": 100}One order, amount 100.items = [("o1", "pen"), ("o1", "ink"), ("o1", "pad")]Three items on it.joined = [(o, orders[o], name) for o, name in items]One row per item, amount repeated.
print(len(joined))3: one order became three rows.
print(sum(a for _, a, _ in joined))300: the order counted three times.
print(sum(orders.values()))100: sum the one side, then join.
Your turn
Check the join did not multiply rows.
assert len(joined) == len()
Solve one with the tests running
The trap
A key that repeats on both sides multiplies: three rows each side make nine. Deduplicate the lookup side before joining.