The Job Succeeded and the Numbers Are Wrong: Duplicates, Loss, and Reprocessing

The pipeline has been green for a month. Every run succeeded, every alert stayed quiet, and the dashboard refreshed on time. Then someone reconciles the warehouse against the source system for a quarterly close and finds 412 orders that exist in one and not the other, plus a handful of orders counted twice. Nothing failed. The example is invented, but the gap it describes is not: a successful run means the code finished, not that the data is right.

Those two statements come apart because a pipeline can complete while dropping rows it never knew about, writing the same rows twice, or processing a window whose data had not fully arrived. This article is about that gap — where duplicates and loss actually come from, what a delivery guarantee does and does not promise, what reprocessing changes, and how to find out that the numbers are wrong before someone else does. Product behavior below was checked against documentation in September 2026, at the versions named.

What a delivery guarantee actually guarantees

Every system that moves data makes a promise about how many times a record arrives, and the vocabulary is standard. Kafka’s design documentation states the three delivery semantics plainly: at most once — “messages may be lost but are never redelivered”; at least once — “messages are never lost but may be redelivered”; exactly once — “each message is processed once and only once.” It then adds a warning worth taking seriously: many systems claim exactly-once semantics, “but it is important to read the fine print, because sometimes these claims are misleading.”

The fine print is almost always about where the guarantee stops. Three boundaries matter.

• The producer cannot tell a lost request from a lost acknowledgement. Kafka’s documentation compares it to inserting into a table with an autogenerated key: on a network error the producer does not know whether the write committed. Retrying is what turns that ambiguity into a duplicate, which is why Kafka added an idempotent producer — the broker assigns a producer ID and deduplicates using a sequence number sent with every message.
• The consumer’s choice of order decides the semantics. Per the same documentation, saving your position and then processing gives at-most-once, because a crash in between loses the work; processing and then saving the position gives at-least-once, because a crash in between repeats it. There is no third order that avoids both.
• The destination has to participate. Within Kafka, exactly-once is achievable because the consumer’s offset can be written in the same transaction as the output. Writing to an external system, the documentation says, runs into “the need to coordinate the consumer’s position with what is actually stored as output,” and concludes that exactly-once delivery to other destination systems “generally requires cooperation with such systems.” The default, otherwise, is at-least-once.

Stream processors describe the same boundary from their side. Flink’s documentation states that it can guarantee exactly-once state updates only when the source takes part in its snapshotting mechanism, and that end-to-end exactly-once record delivery additionally requires the sink to take part in checkpointing; its own table of bundled sinks lists several as at-least-once, with the Kafka sink reaching exactly-once only with a transactional producer and the Cassandra sink only for idempotent updates. Spark’s Structured Streaming guide makes the requirement explicit in one sentence: “using replayable sources and idempotent sinks, Structured Streaming can ensure end-to-end exactly-once semantics under any failure.”

Read together, these say something practical. Exactly-once is not a product feature you buy; it is a property of a whole path — a source that can be replayed, a position that is recorded reliably, and a destination whose writes either participate in a transaction or can be repeated harmlessly. Which one is missing changes the answer, and it is worth separating them. If the source can be replayed and the position is durable but the destination cannot commit atomically, the design target is at-least-once plus idempotent application, and the honest thing to tell stakeholders is that duplicates are prevented at the destination rather than in transit.

If replay itself is missing — a source that pushes to you once and keeps no copy, a webhook, a device that discards what it sent — idempotent application does not rescue you. Idempotence is a property about duplicates; it says nothing about an event destroyed before anything recorded it. There are only three real options, and one of them has to be chosen deliberately: make the receiving edge durable before you acknowledge, so the buffer becomes the replayable source; negotiate re-delivery with the producer as a stated contract; or accept a bounded loss and say what the bound is. Declaring at-least-once over a source that cannot re-deliver claims a guarantee nothing in the path provides.

Where duplicates come from

Cause	What it looks like	What removes it
Retry after an ambiguous acknowledgement	A few duplicated rows clustered around a network blip	Idempotent producer, or a key the destination can deduplicate on
Restart before the position was saved	A contiguous block of repeated records after a crash or deploy	Idempotent writes keyed by a stable identifier
A rerun of a batch that already loaded	The whole partition’s values doubled	Overwrite the partition instead of appending; make the load a replace, not an add
Upstream resends the same file or extract	Exact duplicate rows, often a whole day’s worth	A delivery manifest and a record of which files were already consumed
Fan-out in a join during transformation	Totals inflated by a clean factor, no duplicate keys in the source	Fix the query, not the ingestion — nothing was delivered twice

