Before this: Joins where missing keys never match
The idea
Every join has an expected shape. A lookup from orders to customers is many-to-one: many orders, one customer each.
Test the expectation: count keys on the lookup side; in pandas pass validate="many_to_one"; in SQL check that a GROUP BY key HAVING COUNT(*) > 1 returns nothing.
If the expectation fails, stop and fix the lookup instead of deduplicating the result.
Worked example
Input: customers: K1 (segment A), K1 (segment B)
- Visual
Inspector → customers.customer_key → duplicates
- SQL (DuckDB)
SELECT customer_key FROM customers GROUP BY 1 HAVING COUNT(*) > 1
- pandas
orders.merge(customers, on="customer_key", how="left", validate="many_to_one")
Result: pandas raises MergeError; SQL lists K1
Common mistakes
- Joining without checking.
- DISTINCT on the result to hide fan-out.
- Ignoring null keys on the lookup side.
Check yourself
What happens to totals if the lookup has duplicate keys?
Matching facts are duplicated and totals inflate.
Practice it
Join orders to customers without matching blanks.