Database migration reconciliation: evidence before cutover

A database migration is ready for cutover when the receiving team can show that the target contains the expected records and values at a defined comparison point, explain every permitted transformation, and operate a recovery plan if the checks fail. Matching row counts alone cannot establish that result. This guide gives engineering leads and prime contractors a reconciliation worksheet for accepting a bounded migration work package.

The checks below are proposed engineering controls, not a guarantee of a lossless migration or a universal cloud-service requirement. Agree the data owner, scope, comparison boundary, and decision authority before the cutover window begins.

First define what must match

List each table or business entity in the work package and its authoritative source. Include background jobs, direct database consumers, and other writers. A comparison cannot settle whether a difference is a defect until the team has agreed which records should move and which transformations are intentional.

For example, excluding cancelled orders is a population rule. If the source count includes them and the target count does not, the counts should differ. State that expected difference and compare the same eligible population on both sides. Do not label all differences “replication lag” without showing the boundary and the pending work.

Rainier's legacy application assessment worksheet helps identify data writers and change boundaries before selecting a migration scope. This article begins after a scope is chosen and asks whether the resulting data is correct enough to switch readers and writers.

Compare the same logical point in time

If the source accepts writes while you read it, and the target applies changes later, two queries run at 14:00 can legitimately disagree. A clock timestamp by itself is not a shared transaction boundary. Record a source log position or other tool-specific checkpoint, ensure the target has applied through it, and compare a stable scope. If the tool cannot establish that relationship, use an agreed write pause and drain pending work before taking final measurements.

For PostgreSQL, a REPEATABLE READ transaction provides a stable view within that database from its first non-transaction-control statement. PostgreSQL's transaction-isolation documentation explains that behavior. Separate repeatable-read transactions on the source and target do not automatically share a snapshot; the migration procedure must establish an equivalent boundary across them.

For a change data capture (CDC) migration, retain the checkpoint and evidence that queued changes through it were applied. AWS Database Migration Service's ongoing-replication documentation describes engine-specific log positions and full-load-plus-CDC versus CDC-only tasks. Its mechanisms are examples, not a claim that every migration uses AWS DMS. AWS DMS monitoring distinguishes source and target CDC latency and reports validation outcomes, including pending and suspended rows. A low latency graph is operational evidence, but it is not a row-by-row correctness check.

Record when writes resumed or switched. A comparison against the last source checkpoint does not cover new writes accepted at the target after cutover; include a separate post-switch check for those operations.

Use several checks that fail differently

Use a layered comparison at the agreed boundary. Apply each check to the included population and document the actual query or tool setting so another engineer can repeat it.

1. Completeness: count records by table and meaningful partition, such as tenant, month, or status. Check missing and unexpected keys in both directions. Equal totals can hide one missing and one extra record.
2. Value fidelity: compare selected business fields by stable key, after applying only the documented transformations. Compare money in integer minor units when that is the agreed representation, and specify rounding when it changes.
3. Business invariants: compare totals and relationships that operators rely on, such as order amount by status and orphaned line items. Equal global sums can conceal offsetting errors.
4. Operational state: check unapplied changes, validation failures, suspended comparisons, indexes, constraints, sequences and application read/write behavior in the target environment.

AWS DMS data validation compares corresponding source and target rows and reports mismatches. Its documented pending and suspended states also matter: they are not passes. Validation reads consume source, target and network resources, so plan the load and time needed for the review. If using a different migration tool, identify its actual comparison depth rather than assuming that a “validated” dashboard means the same thing.

A sample can help examine a transformation, but a passing sample does not establish complete parity. Select risk-focused records deliberately, including nulls, long text, boundary dates, deleted items, multi-tenant keys and values near a rounding threshold. Keep full-population or partition-wide checks for the fields and risks that require them.

Worked example: counts and totals agree while records do not

The following is a hypothetical fixture, not a Rainier client migration or test result. Suppose an orders table has stable text IDs, amount_cents, and status. All three records are in scope, and no transformations are allowed.

Both databases have three rows and a total of 25000 cents. A global count and sum pass, but A is short by 1000 cents and B is high by 1000 cents. A per-key comparison catches both. A second fixture could replace C with an unexpected ID D carrying the same amount and status: counts and totals would still pass, while a key-set comparison would find C missing and D extra.

For a small PostgreSQL fixture with the same schema on both sides, run these read-only queries separately after establishing the comparison boundary:

SELECT status, COUNT(*) AS rows, SUM(amount_cents) AS cents
FROM orders
GROUP BY status
ORDER BY status;

SELECT id, amount_cents, status
FROM orders
ORDER BY id;

Save both outputs, compare the keyed rows, and report each difference. This is illustrative SQL, not a production-scale comparison algorithm: a full row export can be costly and may expose protected data. In a large system, compare bounded partitions or use an approved row-validation tool, keeping raw customer data within authorized environments. Agree canonical representations before computing hashes across engines; different serialization of the same logical value can create a false mismatch.

The acceptance result for this fixture is fail despite matching totals. The correction is not to widen an arbitrary tolerance to 1000 cents. Trace why the two amounts changed, rerun the affected migration segment or fix the mapping under change control, and compare again at a new documented boundary.

Rainier's published Ever case study confirms work extending reservation and sell flows within an existing product and a typed PostgreSQL data layer. It does not report a database migration, use of this worksheet, or the hypothetical amount mismatch. It is delivery context for working inside an existing data-backed application.

Decide whether to switch, pause, or restore

Write decision gates before the cutover. An illustrative policy is below; replace its conditions with the work package's actual risk and recovery limits.

“Rollback” needs special attention after the target becomes a writer. Routing traffic to the old database does not automatically copy new target writes back. Specify the point after which the fallback requires reconciliation or a forward fix. Microsoft's PostgreSQL migration tutorial checks for caught-up changes and row counts before cutover, then calls out post-cutover sequence recreation for its specific workflow. Treat it as an example of explicit handoff steps, not a complete validation plan for every engine or provider.

Record one decision per cutover attempt:

Migration revision / environment / date:
Business owner and decision authority:
Included population and mapping version:
Source checkpoint or write-pause interval:
Target applied-through evidence:
Counts by partition / missing and extra keys:
Value comparison / business invariants:
Pending, suspended and failed validations:
Application read/write checks:
Accepted exceptions with authorizer and reason:
Recovery trigger, owner and last safe decision time:
Result: proceed / pause / recover
Post-switch verification owner and time:

A failed check is useful when it stops an unsafe cutover. An unperformed check belongs in the record as not tested, with a named owner. The receiving engineer should be able to reproduce the comparison from the delivered revision without borrowing the migration author's laptop or access to undocumented files.

The software subcontractor acceptance checklist explains how this evidence joins deployment, operations and handover. For related API changes, the retry and idempotency checklist covers duplicate operations when responses are lost. Rainier's software subcontracting service includes bounded modernization and integration scopes. Bring the data boundary, writer map and cutover constraints to a scoping discussion.

Discuss a database migration work package→