ORIGINAL TECHNICAL PRACTICE
Deduplicate events without erasing corrections
An ingestion table contains duplicate event IDs and occasionally revised payloads. What rule would you define before deduplicating?
Written for independent practice. The model answer presents one approach; clarify constraints and explain alternatives.
A REASONED APPROACH
Model answer.
Duplicate removal needs a domain-specific identity and survivor policy. Exact retransmissions and later corrections are different cases, so keeping an arbitrary row can silently discard the intended state.
Work through it
- Define the event key and whether source version, effective time or ingestion time determines the authoritative record. Establish a deterministic final tie-breaker.
- Use a ranking over the defined key to select the survivor, or preserve version history when auditability is required. Quarantine conflicting records if no valid precedence exists.
- Reconcile input rows, exact duplicates, corrections and retained events. Test identical timestamps, different sources and missing identifiers.
Tradeoffs to discuss
- Keeping only the current version simplifies downstream queries but loses historical state. Preserving versions adds storage and requires explicit as-of queries.
Common pitfalls
- SELECT DISTINCT removes identical rows, not conflicting duplicates. Choosing the greatest ingestion timestamp alone may promote a late-arriving stale record.