Data Migration Testing: A Guide to Validating Migrated Data

A data migration can finish on schedule and still be wrong. The job log shows no errors, and the target database comes online. Chaos hits the next morning when the users log in and find missing orders. Some customer records appear twice, and the last month’s revenue total no longer matches the number in the old system.

Data migration testing catches these problems before users do. It runs alongside the migration itself, starting at the data mapping stage and continuing through business sign-off in production. That mapping stage is one of the seven phases we score when weighing in-house against a consultant.

This article explains what data migration testing covers, how the testing process proceeds stage by stage, and how reconciliation verifies that the target matches the source.

Data migration testing is the process of checking that the data moved from a source system to a target system is complete, accurate, and usable. It works by comparison, where every check asks whether the target stores what the source stored. These checks are designed not to conflict with the agreed transformation rules.

A small example can help you understand it better. A CRM database stores 1,000,000 customer rows. After the migration, the target should store 1,000,000 customer rows with the same IDs. Every email address should survive the move unchanged.

However, counts alone don’t settle it. The target can store the right number of rows and still truncate an address field. A date can shift by a day after a time zone conversion. A currency value can round in the wrong direction.

So the checks run wider than volume. They cover completeness, accuracy, integrity, transformation logic, data quality, application behavior, performance, and access control.

Testing also starts earlier than most plans assume. The mapping document is the first testable artifact, because one wrong rule there produces wrong data in every run that follows.

Migration defects don’t stay technical for long. They soon turn into business nightmares. Missing financial records change what the business reports, and broken foreign keys break the screens that depend on them. A support agent who opens a duplicate record gives the wrong answer to the person on the phone.

Beyond these business problems, compliance carries its own cost. Personal data that lands in an unmasked column, or in a system without the right access controls, becomes a reporting obligation rather than a bug.

The expensive part is timing. A defect found in the test environment costs one table and one rerun. The same defect found six weeks after cutover means correcting live records while the business keeps writing new ones.

It’s important to remember that the load tool is rarely the problem. The rules that feed it are the culprits in most scenarios. The same pattern runs through most cloud data migrations that fail even with strong engineering teams.

Data migration testing splits into checks that answer different questions about the same load.

• Data Completeness: Checks that every required record and field has arrived. Start with row counts for each table, then check for missing values in required fields.
• Data Accuracy: Matches field values between the source and target. Check all critical fields and use samples for the rest.
• Data Integrity: Checks primary keys, foreign keys, and other rules in the target system. Common dbt data tests can help check these rules.
• Data Transformation: Checks that each mapping rule gives the correct result. Test things like combined names, converted currencies, and changed status codes.
• Data Quality: Looks for duplicates, wrong formats, and values outside the allowed range. This check can also be done before migration to find bad source data.
• Application Testing: Checks that applications and business processes work correctly with the new data. Reports and integrations should also return the expected results.
• Security and Access: Checks user roles, permissions, encryption, and data masking. Sensitive data should have the right protection after migration.
• User Acceptance: Lets business users check their own data. They can find problems that automated tests may miss.

NOTE: Reconciliation and performance testing both get their own sections below, because each one does more work than its label suggests.

It runs in stages, and each stage has an owner, a piece of evidence, and a pass rule agreed in advance.

Two stages carry most of the risk. Profiling is the first, because it sets the baseline every later comparison depends on. The trial migration is the second, since it turns mapping assumptions into rows somebody can query.

Evidence matters as much as the result. A saved result set with its query text and timestamp beats a screenshot of a passing check.

Pass criteria belong in the plan before the run, not in a discussion after it. Some of the checking behind them is already automated for you. AWS Database Migration Service compares each source row with its matching target row, then reports the mismatches it finds.

Data migration reconciliation testing compares source and target datasets to prove that nothing was lost, added, or altered on the way across. Reconciliation runs at four levels. 

It begins by recording counts per table to catch whole batches that never arrived. Then, aggregates compare sums, averages, and extremes on numeric columns. Next, field-level comparison covers the columns the business depends on. Finally, hash or checksum comparison catches the differences that counts and sums both miss.

