What deduplication really means (and why fuzzy matching is not enough)

Deduplication is usually sold as a one-click job. Every CRM has a "manage duplicates" button, every data tool has a merge feature, and the implicit promise is the same: press it, and your database is clean.

In reality, the pressing is the last two percent of the work. Before that comes a series of judgment calls that no algorithm can make for you: what counts as "the same" record, which record survives the merge, which field values win when the survivors disagree, and what happens to the emails, calls, deals, and tickets attached to the records that lose. Get those calls wrong and the merge button doesn't clean your data - it destroys evidence, at scale, irreversibly.

Here's how we think about it, and why the matching algorithm is the least interesting part.

Not one problem, but three

"We have duplicates" describes at least three different problems, and they need different treatment.

Identical rows are the trivial case: same email, same name, same company, usually created by a double import or an integration retrying a failed sync. Exact matching catches these, and merging them is genuinely low-risk. If this were all deduplication meant, the one-click pitch would be honest.

Near-misses are the real workload: "Jon Smith" and "John Smyth" at the same domain, "Acme Inc." and "Acme Incorporated", the same company with and without punctuation, a mobile number formatted three ways. Exact matching is blind here - matching only on identical field values misses an estimated 30-40% of real duplicates, which is why every serious tool adds fuzzy techniques: edit distance, phonetic codes, token matching.

Entity ambiguity is the hard case, and it's where fuzzy matching stops being the answer and starts being the risk. A trading name versus a registered name. "HSBC" the group versus "HSBC UK" the subsidiary you actually have a contract with. Two branches of the same franchise. A father and son with the same name at the same family firm. These records look more similar than many true duplicates - and merging them is not cleanup, it's data corruption. No string-similarity score can tell you whether two legal entities are the same business relationship. That's a question about your business, not about your text.

This is the core argument: fuzzy matching widens the net, but it cannot decide what's actually in it. Similarity is evidence, not identity.

The scale of the mess (and what it costs)

If you've never run an active data quality programme, expect duplication rates of 10-30% - that range is depressingly consistent across CRMs and industries. The cost is not abstract: one commonly cited estimate puts it around $96 per duplicate record once you account for identification, review, wasted outreach, and merge time. The operational symptoms are familiar to anyone in RevOps: inflated pipeline reports, a contact's history split across two records, and the same lead routed to two reps who both believe they own it.

So the problem is worth solving. It's just not worth solving carelessly - because a bad merge is worse than a duplicate. A duplicate is annoying and visible; a wrong merge quietly welds two customers' histories together and there is no unmerge button that restores what you had.

The judgment calls the button doesn't make

1. Which record is the golden one?

When a match group has three records, one must become the surviving "golden" record. The common defaults - keep the oldest, keep the most recently updated - are both defensible and both wrong in different situations. The oldest record carries the original attribution and consent history; the newest carries the current job title and phone number. In practice the sensible policy is rule-based survivorship built on recency, completeness, and source priority - but which of those wins is a policy decision someone accountable has to make, per object, before anything merges.

2. Which fields win?

Golden-record selection isn't all-or-nothing. The better approach is selective survivorship: decide winners field by field - most recent value for job title and phone, most complete value for address, and source-priority for anything fed by integrations (the ERP beats the enrichment tool, which beats a three-year-old form fill; manually entered sales notes beat everything). Original create date and first-touch attribution should survive from the earliest record even when everything else comes from the latest one - otherwise your funnel reporting quietly rewrites history.

3. What happens to the losers' baggage?

This is the question that separates real deduplication from row deletion. Every losing record carries attachments: emails, call logs, meeting notes, deal associations, tickets, list memberships, workflow enrolment history, legal consent records. The whole point of merging is to end up with a single trusted record without losing history - so every activity must be re-parented to the survivor, associations must be preserved without creating duplicates of those, and consent status must merge conservatively (an opt-out on either record means the golden record is opted out; anything else is a compliance incident waiting to happen). Different platforms handle this differently, and some handle parts of it badly - which is exactly the kind of thing you want to know from a test batch, not from production.

How we actually run it

Our process combines both matching families, because each covers the other's blind spot. Deterministic matching on exact identifiers - email address, domain, company registration number, phone after normalisation - produces matches you can trust enough to automate. Fuzzy matching on names, trading names, and addresses produces candidates, each with a similarity score, and candidates get triaged: high-confidence groups can be auto-merged under the agreed survivorship rules, mid-confidence groups go to human review, low-confidence groups stay untouched. This layered approach - exact rules first, probabilistic scoring for the remainder, human judgment for the grey zone - is the standard architecture in entity resolution for a reason: it puts automation where it's safe and people where they're needed.

But the step that actually prevents disasters isn't algorithmic. We always show the rules to the people who own the data before we press go. That means a written matching spec (what makes two records a candidate pair), the survivorship policy (who wins, field by field), and a sample of real match groups from their data - including the awkward ones. Every time, the data owners catch things no algorithm could: those two "duplicate" accounts are a parent company and its subsidiary billed separately; that contact appears twice because he genuinely holds roles at two group companies; that batch of near-identical companies is a franchise network and must never be merged.

Ten minutes of review by someone who knows the business is worth more than any threshold tuning we can do. Then - and only then - we run a test batch, verify the activity re-parenting survived intact, and press go on the rest, with a full before-and-after export as the undo button the platform doesn't provide.

The takeaway

Deduplication is not a matching problem with a merge step bolted on. It's a decision-making exercise - about identity, about survivorship, about history - that happens to use matching algorithms as its evidence-gathering layer. Fuzzy matching is necessary: without it you miss a third or more of your real duplicates. But it is nowhere near sufficient, because the expensive mistakes aren't the duplicates you miss - they're the distinct records you wrongly merge.

One-click deduplication is a real product. It's just not a real process. The rules deserve daylight before they touch your data, and the people who own that data deserve to see them first.