RecoveryGENERALBROKER-SPECIFICSIMULATED

Deduplication

Which key you deduplicate on decides which duplicates you can see — and a producer retry with a fresh id is invisible to every id-based scheme.

What actually happens How to build it Can I trust it?

Who needs this, what one row is, and why the obvious build breaks

Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.

The question

The same order appears twice in the fact table. Which of the two copies is wrong, and which key would have told you they were the same thing?

Who needs this

Every additive measure downstream. A duplicate does not break a query, raise an error or move a row count that anyone is watching — it inflates revenue, order counts, active users and conversion, all in the direction people are least likely to question.

What one row is

Deduplication is defined by the key you claim identifies one real-world thing. The grain of the check *is* that claim: one row per event_id, per (source_id, source_offset), or per order_id. Choosing the key is choosing which duplicates exist.

The obvious build

Deduplicate on the event id the producer assigns. It is unique per message, it is already in the payload, and a DISTINCT or a window function over it removes redelivered messages in one line. For redelivery from a broker this is exactly right and it is the correct first thing to build.

Why it breaks

The producer's HTTP call timed out after the server had committed. It retried, generated a new event id, and sent the same order again. Two distinct ids, one real order, and id-based deduplication cannot see it — this is the failure mode the dedupe-on-event-id mitigation in src/de/sim/pipeline.ts explicitly does not cover (At-Least-Once Delivery).

How it breaks with real data

The producer's HTTP call timed out after the server had committed. It retried, generated a new event id, and sent the same order again. Two distinct ids, one real order, and id-based deduplication cannot see it — this is the failure mode the dedupe-on-event-id mitigation in src/de/sim/pipeline.ts explicitly does not cover (At-Least-Once Delivery).
The dedup window is twenty-four hours and a redelivery arrives after a three-day consumer outage. The window has forgotten the first copy, so the duplicate is accepted as new (Late-Arriving Data).
A backfill re-appended a whole period. Every row has the event id it always had, so window-based dedup treats none of them as duplicates unless the window happens to span months, which no window does (What Backfills Break).
A CDC snapshot re-runs and re-emits the initial state of every row alongside the ongoing stream, so every entity appears once from the snapshot and again from the stream (Snapshot and Stream: the Bootstrap Problem).
Deduplication on the business key silently discards a genuine second event — a customer really did place two identical orders a minute apart — because the key was never as unique as the model assumed (Surrogate Keys).
A fan-out join against a dimension with duplicate keys multiplies rows after every dedup step in the pipeline, producing duplicates that no upstream deduplication could ever have prevented (Grain: What Does One Row Represent?).

Source→Ingestion→Raw→Transformation→Validation→Storage Model→Serving→Consumer→Observability

What is actually happening

There are three families of key and they detect different things. Event id: a value the producer assigns per message, which catches transport-level redelivery and nothing else. Source coordinate: a (source, offset) or (source, log position) pair, which catches re-reading the same position and is immune to producer behaviour. Business key: the identifier of the real-world thing, which is the only one that catches a duplicate created before the message existed (Event Keys and Partition Assignment).
Every scheme has a window, explicitly or otherwise, because remembering every key ever seen is unbounded state. The window is the period over which a repeat is recognisable, and a duplicate arriving outside it is not detected — it is welcomed (Streaming State).
The producer-retry case is the one that defeats the intuitive scheme. From the pipeline's point of view the two records are genuinely different messages describing the same event, and only a key derived from the *content* — the business key, or a deterministic hash of the meaningful fields — relates them (Idempotency Keys: The Mechanism in Backend is the same problem solved on the producer side, which is where it should be solved).
Deduplication and idempotent writing are two solutions to one problem, applied at different points. Dedup filters the input; a merge on the business key makes the output converge regardless of the input. The second is stronger, because it also survives a whole period being replayed (Upserts and Merges).
Choosing between "first wins" and "last wins" is not a detail. For an immutable event, first wins is right. For a record that represents mutable state, last wins is right and requires an ordering you can trust, which arrival order is not (CDC Ordering and Transaction Boundaries).
Deduplication is never finished at one hop. Each stage can introduce its own duplicates, so the assertion belongs at the serving layer as well as at ingestion, and it is the serving one that catches the backfill (Data Tests).

