What is deduplication?

Deduplication removes or collapses duplicate records so each business entity appears once at the intended grain.

Duplicates come from retries, at-least-once delivery, overlapping batch windows, or bad upstream keys.

raw events:
  order_id=9 event_id=A  amount=10
  order_id=9 event_id=A  amount=10   <- retry duplicate
  order_id=9 event_id=B  amount=10   <- legitimate update?

Need a rule: unique key + "keep latest" policy

Common techniques

• Unique key + drop duplicates in Spark/pandas
• Window + row_number() keep latest by updated_at
• MERGE into a table keyed by business id
• Kafka idempotent producer / exactly-once sinks (helps upstream)
• Content hash for identical payload detection

Tiny SQL pattern

SELECT *
FROM (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY order_id
           ORDER BY updated_at DESC
         ) AS rn
  FROM staging_orders
) t
WHERE rn = 1;

Interview tip: Say where duplicates come from (retries), name the grain key, then describe keep-latest or merge. Dedup without a clear key is guessing.