DebuggingGENERALBROKER-SPECIFICSIMULATED

Duplicate Rows

Revenue is up, nothing launched, and every check is green except uniqueness. Inflation is the failure people question least and notice last.

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

A measure jumped overnight with no product change, no marketing push and no failed task. Which of the five duplication mechanisms produced it, and why did only one check notice?

Who needs this

Every additive measure and every rate built on one: revenue, order counts, active users, conversion rates whose numerator or denominator moved. Duplication is more dangerous than loss for exactly one reason — a number that goes up is challenged far less often than a number that goes down.

What one row is

One row of the serving table must equal one real-world event. Duplication is the violation of that equation, and it comes in two kinds: the same event stored twice under the same key, and the same event stored twice under two keys. Only the first is detectable by a uniqueness test (Grain: What Does One Row Represent?).

The obvious build

Add SELECT DISTINCT to the model, or a GROUP BY on the business key, and the number comes back to where it should be. This genuinely works, it is fast, and for a one-off redelivery it is a reasonable emergency measure.

Why it breaks

DISTINCT deduplicates identical rows. Two deliveries of the same event that differ in an ingestion timestamp, a batch id or an event id are not identical, and both survive (Deduplication).

How it breaks with real data

DISTINCT deduplicates identical rows. Two deliveries of the same event that differ in an ingestion timestamp, a batch id or an event id are not identical, and both survive (Deduplication).
The duplication was not in the source data at all — a join to a dimension with two rows for one key multiplied every fact, and deduplicating the fact table afterwards discards real rows along with fake ones (Slowly Changing Dimensions).
A GROUP BY on the business key silently picks arbitrary values for every non-grouped column, so the number is right and the attributes are now non-deterministic (Grain: What Does One Row Represent?).
A backfill appended a second copy of a period rather than replacing it. Deduplicating fixes the count and leaves the pipeline still non-idempotent, so the next re-run does it again (What Backfills Break, Idempotent Data Pipelines).
The producer retried a request that had actually succeeded and emitted the same business event with a new event id. Deduplication on event id sees two distinct events and keeps both (Idempotency Keys: The Mechanism).
Two sources overlap — a batch extract and a stream both carrying the same records for the boundary hour — and the union counts every record in the overlap twice (Batch vs Streaming Ingestion).

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

What is actually happening

At-least-once delivery is the normal, correct behaviour of every durable messaging system. A consumer restart replays from the last committed offset, and every record since is delivered again. This is not a malfunction to be fixed upstream; it is a property to be handled downstream (Offsets and Commits, At-Least-Once Delivery).
Deduplication requires an identity and a window. The identity must be the business key where you can get it, because a technical event id only deduplicates redeliveries of the same emission, not re-emissions of the same fact (Deduplication).
Fan-out is duplication created inside your own SQL rather than delivered to it. A join whose right side is not unique on the join key multiplies rows, and every additive measure on the left is multiplied with them (Joins: INNER, LEFT, RIGHT, FULL, CROSS, SELF).
Type-2 dimensions are the most common source of accidental fan-out, because a key that is unique in the source has several rows in the dimension — one per validity period — and a join that forgets the validity predicate matches all of them (SCD Type 2 in Practice).
A non-idempotent pipeline turns every retry into duplication. Append-only writes plus an orchestrator that retries on failure is a combination that produces a second copy of a period whenever a task fails after it wrote and before it reported success (Idempotent Data Pipelines, Atomic Publish).
In the in-repo model, redelivery of a share of the stream trips uniqueness and reconciliation while completeness stays green — nothing was lost — whereas an unsafe backfill that appends a whole second copy also moves the distribution check, because the volume itself has doubled. The set of failing checks distinguishes the two mechanisms without any further investigation (The Pipeline Succeeded. The Data Is Wrong.).

Six ways one event becomes two rows

Duplication has two families and telling them apart is most of the diagnosis. Delivered duplicates arrived from outside: redelivery, producer retries, overlapping sources. Manufactured duplicates were created inside your own SQL: fan-out joins, appended backfills, unions of overlapping ranges.

The discriminator is cheap. Count distinct business keys in raw and compare with the row count in raw. If raw already has repeats, the duplication was delivered. If raw is clean and the fact table is not, you made them, and the join that made them can be found by counting rows in and out at each step (Pipeline Metrics).

