Build Migration Parity Tests AI Cannot Talk Its Way Past

Tier: Advanced · Workflow area: Migration & Modernization · ~45 minutes

Cross-engine migrations fail in the last two percent: the conversion runs, the app starts, and three weeks later finance finds totals off by the rounding behavior of one engine's NUMERIC. The defense is a parity suite that executes the same logical checks against source and target and diffs the results mechanically. AI helps you draft the suite fast — but the suite's whole point is that it is code, not conversation, and a confident model explanation of why outputs "should match" counts for nothing against a failed diff.

When to use

• Any heterogeneous migrationOracle to PostgreSQL, SQL Server to Aurora, Sybase to anything. Different engines disagree on semantics in ways syntax conversion never surfaces.
• After AI-assisted procedure conversionthe output of a transpiler-plus-LLM pass (pb-13) is precisely the code most likely to be subtly wrong. Parity tests are its exit gate.
• Ongoing dual-run periodswhen old and new systems run in parallel, the suite becomes the nightly reconciliation that says the shadow system still agrees.

When NOT to use

• As the only test layerparity proves target matches source; it does not prove either is correct. Functional tests own that.
• On moving data without a sync pointcomparing a live source against a target loaded yesterday manufactures false failures. Fix a snapshot boundary (an LSN, an SCN, an export timestamp) first.
• When sampled-row comparison would move regulated datapulling row samples cross-environment may cross a compliance boundary. Compare hashes instead of values where classification demands it.

The recipe

1. 1.

   Draft the check inventory with AI — from the schema, not from hope. Feed the model both schemas and ask for a parity plan:

   Copy

   Source: Oracle 19c. Target: PostgreSQL 16. Both schemas below.
   Draft a migration parity test plan with four layers:
   
   1. ROW COUNTS — per table, plus per-table counts grouped by status/type
      columns you identify.
   2. AGGREGATES — SUM/MIN/MAX/COUNT DISTINCT on every numeric and date
      column that looks financial or key-like. Flag columns where the two
      engines could disagree on precision or rounding.
   3. SAMPLED ROWS — a deterministic sample spec (e.g., mod 997 on the PK)
      and the column list to compare verbatim.
   4. SEMANTIC EDGE CASES — for THIS schema, list concrete checks for:
      empty-string-vs-NULL, date/timestamp truncation and timezones,
      collation-dependent ordering and uniqueness, numeric precision and
      rounding, implicit type conversion in join keys.
   
   Output as a table: check_id, layer, table, SQL-source, SQL-target,
   comparison rule. Where dialects differ, write both dialects.
   
   [paste both schemas]
   

   Review the edge-case layer hardest — that is where the model's engine knowledge earns its keep, and where you add what it missed (your collations, your timezone columns).

2. 2.

   Wire the mechanical diff with datacompy — not with eyeballs. Pull both sides into DataFrames and let the library report:

   Copy

   import datacompy, pandas as pd
   
   src = pd.read_sql(CHECK["sql_source"], oracle_conn)
   tgt = pd.read_sql(CHECK["sql_target"], pg_conn)
   
   cmp = datacompy.Compare(src, tgt, join_columns=["id"],
                           abs_tol=0, rel_tol=0,
                           df1_name="oracle", df2_name="postgres")
   print(cmp.report())
   

   Start with zero tolerance. Every tolerance you later add must carry a written justification — "float column, engine rounding, tol 1e-9, approved by M.R." — or it is just a way to make red lights green.

3. 3.

   Use DuckDB as the neutral referee for file-based comparison. For large tables, export both sides to Parquet and diff without holding either engine's connection open:

   Copy

   -- duckdb
   SELECT COALESCE(s.id, t.id) AS id,
          s.amount AS src_amount, t.amount AS tgt_amount
   FROM 'oracle_export/orders.parquet' s
   FULL OUTER JOIN 'pg_export/orders.parquet' t USING (id)
   WHERE s.id IS NULL OR t.id IS NULL
      OR s.amount IS DISTINCT FROM t.amount
   LIMIT 100;
   

   IS DISTINCT FROM is the operator that treats NULL honestly — plain <> silently skips NULL disagreements, which is exactly the bug class you are hunting.

4. 4.

   Plant tripwires for the four classic engine disagreements. These checks catch real conversion bugs weekly in the wild:

   Copy

   -- Empty string vs NULL (Oracle '' IS NULL; Postgres it is not)
   SELECT COUNT(*) FROM customers WHERE trim(coalesce(middle_name,'')) = '';
   
   -- Date truncation drift
   SELECT COUNT(*), MIN(created_at), MAX(created_at)
   FROM orders WHERE created_at <> date_trunc('second', created_at);
   
   -- Collation-dependent uniqueness ('Müller' vs 'MULLER' vs 'muller')
   SELECT lower(name), COUNT(DISTINCT name) c FROM vendors
   GROUP BY lower(name) HAVING COUNT(DISTINCT name) > 1;
   
   -- Precision: does the sum survive the type mapping?
   SELECT SUM(amount), COUNT(*) FROM order_lines;
   

   Run each on both engines; the comparison rule is exact equality of the result sets. A finding looks like: SUM oracle 84,213,997.4300 / postgres 84,213,997.43 — equal; COUNT vendors collation groups: oracle 3, postgres 0 — INVESTIGATE.

5. 5.

   Run, record, and refuse narrative explanations. Execute the whole suite; land results in a scorecard table: check_id, source value, target value, verdict. When a check fails and the model (or a colleague) offers "that difference is expected because…", the response is fixed: encode the expectation as an explicit transform in the check, rerun, and require green. Expected differences become documented rules; unexplained differences block sign-off.

6. 6.

   Schedule it if there is a dual-run window. The suite that proved cutover parity becomes the nightly job during parallel running, with failures paging the migration channel — drift found on day 3 is a fix, drift found at decommission is an incident.

Make it yours

• SQL Server to PostgreSQLadd checks for datetime (3.33 ms rounding) vs timestamp, case-insensitive default collation vs Postgres case-sensitive, and MONEY precision.
• MySQL targetscheck zero-dates (0000-00-00), silent truncation history in old sql_mode data, and utf8mb3 vs utf8mb4 comparisons.
• Hash mode for regulated datareplace verbatim sampled-row comparison with per-row MD5 over a canonicalized concatenation (fixed formats for dates and numerics on both sides), so values never leave their environment.

Quality check

• Every check ran as code with a recorded resulta parity claim without a stored source/target value pair is an opinion.
• NULL-aware comparison everywhereaudit the suite for bare = / <> on nullable columns; each one is a hole.
• Tolerances are justified in writingan undocumented rel_tol is a silenced alarm.
• The snapshot boundary is statedevery scorecard names the SCN/LSN/export time both sides were frozen at; without it, failures are unactionable.
• Failed checks blocked sign-offif any red check was waved through on verbal explanation, the suite is theater.