Data migration, stage by stage: what each stage has to prove

Most data migrations that go wrong were planned as a build project. The estimate puts most of the effort into the extract, transform and load, and gives the rest of the plan a few lines each. Then profiling finds data that is dirtier than anyone said, acceptance testing turns up business rules nobody wrote down, and the cutover weekend runs longer than the window the business agreed to.

The code is rarely what goes wrong. The data is. A migration plan that holds up is mostly about two things that happen around the build: understanding the source before anyone commits to a date, and proving the result before anyone switches over. What follows is how I run one, stage by stage, and what each stage has to prove before the next one starts.

A framework is easier to judge with numbers in it, so the article follows one worked example through every stage: a CRM migration with its volumes, timings and test results. The company and every figure in the example are fictional. After that come ten scenarios where a migration goes off-plan, a self-check for your own plan, and templates you can download. If you are short of time, read the short version below and the five questions near the end.

The short version

If you sponsor or approve a migration and read nothing else, these are the points that decide how it goes:

• Most of the work is not the build. It is understanding the data before the build and proving it is right after. Plan and staff for both.
• Do not accept an estimate or a date until the source data has been measured on its full volume. Estimates made from documentation are guesses.
• The business signs the rules, not engineering: what each old value becomes in the new system, and which record survives when duplicates are merged.
• Proof is automated comparison of old and new after every test load, and a full rehearsal of the switch-over weekend until it runs clean. A job that finished without errors proves nothing about the data.
• Decide in writing, before the weekend, when you will go back to the old system: a time and a size of discrepancy, with one named person who makes the call.
• The project ends when the old system is switched off, not at go-live.

Terms used in this article

Cutover

The planned window, often a weekend, in which the final data is moved and the business switches from the old system to the new one.

Mock cutover

A full rehearsal of the cutover, on all the data, with the same people, timed step by step.

Go-live

The moment users start working in the new system.

System of record

The one system whose copy of a piece of data counts as the official one.

Profiling

Measuring what the data actually contains: how many records, how many are empty, which values occur, which look duplicated.

Source-to-target mapping

The document that says, for every field in the new system, where its value comes from in the old one and how it is converted.

Reconciliation

An automated comparison of the old and new data after a load, to prove nothing was lost or changed.

Variance

The difference reconciliation finds. An explained variance has a known, accepted reason; an unexplained one is a possible defect.

Hard-stop

A clock time agreed in advance: if a milestone has not been reached by then, the cutover is abandoned.

Rollback and roll-forward

Rollback is returning to the old system. Roll-forward is staying on the new one and fixing problems there.

Point of no return

The moment after which rollback is no longer planned, because too much new work exists only in the new system.

Hypercare

A period of closer support straight after go-live, with the project team still in place.

Decommission

Switching the old system off for good, after its history has been archived.

Delta load

Loading only what changed in the source since the previous load.

Change data capture

Reading the source database's own change log so every new edit is copied to the target as it happens.

Deduplication and survivorship

Finding records that describe the same customer, and the rule for which one survives the merge.

First, decide whether it is a migration

"Move the data from A to B" describes three different kinds of work, and the first decision is which one you have.

• A migration moves data once. The source system has a shutdown date, and the project ends when it is switched off.
• An integration moves data continuously. Both systems stay in service, and the pipeline is something you operate for years.
• Coexistence is both, in sequence: the systems run side by side for a while, kept in step by a temporary sync, until the old one is retired.

The label matters because each one brings its own discipline. Treat a migration like an integration and you get a well-engineered pipeline with no cutover plan, no rollback and no reconciliation sign-off. Treat an integration like a migration and you get a one-off script that ends up running every night for years with nobody responsible for it.

The question that settles it is: when is the source switched off? If there is a committed date, it is a migration. If the answer is "never", it is an integration. If the answer is "eventually", treat that as a finding. Coexistence without an end date is how a temporary sync becomes permanent, and it needs an end date before anyone designs it.

The worked example

The example is a regional equipment distributor with six branches. It has run its sales operation on Salesforce for nine years, and its finance and operations already run on Dynamics 365 Finance and Operations. It decides to consolidate customer data onto the same platform and move its CRM to Dynamics 365 Sales, with 11 months from approval to go-live.