That last row matters because it changes who investigates. Duplicated rows and duplicated counting produce similar-looking totals from entirely different causes, and pipeline teams lose days chasing delivery bugs that live in a SQL join.

The general defense is to make the write idempotent so that repetition is harmless. Kafka’s documentation notes the reason this is usually available: “in many cases messages have a primary key and so the updates are idempotent.” Where no such key exists — clickstream events, log lines, sensor readings — one has to be constructed and agreed with the producer, which is a contract discussion rather than a code change. See event deduplication for what a dedup key has to satisfy and how long the window must be.

Where loss comes from

Loss is the more dangerous failure, because duplicates usually announce themselves in a total that looks too big while missing rows just look like a slightly quieter day.

• The position was advanced before the write committed. This is the ordering rule from the previous section applied to any pipeline: record where you got to after the destination has durably accepted the data, never before.
• The acknowledgement was weaker than assumed. Kafka considers a message committed only when every in-sync replica has applied it; a producer configured to wait only for the leader is choosing lower latency and accepting a window in which a leader failure loses the write.
• Rows were filtered out silently. A join that drops unmatched rows, a type conversion that nulls a value and a later filter that excludes nulls, a malformed record skipped by a permissive parser. Each is a deliberate feature behaving as designed on data nobody expected.
• Rows went to an error path nobody reads. A dead letter queue that no one monitors is indistinguishable from data loss, with the added disadvantage that everyone believes the data is safe.
• The window closed too early. Records that arrived after the watermark advanced are dropped or land in a later window, depending on the configuration — a correctness question decided by a lateness setting.
• The source’s retention expired. A consumer stopped for longer than the log kept its data cannot resume; what it missed has to be recovered from somewhere else, if it still exists.

Only the first two are about delivery machinery. The rest are decisions — about filters, error paths, lateness, and retention — that happen to have loss as their consequence. That is why a pipeline whose transport is provably at-least-once can still lose data.

The commit order that makes both survivable

Most of the above collapses into one sequencing rule and one property.

read a bounded batch
  -> transform
  -> write to the destination, keyed so a repeat is a no-op
  -> destination commit succeeds
  -> only now record the position (offset, watermark, file marker)

Crash before the write: nothing was recorded, the batch is read again, nothing is lost. Crash between the write and the position update: the batch is read again and re-applied, and because the write is idempotent the second application changes nothing. The cost of this arrangement is that duplicates must be harmless, which is a property of the destination schema — a stable key, a version column, a merge rather than an append. See checkpoint and replay for the mechanics and committed offset and consumer lag for what the recorded position means for monitoring.

Kafka’s documentation describes an elegant version of the same idea for external destinations: rather than a two-phase commit between the position store and the output store, let the consumer “store its offset in the same place as its output,” so that either both are updated or neither is. A pipeline writing to a warehouse can do exactly this by writing the batch and its high-water mark in one transaction.

Reprocessing: three different operations

“Just rerun it” hides three operations with different risks. Naming which one is being done prevents most reprocessing accidents.

	Replay	Backfill	Restatement
Why	The pipeline failed or produced wrong output; the source data is fine	New history is needed — a new column, a longer window, a newly added source	Published figures were wrong and consumers must be told
Scope	A known range of positions or partitions	A historical period, often large	The affected published outputs and everything derived from them
Main risk	Doubling data if writes are not idempotent	Load on the source; interleaving with live loads	Consumers acting on the old numbers without knowing
Also needs	A bounded range and a way to prove the range was covered	A cutoff that does not overlap the live pipeline ambiguously	Communication: what changed, for which period, and why

See data backfill for the second and delivery revision and restatement for the third, where the hard part is social rather than technical.

What makes a rerun produce different output

Reprocessing assumes that running the same logic over the same input yields the same result. Several ordinary things break that assumption, and each is worth checking before a backfill rather than after:

• current_timestamp, current_date, or “today” computed at runtime, which silently re-anchors every window to the rerun date rather than the original one.
• Lookups against a mutable dimension: the customer’s region today is not the region they had in March, which is what versioned dimensions and point-in-time lookups exist to solve.
• Calls to an external service whose answer changes, or whose rate limit turns a large backfill into a partially failed one.
• Random sampling, hashing with a per-run seed, or any tie-break that is not fully determined by the data.
• Transformation code that has changed since the original run — which may be exactly the point, but then the output is a restatement and should be labeled as one.

