9 Database Migration Testing Strategies, with a Reconciliation Example

Section 01

1. Full data validation testing

Compare key sets and mapped values at a consistent boundary. Identify intentional filters and transformations before comparing raw source and target representations. Check missing and extra keys, duplicates, nulls, precision, encoding, and relationships as well as counts and totals.

Hashes can accelerate comparisons when both sides use the same canonical representation. Define column order, null markers, decimal formatting, timezone, encoding, and row ordering. Keep enough detail to inspect each mismatched row. A hash of differently formatted values can report a false discrepancy; a matching total can conceal offsetting errors.

AWS DMS validation compares corresponding rows and reports failures, pending records, and suspended records. Keys are required, some data types are unsupported, and columns with masking rules are skipped, so masking a key column skips the whole table. Validation also adds load. Record coverage and unfinished checks in the acceptance report; unfinished checks have not passed.

Worked reconciliation: dollars and status codes

This example moves three invented orders from the legacy format to the target format:

The transformation contract uses exact decimal arithmetic to multiply dollars by 100, rejects non-finite amounts or fractional cents, maps A to active and C to closed, and retains IDs. There are no filters, deduplication rules, or accepted exceptions. The expected key set is {101, 102, 103}, row count is 3, and total is 4,000 cents. Check each row as well as the total and key set.

Suppose order 102 arrives as 751 cents. Counts still match, but the amount comparison fails and the target total becomes 4,001 cents. The exception record should identify the boundary, mapping version, key 102, field cents, expected 750, actual 751, and the failing test. Do not silently round or adjust the acceptance tolerance to make the report pass.

The order-data owner approves monetary values and status mappings. The application owner approves order workflows, and the cutover lead records the final go/no-go decision. Replace these roles with names and alternates in your change record. The migration engineer investigates the transform or load defect and reruns the comparison against a new recorded boundary. Hold cutover until the data owner accepts the evidence. Escalate unexplained mismatches to the data owner and cutover lead; a supplier or test operator cannot waive them alone.

Make the report reviewable

Record the dataset, source and target boundary, mapping version, compared and excluded fields, extracted/loaded/rejected/filtered counts, missing/extra keys, value mismatches, totals, unresolved checks, exception decisions, and named approval. If a running source changes during comparison, use a consistent snapshot or a documented commit boundary and wait for replication to reach it. Record the boundary method and its limitations.

Section 02

2. Shadow testing and parallel runs

Compare application outputs before moving the authoritative workload. Exercise critical transactions, batch cycles, and edge cases with the same inputs. Explain expected differences such as generated identifiers or timestamps through an explicit comparison rule.

Keep shadow effects isolated: a shadow checkout must not charge a customer, send another confirmation, or mutate authoritative inventory. Use approved copies or sanitized inputs with controlled external integrations. Run the test long enough to cover the required workload and failure cases.

Specify which system accepts writes during parallel execution. Google Cloud's migration principles warn that writing to two databases involves separate transactions and can change transaction ordering. Test partial-write failure and repair if dual-write is part of the design.

Section 03

3. Functional regression testing

Run critical workflows against migrated data and the target database drivers. Cover successful transactions, rejected transactions, retries, concurrency, rollbacks, and report generation. Have the owners of those business processes review results that depend on their rules.

Examples include placing and cancelling an order, issuing a refund, changing an entitlement, and closing a reporting period. Check the resulting data and external effects, not just the HTTP response. Database differences in collation, null handling, identity allocation, or transaction isolation can change behavior while queries still execute successfully.

Reuse existing application regression suites where they exercise these contracts. Add targeted cases for the schema, driver, or query semantics that actually change.

Section 04

4. Performance benchmarking and load testing

Measure critical query and transaction latency, throughput, error rates, lock waits, resource consumption, and queue backlog. Compare source baselines with the target under a documented workload and service objective.

Use representative data volume, key distributions, index definitions, concurrency, and a realistic mix of reads, inserts, updates, and deletes. Include warm and cold caches, background jobs, and replication or validation overhead where relevant. A small fixture or one fast query cannot establish production capacity.

Record the workload generator, dataset, configuration, duration, and known differences from production. The service owner decides whether measured performance satisfies the approved objective; this guide supplies no universal percentage tolerance.

Section 05

5. Data quality and cleansing validation

Test the approved cleansing rules separately from transfer fidelity. A planned merge, exclusion, or default changes the expected result and requires traceable evidence. Reconcile source counts to loaded, filtered, merged, and rejected records under those rules.

Include duplicate identities, missing required values, invalid dates, extreme amounts, and ambiguous records. Retain the original identifiers and transformation decisions needed to investigate each case. Data owners approve meaning-changing rules before execution. For profiling and remediation, use the data quality in migration projects guide.

Test source scale and target precision. Reject values the target cannot represent unless the data owner has approved a conversion rule; a type change alone does not justify lost precision.

