Define the canonical record
Specify valid date formats, address components, identifiers and authoritative fields. Establish precedence between systems, including how to handle a newer blank value or a historical record that must remain separate.
Review merges as business changes
Use agreed match keys to flag candidates. Keep uncertain pairs unmerged, record the reason and identify the reviewer. Preserve original identifiers and relationships so an approved merge can be traced and, where feasible, reversed.
Assess complexity before volume
A pilot should distinguish mechanical formatting from research and duplicate adjudication. Price depends on rule maturity, source inconsistency, merge review and target-system updates; a row count without these details is insufficient.
Separate normalization, validation and identity decisions
Normalization changes representation: trimming spaces or applying an approved telephone format. Validation tests a value against a rule or reference. Deduplication decides whether records represent the same entity. Combining all three under “clean the database” hides the point where a mechanical operation becomes a business decision.
Write rule IDs that can appear in a change log. A rule needs its input conditions, transformation, exclusions and owner. Preserve the original snapshot and relationships before applying it. A valid-looking phone number is not proof of ownership, and a standardized address is not proof that two businesses should share one account.
Illustrative example: a four-record customer cleanup
A hypothetical client supplies records C101–C104 and approves rule N01 for outer whitespace, N02 for UK phone formatting where country is explicitly GB, and V01 for checking customer status against a supplied reference list. M01 only creates duplicate candidates when company identifier and billing address match; it never merges automatically. The operator logs each before-and-after value and produces one candidate pair, C101/C102.
The account owner confirms C101/C102 are the same legal customer and approves C101 as survivor under decision D17. The operator redirects approved contact relationships and preserves C102 as an alias according to the agreed target-system procedure. C103 and C104 retain separate identities despite similar names. Acceptance includes normalized data, D17, relationship reconciliation and an untouched source snapshot; it does not require merging every suspicious pair.
| Record / field | Before → after | Rule / approval |
|---|---|---|
| C101 / name | “ Acorn Ltd ” → “Acorn Ltd” | N01; mechanical trim allowed |
| C102 / phone | 020 7946 0123 → +44 20 7946 0123 | N02; country GB explicitly supplied |
| C103 / status | “pending?” → unchanged, validation flag | V01; not in approved values |
| C101 + C102 / identity | Two records → survivor C101 with alias C102 | M01 candidate; owner decision D17 |
| C103 + C104 / identity | Similar names → two records retained | No matching company identifier; no merge |
Protect relationships when a merge is approved
A merge can affect orders, balances, consent records and account ownership. Ask which fields survive when values conflict, whether historical transactions may move, and which downstream systems receive the new identity map. “Keep the newest value” is unsafe when the newest record contains a blank or belongs to another business unit.
Test a small approved merge in a controlled destination before a broad update. Record source IDs, survivor ID, affected relationship counts and the available recovery method. If the target system cannot reverse a merge, approval must cover that limitation before execution rather than discovering it during correction.
Measure changes that improve use, not just change volume
A cleanup report should distinguish transformed values, validation failures, duplicate candidates, approved merges and unresolved decisions. Report denominators: ten approved merges among twelve reviewed candidates is different from ten merges across an unreviewed database. Do not describe unreviewed records as clean.
Define a release condition around the buyer’s intended use, such as importing only records with a valid customer identifier while holding uncertain status values. Future updates need the same rule version or an explicit migration. Otherwise the next export can reintroduce the inconsistency that the project removed.