The sponsor sets one condition: "Sales must not lose a working day, and the pipeline numbers must not move." It is a good condition because it can be tested. It turns into a cutover window that ends before Monday morning, and into a reconciliation check on open pipeline value that must come back clean. Every stage below shows what this project found and decided.

Ten stages, and what each has to prove

A complete migration has ten stages. Scale changes how deep each one goes. It does not change the list. Moving one reference table and replacing a core system both go through the same ten. A small migration simply spends minutes on stages a large one spends weeks on.

Three things about this sequence are worth saying plainly.

The first half is understanding the data, not moving it. Stages one to four produce no migration code, and they decide whether the project succeeds. Almost every later failure (a test that fails, a reconciliation that does not balance, a busy first month after go-live) goes back to something in the source that nobody understood: a case the mapping missed, a business rule that was never written down, data dirtier than assumed. The later a mapping error is found, the more it costs. Found during profiling, it is one line of a spreadsheet. Found after go-live, it is a correction to production data and to users' trust in the new system.

Testing and cutover are the other half. A migration cannot ship and patch later the way an application release can. From the moment of cutover, the business starts writing to the new system, and every hour makes going back harder. So the data has to be right when it lands, and the only way to know that in advance is to rehearse the whole thing.

Decommission is in scope. The business case was approved to replace a system, not to add one. While the old system is still running, you pay for both, some users carry on entering data in the old one, and nobody is sure which system is the system of record. If the plan stops at go-live, the savings in the business case never arrive.

Between stages there is a gate review: the deliverable, the exit criterion, and a recorded decision to proceed or not. The gate is not paperwork. It is the cheapest point in the project to stop. A gate that ticks "mapping complete" while hundreds of mapping rows still say "to be confirmed" lets the project carry the problem into a stage where it costs much more to fix.

The sequence is not strictly linear, either. Profiling surfaces something new and mapping changes. A test fails and cleansing reopens. Mock cutover finds a missing step and runs again. Plan time for at least one loop back, because there will be one.

The decisions that come before any code

A migration strategy answers five questions before any technical design: what is in scope, in what order it moves, by which approach, inside which cutover window, and how much risk the business will accept. Those answers belong in a short strategy document that the business signs. While they live only in the architect's head, they can be renegotiated in every meeting.

Scope, and what you choose not to move

Not migrating every record is a legitimate decision, and often the right one. Old history that nobody edits can go to a readable archive instead of into the new system's model. Deciding this at discovery shrinks mapping, cleansing and testing. Deciding it at cutover is an emergency.

Waves and dependency order

A wave is a group of data and functions that move together, and it is the unit you plan in. Inside and between waves the order is fixed by dependencies: reference data first, then master data, then open transactions, then history. A load that goes against that order creates its own integrity errors: orders that point at customers who do not exist yet, contacts attached to missing accounts.

Big bang, phased or trickle

• Big bang: everything and everyone moves in one cutover window. The simplest design, with all the risk in a single event.
• Phased: waves move by business unit, domain or module, each with a smaller cutover. Less risk per event, paid for with a period of coexistence and a sync between the two systems.
• Trickle: a full initial load, then change data capture copies every new edit across while the old system keeps running. Cutover becomes a switch rather than a long load. It needs the most engineering discipline of the three.

Preference should not make this choice. One comparison should: how long a full load actually takes, measured on real volume, against the window the business can give you. If the measured load fits in the window with room for reconciliation and a rollback, big bang is the cheapest choice. If it does not, you either split the scope into waves or keep the systems in step with change data capture. You only know which once you have measured it.

Understanding the data

Profiling: measure before you commit

Discovery tells you what exists: systems, tables, flows, owners. Profiling tells you what the data actually looks like: volumes, null rates, distinct values, patterns, orphaned records, duplicates. Both are done on the data itself, not on its documentation.

A data dictionary describes the system as it was meant to be. Profiling shows you what it actually holds. The status field that the documentation says has a handful of values usually has more, and each of the extra ones is either a business rule nobody mentioned or a defect someone has been working around for years. You want to find them in the first weeks, not in acceptance testing.

This is also why an estimate made before profiling is a guess. Estimates built on a count of tables break because tables are not equal: a few complex master-data tables can take more effort than every simple transaction table put together. Profile first, then estimate from mapping complexity and measured source quality. And profile again before cutover, because the source keeps changing while the project runs: new status values appear, volumes grow, and the data you tested is not quite the data you move.

