SQL, data modeling, data quality, EDA, visualization · insight-0038
Aggregate each fact table to a shared grain before joining two facts together
- Evidence type
- practitioner heuristic
- Difficulty
- medium
- Roles
- Data analyst, Data scientist
- Industries
- aviation, e-commerce, retail
Summary: situation, action, reason, boundary
Situation
You need bookings and ancillary revenue per flight, and both live in tables with many rows per flight.
Action
Aggregate each table separately to the target grain (flight_no, date) in its own subquery or CTE, then join the aggregates on that key. Check that each aggregate has one row per key before the join.
Reason
Two many-to-one relationships through a shared key form a many-to-many join; aggregating first removes the fan-out.
Boundary
Combining orders with payments, bookings with ancillaries, tickets with refunds and any two event tables.
Symptom or risk
Joining the two detail tables directly multiplies rows (each booking paired with each ancillary sale), so both totals are inflated.
Exceptions
When you need row-level pairing (which payment settled which order), join on the pairing key, not the shared parent key.
Trade-offs
Pre-aggregation loses detail; keep the detail tables for drill-down.
Pitfalls
Computing ratios after the inflated join, where numerator and denominator are inflated by different factors.