A new system does not make your data cleaner. It only makes dirty data spread faster and become harder to fix, because the error now sits inside reports and documents. Data cleanup belongs before go-live day, and this is the seven-step order that has proven itself across rollouts.
Step 1 — Decide what the master copy is
For every kind of data there must be exactly one source treated as correct: where the customer list comes from, where the employee list comes from, where stock levels come from. It sounds obvious, yet this is the most skipped step — and skipping it means redoing everything after it.
Write a short table: data type — source — responsible person. That same table is the foundation for designing permissions later.
Step 2 — Fix the coding rules
Customer codes, product codes and project codes need one consistent rule before anything is loaded. A few practical principles:
- A code must not carry information that can change. A code containing a region will be wrong the moment the customer relocates.
- Fixed length, and no characters that are easy to confuse between letters and digits.
- Never reuse the code of a retired record — history will get mixed together.
Step 3 — Merge duplicates
Duplicates come from people creating new records instead of searching first. How to handle them:
- Match on the field least likely to change: tax number, phone number, email — not on the name.
- When merging, keep the record with the longest transaction history and move the remaining transactions onto it.
- Keep the "old code — new code" mapping. Six months later someone will ask, and nobody will remember.
Step 4 — Normalise formats
Dates, phone numbers, units of measure, currencies, capitalisation of names. Three of these cause the most trouble in practice:
- Units of measure: case, box, piece — each needs an explicit conversion factor, otherwise stock will never reconcile.
- Currency and exchange rate: store the original amount and the rate applied, not only the converted figure.
- Trailing spaces and invisible characters when data comes from spreadsheets — the classic reason automated reconciliation reports a mismatch.
Step 5 — Decide how much history to carry
Not all history is worth migrating. A sensible split:
- Opening balances and open items (receivables, undelivered orders, live contracts): mandatory, and they must match exactly.
- Recent transaction history: migrate if you need year-on-year comparison reports.
- Older history: leave it in the old system as read-only, or export it to an archive. Forcing every year of history into the new system usually costs more than it returns.
Step 6 — Trial run and reconcile
Load into a test environment, then reconcile three groups of numbers against the old system: record counts, total values, and a random sample checked field by field. If they differ, fix the migration script and run it again from scratch — do not patch the loaded data by hand, because the real run will lose those edits.
The rule: every cleanup action must be repeatable by script. A manual fix happens once, but a migration always runs at least twice.
Step 7 — Freeze and cut over
Before go-live, pick a freeze point: from that hour nobody enters anything into the old system. Run the final migration, reconcile again, then open the new system. The freeze window should fall on a weekend or a quiet period, and every department must be told in advance.
After go-live
Keep the old system read-only for a while, long enough to look things up during disputes. At the same time, put rules in place to stop the data getting dirty again: force a search before creating a record, check duplicates on save, and give edit rights on master data to exactly one person. Cleaning data is a one-off job; keeping it clean is forever.
