TL;DR
- Validation is three separate checks, not one. Completeness, accuracy, and referential integrity each fail independently, and a migration can pass on one while quietly failing the other two.
- Matching record counts does not mean the migration worked. Batch-level drops and duplicates routinely cancel out in the aggregate count while the underlying data is still wrong.
- The five standard techniques only work against the full dataset, not a sample. Low-frequency errors are, by definition, the ones a small sample is unlikely to catch.
- A validated dataset is still only half the check. The application built on that data can behave differently after migration even when every record is confirmed correct, and that is a separate risk data validation alone does not cover.
What Data Migration Validation Means
Data migration validation confirms that every record, relationship, and value moved from a legacy system to its target survived the move intact, accurate, and usable. It is also the last point in a migration where a data problem can be caught and fixed before it reaches production.
The financial exposure behind this is not abstract. More than a quarter of organizations report losing at least $5 million a year to poor data quality, with 7% reporting losses of $25 million or more, and pipeline failures separately cost enterprises an average of $3 million a month in business exposure [1][2].
Validating a migration means checking three things independently.
| Dimension | Question It Answers | Common Failure If Skipped |
| Completeness | Did every record make the move? | Batch drops or duplicates that cancel out in the aggregate count |
| Accuracy | Is each value correct in the new system? | Type conversion errors that pass a naive checksum |
| Referential integrity | Do relationships between records still resolve? | Orphaned foreign keys from partial batch failures |
A migration can pass one of these and fail the other two. That is the core reason treating validation as a single pass/fail gate misses most of the risk it exists to catch.
The Data Migration Validation Checklist: Five Techniques and Their Blind Spots
Every ranking page on this topic converges on the same five techniques. That convergence means the list itself is correct. The standard treatment of each item on it usually stops well short of the depth needed to catch a real failure before cutover.
Record Count Matching: What It Misses
Record count matching confirms table-level or file-level counts tie out between source and target. It is fast, and it is the first check every migration script runs.
It is also the easiest check to pass while still being wrong. A pagination bug that drops the last row of every batch and a retry bug that inserts a duplicate row can cancel each other out in the aggregate count while both are actively corrupting the dataset. Row count matching validates completeness at the aggregate level only, and it says nothing about whether the specific rows present are the right ones.
What catches what count matching misses: row-level key matching, confirming every primary key in the source has exactly one corresponding key in the target, checked at the batch or partition level rather than only the full-table level.
Checksums and Hashing: What They Miss
Checksums, row-level or column-level hashes compared between source and target, catch what count matching cannot. They reveal whether the values inside a record changed during the move, even when the record itself is present and accounted for.
Checksums fail silently in one specific way. Hashing a field before its data type conversion tells you nothing about whether the conversion introduced an error, since the source varchar value “true” and the target boolean value true are not comparable representations once hashed independently. A checksum strategy that skips type normalization before hashing will report a mismatch on data that is correct, or a match on data that is not.
What catches what a naive checksum misses: column-level hash comparison after normalizing types on both sides, run against the full population rather than a spot sample.
Schema Validation: What It Misses
Schema validation confirms structural correctness. Data types, nullability constraints, length limits, and default values on the target need to match what the source schema intended, not just what the migration script happened to produce.
The failure mode here is nullability drift. A column that was NOT NULL in the legacy schema can migrate into a nullable column in the target without anyone deciding that should happen, usually because migration tooling defaults to permissive types when a mapping is ambiguous. The schema technically validates, and null values are now silently accepted in a field the downstream business logic assumes can never be empty.
What catches nullability drift: an explicit schema diff between source and target run before data movement starts, not a target-schema check performed only after the fact.
Referential Integrity Checks: What They Miss
Referential integrity checks confirm foreign key relationships resolve correctly in the target. Every child record’s reference has to point to a parent record that exists.
Orphaned foreign keys are the most common failure here, created when parent and child records migrate in separate batches and the parent batch fails partway through. The child records migrate successfully, reference a parent ID that was never written, and the referential integrity constraint either fails at write time, if the target enforces foreign keys, or is accepted silently if it does not. The silent case is the one that reaches production undetected.
What catches orphaned keys: validating parent-child relationships as a distinct pass after both sides have finished migrating, rather than assuming that migrating tables in dependency order is protection enough on its own.
Automated Regression and Reconciliation Testing: What It Misses
This technique catches what the first four cannot. It checks not whether individual records are correct, but whether the target system, taken as a whole, reconciles against the source at scale, across the full dataset, on an ongoing basis rather than a single point-in-time pass.
Manual spot-checking a sample of records is common practice, and it is also how most validation gaps reach production. A 2% sample that misses a bug affecting 0.3% of rows is the sampling method behaving exactly as designed, against an error class it was never built to catch.
What full reconciliation catches that sampling misses: systematic, low-frequency errors that only appear at scale, the exact category of error a small sample is mathematically unlikely to surface.
| Technique | What It Validates | Common Blind Spot |
| Record count matching | Completeness (aggregate) | Batch-level drops and duplicates that cancel out in the total |
| Checksums and hashing | Accuracy (value-level) | Type conversion errors hashed before normalization |
| Schema validation | Structural correctness | Nullability drift from permissive default mappings |
| Referential integrity checks | Relationship correctness | Orphaned keys from partial batch failures |
| Automated regression and reconciliation testing | All three, at scale | Low-frequency errors invisible to manual sampling |
Start a $0 Modernization Assessment
+moreCase Study: Migrating Ab Initio ETL to Apache Spark at a Credit-Scoring Firm
A global credit-scoring firm’s Ab Initio to Apache Spark migration carried 1.5M+ lines of ETL logic used for credit risk scoring and regulatory reporting, moved to Apache Spark and Airflow. Reconciliation testing across legacy and modernized pipelines, not a one-time record count check, anchored the validation strategy. Outcome: zero data loss, 80%+ of the transformation automated, 55% total cost of ownership reduction, and 60% faster time-to-market.
This migration is why reconciliation testing has to anchor validation on high-stakes data conversions, not record counts. Credit risk scoring and regulatory reporting pipelines have close to zero tolerance for the kind of error that only a full-population reconciliation catches, since even a small, low-frequency mismatch can change a risk score or misstate a regulatory figure.
Teams moving Ab Initio ETL pipelines to Apache Spark or any comparably complex data conversion are validating against the same three-dimension standard covered above, applied to pipeline transformation logic rather than only to static tables.
An unvalidated data conversion does not stop being a risk once cutover is declared complete. It becomes data debt, a liability that compounds the longer it stays undetected.
Data Validation Does Not Confirm Application Behavior
A dataset can pass every check in the sections above, completeness, accuracy, referential integrity, full reconciliation, and the application built on top of that data can still behave differently than it did before the migration. Data validation and application behavior validation are two separate disciplines, and a migration plan that only budgets for the first is missing half the risk surface.
The gap is structural. Application logic frequently encodes assumptions about the data underneath it, for example:
- A default value a report has always filtered out
- A null a workflow has always treated as “pending”
- A duplicate that downstream logic silently deduplicates
When data validation confirms the migrated dataset is now more complete or more structurally correct than the legacy dataset was, that improvement can trigger different application behavior even though no code changed.
The Knight Capital Illustration
Knight Capital lost more than $460 million in August 2012 in an incident that illustrates this exact failure category, though its specific trigger was a deployment error rather than a data migration [3]. Old, dormant code was reactivated on one production server that had not received a configuration update the rest of the fleet had, and the system began executing trades against logic nobody expected to still be live.
Every individual system component behaved correctly on its own. The interaction between them did not, and nothing in a pre-deployment data check would have caught it, because the failure was never in the data itself.
For a migration, the equivalent risk is a system that reads validated, accurate data and still produces a different result than it used to, because a code path, a default, or a business rule interacts with the modernized data differently than it interacted with the legacy version. Catching this takes behavioral parity testing, running the same inputs through legacy and modernized systems and comparing outputs, as a distinct exercise from data-layer validation, not a byproduct of it.
This is the safety nets that catch what data validation can’t, and it is worth budgeting as its own line item in a migration plan rather than an assumption folded into the data validation work.
Surface the Behavioral-Parity Gap Before Cutover
A $0 Modernization Assessment is scoped to surface whether an application behaves the same way after migration, a separate, harder problem than confirming the data itself is correct.
How Legacyleap’s Gen AI Agents Validate Data and Code Together
Legacyleap is a Gen AI-powered legacy modernization platform built on multi-agent orchestration, distinct from a single-purpose data validation tool or a general coding copilot. The platform runs the full modernization lifecycle, Assess, Comprehend, Modernize, Validate, and Deploy, with validation as a stage inside that sequence rather than a project bolted on at the end.
QA Agent. The QA Agent is the stage most directly relevant to everything above. It auto-generates unit, integration, regression, and API test cases against both the legacy and modernized systems and produces parity validation reports comparing behavior across both, closing the behavioral-parity gap described in the previous section rather than stopping at record-level checks. Roughly 70-80% of test case generation is automated, with engineers reviewing and directing the remainder.
Assessment and Documentation Agents. Before validation runs, the Assessment Agent and Documentation Agent establish what the legacy system’s dependencies, data flows, and business logic are, frequently starting from no existing documentation. That comprehension step is what makes the Modernization Agent’s transformations traceable back to a known baseline, and every change it produces arrives as a diff-based pull request for human review. The Modernization Agent does not merge, deploy, or execute code on its own.
Conclusion: Building Data Migration Validation Into the Migration Plan
Data migration validation done properly is not a single gate at the end of a project. It is completeness, accuracy, and referential integrity checked independently, five techniques run against the full dataset rather than a sample, and a separate confirmation that the application behaves the same way on the other side.
The organizations getting this right build validation into the migration plan from the start, with the same rigor applied to the migration itself, rather than treating it as a final sign-off before cutover.
For teams planning a legacy data migration, or validating one already underway, the $0 Modernization Assessment maps the dependency graph, data flows, and risk areas across a legacy codebase before transformation work starts, at no cost and inside the client’s own environment. Teams further along can go straight to a technical demo to see how the Assessment, Documentation, Modernization, and QA agents apply this validation standard against a live codebase.
Map Your Migration’s Data and Risk Areas
A $0 Modernization Assessment maps the dependency graph, data flows, and risk areas across a legacy codebase before migration work starts, at no cost and entirely inside your own environment.
FAQs
Data quality testing checks a dataset against quality rules on its own. Data validation after migration checks a moved dataset against its original source, confirming the target matches what the legacy system held, not only that the target data looks internally consistent.
Schema validation and record count matching first, since both are fast and surface large structural problems early. Checksums and referential integrity checks next, run against the full dataset. Reconciliation and regression testing last, since it depends on the earlier checks already passing and is the most resource-intensive of the five.
Sampling works for early smoke-testing, but low-frequency errors affecting a small share of records are, by design, unlikely to appear in a sample. Full reconciliation testing is what catches them.
Yes. If the source system can still be written to while validation runs, the comparison baseline drifts mid-check, and a real discrepancy can look like a false mismatch or get missed entirely.
Not in which checks apply. A conversion moves data through transformation logic, an ETL pipeline reshaping or recombining fields, rather than a straight lift-and-shift, so validation has to run against the pipeline’s transformation logic and its output, not only against the static tables it writes to.
References
[1] Forrester. Millions Lost in 2023 Due to Poor Data Quality
[2] Fivetran, via Business Wire. 2026 Data Pipeline Benchmark
