Data migration

Why it matters

Data migration is the part of a system replacement most likely to delay go-live. The new system can be perfectly configured, but if customers' loyalty points are wrong on Monday morning, store staff will lose trust in it within an hour. A good migration is planned like a small project of its own, rehearsed several times, and proven correct with numbers rather than with "it looked fine".

The core idea: extract, transform, load, reconcile

1. Extract data from the source systems into a staging area, ideally a read-only snapshot.
2. Transform it with the rules from the source-to-target mapping: reformat, look up codes, de-duplicate, apply defaults.
3. Load it into the target, in dependency order (customers before orders, orders before order lines), using the target's import tools or APIs.
4. Reconcile: prove the target matches the source after transformation, and explain every difference.

The migration plan

A migration plan answers:

• Scope: which entities and how much history ("all customers; orders from the last 3 years; older orders archived as read-only files").
• Data quality and cleansing: what is fixed in the source before migration, what is fixed by rules, and who approves.
• Approach: big bang (everything moves in one cutover window) or phased / trickle (by region, by entity, or with both systems running in parallel for a while). Big bang is simpler; phased reduces risk but needs temporary interfaces.
• Rehearsals: at least two or three full trial migrations into a test environment, timed, with the same scripts as the real one.
• Reconciliation checks and sign-off criteria.
• Fallback: what happens if the migration fails during cutover, and the latest time you can decide to roll back.

Reconciliation: proving it worked

Reconciliation works at three levels, from cheapest to most thorough:

1. Counts         source rows in scope vs target rows, per entity
2. Totals         sum of money and points, per entity and per period
3. Record level   every key matched, and key fields compared

Counts and totals are quick and catch big failures. Record-level checks find the specific records to fix. A reconciliation report looks like this:

Entity      Source  Target  Diff  Explained
Customers   180,412 180,377   35  35 duplicates merged (list attached)
Orders      612,950 612,948    2  2 orders failed validation (ticket MIG-41)
Points sum  9.81M   9.81M      0  -

Every difference must be explained in writing, not just small. "Two orders missing" is a defect until someone proves why.

Worked example: finding exceptions in SQL

With source and target tables side by side (or extracted to a staging database), set-based queries find every exception:

-- In source but not in target
SELECT s.order_id
FROM source_orders AS s
LEFT JOIN target_orders AS t ON t.order_id = s.order_id
WHERE t.order_id IS NULL;

The same pattern, with the tables swapped, finds records that appeared in the target without a source (for example test data left behind). Comparing key fields where both sides exist finds transformation errors, such as an amount that lost a digit. Storing money as whole cents avoids rounding noise in these comparisons.

Cutover timing

Measure each rehearsal. If extract, transform, load and reconciliation take 14 hours, a weekend window works; if they take 50 hours, you need a different approach (phased, or migrating history in advance and only the latest changes at cutover, often called a delta load).

Common mistakes

• Treating migration as a one-off script written in the last month.
• No rehearsals, so the first real run is the cutover.
• Reconciling counts only; a wrong amount passes a count check.
• Migrating everything "just in case", including decades of history nobody uses.
• Unclear ownership: the business, not IT, must sign off that the data is right.

Quick recap

• Extract, transform with the mapping, load in dependency order, then reconcile.
• Plan scope, cleansing, approach (big bang or phased), rehearsals and fallback.
• Reconcile counts, totals and records, and explain every difference.

Try it

In the Do tab you write a reconciliation query that lists every exception from a trial migration.