How to Build a Data Migration Strategy for Legacy Modernization

A modernization project can ship new code every week and roll a bad release back in minutes. Data does not work that way: once customers write to the new database, going back means taking their changes with you. That is why the data migration sets the cutover date and carries most of the risk, and why its strategy should be built around keeping a way back open.

What is a data migration strategy?

A data migration strategy is the plan for moving a system's data from its current store to a new one without losing records, changing their meaning or stopping the business for longer than it can afford. It is written before any tool is chosen, and it answers six questions:

1. Which data moves, which is archived and which is left behind?
2. How does each table and field map to the target model, and who signs off the rules?
3. Which method: one cutover, part by part, or continuous replication?
4. How do we prove that the target matches the source?
5. What happens at cutover, and up to which point can we still go back?
6. When is the old store switched off?

In a legacy modernization, the data migration is rarely a separate project. It runs alongside the code changes, often for months, and it decides when the old system can finally be switched off.

Why data migration decides the risk of a legacy modernization

Code and data fail differently. A broken release is replaced with the previous one. A broken migration leaves the business running on records it cannot trust, and every hour of new transactions makes the way back harder.

TSB's 2018 migration shows the difference. In April 2018 the bank moved the data for its corporate and customer services to a new platform. According to the Financial Conduct Authority, the data itself migrated successfully, but the platform failed as soon as it went live: a significant share of TSB's 5.2 million customers were affected, the bank did not return to business as usual until December 2018, and it was fined £48.65 million in 2022. TSB's board noted that the go-live decision followed nine successful dress rehearsals.

Correct rows are only half of a migration. The other half is whether the business runs on the new system the morning after, and whether there is a way back if it does not. Our legacy system modernization guide covers the TSB program and other big-bang replacements in more detail.

Done well, the same work pays for itself. Amazon's consumer business moved 75 petabytes from nearly 7,500 Oracle databases to AWS database services "with little or no downtime", and reported database costs down by more than 60% and latency of consumer-facing applications down by 40% (AWS News Blog, 2019; Amazon sells the services it moved to).

Legacy data migration challenges

The difficult parts of a legacy data migration are rarely the volume. They are the rules that nobody wrote down.

Rules hidden in the data and in old code

A system that has run for fifteen years encodes business rules in its data: a status code that means something different before and after a 2014 release, a date of 31 December 9999 that stands for "no end", a negative quantity that marks a return. The code that interprets those values is the only documentation, and the new system has to interpret them the same way or the migration changes the meaning of records that look identical.

Database engines add their own semantics. Oracle treats an empty string as NULL; PostgreSQL does not. A query that finds customers with no email address returns different rows on the two databases, and nothing fails to tell you.

Data quality problems you find only on the first load

Constraints the old system never enforced turn up as load errors: orders that point to deleted customers, duplicates that differ only in letter case, phone numbers in an address field. A target schema with proper foreign keys and checks rejects them, so each class of bad record needs a decision: fix it at the source, fix it in the mapping, or load it into a quarantine table for the business to clear.

Profile the data before designing the mapping, not after the first failed load.

Volume and the downtime window

The arithmetic is worth doing early. At an effective 200 MB per second, copying 4 TB takes about 5.6 hours, before indexes are rebuilt, constraints validated and the reconciliation run. (The numbers are illustrative; measure your own throughput in a rehearsal.) If the business can stop for a night but the load takes two, the method is decided before any other question is asked.

Everything else that reads the database

Legacy databases are integration points. Reports query them directly, batch jobs write to them at night, other applications read them through database links, and a spreadsheet in finance pulls from a view nobody remembers creating. Each one breaks when the database moves.

The inventory of what touches the database is as much part of the strategy as the schema mapping. Database audit logs and connection logs over a full month, including a month end, find most of them.

Big bang vs phased vs zero downtime migration

Every data migration strategy picks one of three ways to move the data, and the right one follows from the downtime the business can accept and from whether the data splits into independent parts.

Big bang migration: when it is the right call

A big bang migration moves everything in one window. It has a bad reputation because the failures are public, but for a system with tens of gigabytes, a quiet weekend and a handful of integrations it is the cheapest and simplest option, and nothing has to keep two databases in step.

It works when the window is real: the full load, the reconciliation and the smoke tests fit with hours to spare, measured in a rehearsal on production-sized data. The rollback is a restore of the old system, so the plan has to say what happens to transactions written to the new one before the decision to go back.

Phased migration by module or tenant

A phased migration moves the data part by part: one module, one country, one group of customers at a time. Each part has a smaller window and its own way back, and the first one teaches the team what the rehearsals missed.

The cost is the period in between. While some data has moved and some has not, the two systems have to exchange data, and features that span parts need both. It suits multi-tenant systems best, where each tenant's data is already separate.

Zero downtime migration with change data capture

A zero downtime migration keeps both databases live and moves the system across in steps that can each be reversed:

1. An initial load copies the data as of a known point.
2. Change data capture (CDC) reads the source's transaction log and applies every later change to the target.
3. Reconciliation runs continuously until source and target agree.
4. Reads move to the target, behind a switch that can be flipped back.
5. Writes move to the target; the old store is kept in sync in the other direction as a fallback.
6. The old store is switched off once the new one has run through a full business cycle.

The tools for this exist for most engines. Debezium's Oracle connector reads changes through Oracle's LogMiner API; AWS Database Migration Service runs a full load and then applies captured changes to keep source and target in sync; PostgreSQL's logical replication does the same between PostgreSQL databases.

