The worst time to discover a migration problem is after cutover, when the old system is already offline and the target system is live in front of users. A report that used to balance no longer does. A scheduled job that ran fine last week fails silently. Nobody rehearsed the rollback plan, so rolling back isn't really an option anymore.
What data migration testing actually covers
Data migration testing is the structured verification of a migration across three phases:
- Pre-migration readiness checks
- During-migration transfer validation
- Post-migration reconciliation and functional testing
A completed load is not the same thing as a validated one. Data can finish copying and still be wrong, incomplete, or unusable to the applications built on top of it.
Scope matters here too. Successful data migration testing validates not only the transfer of data, but also the ability of applications, reports, integrations, and business processes to operate correctly against the migrated environment. A database that loads cleanly and an application that still works on top of it are two different claims. Planning that scope in before migration starts makes testing far less improvised.
The testing lifecycle at a glance:
| Phase | What it validates | When it happens |
| Pre-migration | Source data quality, target readiness, performance baseline | Before any data moves |
| During-migration | Transfer accuracy: row counts, checksums, schema and type mapping | While data is in transit |
| Post-migration | Business reconciliation, application behavior, performance vs. baseline | After cutover, before sign-off |
A quick checklist across those three phases:
- Before
Profile source data, confirm target dependencies exist, capture a baseline, take a tested backup - During
Validate row counts and checksums, confirm schema and type mapping, account for in-flight transactions - After
Reconcile business reports, run a parallel-run period, verify business processes, test application workflows, compare against baseline
Minimizing risk in a database migration starts with treating each phase as a real gate, not a formality on the way to a predetermined go-live date.
Pre-migration testing: readiness checks and baselines
Skip pre-migration testing and the problems it's meant to catch don't disappear. They just show up later, when they're more expensive and harder to diagnose, usually in production.
Data profiling and quality checks
Before anything moves, use data profiling to check the source system for issues that don't surface until they break something downstream, such as duplicate records, orphaned foreign keys, inconsistent formatting, and fields that have quietly drifted from what the schema says they should contain. Alongside data quality, inventory the extensions and dependencies the source relies on and confirm each exists and is compatible on the target system. A migration that copies the data correctly but omits an extension the application depends on fails just as completely as one that loses rows.
Capturing a performance baseline
Document how the current system performs before migration begins, including query response times for the workloads that matter, throughput under normal load, and resource utilization at peak. This baseline gives post-migration performance testing something concrete to compare against. Without a documented baseline, a slower query after migration becomes a debate about whether it was always that slow, instead of a diagnosis of what changed.
Pre-migration backup deserves to be treated as a readiness gate, not an afterthought scheduled the night before cutover. A tested rollback plan depends on a known-good backup and recovery process already validated, not one assumed to work because it always has before.
During-migration testing: validating the transfer itself
Once data starts moving, the goal shifts from readiness to proof that what arrived matches what left, exactly.
Row count and checksum validation
Row counts confirm nothing was dropped or duplicated in transit; a checksum comparison between the source system and target system confirms the data itself wasn't altered along the way, not just that the right number of rows showed up. Both are mechanical checks that lend themselves to automated testing on every migration rather than occasional spot checks.
Schema and data type validation
Confirm every column is mapped to the correct type on the target, not just a compatible-looking one. A timestamp that loses timezone information, or a numeric field that silently truncates precision, will pass a row count check and still corrupt the data it's supposed to be preserving.
For systems that can't take a full outage window, in-flight transactions add another layer, since data written to the source after the transfer started needs its own validation pass, and near-zero-downtime approaches depend on getting this right. Consistency point-in-time validation matters here too, confirming the source and target reflect the same logical point in time rather than comparing systems that are a few transactions apart. A migration tooling comparison can help decide how much of this to automate versus script by hand.
Post-migration testing: reconciliation and application-level checks
Business-level data reconciliation comes first, comparing key business reports and critical tables between the source and target systems, line by line where it matters, not just row-count totals. A parallel-run period, where both environments operate side by side before the source is decommissioned, gives the team a live comparison window instead of a single point-in-time check. Application-level functional testing follows from there, since database-level checks alone don't prove that dependent applications, integrations, and scheduled jobs behave correctly against the migrated data.
Common post-migration failures worth testing for explicitly, because they surface in exactly this phase:
- Reports return different results
- Application workflows behave differently
- Scheduled jobs fail due to path, permission, or sequence-related gaps
- Integration interfaces break
- Performance differs from baseline
Access controls and permissions are part of post-migration testing too. Security configurations that worked correctly on the old system don't automatically carry over with the same effect, and a permission gap discovered by a user rather than a test is the worst way to find one.
The performance comparison against the pre-migration baseline is the final proof point before sign-off. Post-migration performance testing should confirm that the target can handle representative workloads without unacceptable degradation. A system running measurably slower under real workload than baseline is a finding, not a footnote.
Building a rollback plan and go/no-go framework
A rollback plan that exists only as an assumption isn't a rollback plan. It's a hope.
Rollback becomes progressively more difficult as users begin creating new transactions in the target environment. The rollback window should be explicitly defined, documented, and tested before migration begins, not worked out under pressure once something has already gone wrong. Cutover planning that treats rollback as a real decision point, including the point beyond which returning safely to the source is no longer practical.
A go/no-go decision needs more than an engineering team saying it looks fine. It needs validation evidence a business stakeholder can actually review: the reconciliation results, functional test outcomes, and performance comparison against baseline, plus explicit approval from whoever owns the business risk, not just a technical sign-off.
Fujitsu Enterprise Postgres's migration methodology is an example of building this readiness into the migration process from the start. Structured architecture reviews and migration assessments happen before any commitment is made, so the readiness work above happens well ahead of a cutover date rather than in the days leading up to it.
Manual vs. automated testing: finding the right balance
Automated testing earns its place in the mechanical, repeatable checks, row counts, checksums, and regression tests that need to run identically across every test cycle without human variation creeping in. Automation becomes valuable when the same checks must run repeatedly across test cycles.
Manual validation still matters where judgment does, covering business logic sign-off, edge cases a script wasn't written to anticipate, and stakeholder review of results a checksum can't evaluate. Approval belongs here explicitly, not as an implied side effect of someone looked at it. A named business stakeholder reviewing the evidence and formally signing off is a different, more accountable step than an engineer noting the numbers look right.
Most teams land somewhere in between, and the specific tools matter less than having both categories covered deliberately. Redgate's 2025 State of the Database Landscape found that 17% of respondents frequently encountered data-related issues in production that testing had failed to catch, while another 45% encountered them occasionally. Teams that refreshed test data for every new piece of work also reported fewer frequent failed deployments, falling from 17% to 9%. That reinforces the value of repeatable testing without removing the need for human review and approval.
Turning migration testing into a repeatable practice
A team's first migration can easily turn testing into a one-off fire drill. Manual checks get assembled under pressure and thrown away once cutover succeeds, only to be rebuilt from scratch for the next migration or system upgrade. Operational maturity means treating the testing framework itself as a reusable asset: a checklist, cadence, automation scripts, and reconciliation queries that improve each time they're used.
This is also where Fujitsu Enterprise Postgres becomes relevant. Fujitsu helps establish confidence in migration readiness through a structured approach that includes:
- Architecture reviews and migration assessments
Assess the existing environment, requirements, dependencies, and target architecture before cutover planning - Migration and PostgreSQL expertise
Support planning and implementation through Fujitsu's migration and professional services and PostgreSQL consulting services - Post-migration support
Provide 24/7 global enterprise support once the migrated environment is live
A global superannuation platform used this structured approach to migrate off Oracle over a single weekend, after its workloads had been assessed and the cutover sequenced in advance.
Reusing the same testing framework across future migrations and upgrades makes validation more consistent and reduces the amount teams have to reinvent each time. Talk to Fujitsu about building a testing and validation plan into your next migration before cutover.
Frequently asked questions about data migration testing
What is data migration testing?
Data migration testing is the process of verifying that data moves from a source to a target accurately, completely, and without compromising data integrity. A data migration testing strategy typically covers pre-migration readiness, transfer validation, and post-migration validation. Test cases should check not only data accuracy, but also whether applications and business processes continue to work with the migrated data.
What are the main types of data migration testing?
The main types of data migration testing are pre-migration, during-migration, and post-migration testing. Pre-migration testing covers data profiling, data cleansing, and target readiness. During-migration testing checks data mapping, row counts, checksums, data formats, and data structures as information moves between systems. Post-migration testing covers reconciliation, functional and end-to-end testing, performance, and usability in the migrated environment.
How do you validate data after a migration?
You validate data after a migration by comparing the source and target and confirming that the migrated data and dependent systems behave as expected. Post-migration validation can include row counts, checksums, data reconciliation, business report comparisons, and application test cases in the test environment. For larger data volumes, automated checks can make this validation more repeatable, while business stakeholders verify that critical workflows still produce the expected results.
What should a data migration rollback plan include?
A data migration rollback plan should include clear rollback triggers, technical recovery steps, a defined rollback window, and the people responsible for making the decision. It should also identify the backup or recovery point, explain how new transactions will be handled, and be tested before cutover. This is particularly important when replacing legacy systems, because rollback can become more difficult once users start creating data in the target system.
What is the most important data migration test?
The most important data migration test depends on the failure you are trying to detect, so no single test is sufficient. Row counts can identify missing records, checksums can detect unexpected changes, and data mapping tests can catch structural problems. Business reconciliation and end-to-end testing then establish whether the migrated data actually works within the applications and processes that depend on it.
How do you know a data migration is complete?
A data migration is complete when post-migration validation has passed and the agreed technical and business acceptance criteria have been met. That means confirming data accuracy, application functionality, performance, permissions, and usability rather than treating a successful load as completion. For complex migrations or wider digital transformation programs, real-time monitoring after cutover can also help teams identify issues as the target environment moves into normal operation.
PostgreSQL Insider 