Three keys, three different blind spots

The word "duplicate" hides a family of unrelated events. A broker redelivering a message, a producer retrying a request, a snapshot overlapping a stream, a backfill re-appending a month and a join fanning out are five different causes, and no single key detects more than two of them.

The table below is worth reading by column rather than by row. Read down "catches" and you can see which causes are covered; read down "misses" and you can see what a platform that only does the obvious thing is exposed to. Almost every platform does the obvious thing.

The last row is the one that ends the argument. A merge on the business key is not deduplication at all — it is an idempotent write, and it converges no matter how many copies the input contained or how long ago the first one arrived. It costs more per load and it is the only entry with no window (Upserts and Merges).

Key	What it identifies	Catches	Misses	State it needs
`event_id` from the producer	One message, as the producer labelled it.	Broker redelivery after a consumer restart; a consumer that re-read from an uncommitted offset.	A producer retry that generated a new id — the same real event arriving as two legitimately different messages. Also any duplicate created downstream.	A set of ids over a window. Memory grows with rate times window length.
(`source_id`, log position)	One position in one source log.	Re-reading the same offsets after a restart or a replay; a connector that resumed from an older checkpoint (Offsets and Commits).	Anything that legitimately produced two log entries for one real event — which is exactly what a producer retry does at the source.	The highest position processed per source. Small, bounded and durable.
Business key (`order_id`)	One real-world entity, as the business names it.	Producer retries, snapshot-plus-stream overlap, a re-appended period, and most fan-out — everything the other two miss.	Genuine repeat events that share the key, which it discards as duplicates. Also a key that is not actually unique in the source.	Either a window, or none at all if applied as a merge at the sink.
Content hash of the meaningful fields	One distinct payload, regardless of identifiers.	A retry with a new id and identical content; a re-emitted record whose envelope changed but whose body did not.	Two genuinely distinct events with identical content — two identical orders a minute apart are one row afterwards.	A set of hashes over a window, plus a stable definition of "meaningful fields" that survives schema change.
Business key, as a sink-side merge	The stored state of one entity.	Everything above, plus whole-period replays and repeated backfills, because convergence does not depend on remembering anything.	Nothing about duplicates — but it hides that they happened, so the duplicate rate must be measured separately.	None. This is the property that makes it different in kind from the four above.

A platform typically wants row one at ingestion for cheap transport-level filtering, and row five at the serving layer for correctness. Row three in the middle is where the real duplicates are caught.

The retry that dedup cannot see

SOURCE-SPECIFICThe ordering column depends on the source: a Postgres CDC stream gives an LSN that totally orders commits, a MySQL binlog gives file and position, and a SaaS webhook gives you whatever timestamp the vendor chose — often with second granularity and no tie-break, which makes last-wins genuinely ambiguous.

A checkout service posts an order to an event API. The API commits and the response is lost on the way back. The client, correctly, retries — and its retry generates a new event id, because the id is created per attempt rather than per order.

Two messages now exist. They have different ids, different timestamps, possibly different trace identifiers, and they describe the same order. Every id-based deduplication scheme sees two distinct events and is behaving exactly as designed. The only thing relating them is order_id, which is in the payload rather than the envelope.

This is why identity belongs to the producer. An event id derived from the request's idempotency key is stable across retries, and it collapses this case into the easy one at the cost of one line in the client. Consumer-side deduplication on the business key is the fallback for producers you do not control — which is most of them (Idempotency Keys: The Mechanism).

The SQL below is the fallback done properly: dedup on the business key with an explicit ordering, so the surviving row is chosen rather than arbitrary, and with the discarded copies retained so the duplicate rate stays observable.

Business-key deduplication with an explicit winner and a visible rate

