Proving the Data Arrived: Integrity in Cloud Migrations

Most migration plans treat data movement as a solved step: extract, load, compare counts, proceed. The counts match, the cutover happens, and four months later finance finds a discrepancy in a report nobody ran during testing.

By then the source system is decommissioned and the evidence is gone. Data integrity work is unglamorous precisely because when it is done properly nothing happens, and it is the part of a migration where being approximately right is indistinguishable from being wrong.

Row counts are the weakest check available

Equal counts on both sides confirms nothing about the contents. Every failure mode we actually encounter preserves the count:

• Silent truncation. A target column narrower than the source. The row lands, the tail of the value does not. Counts match perfectly.
• Precision loss. A decimal becoming a float. Values are close and no longer sum to the same total, which is exactly the discrepancy finance eventually reports.
• Timezone drift. Naive timestamps reinterpreted in a different zone. Every record shifts, and month-boundary aggregates change while individual rows look reasonable.
• Encoding damage. Non-ASCII characters mangled. Invisible until a customer sees their own name.
• Null-versus-empty collapse. A distinction the old system relied on to mean "unknown" versus "none", flattened on load.

What actually constitutes proof

Comparisons that would fail if any of the above happened:

Aggregate reconciliation on business quantities. Sum the money columns, per period, per account, both sides. This catches precision loss and boundary shifts in one check, and it speaks a language the business can verify.

Row-level hashing. Normalise then hash the concatenated fields of each row, compare the sets, and list the differences. Expensive on large tables, definitive, and worth it on anything financial or regulatory.

Distribution checks. Min, max, null rate and cardinality per column. Cheap, and catches truncation and collapse quickly across a wide estate.

Referential integrity on the target. Orphaned rows that the source prevented and the target permits are a real and common outcome when constraints do not survive the move.

The part everyone underestimates: reconciling a moving source

Migrations of live systems are not a snapshot. The source keeps changing while you load, so you are reconciling two datasets that were never identical at any instant.

This needs designing, not improvising. Either freeze writes for a window long enough to reconcile — which the business must agree to and rarely wants — or use change capture and reconcile against a defined watermark, accepting that records after it are compared separately.

The failure here is subtle: teams reconcile, find differences, attribute them to in-flight changes, and wave them through. Sometimes that is right. Sometimes it is a genuine defect hiding in the noise, and there is no way to tell without a watermark you decided in advance.

Keep the evidence, and keep the source

Reconciliation output is not a test log to discard. It is the answer to a question that gets asked months later by an auditor or a controller, and it needs to be retained with the run date, the row counts, the aggregate comparisons and the exceptions accepted.

Equally: do not decommission the source on cutover day. Keep it readable, in a restorable form, until a full reporting cycle has closed on the new system — a quarter is usually the right unit. The cost is some storage. The alternative is discovering a discrepancy with nothing to compare against.

Type mapping is where the damage originates

Most integrity failures trace back to a decision nobody made explicitly: how each source type maps to a target type. Migration tools infer this, and the inference is reasonable rather than correct.

The mappings worth checking by hand are the ones where systems genuinely disagree. Fixed-point decimals versus floating point, which decides whether your money still sums. Timestamps with and without timezone, and what the target assumes when the zone is absent. Strings with declared lengths, where the target may silently accept a longer value or silently cut it. Booleans represented as characters, integers or nullable flags in the source. And anything the old system stored as text because the type it wanted did not exist.

Write the mapping down as a table before the first load, have someone who knows the domain review it, and generate the validation checks from that table rather than from the tool's assumptions. It converts a class of silent corruption into a decision with a name on it.

Where we would push back

If the plan allows no time for reconciliation, the plan is not a migration plan. We would rather move the date than cut this, because the cost of an integrity failure is paid at the worst possible moment, in the worst possible forum, with the evidence gone.

We would also push back on migrating everything. Estates accumulate tables nobody reads. Moving them is cost, risk and reconciliation effort for data with no consumer. Archive it somewhere retrievable and migrate what is used — the smaller the payload, the stronger the proof you can afford on what remains.

One more habit worth adopting: reconcile in the target's own query engine, not in the migration tool. Tools report on what they believe they moved, which is the same assumption you are trying to test. A query written against the target, by someone who did not write the pipeline, is independent evidence — and it is the version an auditor will accept.

This work sits inside cloud modernization and data platforms, and it is usually the phase that decides whether the rest of the programme is trusted.