How to validate a data warehouse migration: a reconciliation playbook

It means every in-scope object gives the same output on the same input, and you can show it. Not most objects. Not the reports someone spot-checked. Every object, with a result and a reason.

That sentence has four parts, and each one can fail on its own. "Every in-scope object" needs an agreed list. "Same output" needs a defined comparison. "Same input" needs a pinned snapshot. "Show it" needs evidence a stranger can read.

A migration that skips any part has not been reconciled. It has been tested. Testing finds some problems. Reconciliation proves the absence of differences on a stated input, object by object.

One limit belongs beside the claim. Reconciliation proves equivalence on the tested inputs. It does not prove the original logic was correct. If the old procedure double-counted refunds, the new one should too, and the report should say so.

Because data copies are checkable in bulk, and logic is not. Data migration is solved. Logic migration is not. The cost of proof is set by how many procedures, packages and jobs an estate has, not by how many terabytes.

Most warehouses carry thousands of procedures written by people who have left. They are trusted because they have always run. When that logic moves, every downstream report moves with it.

The debt is real at the budget level. In a McKinsey survey of 50 CIOs, 60% said their tech debt had risen noticeably over three years. The same CIOs estimated it at 20% to 40% of their technology estate's value.

Run cheap checks first and stop an object at its first failure. The order we use is schema, row counts, column aggregates, sampled or hashed rows, then the business owner's own check queries.

Each tier is more expensive than the one before. A schema check reads metadata. A row-level comparison may scan billions of rows on both platforms. There is no point scanning rows when a column is missing.

Stopping early is not cutting corners. A failed tier is a finding, with a cause. Fix it, rerun from the top, and let the object earn its pass through every tier again.

1. Schema: names, types, precision, scale, nullability and column order of every output.
2. Row counts: per output table, and per partition or business date where the volume is large.
3. Column aggregates: sums, counts, distinct counts, minimums and maximums on numeric and key columns.
4. Rows: sampled rows matched on keys and compared field by field, or keyed hashes where every row matters.
5. Business checks: the queries the business owner already trusts, run on both sides.

It confirms the converted object produces the same shape of output. That means the same columns, in the same order where order matters, with compatible types, precision, scale and nullability.

Type compatibility is the part teams underrate. A DECIMAL(18,4) that lands as a DOUBLE will pass a row count and fail an audit. A VARCHAR that becomes a fixed-width string changes how trailing spaces behave.

Write the type map down before conversion starts. Then the schema check tests against the map, not against someone's memory of the old system.

No. Row counts catch lost and duplicated rows, and nothing else. A join that swaps one customer's balance for another's keeps the count exact.

Still, counts are the cheapest strong signal you have. Count per output table, and per business date or partition on large tables. A total that matches can hide one day missing and another day loaded twice.

On the estates we have delivered, a count failure usually points to one of three things. A filter on a NULL that behaves differently, a join that fans out, or a source that moved between the two reads.

Compare sums, counts, distinct counts, minimums and maximums on every numeric and key column. Do it per output, and per business date where the table carries one.

Aggregates catch most value errors at a fraction of the cost of a row comparison. A rounding change shows up in the sum. A truncated key shows up in the distinct count. A time zone shift shows up in the minimum and maximum dates.

Count NULLs as their own aggregate. A column whose sum matches but whose NULL count moved has changed meaning, even if no total moved.

Use them when every row matters and sampling is not enough. Ledger balances, regulatory returns and customer-facing statements are the usual cases.

The method is simple. Build a key, normalize the remaining columns into one canonical string, hash it on both sides, then join on the key and compare hashes. Only rows with different hashes need a field-by-field look.

The hash has to be the same function over the same bytes on both platforms. Engines differ here. SQL Server's HASHBYTES deprecates every algorithm except SHA2_256 and SHA2_512 from SQL Server 2016. The xxhash64 function on Databricks depends on each input's data type, so cast inputs explicitly first.

In practice, hash a canonical string rather than native values. Cast every column to text in a fixed format, then hash that string with SHA2_256 on both sides. The canonical format is the real control, not the hash.

