How to Run a Successful Data Migration

Who This Guide Is For

This guide is for project leads and operations managers overseeing a data migration between two systems — moving from one platform to another, consolidating multiple data sources, or migrating to a custom-built replacement. The stakes are high because the data represents your business’s operational history, and losing or corrupting it disrupts everything downstream.

Before You Start

You should have the destination system ready to receive data — schema defined, access configured, and import mechanisms available. You should also have a complete understanding of what data exists in the source system. If you are migrating from spreadsheets specifically, How to Migrate From Spreadsheets to a Proper System covers that simpler scenario in more detail.

This guide covers system-to-system migrations where both source and destination are structured platforms with defined schemas.

Step 1: Inventory and Profile the Source Data

Before mapping anything, understand what you have. Profile the source data to answer:

• Volume: how many records across each entity type (clients, projects, transactions, etc.)?
• Quality: what percentage of records are complete? What percentage have known issues (duplicates, missing fields, formatting inconsistencies)?
• Relationships: how do records relate to each other? Clients to projects, projects to invoices, invoices to line items. These relationships must survive the migration intact.
• History depth: how far back does the data go? Do you need to migrate everything, or only records from a certain date forward?

Profile the data programmatically where possible. For databases, query for null counts per field, duplicate detection, and referential integrity checks. For exports, import into a staging table and run the same checks. The profile reveals the data quality problems you will need to solve before migration, not after.

Step 2: Design the Mapping

Create a field-by-field mapping from source to destination. For each source field, document: the destination field it maps to, any transformation required, and what happens if the source value is null, invalid, or unexpected.

Common transformations:

• Format conversion: dates from one format to another, currency precision, character encoding
• Value mapping: source status “Active” and “Current” both map to destination status “Active”
• Splitting or merging: a source “Full Name” field splits into destination “First Name” and “Last Name”
• Lookup replacement: source uses IDs that do not exist in the destination; map to new IDs based on a lookup table

Document fields in the destination that have no source equivalent. These need default values or will require manual population after migration.

Document fields in the source that have no destination equivalent. Decide whether this data is archived, discarded, or stored in a notes/metadata field.

Step 3: Build and Run Test Migrations

Extract a representative subset of source data — ten to fifteen percent, selected to include all entity types, edge cases, and known problem records. Run this through the mapping and load it into a staging instance of the destination system.

Validate the results:

• Record counts: do the numbers match? If you migrated 500 client records and the destination shows 498, find the two missing records.
• Relationships: are parent-child relationships intact? Does every project still belong to the correct client?
• Values: spot-check individual records against the source. Are names, dates, amounts, and statuses correct?
• Edge cases: check the records you identified as problematic in the profiling step. How did they survive the migration?

Fix mapping issues and run the test again. Plan for two to four test cycles. Each cycle will reveal new edge cases — particularly around character encoding, date handling near timezone boundaries, and records with unusual relationship structures.

Step 4: Plan the Cutover

The cutover is the moment you switch from the old system to the new one. How you handle it depends on whether you can afford downtime.

Scheduled cutover (with downtime): freeze writes to the source system, run the final migration, validate, then switch. Suitable for systems where a few hours of read-only access is acceptable (e.g., migrating over a weekend).

Rolling cutover (minimal downtime): sync data continuously from source to destination, with a final delta migration at cutover that captures changes made since the last sync. More complex but limits downtime to minutes.

Parallel running: both systems run simultaneously for a defined period. All writes happen in the new system, but the old system remains available for reference. The safest option but operationally the most demanding.

Whichever approach you choose, define a rollback plan. If the migration fails or the destination system has a critical issue in the first 48 hours, you need to know how to revert. The rollback plan should be documented, tested, and available to the team responsible for the cutover.

Step 5: Execute and Validate

Run the final migration according to your cutover plan. During execution, monitor progress and log any errors or skipped records.

After the migration completes, run a full validation:

• Record count reconciliation: source count matches destination count for every entity type
• Checksum or hash comparison: for critical financial or transactional data, verify that aggregate values (total amounts, sums, counts) match between source and destination
• Relationship integrity: verify referential integrity in the destination — no orphaned child records, no broken links
• Sample verification: randomly select fifty records across different entity types and verify each one field by field against the source

Do not decommission the source system until validation is complete and the team has operated on the new system for at least one full business cycle (typically one to two weeks). The source system is your safety net until the migration is proven.

Common Mistakes

• Skipping data profiling. Migrating data you have not profiled means discovering quality problems in the destination system, where they are harder to fix. Profile first, clean, then migrate.
• No test migration. Running the full migration as the first attempt means discovering mapping errors on production data. Test with a subset first, iterate, then run the full migration.
• No rollback plan. If the migration goes wrong and you have no way to revert, you are stuck with a broken destination and a stale source. Define and test the rollback before executing.
• Migrating everything. Five years of archived data may not need to move. Define a history cutoff and archive older data separately. Less data means faster migration and fewer edge cases.
• Validating only the counts. Matching record counts does not mean the data is correct. Field-level spot checks catch mapping errors that count-based validation misses.

What Good Looks Like

A successful data migration looks like this: every record that should have moved is in the destination system with correct values and intact relationships. The team is working in the new system with confidence that the data is complete. The source system is archived but available for reference. No data was lost, no relationships were broken, and the business continued operating throughout the transition.

Next Steps

If the migration is part of a broader system modernisation, How to Roll Out a New Internal System covers the deployment side. For integrations that need to run continuously after migration, How to Integrate Multiple Systems Without Downtime covers that process. If you want the migration planned and executed for you, get in touch.