Row Identity and Why Deduplication Needs a Key, Not a Comparison

A retry delivers a batch twice. A producer restarts and replays the last hour from its buffer. Two source systems each export the same customer, and the union of the exports contains that customer twice with slightly different addresses. In each case the pipeline has to decide whether these are two records of the same thing or two things that look alike, and that decision is not present in the data.

No comparison of the records resolves it, because both pairs are equally similar and equally different. It is resolved only by a declaration about which fields constitute identity, and every deduplication technique is a way of applying that declaration consistently.

Comparing rows answers a different question

The obvious approach is to compare whole records and discard exact duplicates. It works, and it fails in a specific way: it treats a corrected record as a new one.

Consider a record describing an order, delivered once with status pending and again, an hour later, with status shipped. The two rows differ in every byte of the status column, so exact comparison keeps both. The table now holds two rows for one order, and any count of orders is wrong. The pipeline has not deduplicated; it has deduplicated the subset of duplicates that happened to be byte-identical, which is the subset that arrives from a literal retry and not the subset that arrives from a legitimate update.

The inverse failure is worse. Two distinct customers can share every field a form collects: same name, same city, same postal code, and no other distinguishing attribute. Exact comparison merges them, and the merge is invisible, because the resulting table is well-formed and the row count is merely one lower than it should be. Both failures come from the same mistake: using similarity of content as a proxy for identity, when identity is an assertion about the world that the data does not carry.

There is a third case that breaks both naive strategies: a correction that should replace a value. An address is corrected from one street to another. Under exact comparison the corrected row is kept alongside the stale one. Under last-writer-wins on a timestamp, the correction replaces the stale value only if its timestamp is later, which is a separate assumption about clocks that may not hold when the two records came from different systems.

What a key has to be

A deduplication key must satisfy three properties, and the third is the one usually skipped.

It must be stable: the same real-world entity yields the same key value every time it is observed. It must be discriminating: distinct entities do not collide. And it must be available at the point where deduplication happens, without a join to something that may not have arrived yet.

Source-assigned identifiers satisfy all three when they exist. An order system that mints a monotonically increasing identifier, or a UUID, gives downstream consumers a value that is stable and discriminating. The remaining problem is only that different source systems mint identifiers in different spaces, so a union of two systems needs a composite key of source and identifier, not the identifier alone.

Natural keys, built from business attributes such as a combination of account number and effective date, are discriminating within a source and unstable across time, because business attributes change. They are the right key when the source has no identifier, and they are the wrong key when an attribute in the key is itself subject to correction, since correcting it changes the identity of the row and orphans everything that referenced it.

Composite keys carry a cost that is easy to underestimate: the key is compared on every dedup decision, so its width sets the cost of the comparison and the size of any index over it. A key of four string columns is several times more expensive to hash and to store than a single 64-bit integer, and the difference propagates into every downstream join.

The availability requirement is what rules out many otherwise-correct designs. Deduplicating on a key derived from a dimension table requires the dimension to be present, which makes deduplication depend on load order. Deduplicating on a key computed from the record itself requires no coordination at all, which is why self-contained keys dominate in streaming pipelines.

Event identifiers and the limits of producer-side dedup

The cleanest arrangement puts a unique identifier on every event at the moment it is created, so that a duplicate is byte-identical in the field that matters and the consumer only has to remember which identifiers it has seen.

That memory is the cost. A consumer that must suppress duplicates across an unbounded stream needs to retain every identifier it has seen, which grows without bound. Practical implementations bound the memory by a time window: identifiers are retained for as long as a duplicate can plausibly arrive, then discarded, on the argument that a producer’s retry horizon is shorter than the window. The argument is an assumption about producer behaviour, and it fails when a producer is offline for longer than the window and replays from its own durable buffer. The duplicates that survive are exactly the ones from the longest outages, which are also the ones that arrive in the largest batches.

A second limit is that the identifier must be assigned before the retry, not after. A producer that generates a fresh identifier on each attempt produces two events that are genuinely distinct by the only criterion the consumer has. Fixing this requires the producer to derive the identifier deterministically from the payload or from a client-supplied request key, which is the same idea as an idempotency key in an HTTP API: the client chooses the key, so a retried request is recognisable as the same request.