One row in the table below is not detectable by any uniqueness test at all, and it is worth pausing on. A producer that retries after a timeout and generates a fresh event id has emitted two distinct events describing one fact. Every technical check passes. Only reconciliation against the source — which knows there was one order — will notice (Reconciliation).

Where the second row came from

Trigger	Symptom	Cause	Response
A stream consumer restarts, rebalances, or exceeds an acknowledgement deadline.	A burst of duplicate event ids concentrated around a restart. Uniqueness fails, completeness passes — nothing was lost.	At-least-once delivery replaying from the last committed offset. Correct broker behaviour, and the consumer's responsibility to absorb (Offsets and Commits).	Deduplicate on event id in a window comfortably wider than the redelivery interval. Rebuild the affected range from raw; do not delete rows in place.
A producer times out on a request that actually succeeded, and retries with a newly generated event id.	Every uniqueness test passes and the total is still too high. Reconciliation against the source is the only check that disagrees.	Two distinct events describing one business fact. There is no technical duplicate to find (Idempotency Keys: The Mechanism).	Require a stable idempotency key derived from the business fact, agreed in the data contract. Until then, reconcile and correct against the source as authoritative.
A fact table joins a type-2 dimension without a validity predicate.	Every measure multiplied by roughly the average number of versions per key. Uniqueness on the fact's grain fails; raw is clean.	The dimension has one row per validity period, so the join matches several. The fan-out is in your SQL, not in the data (SCD Type 2 in Practice).	Add the validity predicate, or join to a current-version view. Assert output row count equals driving-side row count as a standing test.
A backfill appends into the serving table instead of replacing the partition.	A whole period present exactly twice. Uniqueness fails, volume and distribution move, and reconciliation is off by a clean factor.	The pipeline is not idempotent, so re-running it is not a repair — it is a second copy of a period that was already correct (What Backfills Break).	Make the publish a partition replace or a merge on key, then rebuild the period once. Fixing the data without fixing idempotency guarantees a recurrence.
A batch extract and a stream both cover the boundary hour of a cutover.	Duplicates confined to a narrow, explicable time range, often at a deployment or migration boundary.	Two ingestion paths were live at once and the union of their outputs double-counts the overlap (Batch vs Streaming Ingestion).	Declare which path owns the boundary and filter the other by an explicit range. Reprocess the overlap window from one source only.
An orchestrator retries a task that failed after writing its output and before reporting success.	Duplication that correlates exactly with task retry counts, and only on runs that had a transient failure.	The write is not atomic with the success report, so a retry re-executes work that had already landed (When a Task Fails Mid-DAG, Retries in Pipelines).	Write to a staging location and publish atomically as the final step, so a retry re-does work that was never visible (Atomic Publish).

Duplication that changes the grain

GENERALFan-out from a non-unique join key is a property of relational algebra and appears identically in every engine. What differs is whether the tool warns you: some transformation frameworks can assert relationships between models, and raw SQL never will.

The fan-out family deserves separate treatment because the symptom is not really duplication — it is a grain change. After a fan-out join, one row of the model no longer represents one order; it represents one order-version pair, and every additive measure on it is now summed at a grain nobody declared.

This is why deduplicating the output is the wrong fix. DISTINCT on the fan-out result collapses genuine variation along with the artefact, and a GROUP BY on the order key picks arbitrary values for the dimension attributes you joined for in the first place. The fix is at the join, always.

Track the word "one" down the table below. Each row is a legitimate grain; the failure is never that a grain is wrong in isolation, it is that two grains meet in a join and nothing asserts which one survived (Grain: What Does One Row Represent?, Fact Tables).

What one row means before and after the join

Stage	One row is	Breaks if
Raw change records	One delivered change to one order — and the same change may be delivered more than once.	You count rows and call it orders. Three updates to one order are three records and one order, and a redelivery makes it four (What a CDC Event Contains).
`stg_orders` after deduplication	One order, at its latest known state, deduplicated on the business key.	The dedup key is the technical event id rather than the order id, so a re-emission under a new id survives as a second order.
`dim_customer` (type 2)	One customer version — one validity period of one customer.	Anyone treats it as one row per customer. It is unique on `(customer_key, valid_from)` and not on `customer_key` (SCD Type 2 in Practice).
`fct_orders` joined to `dim_customer`	One order — if and only if the join carries a validity predicate.	The predicate is missing. One row becomes one order-version pair, every measure is multiplied, and the table still looks like a fact table (Star Schema).
`fct_order_lines`	One line of one order, with a line-level amount.	An order-level measure such as shipping is carried on every line and then summed, multiplying it by the line count (Fact Tables).
`revenue_daily` mart	One country-day with revenue pre-aggregated.	Someone joins it back to an order-level table and re-aggregates, double counting a total that was already summed (Data Marts).

