SQL, data modeling, data quality, EDA, visualization · insight-0064
Check row volume per entity and period to find gaps that schema tests cannot see
- Evidence type
- practitioner heuristic
- Difficulty
- easy
- Roles
- Data analyst, Data scientist, ML engineer
- Industries
- aviation, energy, logistics
Summary: situation, action, reason, boundary
Situation
A table passes all type and null checks, yet a route's history has a hole of several days.
Action
Compare actual rows per entity and period with expected rows from a schedule or calendar, list missing keys, and classify each gap as a real absence (no flights operated) or a data loss. Fail or flag loads with unexplained gaps.
Reason
Completeness is a property of the set of rows, not of any single row, so row-level validation cannot detect it.
Boundary
Time series, sensor data, daily partner files and panel datasets.
Symptom or risk
Missing periods distort averages, lags and seasonal profiles, and a gap is easily mistaken for zero demand or a service suspension.
Exceptions
Event data with no expected schedule; monitor volume against a forecast band instead.
Trade-offs
Expected-row tables need maintenance when schedules change.
Pitfalls
Interpolating across a data-loss gap before deciding whether the period should exist.
Example
Synthetic airline data: flight_day_raw lacks IST-TZX rows from 2023-03-04 to 2023-03-12 and about 0.5% of other route-days.