Moving data from the old system to the new one: mapping, rehearsals, reconciliation and cutover

In any move to a new system, data migration is the line item most often underestimated and most often late, because from the outside it looks like copying. The actual work has four parts: deciding what moves at all, mapping every field one by one, rehearsing repeatedly against real production data, and proving the result by counting. And the real risk is not losing records. Loss gets noticed, because someone goes looking and comes back empty-handed. The dangerous outcome is data that arrives quietly wrong: a truncated description field, an order status mapped to the wrong bucket, a balance that lost a decimal place. Those surface months later, usually through a customer complaint rather than an alert.

You do not have to move everything

Start by sorting data into three boxes. Master data: customers, products, suppliers, chart of accounts, price lists. Open transactions: pending orders, unpaid invoices, current stock levels, open service tickets. Closed history: orders delivered three years ago, closed periods, archived correspondence. The first two boxes belong in the new system. The third usually does not.

Deciding to migrate closed history is the single choice that most reliably doubles a project's timeline. Fitting five years of order history into a new schema means fighting business rules that schema never knew about: discount types nobody uses anymore, warehouses that closed, company codes that merged. The cheaper path is normally this: freeze the old database as a read-only archive, put one search screen in front of it, and carry only opening balances and summary totals into the new system.

Retention rules shape this decision without forcing a migration. In Turkey, article 82 of the Turkish Commercial Code sets a ten-year retention period for commercial books and supporting documents, while article 253 of the Tax Procedure Law sets five years counted from the calendar year following the one the records belong to. The obligation is to keep records accessible and readable, not to keep them inside your live application tables.

Measure the data before you move it

Profiling is the cheapest step in a migration and the one most often skipped. For each table, produce a handful of numbers: row count, null rate in fields that will become mandatory, duplicate count, malformed value count, orphan record count. One day of work tells you the real size of the project.

The picture that comes back tends to rhyme across companies. The same customer sitting in the table four times under four spellings. Tax IDs stored with dashes, spaces and dots in the same column. Hundreds of 1900-01-01 birth dates standing in for "unknown". Order lines pointing at products that were deleted. A few hundred records with an empty currency field, left blank back when there was only one currency. Every one of these becomes a decision the moment the new system makes that field required.

Clean at the source wherever you can. Every cleanup rule written into the migration script runs again on every rehearsal, and meanwhile the team still entering data in the old system keeps producing the same defect. Each cleanup decision also needs an owner. "Which of these 1,200 duplicate customer records survives" is not a developer's call; the answer lives with the people who deal with those customers.

The mapping sheet is the real contract

Field mapping is documentation work, and it lives in one table: source table and column, target field, transformation rule, what to write when the source is empty, who decided. Developers do not fill this in, they implement what has been filled in. A mapping approved without the business actually reading it turns into a "that is not how we used it" meeting three weeks after go-live.

The hard cases cluster in predictable places. Splitting one column into two, such as deriving first and last name from a single name field, including what happens when there are three words. Enum mismatches: eleven order statuses in the old system, six in the new one, and no written answer for the other five. Decimal precision, currency and exchange rate columns. Character encoding, which in Turkish data means legacy latin5 tables and the accented characters that come out of them. Then field length: if the old description column holds 500 characters and the new one holds 255, depending on database settings the overflow can be dropped without raising an error. Silent truncation is the nastiest failure mode in this whole exercise, because row counts still reconcile perfectly.

Keep the old identifiers

For every migrated record, keep a table mapping the legacy identifier to the new one, and keep it after the project ends. It earns its place in four situations: finding the gap during reconciliation, supporting a customer who calls about "order 148322", serving integrations that still send legacy identifiers, and re-running the migration.

Which leads to the second rule: the migration script has to be safely re-runnable. Put a unique index on the legacy identifier column in the target and write the load as "update if present, insert if not". The script will fail partway through at some point. A network drops, a disk fills, one table throws on an unexpected value. A half-finished load that does not duplicate records when it runs again makes cutover night far less tense.

A plan you have not rehearsed is not a plan

Rehearse against a full production copy, not a sample. Samples hide both the runtime and the data-quality surprises. Measure three things every time: total duration, number of rejected records, and which table failed with what error. If those three numbers are not falling rehearsal over rehearsal, you are not ready.

Duration alone can change the plan. If the load takes 26 hours and your window is eight hours on a Saturday night, you want to know that three months out. Knowing early means you still have options: build indexes after loading instead of during, load tables in parallel, drop historical data from scope, or switch strategy altogether. At 2am on cutover night none of those options exist.

The production copy in your rehearsal environment should have personal data masked and real API keys and payment provider credentials stripped out. Feeding test environments with production-shaped data is the right instinct. Feeding them with unmasked production data hands every person with development access a full customer database.

