Duplicates are near-universal
Every business with more than a few years of records has duplicate customers, suppliers or products. They accumulate through data entry, imports, mergers and systems that do not check.
Duplicate master records are the single most common blocker in integration and reporting projects. Validation against a master with four versions of one customer produces four different answers.
Two stages
- Exact matching on identifiers — registration numbers, email addresses, codes
- Fuzzy matching on names, addresses and other text, scored and reviewed
The first is safe to act on automatically. The second is not, and should always produce a review queue rather than a merge.
What makes fuzzy matching work
- Normalise first — case, punctuation, common abbreviations, legal suffixes
- Compare several fields, not one
- Weight the fields by how distinctive they are
- Block on something cheap first, so you are not comparing everything to everything
- Score, then threshold, then review
Merging is the risky part
| Consideration | Why |
|---|---|
| Which record survives | Usually the one with most history |
| What happens to related records | They must all repoint |
| What if it was wrong | Merges are hard to reverse — keep an audit |
| Who approves | A person, for anything fuzzy |
| What about external systems | They may hold the old identifier |
Keep a record of every merge and what it combined. Undoing a wrong merge without that record is close to impossible.
Prevent recurrence
Deduplication is a one-off exercise unless the creation process is changed. A check at the point of creation — does something similar already exist? — prevents most of it.
Otherwise you will be running the same exercise again in two years.