Skip to content
Sharan L · QA Engineering
Menu

← All case studies

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

Tests

Defects found

IDDefectType
DM01Rows that fail conversion are skipped silently: 176 of 1,200 orders and 546 lines lost, nothing recordedHigh
DM02Money truncated when converted to cents: 139 order totals, 9 prices, 1,006 line prices a cent shortHigh
DM04Day and month swapped in dates: 371 orders, 106 customersHigh
DM07Re-running the delta load duplicates order lines: 50 duplicate lines, more on each retryHigh
DM08Late changes to existing orders are ignored: 20 delivered orders still "shipped"High
DM03Accented names corrupted ("Noémie" → "No?mie"): 72 customersMedium
DM05Unknown status spellings become "placed": 246 ordersMedium
DM06Lower-case product codes lose their product link: 96 order linesMedium

Evidence

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

  1. 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.
  2. Make rejects explicit. "Source = migrated + rejected" turns silent row loss into a number someone has to sign off.
  3. 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.

Scope BuilderGet a quote

Next case study: Online shop API: a customer-data leak and pricing bugs caught before launch →