Why do event pipelines receive duplicates?

Brokers and webhooks deliver at least once: when an acknowledgement is lost or times out, the sender redelivers the same event, because it can't tell whether the receiver processed it.

How do you deduplicate events in SQL?

Rank copies per event ID with `ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY received_at)` and keep rank 1, or MERGE into a table keyed by `event_id`.

What does 'exactly-once processing' really mean?

At-least-once delivery combined with an idempotent or transactional sink, so reprocessing a duplicate doesn't change the result. True exactly-once delivery over an unreliable network isn't possible.

Deduplication and the Exactly-Once Myth — ETL and Data Pipelines

Scenario

Example company: checkout events reach the warehouse through a message broker and a webhook from the payment provider. Both deliver at least once: after timeouts they redeliver, and the same event can arrive two or three times.

What you see in production

The "orders placed" metric is 2 to 3% higher than the number of orders in the database.
Spikes appear during incidents, exactly when retries increase.
Duplicate events have the same event_id but different received_at timestamps.

The naive code

SELECT COUNT(*) FROM raw_events WHERE event_type = 'order_placed';

Reproduce it

The raw event table below contains redeliveries. The query compares the naive count with a deduplicated one that keeps the first copy of each event_id.

Counting raw deliveries vs unique eventsTry it yourself

CREATE TABLE raw_events (event_id TEXT, event_type TEXT, order_id INTEGER, received_at TEXT);
INSERT INTO raw_events VALUES
  ('e1', 'order_placed', 1, '10:00:01'),
  ('e2', 'order_placed', 2, '10:00:02'),
  ('e2', 'order_placed', 2, '10:00:09'),
  ('e3', 'order_placed', 3, '10:00:04'),
  ('e3', 'order_placed', 3, '10:00:31'),
  ('e3', 'order_placed', 3, '10:01:15'),
  ('e4', 'order_placed', 4, '10:00:05');

SELECT 'naive' AS method, COUNT(*) AS orders_placed FROM raw_events WHERE event_type = 'order_placed'
UNION ALL
SELECT 'deduplicated', COUNT(*) FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY received_at) AS copy_number
  FROM raw_events WHERE event_type = 'order_placed'
) WHERE copy_number = 1;

Output

method | orders_placed
naive | 7
deduplicated | 4

Root cause

Delivery systems guarantee at least once, not exactly once, because a sender can't tell "the receiver got it but the acknowledgement was lost" from "the receiver never got it". Counting rows counts deliveries, not events.

The fix

Make sure every event carries a stable unique ID set by the producer (not by the receiver).
Deduplicate on that ID when building clean tables: ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY received_at) = 1, or MERGE into a table keyed by event_id.
Bound the deduplication window when tables are huge (for example, only look for duplicates within 7 days of the event), and accept the documented risk outside it.
What systems call exactly-once is in practice at-least-once delivery plus an idempotent or transactional sink. Design for that.

Trade-offs

Deduplicating huge tables is expensive; incremental models that dedupe only new data plus a recent window keep it affordable.
When producers don't send IDs, you must build a key from fields (order ID + event type + timestamp), which can merge genuinely distinct events.

Detect it in production

Track the duplicate rate (COUNT(*) - COUNT(DISTINCT event_id)) per day and source.
Reconcile event counts with the source of truth (orders in the database).

Key points

Real delivery is at least once; duplicates are normal.
Require a producer-assigned event ID and deduplicate on it.
"Exactly once" = at-least-once delivery + idempotent processing.