Why duplicates happen and why they matter
Duplicates accumulate through ordinary operation: a customer orders as a guest then creates an account, a company is entered with and without 'Ltd', a CRM import runs twice, two branches each add the same account.
The consequences run through everything downstream - overstated customer counts, credit limits applied per record rather than per customer, marketing that contacts the same person three times, and any model trained on customer history learning from fragments.
Comparing records without a shared key
Probabilistic matching compares pairs across several fields and combines the evidence. Each field contributes according to how discriminating it is: matching on an unusual surname is far stronger evidence than matching on a common first name.
- Normalise first - case, punctuation, company suffixes, address formats, phone number formats.
- Use fuzzy comparison per field - edit distance for names, specialised comparison for addresses and dates.
- Weight by discriminating power - a shared postcode in a dense city means less than one in a rural area.
- Handle missing values explicitly - a blank field is not a mismatch, and treating it as one loses real matches.
The pairwise comparison is expensive on a large file, so blocking - only comparing records that share something coarse such as postcode area or first initial - is what makes it practical.
The threshold is a business decision
Any matching system produces a score, and someone must decide where to cut. This is not a technical choice, because the two error types have very different costs.
| Error | What happens | Typical severity |
|---|---|---|
| False merge | Two different customers combined | Severe - data protection exposure, wrong balances, wrong contact |
| Missed match | Duplicates remain | Moderate - inefficiency, overstated counts |
Because a false merge can expose one customer's data to another, most businesses should set the automatic threshold conservatively and route the middle band to human review rather than merging aggressively.
Merging without losing information
A merge should never be destructive. Keep both source records, link them to a surviving master, and record which fields came from where and when.
- Create a master record referencing the sources rather than overwriting.
- Define field survivorship rules - most recent verified address, richest contact details - and write them down.
- Retain an audit trail sufficient to reverse a merge, because some will be wrong.
- Provide staff with an unmerge route that does not require a developer.
The reversibility requirement is not optional in any business handling personal data, and building it in afterwards is considerably harder.
Stopping duplicates at the door
De-duplication is a cleanup; prevention is the fix. Checking for likely matches at the point of entry - and showing the user 'is this the same customer?' - costs one screen and prevents the problem recurring.
Cleaning duplicates without fixing entry is mopping with the tap running.