Failure Patterns and Official Sources
ERP data migration mistakes: cleaning, history and opening balances
The data migration mistakes that derail ERP go-lives, how much history to move, how to reconcile opening balances and sales-tax accounts, and what migration costs.
Data migration is where many ERP projects lose weeks. The common mistakes are moving dirty data unchanged, migrating too much history, leaving cleaning to the partner, running too few test loads, and not reconciling opening balances (including GST/HST and QST accounts) before go-live. Each is avoidable if you decide early what to move, who cleans it, how many rehearsals you need and how you will prove the numbers match. Your team usually knows the data best, so plan for internal time, not only partner hours.
This guide is part of the failure patterns and official sources pillar. Amounts are illustrative.
Mistake 1: moving dirty data as-is
Duplicates and inconsistencies that you work around in an old system become blocking errors in a new one, because ERP processes rely on clean master data. Typical problems:
- the same customer entered several times with different spellings or addresses;
- items with different units of measure for purchasing and selling, or no standard cost;
- inactive vendors and items mixed with active ones;
- customers in Quebec without correct tax codes for QST, or exempt customers without supporting information;
- missing or invalid GST/HST registration numbers on vendor records, which you need to support input tax credits.
Countermeasure: start a cleaning project before implementation begins. Archive inactive records, merge duplicates, standardise units and naming, and fill required fields. Name an owner for each master data type (customers, vendors, items, chart of accounts).
Mistake 2: migrating every transaction since the business started
More history means more mapping, more test-load time and more things to reconcile. Many businesses move:
| Data | Common choice | Why |
|---|---|---|
| Customers, vendors, items | Active records only | Old records add noise |
| Open sales and purchase orders | All open items at cut-over | Needed to keep operating |
| Open receivables and payables | Open invoices at cut-over | Needed for collections and payments |
| Inventory | Quantities and costs at cut-over | Starting point for costing |
| General ledger | Opening balances, plus monthly summarised balances for one or two prior years | Comparative reporting without moving every transaction |
| Detailed transaction history | Left in the old system or an archive | Access for audits without migration cost |
The old system's detail still matters: the CRA generally expects records to be kept for at least six years from the end of the last tax year they relate to. Plan how you will keep that data readable after you stop paying for the old system. See CRA record-keeping and cloud ERP.
Mistake 3: leaving cleaning to the partner
Partners can provide tools and templates, but they rarely know which of two customer records is correct. If the statement of work assumes you clean the data, that is internal time you must budget. If you ask the partner to do it, expect more hours and more questions. Either way, make it explicit in the implementation cost breakdown.
Mistake 4: too few test loads
A single trial migration rarely works. Plan at least two or three full test loads before the final cut-over, each followed by reconciliation and fixes. Time each load so you know how long the final cut-over window must be.
Mistake 5: not reconciling before go-live
Reconcile every test load and the final load:
- Trial balance in the new system equals the old system at cut-over, account by account.
- Receivables and payables sub-ledgers equal their control accounts.
- Inventory quantity and value match the count and the ledger.
- Sales-tax accounts: GST/HST collected and paid, and QST, match the old system and your last filed return period. Mistakes here show up on the next return.
- Bank balances match statements.
Have the controller or your accountant sign off each reconciliation.
Migration cost (illustrative)
| Line (illustrative) | Low | High |
|---|---|---|
| Internal cleaning (hours × loaded rate) | 80 h × $40 = $3,200 | 400 h × $45 = $18,000 |
| Partner mapping and load scripts | $3,000 | $20,000 |
| Each additional test load and reconciliation | $1,000 | $5,000 |
| Archive of old system data (one-time + yearly) | $500 | $8,000 |
Add the total to the fixed field of the TCO estimator. If data quality is poor, plan towards the higher end and add contingency; see budget and schedule overruns.
Personal information in migration files
Migration files often contain customer contacts, employee details and payment information. Limit who receives them, use secure transfer, delete working copies after go-live, and do not email spreadsheets of personal information. If files travel outside Quebec for an organisation subject to Quebec's private-sector privacy act, consider whether the privacy impact assessment covers that transfer. See PIPEDA, Quebec Law 25 and data residency.
FAQ
How many years of history should we migrate?
Often only open items and opening balances, plus summarised history for comparisons. Keep the detail accessible in an archive for record-keeping.
Can migration happen mid-year?
Yes, but it is simpler at a fiscal year or quarter start. Mid-year migration means more balances to reconcile and payroll year-to-date figures to carry over.
Who signs off the migrated data?
The business, usually the controller for financial data and process owners for master data. The partner should not sign off its own work.
What happens to the old system?
Keep read-only access or a full archive for as long as records must be retained, and budget for it.
Sources
- Canada Revenue Agency — keeping records and retention periods
- Revenu Québec — record-keeping and QST
- Office of the Privacy Commissioner of Canada — safeguarding personal information during transfers
