Deduplication: three different problems in one word

Why does fuzzy matching have a recall ceiling, and why does loosening a threshold collapse clusters?

First, a boundary. Transport-level duplicates, the same message delivered twice, are solved exactly with sequence numbers or idempotency keys, because the duplicate carries proof of its own duplication. Everything in this section is the case where no such proof exists: two records that represent the same real-world entity and were created independently.

Exact dedup on a business key

Exact dedup on a business key (say, (phone, name)) is uniqueness enforcement in different clothes, and it inherits the same constraint from The free tier: candidate matches must meet, so you either co-partition by the key or you exchange data.

The interesting variant is windowed dedup, which bounds memory by bounding time: "treat two records as duplicates only if they arrive within 24 hours of each other". The bound is an assumption: that duplicates arrive close together. It cannot be verified from inside the pipeline. When it is wrong, you get no error. You get a duplicate that passed. Whoever shrank the window to save memory has silently bought a defect rate with it, and the purchase is invisible in every metric the pipeline reports.

Fuzzy dedup: a two-stage filter

Fuzzy dedup (names with typos, different spellings, different formats) is entity resolution, and its mechanics are taught there. In short: comparing every pair is quadratic (about 5 × 10¹³ pairs for 10⁷ records), so every real system splits the work in two:

A cheap blocking step that produces candidate pairs (a shared postcode, a phonetic code, MinHash bands).
An expensive comparator that scores each candidate pair.

For a check, what matters is how that design goes wrong.

The consequence: recall is capped by the cheap stage

The consequence is sharp, and applies far beyond customer matching.

Recall is capped by the prefilter; the comparator only moves precision. Teams tune scoring thresholds for months, while their recall ceiling was fixed the day someone chose to block on postcode. A customer who moved house, or whose postcode has a typo, is never compared with their other record.

The same holds for every two-stage filter: blocking before matching, Bloom filters before joins, candidate generation before ranking, partition pruning before scanning. The discarded set is unrecoverable, so the cheap stage decides what can be found at all.

The standard escape is multi-pass blocking with several independent keys (postcode, phone prefix, phonetic surname). The price is a candidate set that grows with every pass.

Failure mode 1: transitivity and the percolation collapse

Pairwise matching is not transitive (A matches B, B matches C, A does not match C), yet any clustering step forces transitivity: it puts A, B and C into one group.

Suppose you build clusters as connected components (everything linked, directly or through others, becomes one group). Then a single false link merges two clusters permanently. Now loosen the threshold slightly. The density of links crosses a percolation point, the way a few more open paths suddenly connect a whole grid: a giant component appears and swallows thousands of entities at once. That is Ravi's cluster of 2,300 people.

Quality does not degrade gradually as you loosen the threshold. It collapses. So production systems cap cluster size, require several independent supporting links, or cluster with a method that does not take transitive closure.

Failure mode 2: the two errors are not equal

A missed merge leaves two records, which is easy to repair later. A wrong merge, if it is a destructive rewrite, destroys the information needed to undo it. So the converged design keeps resolution as an overlay: a mapping from source records to entity IDs, with the source records kept untouched, instead of rewriting the source.

-- Overlay: sources stay as they are; the mapping can be revised or reversed.
CREATE TABLE customer_entity_map (
  source_system  VARCHAR,
  source_id      VARCHAR,
  entity_id      BIGINT,      -- the resolved real-world person
  matched_by     VARCHAR,     -- which rule or model version decided this
  matched_at     TIMESTAMP
);
-- To undo a bad merge: update the mapping rows. No source data was destroyed.

This is immutability buying reversibility, and also another case of "costs are conserved": the price is storage plus an extra join on every read, paid to keep a bad decision recoverable.

Compression: fuzzy matching is a two-stage filter. The cheap stage fixes recall forever, and the expensive stage only argues about precision.

Case, resolvedConstructed case · Ravi at Freshlane

2,300 people merged into one customer

Marketing counted 5.0 million customers and the CRM 4.2 million, and loosening the match threshold slightly merged thousands of people into one giant cluster. Where it started: Reconciliation and dedup →

The giant cluster. Ravi stops using transitive closure. A new link joins two clusters only if at least two independent signals agree (for example the same phone and the same delivery address), and any cluster larger than 20 people is sent for human review instead of being merged. The matches go into an overlay table customer_entity_map, so the 2,300-person cluster is undone by updating its mappings. The source records are untouched.

The recall ceiling. He adds a second and a third blocking pass (phone prefix, and a phonetic code on the surname), so customers with a typo in the postcode are still compared. The candidate set grows by about 2.4×, and the nightly match takes 20 minutes instead of 8. Recall improves because the blocking got better, not the scoring.

5.0 million versus 4.2 million. The match finds about 0.55 million duplicate registrations, giving about 4.45 million distinct people. The CRM's 4.2 million misses 0.25 million customers who only exist in the app's own database. Neither number was "the" truth. The reconciliation ladder explains why a differential cannot say which side is wrong.