Every fan-out in this table is a join between two rows that each represent something legitimate. Nothing is malformed; two grains simply met and no assertion decided which one the output has.

What a uniqueness test proves, and what it cannot

Uniqueness is the highest-value single data test in this domain: one line per model, cheap to run, and it catches four of the six mechanisms above outright. It is also the check most often written against the wrong column, and a uniqueness test on a generated surrogate key is a test that can never fail and never informs.

Write it against the declared business grain — the columns that define what one row is — and write the declaration down next to it. A model whose grain is undeclared cannot have a meaningful uniqueness test, and the act of writing the declaration is usually where the disagreement surfaces (Grain: What Does One Row Represent?).

Then accept the blind spot honestly. Duplicates under different keys are invisible to every uniqueness test that will ever be written, and the only backstop is agreeing with the source about how much happened. That is one more reason reconciliation is the check every platform should build first (Reconciliation).

Detecting inflation

Check	Expresses	Catches	Still misses
Uniqueness on the declared grain key	One row of this model is one of the thing it claims to represent.	Redelivery, appended backfills, fan-out joins, overlapping source unions — four of the six mechanisms, in one assertion.	Duplicates under a different key. A producer retry with a fresh event id is two rows, two keys, one real event, and this test passes cleanly (Idempotency Keys: The Mechanism).
Row count in equals row count out, per join	This join enriched rows rather than multiplying them.	Fan-out at the exact join that caused it, before the inflated measure reaches anything a consumer sees (Joins: INNER, LEFT, RIGHT, FULL, CROSS, SELF).	Duplication that arrived in the input. It asserts the join is neutral, which is true and useless when the driving table already had repeats.
Signed reconciliation drift against the source	The serving table agrees with the source about how much happened in a closed period.	Every duplication mechanism including the key-different one, and the sign immediately separates inflation from loss (Reconciliation).	Open periods; anything wrong identically at both ends; a duplicate that offsets a loss of similar size, which reconciles to zero while both errors persist.
Dimension key uniqueness	Each dimension has one row per key, or one row per key-version with the version in the key.	The upstream cause of most fan-out, at the place where fixing it is cheap and before any fact table is built on it (Dimension Tables).	A dimension that is correctly unique and semantically wrong — two customer records for one real customer pass this test and duplicate the business fact.
Delivered-versus-distinct count in raw	How much redelivery the ingestion layer is actually seeing.	The delivered-versus-manufactured split, in one query, before anyone starts reading transformation code.	Nothing about correctness — it is a diagnostic rather than a guard, and a healthy platform with at-least-once delivery is expected to show a non-zero value here forever.

The last row is not a pass/fail check and should not alert. It exists so that the first question of every inflation incident — did we receive them or make them — is answered by a chart instead of an argument.

How to build it

Most important first.

Deduplicate on the business key at the earliest layer where the key is reliable, and keep the raw duplicates. Deleting evidence of redelivery makes the next investigation impossible (Keeping Raw History: The Recovery Position and the Liability, The Raw Landing Zone).
Make publishing idempotent: replace the partition or merge on the key, never append. Then a re-run is a repair rather than a second incident (Upserts and Merges, Idempotent Data Pipelines).
Test uniqueness on the declared grain key of every model, not only the ones you suspect. It is one assertion per model and it is the check that catches fan-out introduced by someone else's change (Data Tests).
Test that joins do not fan out, explicitly: assert the row count of the output equals the row count of the driving side, or assert uniqueness on the right side of each join key (Star Schema).
Ask producers for an idempotency key that is stable across retries — derived from the business fact rather than generated per attempt — so a retried emission is recognisable as the same event (Idempotency Keys: The Mechanism, Webhook Idempotency).
Where a stream and a batch overlap, define which one owns the boundary and filter the other, rather than relying on deduplication to clean up an overlap you designed in (Lambda Architecture).

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.