Normalize only for differences that are documented, agreed and the same for every row. Each rule must be written down before the run, and every rule must appear in the report.

Normalization is where reconciliation is most often bent. A rule that trims all strings will hide a real truncation. A rule that rounds all decimals to two places will hide a real rounding change. Narrow rules, named per column, keep the comparison honest.

Because each engine has its own defaults, and the defaults are rarely the same. Three cases from public documentation show the pattern.

Strings first. Databricks compares strings by their binary UTF-8 form by default, so 'A' and 'a' are not equal. Many legacy estates ran on case-insensitive collations, so the same join can match more rows on the source.

Time next. SQL Server's AT TIME ZONE resolves a daylight saving gap with the offset after the change. It resolves an overlap with the offset before it. A converted job using a different rule moves an hour of events twice a year.

Errors last. With ANSI mode on, Databricks raises an error on division by zero and invalid casts. With it off, the same expressions often return NULL. A procedure that relied on silent NULLs will behave differently on the target.

Because otherwise you are comparing two different days. If the source loads overnight between the two reads, every mismatch you find is noise.

Pin both sides to one input snapshot, give it an identity, and record that identity in the report. Our charters name the snapshot by location and hash, so anyone can rerun the comparison on the same bytes.

On a Delta target, time travel helps you hold the target side still. Queries can read a table VERSION AS OF a number or TIMESTAMP AS OF a time. Mind retention: by default, Delta keeps table history 30 days and deleted data files 7 days, and VACUUM removes old versions.

On the source side, a database snapshot, a restored backup or a masked copy all work. The rule is the same. Freeze it, name it, hash it, and do not change it after the run starts.

The business owner and the delivery lead set them together, in writing, before the first run. A tolerance agreed after a mismatch is an excuse, not a tolerance.

Our First Domain charters carry a schedule of inputs and tolerances. It names the snapshot and its hash, the floating-point tolerance, the ordering rules, date, time zone and precision handling, and collation and case. Undefined order is flagged as non-deterministic rather than forced to match.

Tolerances should be narrow and per column. A tolerance of 0.01 on a currency column at two decimal places is meaningful. A blanket 1% across a table is not.

One result per object, pass, fail or review, with the evidence behind it. It should be readable by someone who did not do the work.

Our reports list row counts per output, aggregates on every numeric and key column, and sampled rows compared field by field. Every miss is listed with its cause and its fix. The report is produced inside the customer's environment, stored there, and exportable per object as CSV or PDF.

Review is a real status, not a soft fail. It means every check passed with a documented difference inside an agreed tolerance. In our charters, a review counts as a pass unless the business owner rejects it in writing within 5 business days.

• Object name, type, domain and complexity band
• Input snapshot identity, with location and hash
• Each tier run, with source value, target value and difference
• Normalization rules and tolerances applied, by column
• Result: pass, fail or review, and the reason
• Cause and fix for every miss, with who approved the fix
• Run timestamp and the rule version used for conversion

Here is an illustrative row, not taken from any customer. It shows a review result on a currency aggregate.

The point is that a reader can check every claim in the row without asking anyone. The snapshot is named, the difference is stated, and the tolerance is the one agreed before the run.

It defines what a percentage is a percentage of. Without it, a reconciliation percentage can mean almost anything.

Ours is short. An object counts as automated only if it needs no manual edit, and a one-line fix counts as manual. Dead code is excluded from the denominator and listed separately. Objects resolved by an approved proposal without further edit are reported as assisted.

Under that rule, we typically convert 95% or more of objects with no manual edit, and reconciliation is above 95% on first pass. The report names the rest. About 95% of objects convert by fixed rules, which is the rule share, not the automation rate.

Dead code needs its own care. An object with no caller, no schedule and no reads is excluded with evidence. But dead by reference is not always dead in practice. The business owner of each domain confirms the list before cutover.

Run the original and converted logic on the same inputs, and compare what each one writes. A table copy proves storage. Only running the logic proves behavior.

