The Dedupe Key That Doesn’t Drift

This article was originally published on LinkedIn and is archived here on texlytics.com.

There is a reflex every data engineer has when they need to deduplicate rows: hash the row, compare the hashes, keep one of each. It is fast, it is simple, and on a well-behaved source it is correct. I reached for it without thinking when I built the ingestion for one of our CMiC AP voucher endpoints. It was the wrong call, and the way it failed is worth writing down, because the lesson holds far past CMiC.

Two problems, stacked

The CMiC REST API paginates, but the sort order is not stable between requests. There is no durable orderBy the endpoint honors the way you would expect. Run the same query twice and you can get the same rows back in a different order, and at the page boundaries that means a record can land on page three in one fetch and page four in the next. Pull a fixed set of pages and you can miss a row, or read the same row twice.

That much is a known hazard, and you can throw passes at it. The part that quietly broke my dedup was smaller and meaner: serialization drift. The same logical record did not always serialize identically across passes. A field that came back as null on one pass came back as an empty string on another. Same data, different bytes.

Why the hash fails

A hash is a function of the exact bytes you feed it. Flip one field from null to an empty string and the hash changes. So two responses representing the identical record produce two different hashes, and the dedupe logic concludes they are two different records. The duplicates I was trying to collapse sailed straight through, because the one thing I keyed on was the one thing that was not stable.

So I had two failures sitting on top of each other. Non-deterministic ordering was generating duplicates and risking gaps, and content hashing was structurally unable to collapse those duplicates because the content itself drifted underneath it.

Key on identity, not on content

The fix was to stop keying on derived content and start keying on identity. CMiC’s REST resources expose an Oracle sequence value, oraseq, that uniquely and durably identifies the underlying row. It does not drift between passes, because it is not a function of the serialized payload. It is the identity of the record at the source. Key the dedupe on oraseq and the null-versus-empty-string noise stops mattering entirely. Two responses for the same record now collapse, because they carry the same sequence value no matter how the rest of the fields happened to serialize that time.

Then make it redundant

Keying on identity solved the duplicate-collapse problem. It did not, on its own, solve the missing-rows problem from non-deterministic ordering. For that I fetch the endpoint three times and union the results before collapsing.

The logic is plain. Any single pass over a non-deterministically ordered, paginated endpoint has some chance of dropping a row at a page boundary. If that chance on one pass is p, the chance the same row is missed on all three independent passes is roughly p cubed: small enough to stop being the thing that wakes you up. Union the three passes so every row any pass saw is present, then collapse on the sequence key so the redundancy the union introduced disappears. You are left with one clean copy of each record.

records = [] for _ in range(3):     records.extend(fetch_all_pages(endpoint))   deduped = {} for r in records:     deduped[r[“oraseq”]] = r   # collapse on identity, not on a row hash   result = list(deduped.values())

The dict keyed on the sequence value does the collapsing. Whichever pass’s serialization happens to win the last write does not matter, because the record is the same record either way.

I want to be honest about what this is. It is not a proof of completeness. Three passes drives the miss probability down sharply; it does not drive it to zero. If you need a hard guarantee, you need a different contract with the source: a stable cursor, a reliable high-water mark, something the API actually promises. Absent that, redundancy plus a stable key is the pragmatic floor, and it has held.

Four endpoints, four strategies

All of the above assumes the endpoint hands you a durable identity to key on. Not all of them do. In practice each endpoint exposes some subset of three things: a stable unique identity (a UUID or composite key), a created timestamp, and an updated timestamp. The two timestamps together are what let you pull incrementally instead of refreshing everything; the identity is what lets you collapse the pagination overlap cleanly. Whether an endpoint gives you both, one, or neither decides the whole strategy, and it falls into four cases.

Endpoint exposes	How you pull it	How you collapse duplicates
UUID + dates (all three)	Incremental: filter at the source by the updated-date watermark, pulling only what changed	Collapse on the UUID
UUID only	Full refresh, or parent-driven by code; no date to filter on	Collapse on the UUID
Dates only	Incremental by date watermark	No UUID, so collapse on a composite natural key
Neither	Blind full refresh every run; there is no watermark to be incremental against	Composite natural key if you can build one; a row hash only as a last resort

The first two rows are the comfortable ones. We have an endpoint with a UUID and audit dates that runs a clean incremental and collapses on the key, and another with a UUID but no usable date finder that simply full-refreshes and collapses on that same UUID. The mode label barely matters once the loader knows there is an identity to dedupe on.

The bottom two rows are where the lesson from the first half of this article actually bites. With no UUID, you have nothing stable to collapse on, so you reconstruct identity out of the payload: a composite natural key assembled from the fields that together identify a row, like a company code plus a batch number plus a sequence. That is still keying on identity, just identity you had to assemble yourself. The raw row hash sits in exactly one cell of this table, the bottom-right corner, and only when no composite key can be built at all. That is the position it earns: the fallback of last resort, on the one endpoint that gives you nothing else, where you already know you are hashing a moving target.

The part that travels

Takeaway When you deduplicate, key on the most stable identity the source exposes, not on a hash of the payload. The payload is only as stable as the source’s serialization, and a lot of sources are not as stable as they look.

Strip CMiC out of this and the rule is portable. The four cases above are nothing more than that one rule applied to whatever each endpoint happens to expose. A surrogate key, a sequence, a natural primary key from the system of record: any of these survive the cosmetic churn that a content hash cannot. Reach for content hashing only when the source genuinely has no stable identity to offer, and even then, know that you are hashing a moving target. And when the ordering underneath you is non-deterministic, do not trust a single read. Add redundancy, then let the stable key clean up after it.

Before you write the hash

Three questions worth answering before you deduplicate records from any API:

• Does the source expose a durable identity for each record? If it does, use it.
• Does the same record always serialize byte-for-byte identically? If you are not sure, assume it does not.
• Is the pagination order stable across requests? If not, one pass is a gamble.

Get those three answered and the dedupe design usually writes itself.