This investigation has a short list of usual causes. For example, a filter in the extract query may have excluded rows or a constraint in the target may have rejected them. Similarly, a transformation rule may have failed on a specific pattern, such as a date before 1900, or a name with an unusual character. Also, deduplication logic may have merged records the business wanted kept apart.

Once the systems are intentionally kept apart, the next question is how to determine whether the differences between them are acceptable. That requires explicit tolerance thresholds and a way to identify exceptions when those thresholds are exceeded.

Tolerance thresholds are legitimate, but each one needs a number and an owner. A 0.01% variance on clickstream events may be acceptable to the business. The same variance on general ledger entries is not.

Every run should produce an exception report listing the failing keys, not a pass or fail flag on its own. Engineers can only fix what they can identify.

Tooling helps at scale. For example, the Data Validation Tool from Google Cloud runs count, aggregate, schema, and row-level checks between different platforms. Likewise, Percona Toolkit's pt-table-checksum checksums MySQL tables in chunks and reports the chunks that differ.

A data migration test plan answers who tests what, with which data, and against which pass criteria.

The plan names the objectives, the systems in scope, the data objects, the test scenarios, and the sign-off owners. It also carries the schedule and the defect process. A defect with no severity rating and no owner will sit untouched until cutover week.

Entry and exit criteria deserve particular care. Entry criteria say when testing can start, such as a signed mapping document and a loaded test environment. Exit criteria say when it is finished. A signed reconciliation report usually comes first among them.

Test scripts turn the plan into something a tester can run. Each script carries an ID, an objective, the source and target objects, the steps, the expected result, and space for the actual result.

IMPORTANT: Failed scripts need a defect ID, a fix, and a rerun on the same data. A defect marked resolved without a rerun is still open.

Data migration performance testing measures how long the migration takes and what it costs the systems around it. For instance, we need to define the numbers before the first timed run. The cutover window is the anchor, while throughput targets follow from it. A team with an eight-hour window and 400 million rows to move already knows the rate it has to beat.

Measure a full-volume run rather than a sample. A job that moves 100,000 rows in a minute can behave differently at 100 million. Indexes, logging, and locking all come into play at volume. Batch size is usually the first lever worth tuning, and target-side indexing is the second.

Watch the source system too. Extraction competes with the users still working in it. A migration that slows the live application has failed a test, even when every row lands correctly.

Recovery time needs its own measurement. If the cutover fails at hour six of an eight-hour window, the rollback has to finish inside what is left. Teams that already run repeatable loads through production data pipelines get their recovery numbers for free. The same tooling advantage is covered in our roundup of migration tools. The job has run under real conditions many times.

This checklist works as a starting point for most projects. It maps to the stages above.

Before the Migration

• Mapping rules reviewed and signed off
• Source data profiled, with known defects logged
• Baseline counts and control totals captured
• Test environment built to match the target design
• Test cases written with pass criteria
• Rollback plan documented and timed

During the Migration

• Migration logs monitored for rejected records
• Row counts checked per table as batches complete
• Transformation errors captured with their keys
• Runtime and resource use tracked against the window

After the Migration

• Source and target reconciled at count, aggregate, and hash level
• Critical records validated field by field
• Relationships and constraints verified in the target
• Applications, reports, and integrations tested end to end
• Defects fixed and retested on the same data
• Monitoring and alerts are switched on in production

A migration is finished when the target data passes every check in the plan, and the business owner signs it off. Completeness, accuracy, integrity, transformation logic, application behavior, and performance all sit inside the plan. Each of these checks carries a stage, an owner, and a piece of evidence.

Everything above assumes a team with time to build these checks before cutover, and many teams don’t have it. Data Prism plans and validates complex data migrations for teams working against a fixed cutover date. The choice between running it in-house or through a consultant comes first. The same thinking runs through our guide to data migration consulting.

Most teams find their migration gaps after their users do. If you’d rather find them first, book a free consultation call and we’ll walk through your mapping rules and the checks worth running before cutover.