How to Plan a Data Migration

This guide is for anyone responsible for moving business data from one system to another: a CRM, a database, a custom application, or anything where records have to land in a new home without losing information or integrity. By the end you will know how to audit the source data, design the mapping, build the transformation logic, run test migrations, plan the cutover, and validate that what arrived in the target system is actually correct.

Who This Guide Is For

Project managers, operations leads, technical owners, and consultants running a data migration as part of a larger system change: a CRM replacement, a legacy modernisation, an acquisition where two systems have to merge, or a move from a SaaS product to a custom platform. This is the longer, more rigorous version of the work. If you are doing a simpler move from spreadsheets to a new system, How to Migrate From Spreadsheets to a Proper System is a better starting point.

Before You Start

You should have the target system in place: built, configured, ready to receive data. Migrations into systems that are still being built produce moving targets and re-migrations. Get the target stable before designing the migration.

You should also have an answer to two questions. What is the cutover date, the moment the target system becomes authoritative and the source is retired? And what is your tolerance for downtime during the cutover? Those two answers shape almost every other decision in the migration plan.

Step 1: Audit the Source System Properly

Before designing anything, understand what is actually in the source system. Not what the documentation says is there, not what people remember being there. What is there.

The audit covers record counts by entity (how many customers, how many invoices, how many projects), data quality (how many records are missing required fields, how many have inconsistent formatting, how many are obvious duplicates), structure (what fields exist on each entity, what relationships exist between entities), and history (how far back does the data go, are there records that nobody has touched in five years).

A real example. A migration from a legacy CRM with 150,000 contact records. The audit found 30,000 contacts had not been touched since 2019, 12,000 had no email or phone number, 3,000 were obvious duplicates (same email, different name spellings), and 800 had invalid characters in the company name field that broke export. None of this was in the documentation. The audit took two days and saved approximately three weeks of mid-migration firefighting.

Spend the time on the audit. It is the cheapest insurance available in a migration project.

Step 2: Decide What Migrates and What Does Not

A common mistake is to migrate everything. Many migrations should not. Old data, dormant records, and archives that nobody actively uses are no use being put through the migration logic, taking up space in the target system, and confusing the team that has to live with the result.

The decision is per-entity. For each type of record, decide whether it should migrate live, migrate to archive, be left behind (with a documented retention path for the source), or be deleted. Common patterns:

• Active records: migrate live to the target system, with full fidelity
• Recently inactive records: migrate to the target but flagged as inactive
• Historical records: archive to cold storage, accessible if needed, but not migrated to the operational system
• Junk records: identified during audit (duplicates, test records, obvious errors), excluded from migration

Document the decision. “All contacts active in the last 24 months migrate live; older contacts archive; duplicates are merged using the latest activity record as canonical.” Those sentences make the migration scope concrete and prevent the team second-guessing during the build.

Step 3: Build the Mapping Document

The mapping is the central document of the migration. For each field on each entity in the source, decide what happens to it in the target: maps directly, maps with transformation, maps after lookup against another data source, dropped entirely, or split across multiple target fields.

A useful mapping format is a spreadsheet, one row per source field. Columns: source entity, source field, source data type, sample values, target entity, target field, target data type, transformation rule, notes. For a typical migration this document has a few hundred rows.

The transformation rule is where the real complexity sits. Examples of transformation rules that show up often: “Concatenate First Name and Last Name into Full Name.” “Map status values: ‘Active’, ‘Current’, ‘In Progress’ to ‘Active’; ‘Done’, ‘Complete’, ‘Closed’ to ‘Completed’.” “Parse the address string into structured components using a geocoding service.” “Look up the canonical company name against Companies House and use the registered name.”

The discipline of writing the mapping is what surfaces the ambiguities. A source field labelled “Type” that holds twelve different values, three of which seem to mean the same thing, has to be resolved at mapping time, not at migration time when the issue surfaces and blocks progress.

Step 4: Decide the Migration Architecture

The migration logic has to run somewhere. The three patterns that fit most jobs:

• ETL script: a custom script that extracts from source, transforms, loads to target. Usually written in Python, Node, or PHP depending on the team’s stack. Highest flexibility, most appropriate for one-off complex migrations.
• Vendor import tool: many systems offer their own import tooling. If the source can produce CSVs in the right shape, the import tool may be enough. Lower flexibility, faster to build.
• API-driven: the script reads from the source API, writes to the target API. Slowest but most reliable for very large migrations or where there are continuous incremental loads.

For migrations of more than a few thousand records with non-trivial transformations, the ETL script is almost always the right answer. Build it in a language and framework the team can maintain, store the code in version control, and treat it like any other piece of production software.

A realistic architecture for a large migration: an extraction job pulls data from source into a staging database, transformations run on the staging data, loaders push data into the target system, and validations confirm the load. Each stage is observable, and each stage produces logs. When something goes wrong, the diagnosis path is clear.

Step 5: Run Multiple Test Migrations