Brokers and CDC connectors guarantee at-least-once delivery. Never at-most-once, and never once, unless a specific transactional arrangement is in place at both the consumption and the output-write ends (Exactly-Once: Input Consumption, State Update, Output Write).
Deduplication on a key guarantees uniqueness within its window and within that key. Outside the window, and for any duplicate arriving under a different key, it guarantees nothing (Deduplication).
A merge on a business key guarantees idempotent publishing only if the key is genuinely unique in the source. If it is not, the merge either fails or silently keeps one arbitrary row (Upserts and Merges).
A uniqueness test guarantees that the column you named has no repeats. It says nothing about whether that column is the right grain key, and a test on a surrogate key generated per row always passes (Surrogate Keys).
Nothing guarantees the absence of semantic duplicates — the same business fact recorded twice through two different processes. That is a data-modelling and source-system question (Source of Truth).

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

The check is uniqueness on the declared grain key of every model — one assertion, cheap, and it catches redelivery, non-idempotent re-runs and fan-out joins alike.
It misses every duplicate that arrives under a different key. A producer retry with a fresh event id is two rows with two keys and one real event, and no uniqueness test will ever see it.
Pair it with reconciliation against the source, which catches key-different duplicates because the source total simply does not agree, and with a fan-out assertion at each join so that a duplication introduced mid-model is attributed to the join that caused it (Reconciliation).

Freshness

Deduplication windows trade freshness for correctness directly: a wider window catches duplicates that arrive further apart and holds state longer, delaying nothing but costing memory; a window shorter than the redelivery interval silently stops working (Streaming State).
The dangerous case is a duplicate that arrives days later, after a connector was repaired and replayed. Any dedup window sized for normal operation will miss it, which is why replay should be reconciled rather than trusted (Replay from the Log).
Duplicates do not decay. Unlike a shortfall in an open period, an inflated number never converges to the truth by waiting, which is a clean discriminator between the two symptoms (Missing Rows).

When the schema or meaning changes

A source that changes its key generation — a new id scheme, a migration that reassigns ids — breaks deduplication silently, because the old and new representations of the same fact no longer collide (Breaking Schema Changes).
Adding a type-2 dimension where a type-1 dimension used to be turns every existing join into a fan-out. The schema change is additive and backward compatible; the metric change is catastrophic (Slowly Changing Dimensions).
Changing the declared grain of a model — from one row per order to one row per order line — invalidates every uniqueness test and every downstream aggregate, and the tests will keep passing on the new grain while the metrics are silently multiplied (Grain: What Does One Row Represent?).

How to re-run this safely

Deduplicate at the source of the duplication, not at the end. Removing duplicates from the serving table while raw keeps delivering them means doing it again every day (Reprocessing vs Retrying).
Rebuild the affected partitions from raw with the correct dedup logic, into a scratch location, validate against the source, then publish atomically. Deleting rows in place from a live serving table is how a duplicate incident becomes a shortfall incident (Planning a Backfill, Atomic Publish).
Make the pipeline idempotent before backfilling, or the backfill itself adds a third copy (Upserts and Merges).
Where duplicates arrived under different keys, deduplication cannot help: reconcile against the source and take the source's set as authoritative for the affected range (Source of Truth).

What can go wrong

Failure modes

SELECT DISTINCT appears to fix it because the duplicates were byte-identical this time, and stops working the moment an ingestion timestamp is added to the payload.
Deduplication keyed on the event id runs perfectly and the number is still inflated, because the mechanism was producer retries with fresh ids.
The dedup window is sized for the normal case and silently misses everything a replay redelivers.
The fix removes duplicates and also removes legitimate repeat events — two identical orders from the same customer for the same amount in the same minute are a real thing in some businesses (The Dimensions of Data Quality).
A uniqueness test is added on a surrogate key that is generated per row, so it passes forever and asserts nothing (Surrogate Keys).
The dimension is fixed but the fact tables built while it was duplicated are never rebuilt, leaving a multiplied notch in history.

Misreads

