Data migration to a new system: planning and pitfalls

Step 1: inventory

• Which sources hold the data: files, databases, the old system's exports.
• Which entities: clients, contacts, deals, orders, products, invoices, documents.
• Volume and time span.
• Quality issues: duplicates, missing fields, free text where structured data is expected.
• What must be migrated, what can be archived and what can be dropped.

Not everything needs to move. Old completed records can sometimes stay in an archive that remains searchable.

Step 2: mapping

For each target field, specify the source field and the transformation:

• direct copy;
• format change (dates, phone numbers, currencies);
• value mapping (old status "3" → new status "Paid");
• splitting or combining fields (full name into first and last name);
• default values for missing data.

Relationships matter: orders must link to the right clients, contacts to the right companies. Keep the old IDs in the new system for traceability.

Step 3: cleaning

• Deduplicate clients and contacts using rules (phone, email, tax number), with manual review of uncertain matches.
• Normalise phone numbers, addresses and names.
• Fix or flag records with missing critical data.
• Decide what to do with records nobody can explain.

Cleaning is best done by or with the people who use the data daily; they know which "duplicates" are actually different clients.

Step 4: scripts and test runs

Write migration scripts that can run repeatedly on fresh exports. Run full test migrations into a staging environment, check results, adjust mapping and cleaning rules, and repeat. Each run should be faster and cleaner than the last. See staging and testing.

Step 5: validation

• Record counts per entity: source vs target.
• Totals: sums of order values, balances, stock quantities.
• Samples: a set of records checked field by field by users.
• Business scenarios: can a manager find a client and see their history? Does a report match the old one?

Step 6: cut-over

• Choose a time with low activity.
• Freeze changes in the old system.
• Run the final migration with the latest data.
• Validate quickly with the prepared checks.
• Switch users to the new system; keep the old one read-only for reference for a period.

For systems that cannot stop, a phased approach or a period of synchronisation between old and new may be needed.

History and documents

Decide how much history to migrate: full history is valuable but multiplies effort; recent history plus an archive is often a good compromise. Attached files and documents need their own plan: storage, links to records and access rights.

Personal data

Migration is a good moment to delete data you no longer need and to check consents. Protect exports and staging copies that contain personal data. See personal data law.