Count before you call it migrated

Reconciliation works in four layers. Row counts per table. Sums of monetary and numeric columns: total receivables balance, stock quantity per SKU, invoice totals per period. Checksum comparison on key columns. And an end-to-end eyeball check of fifty randomly chosen records, ideally by someone who works with that data daily.

Tooling helps here. AWS Database Migration Service runs validation as part of the task: it compares each source row against its counterpart in the target, checks row counts and checksums, and when you are using ongoing change capture it keeps validating the incremental changes that arrive after the full load. The expected state before cutover is replication lag at zero. If you are not using a tool, write the same checks by hand, because having the checks matters more than what runs them.

Have finance and operations sign the reconciliation report, not the development team. Nobody is obliged to trust a "migration completed successfully" line in a log file. An accountant comparing the numbers against a report they already know by heart is the best test instrument on the project. If the numbers still disagree after the migration, the cause may be definitional rather than technical, which is the subject of why every report shows a different number.

Big bang or phased

A big bang cutover is the simplest shape: freeze data entry, run the load, pass the checks, open the new system. Easy to test, easy to explain. In exchange the rollback window is narrow and the pressure on cutover night is high. When the data volume is manageable and planned downtime is possible, it is usually the right call.

A phased migration moves module by module, or branch by branch, with both systems live for a while. It splits the risk and doubles the operational load, and it forces one question into the open: right now, which system owns this record? If that answer is not written down for every data type, a phased migration becomes a machine for producing duplicate records. If you are building a temporary sync bridge between the two, it is worth reading first about where data synchronisation between systems breaks.

A third option is change data capture: full load first, then continuous replication of source changes, cutting over when lag reaches zero. It shrinks downtime to minutes but requires support from the source database and extra infrastructure to run. The fourth is a parallel run, where both systems operate for a period and outputs are compared. That is the safest and the most expensive; for irreversible processes such as month-end close, running one full cycle in parallel is reasonable insurance. Two questions decide the choice: how many hours of downtime you can absorb, and whether your team can sustain dual data entry for a while.

Cutover day needs a script, a clock and a name

The cutover plan is written minute by minute: the hour data entry freezes, when the final backup completes, each load step with its expected duration, the checkpoints, and who owns each step. One name should be written down as the person who decides at each checkpoint whether to continue. In a crowded room where nobody holds the authority to stop, a cutover that is going badly runs until morning.

Rollback needs a written definition too, and above all a stated point of no return. After that hour, "let us go back to the old system" also means unwinding the records people have already entered in the new one, which is rarely possible. That is why the rollback decision belongs at a predefined checkpoint rather than in the small hours. Several of the techniques in zero-downtime deployments transfer directly to shortening the freeze window.

Data migrated is not the same as migration succeeded

In April 2018, TSB moved 5.2 million customers' data onto a new banking platform. The regulators' finding is worth sitting with: the data migration itself completed successfully, and the platform failed technically almost immediately afterwards. Disruption across branch, telephone, online and mobile banking continued in some form until December of that year. In December 2022 the FCA and PRA fined the bank a combined £48.65 million (£29.75m from the FCA, £18.9m from the PRA), and the bank paid £32.7 million in redress to affected customers.

The lesson is about scope rather than technique. "The data moved" and "the cutover worked" are different statements. Whether the receiving platform holds up under real load, whether integrations cope with the new volume, and whether the support team is staffed for the first week's call spike all belong inside the migration project. Load testing and capacity rehearsal deserve the same place in the plan as data validation.

Go-live is not the finish line

Re-run reconciliation daily for the first month, then weekly. Open an exception queue for records that failed to migrate and name the person who works it. A "we will look at it later" list becomes a spreadsheet nobody opens six months on, and the records in it usually belong to your most complicated customers.

Keep someone who understands the old data model reachable for at least another quarter. When the consultant whose contract ended walks out, so does the answer to "why does this field always contain 3". Birmingham City Council's Oracle programme is the extreme version of this risk: budgeted at £19 million, it left the council unable to produce auditable accounts for an extended period after its bank reconciliation process broke, and the most recent reported forecasts put the total cost at £144 million. The problem there was not a single technical fault but the inability to repair reconciliation after go-live.

A first step that fits this week

Pick your three most important tables, which in most companies means customers, products and open orders. Produce a one-page profile of each: row count, null rate in the fields that will become mandatory, duplicate count, malformed value count, orphan count.

Then write a first draft of the mapping sheet for those same three tables and walk the business through it line by line in a single meeting. Count how many times you hear "actually that field means something else". That number will tell you more about the true size of the project than any estimation session.