SQL, data modeling, data quality, EDA, visualization · insight-1042
Use a coverage table and an anti-join to count what did not happen
- Evidence type
- documented fact
- Difficulty
- medium
- Roles
- Data analyst, Data scientist
- Industries
- aviation, retail, cross-industry
Summary: situation, action, reason, boundary
Situation
Someone asks which scheduled flights had no bookings in a week, or which customers received no offer, and the only table you have lists events that occurred.
Action
Build or find a coverage table of everything that could have occurred (scheduled flight-days, eligible customers), then LEFT JOIN or NOT EXISTS against the activity table and count the unmatched rows. Reconcile the coverage table to an independent source.
Reason
Absence is defined relative to an expected set; the coverage table supplies that set, and the difference is the answer.
Boundary
Attendance, coverage of campaigns, missed events, gaps in sensors and eligibility checks.
Symptom or risk
An events table cannot show absence; filtering it gives only the cases that happened, so the denominator and the "zero" cases are missing.
Exceptions
When the expected set is unknown, flag the question as unanswerable instead of inventing one from observed keys.
Trade-offs
Maintaining the coverage table is real engineering; stale schedules create false absences.
Pitfalls
Deriving "all possible flights" from the activity table itself, which excludes exactly the flights you want.
Lessons and concepts
Claims and their sources
Kimball describes factless fact tables, including a coverage table of possible events and an activity table of events that occurred, whose difference is the set of events that did not occur.