The frightening part of a system change is often not the new software. It is the moment somebody asks for the customer file, the product file and the price lists, and the business discovers that each one exists in several versions with different owners.
Data migration is not mainly a file-transfer exercise. The hard work is deciding which records are authoritative, removing identity problems, rebuilding the relationships between those records and proving the commercial rules before the final cutover. Moving dirty rows quickly just gives the new system a cleaner-looking version of the old ambiguity.
Migration is a sequence of business sign-offs
Before cleaning anything, decide which source is the current master for each important data set. Customers may live in the accounting system, delivery points in a route spreadsheet, prices in a sales workbook and stock in a warehouse system. If the project does not name the owner of each set, two teams can clean different versions at the same time.
| Data set | Decide before migration | Typical relationship to preserve |
|---|---|---|
| Customers | Which account record is authoritative? | Delivery points, credit, pricing, statements. |
| Products | Which code, description, UoM and packaging are current? | Price lists, stock, batches, supplier records. |
| Pricing | Which agreements are current and who owns exceptions? | Customer/group assignments, ranges, promotions. |
| Stock | What opening quantity belongs to which location? | Warehouse, van/location, batch/condition where used. |
| Routes | Which delivery points and patterns are active? | Schedules, windows, drivers/vehicles where assigned. |
Write the decision down. During cutover, the team needs to know which source wins if two records disagree.
Own the source
Choose the current master for each critical data set.
Clean identity and relationships
Fix duplicates, codes and the links between customers, delivery points, products and rules.
Prove a varied sample
Test pricing, credit and operational use before the full load.
Freeze and cut over
Control last changes and final extracts.
Business sign-off
Sales, operations, warehouse and finance accept what they will use.
Gate 1: clean identity before detail
Duplicates are easiest to fix before relationships are rebuilt. Start with stable identifiers: customer/account code, delivery-point identity, product code, supplier code, route or branch. Then find the records that represent the same real-world thing under different spellings or historical codes.
Pay particular attention to customers with multiple delivery points. A single account with three sites is not the same structure as three unrelated customers, even if the legacy spreadsheet treated each address as a separate row. The new data model should reflect the relationship the business actually manages.
Gate 2: rebuild relationships, not just rows
A migration can pass a row-count check and still fail operationally because the links are wrong. The customer moved, but their delivery point did not. The product moved, but the price list assignment did not. The standing order exists, but it points to an inactive account. Opening stock is correct in total but assigned to the wrong location.
For each data set, ask what must still be connected after migration. Customers need delivery points, pricing and credit rules. Products need units of measure, packaging and any batch/expiry setup that applies. Routes need active delivery points and schedules. Finance integrations need code mappings that agree with the ledger.
This is where the business knowledge matters more than the import tool. A file cannot decide whether two similar customer names should be merged or whether an old price exception is still commercially valid.
Gate 3: prove commercial rules on a varied sample
Commercial rules are a high-risk place to discover migration errors because the problem appears as a live order or invoice. Take a customer sample and prove it before the full load. Place the same test order you understand in the old process and in the new configuration. Compare the customer, delivery point, products, units, price, credit behaviour and any minimum-order rule that should apply.
Do the same with awkward accounts, not only the clean ones. Include a group-priced customer, a customer with a specific price, a restricted range, a recurring order and an account with a credit/status rule. These expose relationship errors much faster than a sample of five identical customers.
A controlled sample gives the team a safe place to learn what the target system expects. Import a small but deliberately varied set, then open the records and use them. Do not stop at “100 rows imported”.
When the sample exposes an issue, decide whether it is a source-data problem, a mapping problem or a configuration decision. Fix the category, not only the one row, before the next import.
Gate 4: freeze and cut over deliberately
The cleanest data can still be undermined if people keep editing the old sources while the final extraction is being prepared. Set the cutover point: when the final customer/product/pricing data will be taken, what can still change afterwards, and who is allowed to make an emergency correction.
Record the final counts and balances you will use for reconciliation. For stock and finance-related data, the team should know exactly which moment those figures represent. For routes and customer records, record the extraction version so a late spreadsheet edit cannot silently become the new truth.
Also keep an exception log for the first period after go-live. Some issues will only surface when a real edge-case order is entered. Give each one an owner and classify it quickly so the team can correct the underlying mapping or rule.
Migration scope is not the same as data retention. A business may need access to years of invoices or customer history without needing every historical record imported into the new operational database. Bringing everything across can increase mapping work and make inactive records look current.
Where the migration includes personal data, the ICO data-minimisation principle is a useful external check: keep the data needed for the stated purpose rather than moving obsolete personal information simply because it exists in the source.
Separate three decisions. First, what master data must be active on day one: customers, delivery points, products, pricing, routes and other live setup. Second, what opening operational position must be correct at cutover: stock, outstanding orders, balances or other live transactions that the chosen implementation includes. Third, what older history must remain accessible for reference, audit or customer service. Some of that history may be better retained in the legacy system or a controlled archive rather than converted into active records.
Make that decision with finance, operations and customer service together. The team using the new system should know where to look for pre-cutover history and which date marks the boundary between the old and new sources.
Gate 5: sign off in business terms
Before the final import is accepted, name the people who will sign off each critical area. Sales or commercial should verify representative pricing. Operations should verify customers, delivery points, routes and schedules. Warehouse should reconcile opening stock and relevant product controls. Finance should verify the mappings and opening position that feed the accounting process.
The implementation team can prove that files loaded. The business owners have to prove that the records mean the right thing. Keep a short sign-off record with the sample tested, the known exceptions and the person accepting each area. That creates a clear point at which the team agrees the new operational source is ready to own the work.
RouteMagic migration tools and boundaries
RouteMagic supports bulk CSV import across 24 entity types, including customers, products, orders, routes, price lists and other operational data. Import Data is part of the back-office setup layer, and the platform also supports Custom Fields and Integration Mapping for the records and mappings the implementation needs.
For retained systems, RouteMagic also has a public API and a managed integration pipeline for inbound and outbound feeds, including CSV, FTP and EDIFACT-style transformation where RouteMagic sets up the tenant-specific pipeline. Accounting integrations and other connectors have their own mappings and sync/audit behaviour.
The existence of import tools does not make source-data decisions automatic. The implementation still needs the ownership, cleaning, mapping and reconciliation work described above. Treat the first sample as a business test, not only a technical upload.
Conclusion
A successful data migration preserves the way the business is connected, not merely the rows it stores. Decide which sources are authoritative, clean identity before detail, and rebuild the relationships between customers, delivery points, products, prices, stock and routes. Prove the difficult commercial rules with a sample before importing the full set. Then control the final cutover so the legacy files do not continue changing underneath the project. Import tools can move records quickly; they cannot decide whether two customer rows are duplicates or whether an old pricing exception should still exist. The practical next step is to choose one representative customer group and product range, map every relationship they depend on, and run that small set all the way through a real order. The issues you find there are the ones worth fixing before the full database moves.
