How to migrate systems without losing data

Map the schema before a single record moves

Start by writing down what every field in the old system means and where it lands in the new one, because the expensive surprises are always semantic rather than technical. Two systems both having a “status” column tells you nothing about whether they mean the same six things by it.

This is also where you find the data that has no home: the free-text field three people used as a workflow flag, the customer records with two spellings of the same company, the historical rows that predate a convention nobody documented. Every one of those is a decision, and making them on a whiteboard is cheap. Making them at 2am during a cutover is not.

Run both systems in parallel

Write to both sides for a period before you commit to either, so the new system accumulates real production data under real conditions while the old one is still the source of truth. This is what turns a migration from an event into a process — and it is the difference between discovering a mapping error on a Tuesday afternoon and discovering it after the old system is gone.

Parallel running costs real time and some duplicated infrastructure, which is why it is the first thing cut from an optimistic plan. It is also the single control that most reduces the chance of a migration going badly, so it should be the last thing cut, not the first.

Reconcile every record, not a sample

Spot checks find the errors that are everywhere and miss the ones that matter. A mapping bug affecting 0.3% of records — one currency, one legacy status, one date format that flips day and month — will pass a sample of twenty and quietly corrupt a few thousand rows. Count and compare the whole set instead, and do it on a schedule rather than once.

The useful shape is a reconciliation you can re-run on demand and read in a few seconds: totals per entity, sums of the numbers that have to balance, and a list of what does not match.

-- Row counts and money totals per status, old vs new.
-- Anything with a non-zero delta blocks cutover.
SELECT
  COALESCE(o.status, n.status)          AS status,
  COALESCE(o.rows,  0)                  AS old_rows,
  COALESCE(n.rows,  0)                  AS new_rows,
  COALESCE(n.rows,  0) - COALESCE(o.rows, 0)     AS row_delta,
  COALESCE(n.total, 0) - COALESCE(o.total, 0)    AS total_delta
FROM (
  SELECT status, COUNT(*) AS rows, SUM(amount_cents) AS total
  FROM legacy.invoices
  WHERE created_at < :cutoff
  GROUP BY status
) o
FULL OUTER JOIN (
  SELECT status, COUNT(*) AS rows, SUM(amount_cents) AS total
  FROM app.invoices
  WHERE created_at < :cutoff
  GROUP BY status
) n USING (status)
WHERE COALESCE(o.rows, 0)  <> COALESCE(n.rows, 0)
   OR COALESCE(o.total, 0) <> COALESCE(n.total, 0);

An empty result set is the cutover criterion. Not “looks right”, not a demo — an empty result, reproduced on consecutive nights.

Keep the rollback path open past cutover

Cutover is a routing change, not a finish line, and the old system should stay warm and writable for a defined window afterward. The reason is unglamorous: some errors only appear under the parts of the business cycle you did not sit through — a month-end close, a quarterly report, an annual renewal run — and those can land weeks after everyone has declared victory.

Decide up front what would trigger a rollback and who gets to call it. A rollback path nobody has agreed on is a rollback path that turns into an argument at exactly the wrong moment.

What this looks like for a website

The same discipline applies, with URLs standing in for records: the redirect map is the schema mapping, and rankings are the thing that disappears silently if you get it wrong. Every old URL is accounted for before cutover and verified after, which is why a migration is scoped by template and URL count rather than by page count — the same reason switching page builders is a project rather than a setting.

Whichever direction the move runs, the controls are the same ones I build every systems integration around: mapping first, both sides live, reconciliation you can re-run, and a way back until the numbers say you do not need one.

Questions