"At-least-once is a bug in the broker." It is the guarantee the broker offers and the one nearly every durable system offers. The consumer is where uniqueness is established (At-Least-Once Delivery).
"We deduplicate, so we cannot have duplicates." You deduplicate on one key, within one window. Duplicates outside that window or under a different key pass straight through.
"The number went up, so something is working." Inflation is the failure mode people investigate last, because good news is not investigated. This asymmetry is why duplication is more dangerous than loss despite being easier to detect.
"A uniqueness test on the primary key is enough." If the primary key is a generated surrogate, it is unique by construction and the test asserts nothing about the business grain (Surrogate Keys).
"The duplicates came from the stream." Half the time they were created by a join in your own model, and the stream is blameless. Check the join before blaming the broker (Joins: INNER, LEFT, RIGHT, FULL, CROSS, SELF).

Operating it

How you see it in production

Uniqueness test results per model per run, with the count of violating keys rather than a pass/fail — the count tells you the mechanism and the boolean does not (Data Tests).
Row count in versus row count out at every join, so a fan-out is attributed to a join rather than discovered as an inflated total (Pipeline Metrics).
Reconciliation drift against the source, signed. A positive drift is duplication and a negative one is loss, and having the sign on the dashboard saves an hour every time (Reconciliation).
Consumer restart and rebalance events on the broker, correlated with ingestion volume — a spike in delivered records at a restart is redelivery, plainly visible (Consumer Groups and the Parallelism Ceiling).
Dimension key uniqueness as a standing test, because a duplicated dimension row is the cause that manifests everywhere else (Dimension Tables).

What changes at 10x and 100x

At 10x volume, exact deduplication state stops fitting comfortably. The options are a shorter window, a partitioned dedup keyed so state is local, or a probabilistic pre-filter backed by an exact check (Bloom Filter).
At 100x, merge-on-key becomes the dominant cost of the pipeline, and the design question shifts to partitioning the target so a merge touches few files (Partitioning, File Compaction).
More producers means more retry semantics to reason about. Each new source arrives with its own idea of what a retry is, and a platform-wide idempotency-key convention is the only thing that scales (Data Contracts).

What drives cost here

Deduplication costs state proportional to the window and the key cardinality, held either in the stream processor or as a join against recent history in batch (Streaming State).
Merging on a key is materially more expensive than appending: it reads the target, matches, and rewrites files rather than adding them. That cost is the price of idempotency and it is almost always worth paying (Upserts and Merges).
Fan-out is a compute cost as well as a correctness one — a join that multiplies rows multiplies every downstream shuffle and scan, so a duplication bug often shows up as an unexplained runtime increase before anyone notices the number (The Shuffle, Compute Waste).

What this approach costs

Deduplicating early is cheap and destroys the evidence of how often redelivery happens. Deduplicating late preserves the evidence and pays for the duplicates in every intermediate stage. Keeping raw and deduplicating in staging is the usual compromise.
Merge-on-key buys idempotency and hides the fact that you ran twice. A pipeline that silently absorbs double runs is safer and less informative, so keep the run counts even when the merge makes the data correct.
A wide dedup window catches more and costs more state. There is no window that catches everything, because a replay can redeliver from arbitrarily far back — which means reconciliation is the backstop, not a larger window (Reconciliation).

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.

GENERALAt-least-once delivery and join fan-out are universal mechanisms. What varies is which dominates: log-based platforms see redelivery, heavily-modelled warehouses see fan-out from dimensions, and platforms with manual backfills see append duplication.
BROKER-SPECIFICKafka replays from the last committed offset on rebalance, so duplication is bounded by commit frequency; Kinesis re-reads a shard from the stored iterator; Pub/Sub redelivers on ack deadline expiry, which means a slow consumer produces duplicates without any restart at all.
SIMULATEDThe claim that redelivery and an appended backfill produce different failing-check sets comes from the model in src/de/sim/pipeline.ts and is asserted in scripts/de-sim.test.ts. It is a property of that model, chosen so the two mechanisms are distinguishable — not a measurement.

Where the depth lives

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

Databasejoins relationships-and-keys aggregation-and-grouping

Backendat-least-once-delivery duplicate-detection idempotency-keys job-idempotency

API Designidempotency-vs-deduplication retryability webhook-consumer-idempotency

DSAhash-set bloom-filter

Domains that do not exist yet

— Distributed Systems owns why at-least-once is the delivery guarantee almost every durable system offers, and what an end-to-end effectively-once arrangement actually requires of the input consumption, the state update and the output write together.