Source-to-target mapping: the contract the business signs

The mapping document is the centre of a migration. For every target field, it states the source field, the transformation rule, the default when the source is empty, and who approved it. It has to be field-level: "table A goes to table B" is not a mapping. Most of the difficulty sits in individual columns: type conversions, code translations, conditional logic, null handling.

A transformation rule looks technical but is usually a business decision. What status 3 means, whether a dormant account counts as active, which date a record belongs to: an engineer should not decide any of these alone, however reasonable the guess. So the business signs the mapping and engineering implements it. Every rule is checked against the values profiling actually found, not against the values on paper.

Most rows in a mapping are straight moves or format conversions, and they go quickly. The rows that take the time are derivations and code translations, because each one is a conversation with the business. Plan review time by the number of decisions in the mapping, not by its number of rows.

The mapping stays current for the whole project. When testing finds a defect, the fix goes into the mapping first and the code second. If the code is fixed and the mapping is not, the project no longer has one source of truth about what the migration does.

Cleansing and deduplication

Cleansing for a migration means clean enough to work in the target system, not perfectly clean. "Clean enough" is defined by the target's validation rules and by thresholds the business agrees to. The main decision is where to clean:

• At the source is the most durable fix and the slowest, because it needs the source owners' time.
• In the pipeline is fast, but it creates a gap between source and target that reconciliation then has to explain.
• In the target after loading is risky, and only acceptable for work that was planned that way.

My default is source first, pipeline second, target only by exception. Each issue from profiling becomes a backlog item with an owner, an approach and a status. Cleansing run as a background activity never finishes.

Deduplication is the hardest part. Finding likely duplicates is a technical problem. Deciding which record survives, and which field values it keeps from the others, is a business decision that needs a signature, because a wrong merge destroys history that cannot be rebuilt. Split the candidates into bands by match score: the clearest pairs can merge automatically, the uncertain middle goes to people, and the rest are confirmed as different. And log every merge, old ID to surviving ID, permanently. Data that cannot be cleaned in time does not stop the project. It needs a written decision: migrate it as-is with a flag, load a default and correct it later, or take it out of scope.

Where AI helps with mapping, and where it stops

A first draft of the mapping is one place a language model saves real work. Give it the source and target schemas and a sample of masked values, and it can propose candidate field pairs, rough format conversions and a first match between two code lists. Matching on sample values does better than matching on names alone, because a field called ref that holds dates is easier to place once you have seen what it holds. The analyst's job changes from writing a long mapping from a blank page to reviewing a draft.

The responsibility does not move. A drafted mapping row is a hypothesis. The model will confidently pair a delivery date with a due date, and it cannot know what status 7 means in this particular business. So every drafted row stays marked as a draft and goes through the same review and sign-off as a row a person wrote. Derivation rules and defaults for mandatory fields are still agreed between people. In the example, a model could have proposed the lookup from 214 industry values to 88, but only the head of sales could decide what happens to the records that match nothing.

The same approach works in reverse when the old system has no documentation: a model reads the legacy transformation code and drafts the rules it implements, and you verify the draft by comparing outputs, not by trusting the summary.

This is the problem the Data Migration AI Toolkit is built around: field mapping drafted by a model, with every row reviewed by a person before it is used. It is still in development.

A pipeline you can run again

A migration pipeline is designed for different things than an integration pipeline. It has to be correct, re-runnable, and fast enough for the cutover window. It does not have to run for years. The structure I use is extract, staging, transform, load, with staging at the centre. Staging is an intermediate database between old and new: every record stops there to be validated, counted and compared, and staging is the point you restart from.

Three properties are not optional:

• Restartable. If a run dies partway through, it resumes from the point of failure rather than from the beginning. In a cutover window, starting over may not fit.
• Idempotent. Running a step twice produces the same result as running it once, with no duplicates. Re-running should be the first thing anyone tries, and it should be uneventful.
• Instrumented. Every step counts what came in, what went out and what was rejected. Reconciliation is built from those counts.

