Engineering, production, governance, cost, communication · insight-0264
Deduplicate late-arriving corrections by business key and load time, explicitly
- Evidence type
- practitioner heuristic
- Difficulty
- medium
- Roles
- Data analyst, ML engineer
- Industries
- aviation, retail, banking
Summary: situation, action, reason, boundary
Situation
A flight-day feed sometimes sends the same row twice, the second with an updated revenue figure.
Action
Define the business key, keep the record with the latest load timestamp for current reporting, keep all versions for point-in-time training, and count dropped duplicates as a monitored data-quality metric.
Reason
Duplicates with different values are corrections, so the rule must state which version wins and when.
Boundary
CDC feeds, vendor files with resends, event streams with at-least-once delivery.
Symptom or risk
A naive DISTINCT keeps both rows and doubles revenue; keeping an arbitrary row may discard the correction.
Exceptions
True event logs where duplicates are distinct events need event ids, not business-key dedup.
Trade-offs
Keeping versions increases storage and query complexity.
Pitfalls
Deduplicating before joining, then reintroducing duplicates through a join on a non-unique key.
Example
The synthetic flight_day_raw table has about 0.8% duplicate rows, half with a late revenue update.