Migrating data without stopping the business

In the projects that go badly, the point where they go badly is almost always the same: the day the old data has to move into the new system. Up to then everything works, because everything was tested with ten rows written for the purpose. Then eight thousand real customer records arrive, and the new system fills up with things that don't add up while the old one has already been switched off.

You don't need luck, you need a method. This is the one I use.

Data gets cleaned first, and at the source

The temptation is to clean during the migration, with automatic rules that fix things on the fly. It's the quickest way to carry the problem with you: in six months nobody will know whether «Rossi Ltd» and «Rossi Limited» really were the same company or two different customers merged by a script.

Cleaning happens in the old system, while it's still in use, by the people who recognise the edge cases. It's dull and it takes weeks. Doing it afterwards costs more.

You rehearse three times

The first rehearsal is to find out what breaks. The second is to check the fixes work. The third is the dress rehearsal: run it with yesterday's data, timing it, because on the real day you need to know how many hours it takes.

Every rehearsal starts from scratch. A migration running on top of the leftovers of the previous one proves nothing.

Verification is done with numbers, not by eye

Opening three records and seeing that it «looks right» is not verification. Before declaring a migration finished you need comparisons a machine can repeat:

• how many records there were and how many arrived, for every data type;
• the sum of open balances, which has to match to the penny;
• ten records picked at random, checked by hand field by field;
• the list of what was rejected, with the reason, read by someone who knows those customers.

The last point is the most important. A silent rejection is lost data that nobody looks for until they need it.

The old system stays on, read-only

For a few weeks the previous system has to remain readable without being writable. It costs little and takes the anxiety out of everything: if something is missing you go and look instead of reconstructing it from memory. It's also the only way to catch the rejections your checklist didn't intercept.

You can decide to switch it off when a whole month has gone by without anyone opening it.

The right day

Not the first of the month, not Monday morning, not the day before a tax deadline, not right before the holidays of the person who knows the system. The right moment is the one where, if something goes wrong, you can roll back calmly — and rolling back has to be planned, written down, and rehearsed at least once.

A migration done well goes unnoticed. It's the only outcome worth aiming for: on Monday morning people open a different screen and find their work where it was.