Data Migration Testing
Data migration is where most ERP programmes fail. The go-live looks fine until the first month-end close, when finance discovers that 40,000 transactions migrated with the wrong cost centre. This lesson teaches you to test the migration itself, not just the destination.
1 The Hook
A Wellington council migrates 8 years of rates payment history — 1.2 million records — from a legacy property management system to Oracle Fusion. Mock migration runs pass visual inspection. Go-live is 1 July.
By 15 July, the finance team notices that the prior-year balance on 3,200 accounts does not match the audited accounts. Investigation reveals the migration script used the account creation date instead of the balance date for a subset of records. Root cause: nobody ran a reconciliation test against the migration extract. The target system showed the data. Nobody checked whether the values were correct.
The correction takes 6 weeks of manual rework at an estimated $180,000 NZD. The council’s annual accounts are delayed. The auditors flag a material weakness. And the go-live that looked clean on day one turns out to have been broken from the first record loaded.
2 The Rule
Never accept a migration on visual inspection alone. Every migration must be verified by count, by sum, and by sample — and the reconciliation must be signed off before go-live, not after.
3 The Analogy
Testing a data migration is like counting bank notes before signing for a cash transfer.
The bag looks full. But you count every note, add them up, and verify the total matches the receipt. You check a sample of serial numbers against the list. Only then do you sign. Handing over a bag of cash because it “looks about right” is not an acceptable control — and neither is accepting a data migration because the target system displays rows.
4 Watch Me Do It
ETL verification queries for a council rates migration from a legacy property system to Oracle Fusion GL. Run these against both source and target after each mock migration run.
SELECT 'Source' AS system, COUNT(*) AS record_count FROM legacy_rates
UNION ALL
SELECT 'Target', COUNT(*) FROM oracle_rates_gl;
-- Must match exactly. If not, find the gap before proceeding.
-- Step 2: Financial total reconciliation
SELECT 'Source', SUM(outstanding_balance) FROM legacy_rates WHERE status = 'Active'
UNION ALL
SELECT 'Target', SUM(balance_amount) FROM oracle_rates_gl WHERE account_status = 'A';
-- Must match to the cent. A $0.01 discrepancy across 1.2M records is a bug.
-- Step 3: Sample verification (spot-check 50 accounts)
SELECT l.property_id, l.owner_name, l.outstanding_balance,
o.account_number, o.account_name, o.balance_amount,
CASE WHEN l.outstanding_balance = o.balance_amount THEN 'PASS' ELSE 'FAIL' END AS result
FROM legacy_rates l
JOIN oracle_rates_gl o ON l.property_id = o.source_id
ORDER BY RANDOM() LIMIT 50;
-- Review every FAIL row. One pattern = a systemic ETL bug.
-- Step 4: Orphan check (records in source missing from target)
SELECT l.property_id FROM legacy_rates l
LEFT JOIN oracle_rates_gl o ON l.property_id = o.source_id
WHERE o.source_id IS NULL;
-- Any row here is a missing record. Investigate before sign-off.
5 When to Use It
Every data migration, regardless of record count. Even 1,000 records can contain financial errors that matter — and the reconciliation approach scales to any size.
Critical scenarios where this discipline is non-negotiable:
- ERP go-live migrations — all historical balances, open transactions, and master data moving to the new system
- System replacement — replacing a legacy system while keeping the data, often with a complex field-mapping exercise
- Archive migrations — moving data to a long-term archive platform; the data may not be touched for years but must be correct when it is
- Year-end close migrations — migrating mid-year when opening balances must match the prior-year signed accounts to the cent
Do not skip reconciliation because the record count is small. A council with 200 properties still needs accurate rates balances. The size of the dataset changes the effort, not the principle.
6 Common Mistakes
🚫 “If the target system shows the data, the migration passed.”
Actually: the target system shows whatever was loaded. It has no idea whether the values are correct — only that rows exist. A migration that loaded 40,000 records with the wrong cost centre will display those records perfectly. Reconciliation against the source is the only verification that the values are right.
🚫 “One successful mock run is enough.”
Actually: run at least 3 mock migrations. The first run finds the obvious errors. The second confirms the fixes. The third verifies repeatability — essential for a controlled cutover where the migration must produce the same results every time it runs. A migration that passes once but varies on re-run is not production-ready.
🚫 “Data quality issues in the source are out of scope for migration testing.”
Actually: bad source data migrates as bad target data. The ETL script faithfully reproduces the problem. Identify and remediate data quality issues before migration, or document each known exception with a business decision about what to do. “We know 34 accounts have missing balance dates — business has approved loading them as zero balance” is an acceptable position. Silence is not.
7 Now You Try
Paste or edit the prompt below, then click Run to get a real AI response.
8 Self-Check
Click each question to reveal the answer.
Q1: What is a mock migration run and why do you need at least three of them?
A mock migration run is a full end-to-end execution of the migration ETL in the test environment, treating it exactly as if it were the production cutover. You need at least three because: the first run surfaces the obvious ETL defects; the second confirms the fixes work; and the third verifies that the process is repeatable — that it produces identical results when run again. A migration that varies between runs has non-deterministic logic that will cause problems during the real cutover, which often involves a tight maintenance window with no tolerance for surprises.
Q2: The source system has 10,000 records. The target shows 9,987. What is your next step?
Do not accept the migration. 13 missing records requires an explanation before sign-off. Run the orphan check query (source LEFT JOIN target WHERE target IS NULL) to identify which 13 records are missing. Investigate the ETL log for errors or rejections on those records. Determine whether they were rejected due to data quality issues in the source, a filtering rule in the ETL, or an ETL defect. Document the finding, get a business decision on each record, and fix the ETL if it is a defect. Never accept a count discrepancy without a documented root cause.
Q3: What is a “day one balance” test and when is it run?
A day one balance test verifies that the opening balances in the new system on the first day of operation match the closing balances from the old system on the last day before cutover. It is the final reconciliation gate before go-live. It is run immediately after the production migration completes, before business users are given access, and before any transactions are processed in the new system. If the day one balances do not reconcile, you roll back — you do not open for business on incorrect data.
9 ISTQB Mapping
CTAL-TM v3.0 — Section 3.2: Test planning, including defining the scope of migration testing as a distinct test phase separate from functional SIT and UAT.
CTAL-TA v3.1.2 — Section 3.2.2: Integration testing applicable to ETL pipeline verification — verifying that data flows correctly through the transformation layer from source to target, and that error handling in the pipeline is tested explicitly.