SQL, data modeling, data quality, EDA, visualization · insight-0053
Never sum across snapshots of a cumulative measure; pick one snapshot per entity
- Evidence type
- practitioner heuristic
- Difficulty
- easy
- Roles
- Data analyst, Data scientist
- Industries
- aviation, banking, retail
Summary: situation, action, reason, boundary
Situation
A booking-curve table stores cumulative bookings at several days before departure, and a query sums cumulative_bookings per flight.
Action
Identify whether each measure is cumulative (stock) or incremental (flow). For stocks, filter to one snapshot per entity, such as days_to_departure = 0 or the latest snapshot at a cutoff. Sum flows, not stocks.
Reason
Snapshots record state at a time; adding states double-counts the same underlying events.
Boundary
Booking curves, inventory levels, account balances, open tickets and pipeline snapshots.
Symptom or risk
Each booking is counted once per snapshot, producing totals many times larger than reality, or averages that depend on how many snapshots exist.
Exceptions
Averages of stocks over time (average inventory) are legitimate when weighted by time.
Trade-offs
Choosing the snapshot is a modelling decision; document it next to the KPI.
Pitfalls
Mixing cumulative_bookings and bookings columns in the same expression.
Example
Synthetic airline DB: booking_curve has 13 snapshots per flight (90 to 0 days out), so summing cumulative_bookings over all snapshots grossly overstates bookings.