Data Migration & ETL Validation
Data migration: 1,200 orders moved to a new schema, reconciled to the cent
SQL reconciliation caught 8 seeded data defects in a migration, including 176 orders lost without a trace, $245,963.48 of missing order value and 371 order dates with day and month swapped.
Demonstration on synthetic data in my own demo project, not client work.
Scenario
The shop's legacy order database (300 customers, 24 products, 1,200 orders, 2,966 order lines) moves to a new schema with amounts in cents, ISO dates and a fixed set of statuses. The cut-over is a bulk load at a snapshot, then a delta load for everything that changed while it ran. Like most legacy data, the source has money stored as text, accented names, DD/MM/YYYY dates and five spellings of "shipped".
Risks
- Orders lost without anyone noticing
- Amounts drifting by a cent per row, so finance cannot reconcile
- Dates silently changed, breaking order history and return windows
- Customer names corrupted on invoices and emails
- Orders shown with the wrong status
- Retries and late changes duplicating or losing data
Tests
- Row counts for every table: source = migrated + rejected with a reason
- Every amount compared row by row and as a checksum, in SQL that works out the expected value from the source without reusing the migration code
- Transformation rules: names, emails, DD/MM/YYYY dates, the status mapping table, product links
- Delta load: late changes, new orders, the change made exactly on the snapshot second, duplicates and a second run
- Guards on the test data, so it keeps containing every awkward value the checks rely on
Defects found
| ID | Defect | Type |
|---|---|---|
| DM01 | Rows that fail conversion are skipped silently: 176 of 1,200 orders and 546 lines lost, nothing recorded | High |
| DM02 | Money truncated when converted to cents: 139 order totals, 9 prices, 1,006 line prices a cent short | High |
| DM04 | Day and month swapped in dates: 371 orders, 106 customers | High |
| DM07 | Re-running the delta load duplicates order lines: 50 duplicate lines, more on each retry | High |
| DM08 | Late changes to existing orders are ignored: 20 delivered orders still "shipped" | High |
| DM03 | Accented names corrupted ("Noémie" → "No?mie"): 72 customers | Medium |
| DM05 | Unknown status spellings become "placed": 246 orders | Medium |
| DM06 | Lower-case product codes lose their product link: 96 order lines | Medium |
Evidence
- Data migration lab in the Proof Lab — Defect table and headline numbers
Fix
v1.1 converts money with decimal arithmetic rounded to the nearest cent, reads dates day first, keeps every character of a name, maps all status spellings and rejects unknown ones, and matches product codes ignoring case.
Rows that cannot be converted go to a rejects table with a reason. The delta load updates existing orders and replaces their lines instead of appending them.
Result
- v1.0 (seeded defects)
- 8 of 8 defects detected; 5 of 16 reconciliation checks pass
- v1.1 orders
- 1,197 migrated + 3 unreadable ones rejected with a reason = 1,200
- v1.1 order value
- $554,646.51 in both systems, 16 of 16 checks pass
- Second delta run
- Changes nothing on v1.1; adds more duplicates on v1.0
Lessons
- Valid is not correct: a status check and an orphan check both passed on the broken migration. Only comparing with the source found the problems.
- Make rejects explicit. "Source = migrated + rejected" turns silent row loss into a number someone has to sign off.
- Test the second run. Retries and late changes are where incremental loads break.
Need this for your product?
Get a suggested scope and starting price in four questions, or tell me about your project.
Next case study: Online shop API: a customer-data leak and pricing bugs caught before launch →