Duplicate Detection: From Exact Matches to Fuzzy Record Linkage

"Jon Smith" vs. "John Smyth" at the same address. Same customer or two? Exact matching says two — and your customer count, revenue attribution, and email targeting inherit the error. Duplicates are the quietest data-quality failure: nothing errors, nothing looks broken, every count is just slightly wrong in the direction that flatters growth. Detection is a ladder with four rungs, and most teams stop one rung too early.

Rung 1–2: Exact and Normalized Matching

Rung 1 is exact matching on a key: same email, same order ID, same hash of the row. It catches true copy-paste duplicates — double-submitted forms, re-imported files — and it should run as an automated check on every import. Rung 2 normalizes before comparing: lowercase, trim whitespace, strip punctuation, unify "Street" vs "St". This catches the duplicates that differ only in formatting, which in messy CSV imports are often the majority. Both rungs are cheap, deterministic, and safe to auto-merge.

Rung 3: Fuzzy Similarity

Typos, transposed fields, and "Bob" vs. "Robert" need similarity scoring. The workhorse is character-level distance (Levenshtein and its cousins) on names, combined with exact or close agreement on strong fields (postcode, phone, date of birth). Score pairs, rank by score, and set two thresholds, not one:

• Above the high threshold: auto-merge. Reserved for near-certain pairs (same phone + same postcode + similar name).
• Between thresholds: human review queue. This band is where the real work happens — sample it regularly to recalibrate.
• Below the low threshold: leave alone. Chasing low-score pairs burns review time on false positives.

Never compare all pairs against all pairs: a million rows means a trillion comparisons. Use blocking keys — compare only records sharing a postcode prefix, a phone-number stem, or a name fingerprint — to cut the candidate space by orders of magnitude before scoring.

Rung 4: Surviving Your Own Merges

Merging is destructive, so engineer it like one. Keep a merge log (which IDs merged into which survivor, when, by what rule) and never delete the losers — flag them. Prefer merging attributes over picking a "winning row": the survivor should carry the best-known email from one record and the best-known address from another. And re-run detection after every bulk import, because duplicates arrive in shipments, not drips.

Measure the program like a classifier: precision (of merged pairs, how many were truly the same entity?) via review samples, and recall via planted duplicates. Report both alongside your quality scorecard — dedup rate over time is one of the few quality metrics that visibly, defensibly improves. In KPI Master, uniqueness checks per column surface the exact-match layer automatically; the fuzzy rungs above are the scheduled job that keeps the customer count honest.