A data migration that has not been tested at scale is going to fail at scale. The first test should be small, fifty to a hundred representative records, and is intended to verify the mapping works. Subsequent tests should grow in size, with the final test covering the full dataset on a copy of the target system.

The pattern that works:

1. Small sample test (50–100 records, representative). Verifies mapping logic. Takes hours; fixes are cheap.
2. Medium sample test (1,000–5,000 records). Verifies the ETL pipeline holds at moderate volume.
3. Full dataset dry run (every record, into a copy of production). Confirms the migration runs to completion, with timing. This often surfaces problems that only appear at scale: memory issues, timeout issues, edge cases in 1% of records that the smaller samples missed.

A real example. A migration test on 1,000 records ran cleanly in ten minutes. The full dataset of 800,000 records took 18 hours, far longer than expected, because some database operations were doing per-record lookups that scaled badly. Without the full dry run, the team would have started the production migration on a Friday evening thinking it was a four-hour job, and discovered the timing problem at 3am.

Budget for at least three test rounds. The first rarely passes cleanly, the second usually surfaces secondary issues, and the third tends to be where confidence builds.

Step 6: Plan the Cutover

The cutover is the moment when the target system becomes authoritative and the source is retired. The two patterns:

• Hard cutover: at a defined moment, the source is locked, the migration runs (typically overnight or over a weekend), the target opens for business. Highest assurance, requires a downtime window.
• Soft cutover with parallel running: the migration runs to bring the target current, then both systems run in parallel for a defined period (a week or two), with the team comparing outputs and the source ultimately retired. Lower risk, higher operational overhead.

For business-critical migrations, hard cutover over a weekend is common. The team locks the source system at 6pm Friday, runs the migration overnight, validates Saturday and Sunday, and opens the target at 9am Monday. The downtime is real but bounded.

For larger or higher-risk migrations, soft cutover is safer. The source remains open for new transactions while the target catches up, then the two run side by side until the team is confident.

Build the cutover plan with timings. Lock source: 18:00. Begin extraction: 18:15. Expected migration runtime: 4 hours. Validation phase: 22:30 to 04:00. Target open: Monday 09:00. If any stage runs over, decision point at this trigger. Documented stage by stage, the cutover is repeatable and reviewable.

Step 7: Validate What Arrived

The migration is not done when the loader finishes. It is done when the data has been validated.

Validation runs at three levels. First, record counts: do the totals in the target match the expected counts from the source, with deliberate exclusions accounted for? Second, sample checks: take 100 records from the target, randomly sampled across the dataset, and compare them field by field against the source. Third, integrity checks: do the relationships hold? Every invoice has a customer, every project has a client, no orphaned records, no foreign keys pointing to nothing.

Automate as much of this as possible. A validation script that runs the counts, samples, and integrity checks in twenty minutes is much more valuable than a manual review that takes a week and is incomplete.

The validation also feeds the sign-off. When the project owner asks “is the migration complete?”, the answer is not “yes, we ran the script”. The answer is “yes, the record counts match, the sample of 100 records was fully verified, all integrity checks passed, here is the report”. That report is what makes the migration final.

Common Mistakes

• Migrating without auditing. Source data is always messier than expected. The audit before mapping is what prevents the migration from inheriting the mess.
• Migrating everything. Old, dormant, junk records take up space and confuse the team. Decide what to leave behind.
• Mapping ambiguity that gets resolved at runtime. “We will figure out the status mappings during the migration” produces inconsistent decisions and a target system full of subtle errors. Resolve the mapping in writing.
• Skipping the full-scale dry run. Migrations that pass on small samples fail on full datasets. The dry run on the full data is non-negotiable for migrations above a few thousand records.
• No rollback plan for the cutover. If the migration is in progress and something goes catastrophically wrong, can you abort and revert? Plan for it.
• Walking away after the loader finishes. Validation is the last and most important step. The migration is not complete until the validation reports clean.
• Underestimating timing. A migration that takes ten minutes on 1,000 records can take fifteen hours on a million. Test at full scale before committing to a cutover window.

What Good Looks Like

A well-planned data migration begins with an honest audit of the source, a documented decision about what migrates and what does not, a complete field-by-field mapping with transformations explicit, and a build of the ETL pipeline as production-quality code. Multiple test rounds across a small sample, a medium sample, and a full dataset dry run surface and resolve issues before the cutover. The cutover plan has timings, decision points, and a rollback option. After the loader finishes, automated validation confirms record counts, sample integrity, and relationship consistency. The migration sign-off is supported by a report, not a feeling. The target system goes live with clean, complete, verified data, and the team trusts it from day one.

Next Steps

If the migration is part of a larger system replacement, How to Roll Out a New Internal System covers the people side of the change, and the wider set of implementation guides walks through the rest of a rollout. If the cutover involves a launch, How to Launch a Software Product Safely is the next read, and when several systems have to move together, How to Plan a Multi-System Integration covers the coordination. For complex migration work involving large datasets, many systems, or regulated environments, see Data Migration Services or get in touch.