SQL, data modeling, data quality, EDA, visualization · insight-0058
Distinguish "zero", "unknown" and "not applicable" instead of letting one NULL mean all three
- Evidence type
- practitioner heuristic
- Difficulty
- easy
- Roles
- Data analyst, Data scientist, ML engineer
- Industries
- aviation, insurance, healthcare
Summary: situation, action, reason, boundary
Situation
A cancellations column is NULL for some flights, and analysts disagree on whether that means no cancellations, missing data or a flight that never operated.
Action
Agree with the producer on the meaning of absence for each field. Encode true zeros as 0, unknown as NULL with a reason code where possible, and not-applicable through a status column; document the convention in the data contract.
Reason
NULL carries no reason. Making the reason explicit removes guesswork at every downstream use.
Boundary
Operational counts, optional attributes, survey answers and fields whose meaning depends on status.
Symptom or risk
Different teams fill the NULL differently, so KPIs disagree, models learn source-specific artefacts, and nobody can audit the choice.
Exceptions
Pure analytical extracts where the producer cannot change the schema; document the assumed meaning in the analysis instead.
Trade-offs
Extra columns and producer effort for fewer silent disagreements.
Pitfalls
Imputing "not applicable" values, which invents data for units where the measure does not exist.