Deduplication

What is deduplication?

Deduplication is the process of finding records that refer to the same real-world thing and merging or removing the unnecessary copies. One customer appears three times in the CRM. One supplier has two spellings. One product has two item numbers. The data contains several records for one entity.

Duplicates appear naturally. Colleagues create the same customer in different ways. An import adds records that already existed. A migration combines systems with different identifiers. If nobody cleans this up, reports count too many customers and people receive the same mailing twice.

In data quality, deduplication is mainly about uniqueness: does each customer, product, supplier, or employee appear once? In storage systems, the word can also mean storing identical blocks only once to save disk space. That is a different meaning from the data-quality work described here.

Exact matching versus fuzzy matching

Exact matching compares fields that must be identical: the same VAT number, email address, customer ID, phone number, or hash of selected fields. It is fast and reliable when the identifier exists and is clean. It misses duplicates with typos, spacing differences, old addresses, or missing fields.

Fuzzy matching compares similarity rather than equality. Jan Peeters and J. Peeters may be the same person. Diestersteenweg and Diesterstwg may be the same address. Algorithms such as Levenshtein and Jaro-Winkler score how similar strings are. A total match score then decides whether records are a match, a non-match, or something a person should review.

Fuzzy matching finds more real duplicates, but it also creates risk. A false positive can merge two different customers. That is often harder to fix than leaving a duplicate in place.

The steps in deduplication

1. Standardise
   Make fields comparable: case, punctuation, phone number format, address abbreviations, country names, company suffixes, and whitespace.

2. Block candidates
   Do not compare every record with every other record. Group likely candidates first, for example by postcode, domain, VAT country, or first letter of surname.

3. Match
   Apply exact or fuzzy rules inside each block. Use thresholds for match, no match, and human review.

4. Merge
   Build one trusted record from the duplicate group. Survivorship rules decide which value wins: most recent, most complete, most trusted source, verified value, or manually approved value.

5. Keep the link back
   Store which original records were merged. Without that lineage, undoing a bad merge becomes painful.

A customer example

Three records may describe the same customer:

• Jan Peeters, jan.peeters@example.com, 0470 12 34 56

• J. Peeters, no email, 0470123456

• Jan Peters, jan.peeters@example.com, no phone

Exact matching on name finds nothing. Exact matching on email finds two records. Fuzzy matching on name, standardised phone number, and address may show that all three belong together.

The golden record might take the full name from the first record, email confirmed by two records, and the most recent phone number. Good deduplication keeps useful fields instead of blindly deleting rows.

Deduplication, entity resolution, and MDM

Entity resolution is the broader discipline of deciding which records refer to the same real-world entity, often across many systems and sometimes across different entity types. Deduplication is a common case inside that wider problem.

Master data management uses deduplication as one building block. MDM tries to maintain trusted master records across systems, with ongoing matching, survivorship, stewardship, and publishing back to connected applications.

A one-off deduplication cleanup can reduce pain. MDM is the operating model that tries to prevent the duplicates from returning.

What to watch out for

Thresholds are business decisions
A stricter threshold leaves more duplicates. A looser threshold creates more false merges. Set thresholds based on the cost of each error.

Human review is part of the design
Uncertain matches need a queue and an owner, usually a data steward or domain expert.

Source fixes beat report fixes
If you deduplicate only in a dashboard, every other system still has the duplicate. Fix the source or master record when possible.

New duplicates keep arriving
Deduplication is rarely a one-time cleanup. Imports, forms, and migrations keep creating new candidates.

Privacy matters
Matching often uses personal data. Keep the process documented, access-controlled, and aligned with the purpose for which the data may be used.