1-- Dedup on the business key, not the event id. The two retried copies of
2-- one order carry different event_ids and the same order_id.
3WITH ranked AS (
4    SELECT *,
5           ROW_NUMBER() OVER (
6               PARTITION BY order_id
7               -- Order by the SOURCE's notion of time, never by arrival:
8               -- arrival order is not commit order and a later-arriving
9               -- older record would otherwise win.
10               ORDER BY source_commit_lsn DESC, event_ts DESC
11           ) AS rn,
12           COUNT(*) OVER (PARTITION BY order_id) AS copies
13    FROM   raw.order_events
14    WHERE  order_date BETWEEN DATE '2026-08-01' AND DATE '2026-08-25'
15)
16SELECT * FROM ranked WHERE rn = 1;
17 
18-- The rows this discarded are the signal. Keep them: a duplicate rate that
19-- changes shape is an upstream event, and it is unrecoverable once dropped.
20-- INSERT INTO quality.dedup_discards
21SELECT order_id, event_id, event_ts, arrival_ts, copies
22FROM   ranked
23WHERE  rn > 1;
24 
25-- The rate, per source and day. Stable and non-zero is healthy for
26-- at-least-once delivery. A step change is not a data problem yet -- it is
27-- a producer or broker problem that will become one.
28SELECT source_id,
29       order_date,
30       COUNT(*)                            AS rows_in,
31       COUNT(*) - COUNT(DISTINCT order_id) AS duplicate_rows
32FROM   raw.order_events
33GROUP  BY source_id, order_date
34ORDER  BY order_date;

ROW_NUMBER over the business key with a source-side ordering is the whole pattern. The two decisions that matter are which column orders the copies — a source log position beats any timestamp the consumer can see — and that rn > 1 is written somewhere rather than thrown away. Note this still cannot separate a retry from a genuine second identical order; nothing downstream of the producer can (At-Least-Once Delivery).

Which check finds which duplicate

Deduplication and the tests that verify it are different things, and the tests are where the blind spots become legible. A uniqueness assertion at ingestion and the same assertion at the serving layer catch different failures, because most of the duplicates that reach a dashboard were created between the two.

The rows below are ordered by how far down the pipeline the check sits. The first two are cheap and narrow; the last two are the ones that catch the failures this module is about — a re-appended period and a fan-out join, neither of which any ingestion-time scheme can see.

Note that none of these separates a producer retry from a genuine duplicate order. That distinction does not exist in the data, only in the producer's intent, which is why the durable fix is upstream and everything here is compensation (Idempotency in Backends).

Duplicate detection, by where it sits

Check	Expresses	Catches	Still misses
Uniqueness on `event_id` in the raw landing zone.	Each delivered message was delivered once.	Broker redelivery, a consumer that restarted before committing its offset, a replay that overlapped what was already read.	A producer retry with a new id; anything created downstream. Also anything outside whatever period the check covers.
Uniqueness on (`source_id`, log position) per load.	The connector did not re-read a range it had already processed.	A connector resumed from a stale checkpoint; two connectors reading the same source (Offsets and Commits).	Duplicates that exist in the source log itself, which is exactly what a producer retry writes there.
Uniqueness on the business key in the serving table.	One row per real-world entity, where consumers read.	Producer retries, snapshot-and-stream overlap, an unsafe backfill that re-appended a period, a fan-out join — everything upstream checks miss.	Duplicates genuinely present in the source; and it cannot tell you which copy is correct, only that there are two.
Row count per partition compared against its own pre-load value.	A load added the number of rows it was supposed to add.	A re-appended period, whose partition count changes by a factor rather than by a delta — the signature of the `unsafe-backfill` failure (What Backfills Break).	Duplicates spread thinly across many partitions, and any duplication that arrived in the same load as the original rows.
Reconciliation of a summed measure against the source for a closed period.	The total we report is the total that happened.	Duplication and loss together, including duplicates whose keys are all distinct — the only check here that catches the producer-retry case end to end (Reconciliation).	Anything wrong identically in source and warehouse; open periods, where lateness looks like loss (Late-Arriving Data).

Read the misses column downward: coverage only becomes complete at the last row, and the last row is the most expensive and the least often built.

How to build it

Most important first.