Engineering teams that have published their migrations describe the same shape. Stripe's four steps were dual writing, then moving reads, then moving writes, then removing the old data. Notion took its database down for five minutes of scheduled maintenance when it sharded Postgres in 2021, and moved to a larger cluster in 2023 without downtime. Figma chose to avoid double writes altogether and rolled out its sharded reads and writes behind feature flags, so it could roll back within seconds by routing traffic back to the original tables.

We prefer CDC to dual writes from application code. Two writes in one request have no transaction around them, so one can succeed and the other fail, and the stores drift apart quietly; our strangler fig guide explains the failure. Stripe made dual writing work by backfilling and comparing afterwards, which is the same reconciliation by another route.

We used the same pattern, an initial load followed by a stream of changes, on a UK hotel metasearch engine: a fast loader populated an in-memory data grid from the database, and a message queue carried intraday updates, with the database kept as the system of record. The engine went on to serve 300 million queries a day at 80% lower infrastructure cost (case study).

Oracle to PostgreSQL migration: what changes besides the syntax

Most Oracle to Postgres migrations start with a license renewal. The published results are encouraging: Samsung moved the accounts of 1.1 billion users off Oracle to Amazon Aurora in less than 18 months, and found that 85 to 90 percent of the PostgreSQL queries matched the existing Oracle ones (AWS case study). At Home, a US retailer, decommissioned its Oracle RAC cluster two months after moving to Aurora PostgreSQL.

The remaining 10 to 15 percent is where the project is spent. The differences that cause most of the work:

The conversion tools take the mechanical part. Ora2Pg, an open-source tool, scans an Oracle database, generates PostgreSQL DDL and converts PL/SQL to PL/pgSQL; its assessment report estimates the effort in cost units of five minutes each, and its own data migration is offline, with downtime. AWS offers the Schema Conversion Tool and DMS Schema Conversion, which flag what they cannot convert, and Google's Database Migration Service moves Oracle to Cloud SQL or AlloyDB for PostgreSQL.

For a Java application, the database change also reaches the code. Native queries need rewriting, the Hibernate dialect changes, and every place where Java code relied on Oracle turning "" into null behaves differently. Integration tests against a real PostgreSQL instance, run with Testcontainers in the build, catch most of it before the data moves.

How to develop a data migration strategy, step by step

These are the steps we follow to build a data migration strategy, in the order the decisions depend on each other.

1. Inventory the data and everything that touches it

List the schemas, tables and volumes, then every reader and writer: applications, batch jobs, reports, exports, database links. Mark what is still used. Tables nobody has read for two years are candidates for an archive rather than a migration.

2. Profile the data before designing the mapping

Run counts of nulls, distinct values, orphans and out-of-range values per column. The profile turns "the data is a bit messy" into a list of decisions with numbers next to them.

3. Write the mapping rules as code

Every transformation lives in version control and runs from a script, so the hundredth rehearsal does exactly what the first did. The business owner of the data signs off the rules, especially the ones that change meaning, such as merging duplicate customers or mapping old status codes.

4. Choose the method from the downtime budget

Ask the business how long it can stop writes, in writing, then compare that with the measured load time.

5. Build reconciliation in from the start

Row counts alone miss most errors. Compare business totals, null counts and value ranges in buckets, so a mismatch points to a small set of rows rather than a whole table. This query runs unchanged on Oracle and PostgreSQL:

-- Run on source and target; every bucket must match.
SELECT MOD(customer_id, 100)   AS bucket,
       COUNT(*)                AS row_count,
       SUM(balance_cents)      AS balance_total,
       COUNT(email)            AS emails_present,
       MIN(created_at)         AS first_created,
       MAX(created_at)         AS last_created
FROM customer
GROUP BY MOD(customer_id, 100)
ORDER BY bucket;

The emails_present column is there on purpose: on Oracle it skips empty strings, because they are NULL, and on PostgreSQL it counts them. A mismatch in that column is the empty-string rule showing up before a customer does. For a bucket that differs, compare the rows by primary key with a normalized hash computed in the migration code.

6. Rehearse on production-sized data

Run the whole migration, timed, on a copy of production at least three times, and change nothing between the last rehearsal and the real run. TSB's nine rehearsals show that rehearsing the data load is not enough on its own: the new system also has to be load-tested with the migrated data, under production traffic patterns.

7. Write the cutover runbook with a point of no return

The runbook lists every step with an owner, a duration and a check. It names the go/no-go criteria in advance (reconciliation clean, smoke tests green, error rates under a threshold) and the point of no return: the last moment at which going back is still a restore rather than a second migration. Agree that moment before the night, not during it.

8. Decommission the old store on a date

Keep the old database read-only for a fixed period after cutover, for audits and for answering "what did the old system say". Then switch it off. A legacy database left running "just in case" keeps its license, its patching and its integrations alive.

Data migration checklist before cutover

• Every reader and writer of the old database is listed, and each has a new source or a retirement date.
• Mapping rules are in version control, and the data owner has signed them off.
• Bad records have a decision: fixed at the source, fixed in the mapping or quarantined.
• The method matches the downtime budget the business confirmed in writing.
• Bucketed reconciliation runs clean on the last rehearsal, and on business totals the finance team recognizes.
• The last rehearsal ran end to end on production-sized data, within the window, with nothing changed since.
• The new system was load-tested with migrated data.
• The runbook names the go/no-go criteria, the owners and the point of no return.
• The old store has a read-only period and a switch-off date.

A data migration is usually one workstream inside a wider modernization. Our Application Modernization Sprint starts with a thirty-minute scoping call and a written plan for both: the order in which the code and the data move, the method for each, and a ballpark estimate.