"The migration is done" is a claim; a parity suite is the evidence.
Writing row-count, aggregate, and sampled-row comparisons for every table in a migration wave is exactly the kind of schema-driven, repetitive generation LLMs excel at — and because the tests themselves are verifiable (they either catch seeded errors or they do not), this is one of the safest places to lean on AI hard. This playbook generates a layered reconciliation suite from schema metadata and proves the suite works before you trust what it says.
Use this at the validation stage of any migration — warehouse-to-warehouse, legacy ETL to dbt, or an A/B rewrite of an existing pipeline — and for recurring pre/post checks around risky backfills.
Triggers:
Assemble the metadata that drives generation. For each table pair: full column list with types, primary/natural key, row-count order of magnitude, and any known acceptable differences (dropped legacy columns, renamed fields, timezone normalization). This last list matters most — undocumented "acceptable" differences are how real discrepancies get waved through.
Generate the layered suite per table.
Generate a reconciliation suite comparing legacy.orders (source) with
analytics.stg_orders (target). Schema and key below.
Produce ANSI SQL (DuckDB-compatible) for three layers:
1. Row count: exact counts both sides, plus counts by order_month.
2. Aggregate fingerprints: per order_month — SUM/MIN/MAX of every
numeric column, MIN/MAX of every date column, COUNT(DISTINCT) of
every key and low-cardinality column.
3. Row-level: full-row hash comparison via deterministic sample —
MOD(ABS(order_id), 1009) = 0 — comparing MD5 of concatenated
normalized columns (cast to canonical text; COALESCE nulls to
'<NULL>'; trim trailing spaces; timestamps to UTC ISO-8601).
Known acceptable differences (encode these, do not flag them):
- target drops legacy column op_flag
- legacy created_ts is local time; target is UTC.
Output each layer as a query returning zero rows when parity holds,
with columns identifying what differs and by how much.
[paste both schemas + key]
Review the normalization rules — that is where the bugs are. Concatenation without delimiters lets ('ab','c') hash-equal ('a','bc'); float formatting differs across engines; NULL handling in string casts varies. Insist on a delimiter, canonical numeric formatting (fixed scale for decimals), and explicit NULL tokens. Ask the assistant to state each normalization choice and why.
Prove the suite catches errors before trusting green. Seed defects and confirm red — this is the verification step, and it is non-negotiable:
-- copy target, then corrupt it three ways
CREATE TABLE audit.stg_orders_mutated AS SELECT * FROM analytics.stg_orders;
DELETE FROM audit.stg_orders_mutated
WHERE MOD(ABS(order_id), 5000) = 17; -- missing rows
UPDATE audit.stg_orders_mutated SET amount = amount + 0.01
WHERE MOD(ABS(order_id), 3000) = 5; -- value drift
UPDATE audit.stg_orders_mutated SET status = 'X'
WHERE MOD(ABS(order_id), 7000) = 2; -- category corruption
Run the suite against the mutated copy. All three defect classes must surface in the correct layer. A suite that stays green here is decoration, and this is a common outcome on the first pass — usually a fingerprint that skips the mutated column, or a sample that misses the mutated rows.
Run against the real pair and disposition every diff. Each nonzero result gets a written classification: conversion bug (fix and rerun), legacy defect intentionally not carried forward (stakeholder sign-off), or an undocumented acceptable difference (add to the encoded list, with justification, and regenerate). No diff is closed by silence.
Package for reruns. Have the assistant wrap the suite into a parameterized script or dbt tests that emit one summary row per table per layer, so the same suite runs nightly during parallel-run and at final cutover without hand-editing.
Never trust a green parity suite that has not first been shown to turn red. Seeded-defect testing of the tests takes twenty minutes and is the difference between evidence and reassurance.
Parity suites are generated from schemas, not data — so in restricted engagements, genericize the schema (neutral table/column tokens via a scripted mapping) and have the suite generated by an approved-tenancy Bedrock/Azure OpenAI model or local Ollama, then map names back before execution. All comparisons, seeded-defect runs, and diff dispositions execute inside the client's platforms; sampled-row output used in dispositions should be masked before it enters any ticket or report. The resulting evidence pack — suite, seeded-defect proof, per-table results, signed dispositions — is exactly what client audit teams ask for at cutover.