Cleaning Historical Data Before a System Migration
Moving fifteen years of records into a new system just in case is a frequent reason migrations run late. Decide what to bring, clean it properly and archive the rest.

The old system holds fifteen years of transactions, thousands of customers who have not bought anything in years, and products nobody remembers stocking. The natural instinct is to move everything into the new system “just in case”. That instinct is usually wrong. Acting on it is a frequent reason migrations run late and new systems start life full of old problems.
Decide how much history you really need
Data cleaning before migration starts with a simpler question: what is worth migrating at all? This is a business decision, not a technical one, and it should be made by finance and operations together with the sponsor. Every record you move must be mapped, cleaned, loaded and checked. Records you leave behind can be archived safely and still consulted when needed.
A common approach separates data into three groups:
- Master data in active use: customers, suppliers, products, employees and accounts that are still relevant. These must be migrated and cleaned carefully.
- Open transactions and balances: unpaid invoices, open orders, stock on hand, fixed assets and opening balances. The new system needs these to operate from day one.
- Closed history: past invoices, orders and journals. These are often migrated as monthly totals, or only for a limited recent period, with the full detail kept in an accessible archive.
| Data type | Typical approach | Points to check |
|---|---|---|
| Active customers and suppliers | Migrate after cleaning and deduplication | Define “active”, for example a transaction in the last two years |
| Products | Migrate active and stocked items only | Items with stock or open orders must be included |
| Open receivables and payables | Migrate each open invoice and bill individually | Must reconcile to the general ledger at cut-off |
| General ledger history | Opening balances plus monthly totals for comparison periods | Confirm what your auditors and finance team need |
| Detailed past transactions | Archive in a searchable, read-only form | Retention requirements and who needs access |
Tax and legal retention rules apply to the archive regardless of what you migrate, so confirm the required retention periods with your auditor or tax adviser before switching anything off.
The problems you will find
Historical data in long-running systems tends to carry the same kinds of issues, and it helps to know what to look for:
- Duplicates. The same customer entered several times with slightly different names, phone numbers or spellings in Arabic and English.
- Inactive and test records. Customers who closed years ago, discontinued products, and records created for training or testing.
- Inconsistent coding. Categories, cities or units of measure that changed over the years, so the same thing appears under several codes.
- Free text used for structured data. Credit terms, contact names or delivery instructions typed into notes rather than proper fields.
- Balances that do not reconcile. Customer and supplier account totals that differ from the general ledger because of old manual adjustments.
- Missing mandatory data. Tax numbers, addresses or product classifications that the new system requires but the old one did not.
None of these problems is a sign that anyone did a bad job. They are the normal residue of years of real work in a system that allowed shortcuts. The point is to deal with them deliberately, before go-live, rather than discovering them one by one afterwards.
A scenario: a pharmacy chain replacing its system
Consider a pharmacy chain with branches across Jordan that has used the same system for over a decade. The product file holds tens of thousands of items, many of them discontinued medicines and duplicate entries created by different branches. The customer file includes insurance companies, corporate accounts and many walk-in customers recorded inconsistently.
The team agrees early that only items stocked or sold in the last two years will migrate, with the rest archived. Duplicate products are matched using barcodes and supplier codes, with pharmacists reviewing uncertain matches. Corporate and insurance customers are cleaned and verified one by one, because their contracts and balances matter. Walk-in customers are not migrated individually.
Opening stock is loaded from a physical count close to go-live rather than from the old system’s balances, which were known to be unreliable. History stays available in a read-only copy of the old database for reference and audit, with access limited to the people who need it.
A practical cleaning routine
- Run a data health check. Run counts, find blanks, spot duplicates and list unusual values before deciding anything.
- Agree cleaning rules with the business. What counts as active, how duplicates are merged and which record wins. Finance and operations should own these rules, not IT (information technology) alone.
- Clean in the source where possible. Fixing records in the old system keeps operations consistent until cut-off and makes repeated test migrations easier.
- Automate the repeatable fixes. Standardising formats, mapping old codes to new ones and flagging duplicates can be scripted so they run the same way on every trial load.
- Assign people to the judgement calls. Uncertain duplicate matches and unclear balances need someone who knows the business to decide.
- Run trial migrations and reconcile. Load into a test environment, compare counts and totals with the source, and repeat until the results are stable.
- Freeze and sign off. Shortly before cut-off, stop creating new master records in the old system where you can, run the final load, and have finance sign off the reconciled balances.
Starting the new system with clean foundations
A new system loaded with old problems quickly loses the trust of its users. Being selective about history and cleaning the master data you keep gives the new platform a fair start. Data architecture and migration are a central part of our Enterprise Systems service, and the repeatable cleaning steps are often automated so every trial load behaves the same way. The time spent deciding what to leave behind is usually some of the best invested in the whole migration.