For a procedure, that means every output it writes, including temp and audit tables that feed later steps. For a package or job, it means the whole chain, in the original order and on the original schedule. Orchestration is logic too.

Watch the side effects. A procedure that writes a log row, updates a watermark or sends a file has outputs beyond its main table. On the estates we have delivered, the misses that reach a business owner are more often in these side paths than in the main result.

Dynamic SQL, cursors and vendor functions with no equivalent are where conversion rules run out. About 5% of objects go to our resolver, on a model the customer approves. A named engineer approves every proposal, and the approval log records engineer, object and timestamp.

Run in parallel per domain, and cut a domain over when all its objects pass. The parallel run should last as long as the flagged objects take, not as long as the estate.

Plan the order by lineage. Shared objects such as reference data and utility functions convert first, with the first domain. A self-contained domain with an owner who will test goes next. A domain whose objects all pass cuts over on its own.

Set the cutover threshold before the run, in numbers. On our Continuous tier, the cutover reconciliation target is 99.9% on cutover domains. That figure is separate from the contract threshold, which defaults to 95% of in-scope objects.

Give the parallel run an end date. The year of parallel running nobody budgets starts when the end date is missing. One domain at a time, reconciled, cut over, is how it ends on a date.

Five questions, in our experience, and each needs an artifact rather than an answer. Prepare the artifact before the meeting.

Production cutover stays the customer's decision. The evidence exists so that the decision can be made by someone who did not do the work.

It is a written reconciliation threshold with a fee remedy. The First Domain charter states a threshold, by default 95% of the agreed objects on the agreed inputs.

Miss it at the readout, after the cure period and the exclusions stated in the charter, and the fee is waived or credited in full. The threshold, the object list and the inputs are agreed in writing before work starts. Inputs changed after signature are measured only by written agreement.

We can offer it because the output is deterministic. A firm that bills hours cannot price a miss. For auditors and risk owners, the clause matters more than the figure. It turns a vendor's claim into a term you can enforce.

Most first-run mismatches are comparison artifacts, not conversion errors. Triage them before anyone touches the converted code.

The false mismatches we see most are trailing spaces, case, NULL handling, time zones, decimal scale and row order. Trailing spaces are a frequent one: SQL Server's LEN excludes them, while byte-level comparisons and binary collations do not.

Row order is the subtle one. A query without a full ORDER BY, or a ranking with ties, can return rows in any order on either side. That is non-determinism in the original, and the report should flag it rather than force a match.

1. Confirm both sides read the same snapshot hash.
2. Check the schema tier: a type or scale change explains many value misses.
3. Apply only the agreed normalization rules, and rerun.
4. Look for undefined ordering, ties and non-deterministic functions.
5. Compare at the narrowest grain that fails: business date, then key.
6. Only then open the converted code, and record cause and fix.

Each part has one named owner, written into the charter. Shared ownership is how a mismatch sits unresolved for a week.

Our charters name a customer sponsor, a named engineer and a business owner per domain, beside the delivery lead. The named engineer approves resolver proposals. The business owner signs off tolerances, check queries and review results. The sponsor decides go-live.

Auditors and risk owners should be in the room at kickoff, not only at readout. If they see the counting rule, the snapshot and the tolerances before the run, the readout becomes a check against an agreed plan.

Use this list at kickoff and again at readout. Every line should point to a document, not a person's memory.

• In-scope object list agreed in writing, with dead code listed separately and confirmed
• Counting rule written down before any percentage is reported
• Type map, normalization rules and tolerances agreed per column
• One input snapshot, named by location and hash, used by both sides
• Tiered checks in order: schema, counts, aggregates, rows, business checks
• Keyed hashes on the tables where every row matters
• Procedures, packages and jobs run end to end, with side effects compared
• A report per object: pass, fail or review, with cause and fix for every miss
• Engineer-approval log for every change that was not rule-based
• Parallel run planned per domain, with an end date
• Cutover threshold and contract threshold written down, each with its label
• Deployment diagram showing what crosses the network boundary