Section 06

6. Rollback and recovery testing

Test recovery after the target has accepted changed data. A restored baseline may start the old application while silently losing later transactions. Include updates and deletes as well as new rows, and compare the recovered state with the accepted recovery boundary.

AWS's cutover guidance distinguishes unchanged-data rollback from recovery after new transactions. For the latter, test the supported data-transfer path before treating the source as a fallback.

Use the cutover and rollback worksheet for decision authority, RPO/RTO assumptions, writer fencing, point of no return, and recovery acceptance. The worksheet records the recovery sequence; the tests here supply its acceptance evidence.

Run the isolated SQLite demonstration

Download data-migration-reconciliation.py and save it with that filename. You need Python 3.9 or later with its standard-library sqlite3 module and SQLite 3.24 or later. It installs no packages and uses no Docker, credentials, network connections, or persistent databases. Inputs are the three synthetic orders above, embedded in the file.

From the directory where you saved the file, run:

python3 data-migration-reconciliation.py

Expected output, with exit status 0:

PASS initial reconciliation: rows=3, total_cents=4000
PASS stale-source check: differences=3 (updated=101, deleted=103, new=104)
PASS fenced rollback reconciliation: journal_boundary=3, rows=3, total_cents=3050
PASS repeated replay: applied_events=3, differences=0
SCOPE: in-memory toy model; no production RTO/RPO or platform recovery claim.

The successful path updates order 101 from 1,250 to 1,400 cents, inserts order 104 at 900 cents, and deletes order 103. SQLite triggers record each operation in the same transaction as the change. The target connection is then made read-only and a write attempt must fail. Replay converts cents and statuses back into the legacy representation and records applied event sequences transactionally. Replaying twice must leave the same state:

Order 103 is absent. The recovered key set is {101, 102, 104} and the total is 3,050 cents. The script checks these fixed expected values independently of the replay result. The unchanged source still has three rows, so row count alone would miss all three changed keys.

Next exercise the initial acceptance failure:

python3 data-migration-reconciliation.py --inject-mismatch

Expected output, with exit status 1:

FAIL initial reconciliation: id=102: expected=(750, 'closed'), actual=(751, 'closed')
STOP: cutover blocked; escalate to the data acceptance owner.

The mismatch blocks cutover before post-cutover writes and replay. Each run creates fresh in-memory databases and closes them on exit. The outputs above were verified on October 1, 2026.

The example models one writer, three known operations, exact reversible mappings, and an ordered journal. The read-only setting fences only its SQLite connection. It does not demonstrate distributed writer fencing, real CDC, concurrent transactions, key changes, crash recovery, journal expiry, production performance, external effects, or an application traffic switch. Use it to understand the checks, then rehearse a platform-specific recovery procedure in an isolated environment representative of production.

Section 07

7. Integration and connectivity testing

Inventory applications, APIs, ETL jobs, reports, queues, and administrative clients. Test each with the migrated schema, endpoint, driver, credentials, and network policy. Include connection-pool refresh and retry behavior so old connections cannot keep writing to the source unnoticed.

Run end-to-end business workflows across dependencies. Check that a committed database change appears correctly in downstream processing and that retries do not duplicate external effects. Test degraded dependencies in a contained environment and record who accepts each integration.

Section 08

8. Security and control validation

Translate applicable security, privacy, and retention requirements into tests with the responsible owners. Check access roles, denied operations, encryption configuration, masking, audit events, backup access, and data location. A successful data comparison does not establish regulatory compliance.

Test service accounts and ordinary users, including attempts to perform forbidden reads or writes. Verify that failed logins, privilege changes, and sensitive mutations reach the expected audit destination. Restrict access to test data and exception reports containing sensitive values.

Section 09

9. Monitoring and observability validation

Verify that operators can see lag, capture failures, rejected changes, reconciliation discrepancies, transaction errors, latency, lock waits, and queue backlog. Trigger representative alerts in an isolated environment and confirm delivery to the correct on-call owner with a usable runbook.

Check collection gaps as well as thresholds. An empty dashboard can mean no telemetry rather than no errors. Version monitoring configuration with the migration and record which signals are needed to authorize cutover, recovery, and later source decommissioning.

Section 10

Build a risk-based acceptance plan

For each credible failure, name the test, evidence to retain, and person who can accept the result. Prioritize by business consequence and detectability. Treat irreversible loss or security failure as a priority even when its estimated likelihood is low.

For outside testing support, the data modernization companies comparison can help identify candidates. Ask the delivery team for explained reconciliation exceptions, workload evidence, and a recovery drill involving changed data. Specify the evidence the supplier must produce and the decisions your owners retain.

Reviewed October 1, 2026. The worked dataset and script are educational examples. The linked AWS and Google documentation supports the validation and capture constraints discussed here; it does not establish universal migration success rates, test durations, or recovery guarantees.