Push identity to the producer where you can. An event id that is stable across retries — derived from the request's idempotency key rather than generated per attempt — turns the hardest case into the easy one, and costs the producer one line (Idempotency in Backends).
Deduplicate on the business key at the point where the grain is declared, not only on the event id at ingestion. The business key is the only one that sees producer retries and re-appended periods.
Size the window from the measured redelivery and lateness distribution, and monitor the share of duplicates caught near its edge. A window with catches piling up at its boundary is a window that is too short (Late-Arriving Data).
Prefer an idempotent write over a filter. A merge keyed on the business key makes the target converge under any number of replays, which is a stronger property than any dedup window and the one that survives a backfill (Upserts and Merges).
State first-wins or last-wins explicitly per dataset, and if last-wins, name the column that orders them — a source log position or a commit timestamp, never arrival order (What a CDC Event Contains).
Keep the discarded copies somewhere for a bounded period. A duplicate rate that changes is a signal about an upstream, and it is unrecoverable once the rows are dropped (Data Observability).
Assert uniqueness at the serving layer as a scheduled test rather than trusting the dedup step, because the step only sees the duplicates it was designed for (The Data Quality Dashboard).

What this actually promises

Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.

Deduplication on a key guarantees at most one row per key within the window, and says nothing about anything outside it. Both halves of that sentence matter and the second is usually omitted.
It guarantees nothing about which copy survives unless an ordering is specified. Two rows with the same key and different payloads resolve arbitrarily under a plain DISTINCT.
It cannot guarantee that two rows with different keys are different things. That is the producer-retry case, and no consumer-side scheme recovers from a producer that did not know it was retrying.
Combined with a business-key merge at the sink, you get convergence of the stored state under repeated delivery of the same input — which is a scoped claim about the output write, resting on the key being genuinely unique, and not a general exactly-once guarantee (Exactly-Once: Input Consumption, State Update, Output Write).

Can I trust it?

A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.

The check that would catch this

A scheduled uniqueness assertion on the business key at the serving layer — not at ingestion — because it is the only place that sees duplicates introduced by any stage, including a backfill (Data Tests).
It misses duplicates that are genuinely present in the source, which are a business question rather than a pipeline defect, and it misses semantic duplicates with different keys entirely: the same order submitted twice with two ids is two valid rows by every check you can write.
Pair it with a duplicate rate metric per source. A step change in the share of records discarded as duplicates is an upstream event, and it is invisible if you only assert the end state (Volume Anomalies).

Freshness

A dedup window is state that has to be held, and holding it is what lets a record be checked immediately rather than after a batch. Streaming dedup buys freshness with memory (Stateful Stream Processing).
Batch deduplication over a partition is cheap and can only see duplicates inside that partition, so a duplicate that straddles a partition boundary survives until something looks across periods.
Deduplicating at the serving layer via a merge adds write latency on every load and removes the need for a window entirely. That is usually the right trade for analytical data and the wrong one for a low-latency stream (Upserts and Merges).

When the schema or meaning changes

If the producer changes its id generation — a new client version, a new SDK, a migration from natural to synthetic ids — the dedup key silently stops matching history, and every record looks new (Semantic Changes).
If the business key changes meaning or cardinality, dedup on it changes what it discards. A key that becomes non-unique turns deduplication into silent data loss, which is worse than the duplicates it was preventing (Data Contracts).
Adding a source to a dataset introduces a second id namespace. Two sources can emit the same id for different things, so the key becomes (source, id) or it becomes wrong (Ingestion Sources).

How to re-run this safely

Removing duplicates already published requires distinguishing the copies, which is easy when they differ by an arrival timestamp or a load id and impossible when the rows are identical — which is exactly what a re-appended period produces (What Backfills Break).
Keep a load id or ingestion timestamp on every row. It costs one column and it is the difference between deleting a duplicate and rebuilding the partition.
The general recovery is a rebuild of the affected partition from raw with a merge on the business key, which converges regardless of how many copies the input contains (Full Refresh vs Incremental).

What can go wrong

Failure modes

A producer retry with a fresh id, invisible to id-based dedup and visible only on the business key.
A dedup window shorter than the real redelivery interval, so late duplicates are accepted as new.
A re-appended period whose rows all carry their original ids, which no window-based scheme detects.
Business-key dedup discarding genuine repeat events, converting an inflation problem into a silent loss problem.
Last-wins resolution ordered by arrival rather than by source position, so an older version of a record overwrites a newer one (CDC Ordering and Transaction Boundaries).
Dedup applied only at ingestion, while every duplicate that matters is introduced downstream by a join or a re-run — the mitigation sitting in the wrong place (Duplicate Rows).

Misreads

