SQL, data modeling, data quality, EDA, visualization · insight-0049
Deduplicate on a business key and keep the latest version by update time, not with DISTINCT
- Evidence type
- practitioner heuristic
- Difficulty
- easy
- Roles
- Data analyst, Data scientist, ML engineer
- Industries
- aviation, banking, cross-industry
Summary: situation, action, reason, boundary
Situation
A raw table contains repeated rows for the same flight and date, some identical and some with a revised revenue value.
Action
Define the business key, rank versions with ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated_at DESC, ingestion_id DESC), keep rank 1, and log how many rows were dropped and how many differed in value.
Reason
Duplicates come in two kinds, exact replays and corrections; only a key plus a version order handles both deterministically.
Boundary
CDC feeds, retried API loads, partner files and manual corrections.
Symptom or risk
SELECT DISTINCT removes only identical rows, so revised duplicates stay and revenue is double-counted; dropping randomly keeps stale values.
Exceptions
Event streams where repeated identical events are genuine (two scans of one boarding pass) need event ids instead of business keys.
Trade-offs
Keeping only the latest version discards audit history; store versions separately if audits need them.
Pitfalls
Ordering by a timestamp with ties and no tie-breaker, which keeps an arbitrary row.
Example
Synthetic airline data: flight_day_raw has about 0.8% duplicate rows, half of them carrying a late revenue update.