SQL, data modeling, data quality, EDA, visualization · insight-0037
Compare row counts before and after every join to catch join explosions early
- Evidence type
- practitioner heuristic
- Difficulty
- easy
- Roles
- Data analyst, Data scientist
- Industries
- aviation, retail, cross-industry
Summary: situation, action, reason, boundary
Situation
A query joins a flight-level table to a table with several rows per flight, and the resulting passenger total looks plausible but higher than finance reports.
Action
Before aggregating, count rows on each side and after the join, and compute the maximum matches per key on the many side. Either filter the many side to one row per key (for example one snapshot) or aggregate it to the left table's grain before joining.
Reason
A join returns one row per matching pair, so a repeated key on the "one" side refines the grain; counting exposes it.
Boundary
Joins between facts and snapshots, facts and line items, and any join on a non-unique key.
Symptom or risk
Each flight row is repeated once per matching row on the other side, inflating sums by the fan-out factor while averages may look normal.
Exceptions
Intentional one-to-many joins, where the output grain is the many side and the query aggregates at that grain.
Trade-offs
Count checks add a few lines and a little runtime per query.
Pitfalls
Fixing an inflated sum with DISTINCT, which also collapses legitimate equal values.
Example
Synthetic airline DB: joining flight_day to booking_curve without filtering days_to_departure repeats each flight 13 times, so passengers are inflated 13x.