Two more decisions belong at the start of the build, not in cutover week. First, design for delta loads from day one: the source keeps changing between the first full load and cutover, and the pipeline has to catch up on the difference. Second, make reject handling explicit. A record that fails validation goes to a quarantine table with the reason it failed. It does not stop the whole batch, and it does not disappear. A row that disappears without a record is a defect nobody will ever trace.

Performance is tuned against measurements, not assumptions. Extract in chunks from a read-only copy of the database rather than the live system, measure a few batch sizes against the target's actual locking behaviour, and drop non-essential indexes during the load. Then rehearse the whole thing, because the figure that matters is the end-to-end time inside the window, reconciliation included.

Proving the data is right

Test in layers, on full volume

Migration testing answers four questions in order. Is each transformation correct (unit tests)? Does the whole flow run end to end (integration testing)? Is the data right for the business (reconciliation and user acceptance)? Is it right at real scale and in real time (volume testing and mock cutover)?

The test cases come from the mapping. Each transformation rule becomes a test: a sample input and the output it should produce, written while the mapping is being agreed, not afterwards.

And test on full volume, not only on samples. Migration defects live in rare historical values and in effects of scale, and a sample contains neither. A load that takes an hour on a sample does not take a predictable multiple of that on the full set. It fails on an old value that the sample never contained, or it runs well past the window. Volume testing also has to cover failure: kill a run partway and restart it, run a step twice, and confirm the counts still balance.

Reconcile in four layers, automatically

A job that finished without errors tells you the job ran. It does not tell you the data is right. Reconciliation does, in four layers, each catching what the one before it misses:

1. Row counts per entity, source against target, allowing for records excluded on purpose.
2. Checksums or hashes, a fingerprint computed from the values in the columns that matter, to catch values changed in transit.
3. Business aggregates the business chose: balances, totals by period, counts by status. These are the numbers a finance or operations lead will check first.
4. Row-level comparison on a targeted set, to explain the differences the aggregates show.

Four things have to be agreed before reconciliation is run, or its results are not evidence: a consistent cut-off time on both sides, the list of aggregates, the acceptable variance per layer, and the rule for classifying a difference as explained or unexplained. Then automate the whole thing to run in a single command after every load and keep the history. Reconciliation done once by hand in a spreadsheet is rarely done a second time. When it runs after every test cycle, a falling variance from one cycle to the next is the best evidence you can bring to the go/no-go meeting.

Acceptance testing is users checking their own records

User acceptance for a migration is not users testing features. It is users confirming that their records are right in the new system: the account manager looking at their own accounts, the finance lead at the balances they already know. Choose the people and the records on purpose, including the awkward ones profiling flagged.

When a defect turns up, trace it to its layer before fixing it: a wrong mapping rule, a cleansing gap, or a load that dropped rows. Each layer has a different fix, and fixing the symptom in code without correcting the mapping leaves two versions of the truth.

Cutover and rollback

Cutover is the moment the system of record changes. Everything before it is preparation; everything after it is support. A good cutover plan turns a high-risk night into a sequence of steps the team has already rehearsed.

A runbook timed from rehearsal

The cutover runbook lists every step with an owner, an expected duration, how the step is verified, and the condition that stops the process. The durations come from rehearsal, not from estimates. Write it in enough detail that a stand-in could carry out any step if its owner were unavailable.

The rehearsal is the mock cutover: the whole runbook, on full volume, with the people who will do it on the night, timed step by step. It finds things no test finds: a missing permission, a step done in the wrong order, a key person who is not available that weekend, a load that does not fit the window after all. Repeat it until it runs clean. If cutover is scheduled before the rehearsals pass, the rehearsals are not doing their job.

Go or no-go on criteria agreed in advance

The go/no-go decision is made against criteria signed before the night: reconciliation within the agreed variance, security checks passed, business sign-off given. It is not a vote taken in the middle of the night. A cutover has two planned exits, go or roll back, and "keep trying a bit longer" is not one of them.

Rollback: numeric triggers and a point of no return

A rollback plan is a way back that was defined, rehearsed and signed before go-live. One line saying "restore from backup" is not a plan. Its most important part is the trigger, and the trigger has to be a number. A hard-stop time: if a named milestone has not been reached by then, you roll back without discussion. And a variance threshold: if reconciliation shows an unexplained difference above the agreed level, you roll back. Without numeric triggers agreed in advance, the decision gets made under pressure, and sunk cost usually wins.

