SQL, data modeling, data quality, EDA, visualization · insight-0036
Write down the grain of every table and assert it before the first aggregate
- Evidence type
- documented fact
- Difficulty
- easy
- Roles
- Data analyst, Data scientist, ML engineer
- Industries
- aviation, cross-industry
Summary: situation, action, reason, boundary
Situation
You start a KPI query on an unfamiliar table, or a table whose definition changed recently.
Action
State the grain in one sentence ("one row per flight_no per departure date") and test it: compare COUNT(*) with the count of distinct key combinations, and inspect a few keys with more than one row. Put the assertion in the pipeline, not only in your notebook.
Reason
Grain defines what one fact means; joins, deduplication and metric definitions all depend on it, and a broken grain rarely raises an error.
Boundary
Fact tables, model training tables, exports from operational systems and any table fed by joins.
Symptom or risk
If one row is not what you think (one flight per day, one booking, one snapshot), every sum, count and average is silently wrong.
Exceptions
Event logs where duplicate rows are legitimate (two identical clicks) need an event id rather than a natural key.
Trade-offs
Uniqueness checks cost a scan on large tables; run them on new partitions only.
Pitfalls
Assuming a column called id is unique without checking.
Example
Synthetic airline data: flight_day is one row per flight_no and date, but flight_day_raw has about 0.8% duplicate rows, so the same check fails there.
Lessons and concepts
Claims and their sources
Declaring the grain (what one fact-table row represents) is a pivotal early design step, and different grains should not be mixed in one fact table.