Checking: the pipeline does not verify itself

Everything above can go wrong without an error, so correctness has to be asserted separately from execution. Three kinds of check do different jobs, and confusing them leaves gaps.

Check	Answers	Misses
Run status and lag	Did it execute, and how far behind is it?	Everything about the content
Data assertions	Does the output satisfy stated rules — keys unique, values in range, no nulls where forbidden?	Rows that were never delivered — if the assertion is row-level. A rule like “no nulls” only reads rows that arrived. Completeness is checkable, but only by a dataset-level assertion with an independent expectation: an anti-join against a list of keys that should be present, or a comparison with an expected partition or row count
Reconciliation against the source	Does the destination hold what the source holds?	Errors the source itself contains

Assertions are cheap and belong in the pipeline. dbt’s data tests are a documented example of the form: an assertion is a query that selects failing records, and “if the test returns zero rows, it passes,” with built-in generic tests for uniqueness, non-null, accepted values, and referential integrity. Put them where they can stop bad data rather than describe it afterwards — between the load and the publish. That phrase describes a structure, not a setting: the new data lands in a staging or versioned relation, the assertions run against it, and only a pass moves the view or alias consumers read. Run against the table consumers already query, an assertion can only report that it is wrong, because the write has happened. That is the operational content of data quality work, as distinct from a dashboard of quality scores.

Designing a reconciliation that means something

Comparing two systems sounds simple until the comparison disagrees for uninteresting reasons. A workable design fixes five things in advance:

1. A cutoff both sides can honor. “As of the source’s state at 02:00” is checkable; “now” is not, because the two sides are read at different moments.
2. A grain. Row counts, key sets, and summed measures answer different questions; counts alone pass when one row is missing and another duplicated.
3. A comparison that scales. Counts, then key-range summaries such as a chunked checksum, then full comparison only on ranges that disagree.
4. A rule for tolerated differences, written down with its reason — rounding at a known precision, or rows in flight within the measured lag — rather than a threshold chosen to make the check pass.
5. An owner and an action. A discrepancy report nobody is accountable for becomes background noise within a month.

AWS DMS ships a documented implementation of row-level validation, and its limitations show the shape of the problem rather than a shortcoming of that product. Validation requires a primary key or unique index and does not support NULL key values or views. It issues its own queries, consuming resources on both source and target. If the target is modified outside the migration during validation, the documentation warns that discrepancies “might not be reported accurately,” and rows being continuously modified cannot be compared at all. It also stops after a configured number of failures — in its default, more than 10,000 failed or suspended records ends the run so the underlying problem can be fixed first. Any reconciliation you build yourself will meet the same four walls: keys, moving data, cost, and what to do when everything mismatches.

When it has already gone wrong

The response to bad data in production is more like an incident than a bug fix, and the order matters.

• Stop the spread first. Pause dependent jobs before fixing anything; every minute the wrong data flows, the number of derived outputs to correct grows.
• Establish the blast radius. Which periods, which tables, which reports. Lineage narrows the search; query logs catch the consumers lineage does not know about.
• Isolate rather than delete. Move the suspect records to quarantine so they can be examined and, if they turn out to be valid, reinstated.
• Correct with a labeled reprocessing, using the vocabulary above, and verify the correction with the same reconciliation that would have caught the problem.
• Tell the consumers, including those who already acted on the wrong numbers. Whether last week’s figures changed is a question people need answered without having to ask.
• Add the check that would have caught it — the missing assertion, the unmonitored error path, the unmeasured lag — as part of closing the incident rather than as a follow-up ticket.

The measure of a pipeline is not that it never delivers wrong data; it is how quickly wrong data is noticed, how precisely its extent can be stated, and whether the people affected hear it from you. Delivery completeness stated as a property you can demonstrate, rather than assumed from a green run, is what makes that possible.

References

Documentation was checked in September 2026 at the versions named. Delivery guarantees and validation features change between versions; confirm them for the version you run.

• Apache Kafka documentation, Design: Message Delivery Semantics (AK 4.3.X)
• Apache Spark, Structured Streaming Programming Guide: Fault Tolerance Semantics (4.2.0)
• Apache Flink documentation, Stateful Stream Processing and Fault Tolerance Guarantees of Data Sources and Sinks
• AWS Database Migration Service User Guide, AWS DMS data validation
• dbt Documentation, Data tests