"We deduplicate on the event id, so we have no duplicates." You have no *redelivered messages*. A producer retry with a new id, a re-appended period and a fan-out join all produce duplicates that scheme cannot see (At-Least-Once Delivery).
"Duplicates are rare." Duplicates are the expected steady state of any at-least-once system. The question is not whether they occur but whether the count is stable and monitored.
"SELECT DISTINCT fixes it." DISTINCT deduplicates whole rows, so two copies differing in an ingestion timestamp are both kept — and if they are identical, DISTINCT also silently merges two genuinely distinct events that happen to look the same (Grain: What Does One Row Represent?).
"Deduplication is an ingestion concern." Most of the duplicates that reach a dashboard were created downstream, by a join or by a re-run, long after ingestion deduplicated perfectly (Duplicate Rows).

Operating it

How you see it in production

Duplicate rate per source, per day: records discarded over records received. A stable non-zero rate is normal for at-least-once delivery; a step change is an upstream event (Pipeline Metrics).
The lag distribution between the first and second copy of a duplicated key, which is what calibrates the window. Catches clustering near the window edge means the window is too short.
A scheduled uniqueness test on the serving table with its failures recorded rather than merely alerted, so the rate over time is visible (The Data Quality Dashboard).

What changes at 10x and 100x

At 10x volume the dedup window becomes the memory constraint of the job, and the tuning conversation moves from correctness to whether the state fits (Streaming State).
At 100x, exact windowed dedup is often replaced by a probabilistic structure that trades a small false-positive rate for bounded memory — which means silently dropping a small share of genuine records, and that trade must be stated (Bloom Filter in DSA covers the mechanism).
More sources make the key composite and the namespace question urgent. Two systems emitting the same id for different entities is not an edge case at scale, it is Tuesday.

What drives cost here

Dedup state costs memory proportional to keys times window. It is the single largest state cost in most streaming pipelines and the reason windows exist at all (Streaming State).
Batch deduplication costs a sort or a hash aggregate over the partition, which is a shuffle and scales with the data rather than with the number of duplicates (The Shuffle).
A merge at the sink costs reading the target as well as writing it — more expensive per load than an append, and it removes the window state entirely. Which is cheaper depends on how much state the window would have held.
The cost of not deduplicating is paid in inflated metrics and in the investigation that follows, which arrives months later and is charged to people (Duplicate Rows).

What this approach costs

Business-key deduplication catches the duplicates that matter and risks discarding genuine repeats. Event-id deduplication never discards a real event and misses the producer-retry case entirely. Most platforms need both, at different stages.
A longer window catches more and costs memory proportionally. There is no window that catches everything, and choosing one is choosing a detection rate.
A merge at the sink is the strongest guarantee available here and hides the fact that duplicates arrived at all — so it must be paired with a duplicate-rate metric, or the upstream problem becomes invisible (Upserts and Merges).

Where this applies

Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.

GENERALThe three key families and the window constraint hold anywhere data is delivered more than once. What varies is where dedup is cheapest to apply — a warehouse merge, a stream operator with keyed state, or the producer assigning a stable id, which is the only place it is actually solved.
BROKER-SPECIFICKafka can deduplicate producer retries within a session using a producer id and sequence number, which addresses redelivery inside the broker and not a producer that restarted and re-sent with new identity; Kinesis and Pub/Sub push the whole problem to the consumer, and Pub/Sub explicitly documents that duplicates are expected.
SIMULATEDThe behaviour of the dedupe-on-event-id mitigation — that it removes redelivered copies and does nothing about a re-appended period or a producer retry with a fresh id — comes from the model in src/de/sim/pipeline.ts and is pinned by scripts/de-sim.test.ts.

Where the depth lives

This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.

Databasetransactions-and-acid relationships-and-keys normalization

Backendidempotency-keys duplicate-detection at-least-once-delivery idempotency-in-backends

API Designidempotency-vs-deduplication webhook-consumer-idempotency

Concurrencysingleflight

Distributed Systemsdeduplication delivery-semantics idempotency-scope

DSAbloom-filter hash-set

Domains that do not exist yet

— Distributed Systems owns why at-least-once is the default: a sender that does not see an acknowledgement cannot know whether the message or the acknowledgement was lost, so it must retry, and duplicates are the price of not losing data.