There is a distributed-systems reason this cannot be solved purely on the consumer side. Exactly-once delivery between two processes is not achievable without cooperation, because the acknowledgement of a message and the processing of it are two separate events that can fail independently. What is achievable is effectively-once processing: at-least-once delivery plus idempotent application of each record. Deduplication by key is the mechanism that makes application idempotent, and it therefore inherits the requirement that the key be part of the record rather than derived from its arrival.

Windowed deduplication and which record wins

When duplicates are removed inside a bounded window, the window has to be defined in a time domain, and the choice of domain changes the result. Windowed by processing time, the window is the consumer’s clock, so a record delayed in transit falls into a later window than its duplicate and both survive. Windowed by event time, the window is the record’s own timestamp, so duplicates fall together, but the window cannot close until the watermark passes, and records arriving after the watermark are late and either dropped or admitted to a window that has already emitted.

Once duplicates are grouped, one has to be chosen. The common rules are:

• Keep the last by event time. Correct when events are ordered and later means more current. Wrong when clocks across producers disagree by more than the inter-event interval.
• Keep the last by ingestion time. Correct when the source is a single ordered log. Wrong when two partitions of the same topic are consumed concurrently and their arrival order interleaves arbitrarily.
• Keep the record with the most non-null fields. A heuristic that prefers completeness, and that silently prefers a stale but complete record over a fresh but sparse one.
• Keep all versions and let the reader choose. This is the only rule that loses no information, and it moves the decision downstream where it may be made differently by different consumers.

The choice is a merge policy, and a merge policy applied per field rather than per row is often better: take the latest non-null value of each attribute independently. That handles the case where one source knows the current address and another knows the current phone number, and neither record is complete. Its cost is that the merged row corresponds to no single observation, so it cannot be traced to one source record when a value is disputed.

-- Last non-null value per attribute, ordered by event time within the key.
SELECT
  order_id,
  max_by(customer_name, event_time) FILTER (WHERE customer_name IS NOT NULL) AS customer_name,
  max_by(ship_city,     event_time) FILTER (WHERE ship_city     IS NOT NULL) AS ship_city,
  max(event_time) AS last_seen
FROM order_events
GROUP BY order_id;

Duplicates that are not duplicates

Three patterns defeat key-based deduplication while satisfying every structural check, and each needs a different remedy.

The first is the re-keyed record. A source system reassigns an identifier, perhaps because a record was recreated after deletion, and the same entity now has two keys. No consumer can detect this from the records alone; the two rows are distinct by the only definition available. Detection requires either a source-side change log that reports the reassignment or a fuzzy match on business attributes, and fuzzy matching has a false-positive rate that has to be managed explicitly.

The second is the legitimately repeated event. Two events with the same key can both be real: a customer places two orders with the same identifier because the identifier is per-day rather than global, or a meter reports the same reading twice at different times. Treating these as duplicates loses data, and the loss is silent. The defence is a key that includes the discriminator, which requires knowing that the discriminator exists.

The third is the near-duplicate from independent capture. Two systems observe the same real-world event, such as a shipment scan and a warehouse log, and neither knows the other’s identifier. The rows are not duplicates in the sense of a retried message; they are two observations of one event. Merging them requires matching on attributes with tolerance, which is record linkage rather than deduplication, and its output is a confidence rather than a decision. Keeping them separate and reconciling counts later is often the better choice, because the reconciliation is a query and the merge is irreversible.

The general principle is that deduplication cannot create information. It can only remove rows that a declared key says are the same, and every ambiguity it resolves is resolved by fiat. The declaration belongs in the pipeline’s contract, next to the schema contract that fixes the column types, so that a consumer can see which notion of identity the table was built on. When the declaration is absent, the pipeline will still deduplicate, using whichever fields happen to be convenient, and the resulting table will be internally consistent and externally wrong.

The same key is what makes a late correction safe to apply. A record replayed from a rejected batch has to land as an update to an existing row rather than as a second row, and that is a statement about identity, not about timing.