The same customer, three times

Where duplicates come from

Three sources, and they need different answers. A retry that timed out after the write succeeded creates an exact duplicate, and the fix is idempotency at the write, covered later in this block. Two systems describing the same real thing (the CRM’s “Acme Ltd” and the accounts system’s “ACME LIMITED”) need matching. And a human typing the same customer in twice on a Tuesday needs a constraint at the point of entry, which is a product decision rather than a data one.

Know which you have before choosing a technique, because a fuzzy matcher pointed at a retry bug is an expensive way to not fix the bug.

Normalise a key before you reach for similarity

The great majority of real deduplication is solved without any matching algorithm at all. Pick a field that identifies the thing, normalise it hard, and group on it.

• Email: lowercase, trim. For Gmail specifically, dots in the local part are ignored and +tag suffixes are aliases: worth handling if consumer emails matter to you, and worth not generalising to other providers, where those rules do not hold.
• Phone: normalise to E.164 with the country code (libphonenumber does this properly and free; hand-rolled regex does not survive contact with real data).
• Company: strip legal suffixes, punctuation and case: acme from Acme Ltd., ACME LIMITED, Acme Limited.
• Tax identifier, registration number, IBAN, national ID where you have one and are allowed to use it. These are the strongest keys available and they end the discussion.

Do this first. It is cheap, exact, and explains itself in an audit, which fuzzy matching never quite does.

When there is no key: block, then compare

Comparing every record with every other is quadratic. Fifty thousand records is about 1.25 billion pairs, which is a job that runs overnight and gets skipped.

Blocking fixes it. Only compare records that share a cheap coarse key: same postcode, same first three letters of the surname, same first token of the company name, same year of birth. Fifty thousand records split into two thousand blocks of twenty-five is roughly six hundred thousand comparisons: seconds instead of hours.

Blocking trades recall for speed: a pair that disagrees on the blocking key is never compared, so choose a key that is unlikely to be wrong on both records. Run two or three different blocking passes and take the union, which recovers most of what any single pass misses.

Within a block, score with a string similarity. Jaro-Winkler is well suited to names because it weights agreement at the start of the string. Levenshtein distance suits short codes and typos. A token-set ratio handles reordering, so that “Kumar Rajesh” and “Rajesh Kumar” score high. RapidFuzz in Python is fast and free; the recordlinkage and dedupe libraries build the whole pipeline if you want blocking and scoring together.

Choose the threshold by asymmetry, not by the F1 score

Two records left unmerged means somebody sees a customer twice: irritating, visible, fixable in ten seconds. Two different people merged into one record means one person can see the other’s orders, invoices and address. That is a data-protection incident, it is discovered late, and unpicking a merge after downstream systems have copied the merged record is hard.

So the policy is three bands, not one threshold:

1. Above a high score, or an exact key match: merge automatically.
2. In the middle band: create a review task with both records side by side. A person decides in seconds.
3. Below: leave them alone and do not record a suspicion.

And make every merge reversible. Never delete the losing record: write merged_into: <id> on it, keep it, and have your reads follow the pointer. An undo that is a single UPDATE is worth building on the first day.

Embeddings are the wrong tool for identity

This is worth stating plainly because it is a common and expensive mistake. An embedding places text near other text that means something similar. “Acme Ltd, Bristol” and “Acme Ltd, Leeds” are almost the same sentence, so their vectors sit almost on top of each other, and they are two different companies. Meanwhile “Ranjit Singh” and “R. Singh” may sit further apart than either does from “Rajesh Singh”.

The mechanism is straightforward: identity is decided by a handful of discriminating tokens (a branch, a house number, a middle initial) and those tokens contribute very little to a vector that is summarising overall meaning. Semantic similarity is designed to be robust to exactly the small differences that identity depends on.

Use embeddings to generate candidates for messy free-text names, then decide with exact fields: postcode, registration number, email domain. Never let a cosine score alone authorise a merge.

Measure it like anything else

Label two hundred pairs by hand: same or not. Report precision and recall separately, and treat precision on auto-merges as the number that must be near perfect. Re-run when the source data changes. A matcher tuned on last year’s records will drift as a new source arrives with different formatting conventions, and nobody will notice until two accounts are joined that should never have met.