The rollback window closes gradually. Before the switch, going back costs almost nothing. Once users start writing to the new system, every hour adds changes that a rollback would lose or have to replay. So define the point of no return explicitly. After it, the plan is no longer rollback but roll-forward: fix it in the new system. Write that procedure down in advance as well.

Two rules make rollback possible. Leave the source untouched during the load: nothing writes to it or changes it, by design rather than by promise, because it is what you would roll back to. And rehearse the rollback path at least once during mock cutover. A rollback that has never been run is not a plan you can rely on.

Give the decision to one named person, usually the executive sponsor, and make sure that person is rested. The people who have worked through the night are the worst placed to decide whether to stop, because they are the ones most invested in carrying on.

Finishing: hypercare and decommission

Hypercare ends on criteria, not on a date

Hypercare is a fixed period of closer support straight after go-live. The project team is still in place, monitoring is tighter, and response is faster than the normal service level. Users decide whether they trust the new system in its first weeks, and that trust is hard to win back once it is lost.

Monitor the data as well as the system: scheduled reconciliation against the read-only legacy system, data quality rules on newly entered records, and a daily comparison of the business totals people care about. Have a controlled route for data fixes, separate from code changes. Hypercare ends when its exit criteria are met. It does not end just because the calendar says so.

Decommission is the real end

Decommission is where the business case pays off: the old system is off, its history is archived under the retention rules, and its licence and infrastructure costs stop. The safe sequence is:

1. List every remaining consumer, from access logs and lineage, not from asking around. There is always one nobody remembered.
2. Cut each remaining feed with notice to its owner.
3. Run the old system read-only for a period, and consider a short planned outage, a brown-out, to flush out consumers the logs missed.
4. Archive what was not migrated, in a form that can be read without the original system, and do a test restore with the people who will need it, such as finance and audit.
5. Switch it off.

Archive before switching off, and verify before archiving. Each delay to decommission costs twice: you keep paying for a system that is no longer used, and you carry the security risk of one that nobody maintains.

When it goes off-plan: ten scenarios

A plan is tested by what happens when something goes wrong. These ten situations come up again and again. Each one lists what happens, what you see, what you decide, and the mechanism that made it cheap rather than expensive. Scenarios 1 to 5 and 9 come from the worked example; the others are separate illustrations. All are fictional.

Read the last line of each scenario again and a pattern shows. None of them is saved by skill on the night. Each is saved by something set up weeks earlier: a profile, a signature, a log, a rehearsal, a trigger.

Five questions to ask of any migration plan

Most migration failures I have seen come down to five, and each one is a stage that was skipped or done thinly under schedule pressure, not a stage done badly. That makes them useful for review. When I have a short time to assess a migration plan, mine or a vendor's, I ask one question per failure mode:

1. Which profiling report is this estimate based on? If there is none, the estimate is a guess, and the schedule will break at cleansing or acceptance testing.
2. Does each transformation rule cover the values the data actually holds, and who signed the derivations? Rules written from documentation show up later as misclassified records in production.
3. Which full-volume run do the runbook timings come from? Timings scaled up from a sample are how a cutover overruns its window.
4. At what variance and by what time does someone roll back, and who decides? If the answer is not a number, a time and a name, the plan has no real rollback.
5. How long does it take to rerun the full reconciliation? One command means it will be run after every load. A week of work means it was done once and will not be repeated.

A sound plan answers with numbers, dates and signatures. A risky plan answers with intentions. Pressure to cut one of these steps to save time is the moment to be most careful: cutting a volume test or a second rehearsal does not remove the risk. It moves it out of the schedule, where everyone can see it, and into production, where nobody can until it fails.

Templates and next steps

Three of the documents in this article do most of the work on a real project, and they are free to download as one PDF, with no form: a source-to-target mapping template, a rollback plan with the trigger table, and a cutover runbook. Each has the columns defined, a short note on how to fill it in, and blank rows.

Download the templates (PDF, 329 KB)

If you are preparing a migration, the data migration readiness checklist turns these stages into a printable list to work through. If you want the sources profiled and a first mapping drafted for the domain that matters most before you commit to a date, that is what the Integration & Migration Review covers. And if the work is a continuous integration rather than a one-off move, what usually breaks in enterprise data integration covers that side.