1. Why migration fails on data, not software
When a new management system project runs late, the cause is almost always the data. Duplicate master records, inconsistent codes, addresses written in different ways and fields used for purposes other than intended only come to light when the new system insists on order. The software may be well designed and still produce wrong reports if it receives dirty data. The first symptom is often a report that does not add up.
For this reason, cleaning must be planned as a piece of work in its own right, with timing and owners. It is not a task to squeeze into the weekend before the switch. People who know the data, meaning administrative and sales staff, must take part, because only they know which duplicates are really the same customer and which records are still in use. Those who know the data should flag anomalies straight away.
2. Inventory of sources and data
List all the sources from which data comes: the current management system, spreadsheets, email used as an archive, accounting and warehouse systems. For each one, note what it contains, who updates it and how reliable it is. Many companies discover only at this stage that an important piece of information lives in a personal file. The result is an ordered list.
Also decide what not to migrate. Very old histories, master records with no activity for a long time and data superseded by other documents can stay in a read-only archive. The less data migrates, the less cleaning is needed and the less surface there is to control. Write the decision down and have the owner approve it. Record the reasons for exclusion too, which are useful if challenged.
3. Profiling data before cleaning it
Profiling means measuring the actual state of the data before acting: how many mandatory fields are empty, which date formats coexist, which codes break the rules, which values appear more than once in fields that should be unique. A profile produced with a simple extract and a few checks is enough to estimate the work and choose priorities. No dedicated software is needed.
The result is a list of problems ranked by impact. An invalid tax code or VAT number often blocks invoicing, so it comes before an incomplete address. This priority should be discussed with the people who use the data every day, because the value of a problem depends on what stops when it exists. A concrete case is worth more than an abstract analysis.
4. Deduplicating and normalising master records
Deduplication is the most delicate step. Two records with the same name may be different people, while two different names may refer to the same company. Do not rely on blind automatic matching: define matching rules, such as VAT number or tax code, and review uncertain cases by hand. The time spent here avoids cleaning the same record twice.
Normalisation makes formats uniform: capitalisation, spaces, telephone prefixes, postcodes and names of provinces or countries in one consistent form. Choose a reference form and document it in the data dictionary, so the new system does not receive values it does not recognise. The same applies to dates, which must have one format throughout the file.
- Export the master records into a working copy, never from the live version.
- Apply validation rules to fields such as VAT number and tax code.
- Group possible duplicates using shared matching rules.
- Have uncertain cases reviewed by someone who knows the customer.
- Choose the main record and archive the others with a reference.
- Record every decision so it can be reproduced in the trial.
5. Field mapping and trial migration
Mapping links each source field to a target field, together with any transformation required. Some fields have no equivalent and must be decided case by case: add them to the new system, convert them to notes or drop them. The mapping should be approved as a document, not left to whoever runs the migration. Give particular attention to master records with several billing addresses. Verify those separately.
Run at least one trial migration on a copy and check the result with reconciliations: balance totals, number of active master records, a sample of records checked by hand. Every difference must be explained. A trial that produces surprises is a useful result, because surprises cost less in a trial than at the real switchover. Record every change in the mapping, otherwise the trial cannot be repeated.
| Check | What it compares | When it is needed |
|---|---|---|
| Record count | Active master records before and after migration | After every trial |
| Financial totals | Open balances and item amounts | Before and after the switchover |
| Manual sample | Records chosen at random and checked against the source | At the end of each trial |
| Mandatory fields | Empty values left after transformation | After cleaning |
6. Common mistakes to avoid
The mistakes that recur most often concern method more than technique: cleaning data in the wrong copy, trusting an export without checking it, trying everything in one go. Here are the most frequent ones to keep under control during the project. They often appear in projects with data accumulated over many years and entered by several people. Recognising them in advance reduces emergency fixes.
Another mistake is thinking cleaning ends at go-live. After release, entry rules and periodic checks are needed, otherwise the data gets dirty again within a few months. The rules should be written into the specifications of the new system, not left to the goodwill of whoever enters the data. Ask the provider which validation checks are available at import. Document the answer.
- Cleaning the live version directly, losing traceability.
- Merging master records without shared matching rules.
- Migrating historical data that nobody will use, increasing the checking workload.
- Not involving the people who know the data in decisions on duplicates.
- Declaring the migration successful without a reconciliation.
7. How to verify that the migration has succeeded
The switchover has succeeded when the reconciliation checks come back with no unexplained differences, staff can carry out their usual tasks on the new system, and the main reports produce the same results as the previous system, except for documented differences. A switchover is not declared successful just because the system has started up. The adviser supporting you on legal matters must be able to confirm this step.
Keep the archive of the previous system in read-only mode for an agreed period, and define who may request access to it. This is also the point at which you check with your compliance adviser which data must remain available and for how long, because retention obligations do not depend on the software you use. A concrete test is worth more than any statement from the provider.
8. How to start this week
Prepare the source inventory: a sheet with the source name, content, owner and estimated reliability. Ask each owner where the information lives that cannot be found anywhere else. No formal project is needed for this step, and it already reveals how much cleaning work lies ahead. No formal project is needed: a shared sheet with a few columns is enough to start.
Then extract a sample of master records and test the validation rules on it. The results will tell you how clean the data is and how much cleaning it will require. With this picture you can ask the supplier of the new system how it wants to receive the data and which checks it provides at import. The picture that emerges is the basis for the field mapping.
Frequently Asked Questions
Can I migrate all my history?
You can, but it rarely makes sense. History needed for retention obligations, accounting and warranties must be migrated or archived. The rest can stay in a read-only archive. Every migrated record has to be cleaned, checked and maintained, so the volume to migrate carries a real cost. Decide by data category, with the owner and with an adviser where legal obligations apply.
Who should clean the data?
The decision about the data belongs to the people who use and know it, such as administration, accounting or the sales team. The technical execution can be carried out by the provider or an external technician, but under written rules and sample reviews. Technical input alone is not enough without people who know the customers.
Is a duplicate always the same person or company?
No. Two records with the same name may refer to different people or companies, while different names may indicate the same party. Automatic rules help find candidates, but the final decision on uncertain cases must be taken by someone who knows the customer. Record the choice so it can be reviewed later.
Do I need consent to migrate personal data?
It depends on the legal basis on which the data was collected and the purposes of the processing. Migrating data to a new system is processing in its own right, so it should be assessed with the data protection adviser.
When can I switch off the old management system?
When the migration has been verified, staff have worked on the new system for an agreed period, archive data is accessible and the rollback procedure is no longer needed. Never switch off the previous system before the final reconciliations are complete.