Data migration plan: the four lines everyone leaves out

The plan was thirty-four tasks, resource-loaded, with dependencies drawn and a critical path highlighted in red. It had been reviewed by three people and signed off by the steering committee. One line read: “Data cleansing: 3 weeks, migration team.”

That line was wrong in a way no amount of reviewing would have caught. Cleansing was not three weeks of work for the migration team. It was eleven weeks of decisions by four site engineering teams who had never been told they were on the project, about 9,000 duplicate parts where no rule could pick a winner. The migration team could prepare the decisions, route them, and record them. It could not make them.

The plan was not incomplete. It was complete about the wrong things.

What every plan has

Open any data migration plan and you will find the same skeleton: extraction, mapping, transformation rules, load, testing, cutover, hypercare. Durations on each. It is a reasonable description of the work, and it is the part that planning software is good at.

It is also the part that rarely goes wrong. Extraction takes about as long as you think. Writing transformation rules takes about as long as you think. The tasks that blow up a migration schedule are almost never the ones with a task box.

Four things decide your date. Most plans name none of them.

1. Arbitration time, which is not development time

At the end of the data audit you know something precise: what share of records pass on a deterministic rule, and what share needs a human to decide. Call it 95/5 on a typical scope.

The 95% is engineering. You control it, you can estimate it, and adding people to it works.

The 5% is different in kind. Each of those records needs someone with authority to say which value is correct, and that someone is a business person with a day job. They will give you a few hours a week, not full time. Adding engineers does not speed this up at all. It is the only line in the plan whose duration you do not control, and it is almost always on the critical path.

Put it in the plan as its own workstream, with three things attached:

• A volume, in decisions, not in records. Ten thousand duplicates grouped into 900 decision sets is a 900-item queue, and that is the number that matters.
• A named owner per data family, on the business side, with the authority to decide. Not a committee. A migration blocked on arbitration is blocked on the org chart, not on technology.
• A rate, in decisions per week, agreed with those people before the date is committed. If four teams can process 80 decisions a week, a 900-item queue is eleven weeks. That is arithmetic, not pessimism.

The failure mode here is subtle. Nobody refuses to do arbitration. They just do it at the speed of a secondary task, which is the speed the plan never assumed.

2. The delta window

Every plan contains the word “freeze”. Very few explain what happens on either side of it.

Source data keeps living until cutover. Between your last full migration pass and the moment the old system goes read-only, the business creates parts, changes suppliers, closes orders. That volume is small in percentage and large in consequence: a missed delta is a record that exists in the old system and not the new one, discovered by a user weeks later.

Your plan needs to answer four questions explicitly:

• When does the source freeze, and for how long? “The weekend” is not an answer if three plants are in different time zones.
• Can the business tolerate a freeze at all? If it cannot, you are not doing a big bang cutover, whatever the plan says. That changes the cutover strategy, and it is better to discover it now than in the dry run.
• How are post-freeze changes captured? By timestamp, by change log, by re-extracting the full set and diffing. Each has a cost and a blind spot. Deletions, in particular, are invisible to most timestamp-based capture.
• Who replays them, and by when? This is a task with a name and a clock, not a step.

A chain where each record carries its own status makes this tractable, because the delta is just more records entering the same pipeline. A batch script makes it a second migration, written under time pressure, at the worst possible moment. That is the practical argument for an asynchronous chain with replay, and it belongs in the plan rather than in the architecture appendix.

3. The rollback call

Ask any programme manager what triggers a rollback and you will get a version of “if it goes badly, we roll back”. That is not a decision rule. It is an intention, and at 4am, with a partial load and a business waiting, an intention produces paralysis.

A rollback line in a plan needs exactly three elements:

• A person, by name, who makes the call, plus a named substitute. Reachable, awake, and with the authority to overrule the room.
• A threshold, as a number. Not “too many errors”. Something like: more than 2% of parts rejected after replay, or any rejection in the open orders family, or the load not past phase three.
• A time, on the clock. “If we have not reached the go/no-go checkpoint by 05:00, we roll back” is a decision that can be made calmly on Tuesday and executed mechanically on Sunday.

The value of writing this down is not that you will roll back. It is that you probably will not, and the reason is that a team with a clear threshold spends the night fixing against a target instead of arguing about whether things are bad enough yet.

4. How many full passes the calendar allows

This is the number I would look at first if I were handed a plan to review.

Count the calendar days between the end of the build phase and cutover. Divide by the time one complete migration run takes, including the correction loop after it. That gives you the number of full passes the plan permits.

If the answer is two or three, the plan is not a migration plan. It is a rehearsal that will discover its exceptions in production. If the answer is twenty or thirty, the exceptions surface in week six, when they cost engineering time instead of an incident. The relationship between that number and how the cutover goes is the closest thing to a rule I know, and the reasoning behind it is in what a passing test actually proves.

The number also disciplines the architecture. If your tooling makes a full pass expensive, the plan cannot allow many, and no amount of scheduling fixes that. Tooling and plan are the same decision seen twice.

The plan is written after the audit, or it is provisional

A date committed before the audit is a date committed to an unmeasured scope. It is not a plan, it is a hope with a Gantt chart around it.

This is uncomfortable because programmes want a date early, and the audit sits at the start when nobody wants to spend three weeks measuring. The workable compromise is to say it out loud: publish a provisional plan with the phases and the dependencies, mark every duration as unqualified, and commit dates only after the audit returns volumes, anomaly rates and the arbitration queue size. A plan that says “eleven weeks of arbitration, measured” survives a steering committee. A plan that says “three weeks of cleansing, assumed” survives until the first real load.

Plan and checklist are not the same document

Worth separating, because they get conflated. The checklist is what you verify: extraction complete, duplicates counted, history scope decided, rollback threshold defined. There is one at the end of the ERP data migration guide, and it is the right thing to run against your scope before committing.

The plan is what you sequence: who does what, in which order, with what duration and which dependency. A checklist item like “reserve arbitration time from the business” becomes, in the plan, a named workstream with a volume, an owner and a weekly rate.

Running the checklist tells you whether your plan is missing something. It does not write the plan. The order is: audit, then checklist, then plan, then dates. Programmes that go audit, plan, dates, checklist find out in month four that the checklist would have moved the date, and by then the date is a commitment rather than an estimate.

The full five-phase sequence these fit into is on our methodology page.