How do you deduplicate events, and what does 'exactly-once' really mean?

Summary

Require a producer-assigned unique event ID and keep one row per ID, for example with ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY received_at) = 1 or a MERGE keyed on the ID. 'Exactly-once' in practice means at-least-once delivery plus idempotent or transactional processing.

Longer Answer

Brokers and webhooks redeliver after lost acknowledgements, so duplicates are normal. For huge tables, deduplicate incrementally within a bounded window. Without producer IDs, a composite key from business fields is a fallback that may merge genuinely distinct events.

Code Example

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY received_at) AS rn
  FROM raw_events
) WHERE rn = 1;

Key Points

• Duplicates come from at-least-once delivery.
• Deduplicate on a producer-assigned ID.
• Window functions or MERGE.
• Exactly-once = at-least-once + idempotent sink.

Deep dive: ETL and Data Pipelines: Deduplication and the Exactly-Once Myth

What interviewers look for

Producer IDs, window-function deduplication and a correct view of exactly-once.