SQL, data modeling, data quality, EDA, visualization · insight-0056
Normalize categorical keys and compare their cardinality with a reference list before grouping
- Evidence type
- practitioner heuristic
- Difficulty
- easy
- Roles
- Data analyst, Data scientist, ML engineer
- Industries
- aviation, retail, logistics
Summary: situation, action, reason, boundary
Situation
A GROUP BY route returns more distinct routes than the network has, including near-identical codes.
Action
Trim and case-normalize codes in a staging layer, then compare distinct values with the reference list and report unmatched values. Fail the load or quarantine rows when unmatched values exceed a threshold.
Reason
Cardinality checks against a known domain catch formatting drift that row-level type checks miss.
Boundary
Route, airport, product, country and currency codes, and any free-text categorical field.
Symptom or risk
Variants such as lower-case or padded codes split one route into several groups, understating each and breaking joins to reference tables.
Exceptions
Case-sensitive identifiers (some product SKUs) must not be case-folded; check the domain rule first.
Trade-offs
Normalizing in staging hides source quality problems unless you also report them to the producer.
Pitfalls
Normalizing in one report but not in the feature pipeline, which creates training-serving skew.
Example
Synthetic airline data: about 1% of flight_day_raw rows carry lower-case route codes.