TL;DR
Data migration testing proves moved data is complete and correct. Check every row of financial and regulated tables. Use counts and spot checks for everyday operational data, and only counts and totals for archive data. Test before the move, during each load and after go-live. Matching row counts are not proof, because a wrong currency rule can hide behind them. Simple SQL queries and vendor tools automate the checks, but business owners still sign off.
Key Takeaways
Data migration testing produces evidence that migrated data is complete, accurate, consistent and usable after the move.
Rank data by business risk first, because the tier decides test depth, tolerance and who signs off.
Row counts are necessary but weak, since a missing row and a duplicate cancel each other out.
Four SQL checks (counts by date, key anti-joins, totals by month and currency, row hashes) cover the core defect classes between them.
Test downstream reports, semantic models and ML features too, because they can break while every table reconciles.
Automate every repeatable comparison, and keep business acceptance and go/no-go sign-off with people.
Watch on YouTube
Enterprise Data Migration Failure: 5 Causes That Derail Modernization
Kanerika breaks down five causes that derail enterprise data migrations, several of which good testing would catch before cutover.
The Row Counts Matched. Why Was the Revenue Report Still Wrong?
Picture a retailer moving its orders table to a cloud warehouse over a long weekend. When Monday comes, the team reports the same number of rows in source and target. So everyone signs off.
Then, at month-end, revenue on the new dashboard no longer matches the ledger. Two causes turn up. The rebuilt pipeline rounds currency conversion per line instead of per order, and orders that arrived after the snapshot never loaded.
The count tied out only because a batch of test rows had loaded twice, filling the gap the missing orders left. Because the test plan checked counts and nothing else, neither problem showed up.
That gap is exactly what data migration testing closes. This guide covers the strategy, the test types, the tools and four SQL checks you can run on your own tables.
What Is Data Migration Testing?
Data migration testing is the work of producing evidence that data moved from a source to a target arrived complete, accurate, consistent and usable. The key word is evidence. A data migration is not finished when the load job turns green.
It is finished when the person who owns the data can see proof that it matches.
That proof pays off in three places: defects surface in rehearsal, when they are cheap to fix, rather than at quarter-end close; sign-off becomes a decision based on numbers rather than confidence; and for regulated data the evidence pack doubles as an audit record.
What Data Migration Testing Proves
Every test in a migration plan should prove one of four properties. If a test does not map to one of them, then it is probably noise.
Completeness: every record that should move did move. For example, 1,204 orders dated March 3 in the source, and 1,204 in the target.Accuracy: values survived the move and every transformation. For example, a euro order still converts to the same dollar amount.Integrity: relationships still hold. For example, every order line points to an order that exists.Usability: people and systems can work with the data. For example, the monthly revenue report returns the same figure on both platforms.
Data Migration Testing vs ETL Testing vs Data Reconciliation
These three terms overlap, so teams often swap them. The real difference is scope and timing.
ETL testing checks a pipeline that will run every day for years, while migration testing checks a one-time move and its cutover. Data reconciliation is the comparison technique both rely on, and it can also run as a standing control after go-live.
Data migration testing ETL testing Data reconciliation
Purpose Show that a one-time move is complete and correct Confirm a recurring pipeline transforms data correctly Prove two datasets agree, such as source and target When it runs Before, during and after cutover During pipeline development and on every release At cutover, and on a schedule afterwards Who owns it Migration QA lead, together with data owners Data engineering and QA Data owners, finance or operations Typical checks Counts, key comparisons, totals, hashes, report parity Mapping rules, transformations, load logic, error handling Counts, control totals, row-level matches
When Do You Need It?
Any project that changes where data lives or how it is shaped needs it. A data warehouse migration to the cloud is the obvious case, such as SQL Server or Teradata to Snowflake, Databricks or Fabric.
ERP and application upgrades are another, because a new system’s data model rarely matches the old one field for field. Legacy replacement also brings years of inconsistent records into a stricter schema. Mergers add a fourth case, where two customer masters must become one and duplicates get resolved instead of copied.
The risk is the same across all types of data migration . Data looks right at the row level and still turns out wrong at the business level.
How Data Migration Testing Works Before, During and After Cutover
Testing runs through the whole data migration life cycle , with a different job in each of three windows.
Before cutover, you capture what the source looks like. During the move, you check each load as it lands. After cutover, you prove the new system behaves like the old one under real use.
Pre-Migration: Profile the Source and Freeze a Baseline
Start with data profiling on the source: row counts per table, null rates, distinct values, date ranges and junk such as test accounts. Profiling tells you what clean looks like before anything moves, and it scopes the data cleansing work. It also exposes source problems worth fixing first, which is the core of protecting data quality during migration .
Then freeze a baseline at a fixed snapshot time. Record counts, control totals such as summed order amounts, lists of business keys and checksums. Every later comparison runs against this snapshot, so a source that keeps changing cannot muddy the result. Store the baseline as tables rather than spreadsheets, so Checks 1 to 4 below can query it directly.
Microsoft’s guidance for complex Dataverse migrations recommends integrity checks for missing references in a staging layer before load. The habit also works on any platform. An orphaned key caught in staging costs minutes, while the same key found after go-live becomes an incident.
During Migration: Validate Every Load and CDC Window
While data moves, test each batch instead of waiting for the end. Compare per-batch counts, read the reject tables, then confirm that every rejected row has a reason.
Change data capture (CDC) adds its own traps. Late rows, source deletes after the initial load and out-of-order updates each need their own test. For example, delete a test order in the source after the snapshot, then confirm it disappears from the target in the next CDC window.
AWS sets a useful pattern here. According to the AWS DMS validation guide (checked October 2026), validation starts after the full load and then checks CDC changes as they occur. Copy that rhythm whatever tool you use.
Post-Migration: Parallel Run, Business Acceptance and Hypercare
After cutover, run the old and new systems side by side for an agreed period. Feed both the same inputs, then compare the outputs, from table totals to the reports people actually read. Business users then run their own acceptance scenarios, such as closing a sample month.
Hypercare follows go-live. Keep the reconciliation queries on a schedule for a few weeks while you watch for drift. Keep the rollback path open until the business owner signs off, and decommission the source only after that.
Migration ROI Calculator
Estimate Your Migration ROI
Decommissioning the source is where migration savings begin. Put rough numbers on effort, savings and payback, including test and rehearsal cycles, with Kanerika’s Migration ROI Calculator.
Calculate Migration ROI → Types of Data Migration Testing and What Each One Catches
Each type of data migration testing exists to catch a specific kind of defect. Thinking in defects instead of test names keeps the plan honest, because a defect class with no test will reach production.
The table maps each type to the defect it catches and one example check. The sections that follow then explain each one.
Test type Defect it catches Example check
Completeness and reconciliation Missing, extra or duplicate records Counts per load date, together with a key-level anti-join Schema, mapping and transformation Truncation, wrong types, broken rules Expected output per mapping rule compared to actual Referential integrity and business rules Orphaned keys, impossible values No order line without a parent order Incremental and CDC Late, lost or reordered changes Changes after the snapshot appear once, in order Performance, security and rollback Missed windows, open permissions, no way back Timed load, permission diff, rehearsed rollback Downstream Reports, measures or ML features that drift even though tables match Top reports return the same figures on both platforms
Completeness and Reconciliation Testing
Completeness testing confirms that every record arrived and no extra records appeared. Counts come first, but ten missing orders and ten duplicated test rows produce the same total. Run the key comparison in both directions too, because a clean source-to-target match says nothing about extra rows in the target.
So pair counts with key-level anti-joins and control totals, such as the sum of order amounts per day. Reconciliation then compares the two sides field by field, either in full or on a sample. A later section explains when each is enough.
Schema, Mapping and Transformation Testing
Schema tests confirm that tables, columns, data types, lengths and precision match the design. Truncation is the classic defect: a 255-character description becomes 100 characters, and nobody notices until months later.
Mapping tests go further, since they check each source-to-target rule, including lookups, defaults and type conversions. Transformation testing is where the revenue report in the opening story went wrong, so write the expected output for each rule before the run, then compare.
Referential Integrity and Business-Rule Testing
Referential integrity tests check that relationships survive. For example, order lines point to orders, orders point to customers, and no foreign key is orphaned. This overlaps with data integrity testing in general, but a migration focuses on links that the load order can break.
Business-rule tests encode what the business knows to be true. For instance, an invoice cannot predate its order, and a closed account cannot carry an open balance. These rules catch data that is technically valid and still wrong.
Incremental and CDC Testing
Large migrations often combine data migration techniques , such as an initial load followed by incremental or CDC feeds until cutover. Test the joins between them.
Check that inserts, updates and deletes made after the snapshot reach the target once and in order. Check the boundary minute too. A row written during the switch from full load to CDC is the one most likely to fall through.
Performance, Security and Rollback Testing
Performance testing confirms that loads fit the cutover window and that target queries meet agreed response times. Security testing also checks that permissions, row-level filters and masking rules came across with the data. A migration that exposes salary data to every analyst has failed, however clean the numbers are.
Rollback testing is easy to skip and expensive to miss. Rehearse the switch back to the source with real timings, so the go/no-go meeting knows how long a retreat would take.
Downstream Testing: Reports, Semantic Models and ML Features
Data rarely ends at the table. It feeds dashboards, semantic models with their own measures, partner extracts and features for machine learning models. Each of these can break while every table reconciles.
So test the consumers as well. Run the top reports on both platforms and compare the figures, then check that measures such as year-to-date revenue return the same values.
For ML features, compare distributions before and after the move. A changed default for null values can shift a model’s predictions without raising a single error.
How to Build a Data Migration Testing Strategy in 8 Steps
A testing strategy is the part of your data migration strategy that decides what to test, how hard, with which tools and who signs off. Without one, effort spreads evenly across tables that do not deserve equal attention. The eight steps below put the effort where the business risk sits, and they fit inside any wider data migration framework .
1. Rank Data by Business Risk
Start by sorting the estate into three tiers, because a general ledger and a table of retired product codes do not deserve the same scrutiny. Tier 1 is financial, regulated or customer-facing data, such as the general ledger, billing and patient records. Operational data that runs the business day to day, such as inventory and shipments, sits in tier 2. Archive and reference data that people rarely query goes in tier 3.
The tier then sets everything downstream: test depth, tolerance, tooling and who signs off. Agree the tiers with data owners before anyone writes a test. Arguing about them during cutover week burns time nobody has.
2. Write Source-to-Target Mappings With Expected Results
A mapping document lists every target column, its source column and the rule between them. Add one more column to it: the expected result for a known input.
For example, a 100.00 EUR order from March 3 should land at that day’s dollar rate, rounded once per order. Testers know what a pass looks like before the run. A disagreement about a rule surfaces in a document review rather than in production.
3. Freeze a Comparable Baseline
Comparisons only mean something if both sides describe the same moment. Pick a snapshot time, stop or capture changes from that point, and record counts, control totals, key lists and checksums. Then store them where the test team can query them next to the target.
If the source cannot stop, capture the change log from the snapshot forward instead. The target is then compared with the baseline plus known changes, not with a source that keeps moving under the test.
4. Design Test Cases and Acceptance Tolerances
Each test case needs a scope, an area, the check itself, a priority and a pass rule. For example, a row in the test register might read: tier 1, orders, monthly totals by currency match the source, high priority, zero unexplained difference.
Tolerances also belong in this step. Write them per tier, then get data owners to approve them now. A tolerance agreed after a failed test looks like moving the goalposts, so auditors will read it that way.
5. Automate Source-to-Target Comparisons
Manual spot checks stop scaling once you pass a handful of tables. Script the comparisons so they run the same way every time, with counts, key anti-joins, totals and hashes per table written to a results log. The fifth rehearsal then costs the same as the first.
Use what the platform gives you before buying anything. For example, AWS DMS has built-in validation, dbt runs data tests on models, and Google’s Data Validation Tool compares both sides from the command line. The tools section below compares eight options side by side.
6. Run Mock Migrations and a Rollback Rehearsal
A mock migration runs the full cutover script against production-like volumes, end to end, with the clock running. It shows whether the load fits the window and whether the tests finish in time to support a decision.
Run at least two mocks. Also include one rollback rehearsal in which the team switches back to the source and records how long it takes. That number goes straight into the go/no-go criteria.
7. Triage Defects, Fix and Retest
Every failed test becomes a defect with an owner, a severity tied to the data tier and a root cause. Sort defects into source data problems, mapping errors and load failures, because each one has a different fixer.
After each fix, rerun the full regression suite rather than only the failed test. Fixing a currency rule can still break a downstream measure that passed yesterday. The automation from step 5 is what makes full regression affordable.
8. Approve Cutover and Keep Post-Go-Live Controls
Cutover approval is a meeting with evidence on the table. That means test results by tier, open defects with a sign-off on each, rehearsal timings and the rollback plan. Business owners approve their own data, rather than the migration team.
After go-live, keep the reconciliation queries running on a schedule through hypercare. Then turn the most useful ones into permanent data validation controls. The new platform then keeps proving itself after the project team moves on.
Kanerika Service
Data Migration Services With Validation Built In
Kanerika plans and runs data migrations with reconciliation built into every load, parallel runs until each workload validates, and an evidence pack for business sign-off.
Explore Migration Services Worked Example: Four SQL Checks for a Migrated Orders Table
Back to the orders table from the opening story. Assume a frozen source copy sits in a schema called src, the migrated table sits in tgt, and one SQL engine can reach both.
Each table has an order_id business key, an order_date, a load_date, a currency_code, an amount and a status. The queries use generic ANSI SQL, so adjust date functions and the hash function to your platform.
Check 1, counts by load date. A grand total hides offsetting errors, while a count per load date points straight at the day that went wrong.
-- Check 1: row counts per load date, source vs target
SELECT COALESCE(s.load_date, t.load_date) AS load_date,
COALESCE(s.src_rows, 0) AS src_rows,
COALESCE(t.tgt_rows, 0) AS tgt_rows
FROM (SELECT load_date, COUNT(*) AS src_rows
FROM src.orders GROUP BY load_date) s
FULL OUTER JOIN
(SELECT load_date, COUNT(*) AS tgt_rows
FROM tgt.orders GROUP BY load_date) t
ON t.load_date = s.load_date
WHERE COALESCE(s.src_rows, 0) <> COALESCE(t.tgt_rows, 0)
ORDER BY 1;Check 2, missing, extra and duplicate keys. The first query finds orders that never arrived. Swap the two tables to find extra rows, then run the second query to catch duplicates.
-- Check 2a: keys in the source but missing from the target
SELECT s.order_id
FROM src.orders s
LEFT JOIN tgt.orders t ON t.order_id = s.order_id
WHERE t.order_id IS NULL;
-- Check 2b: duplicate business keys in the target
SELECT order_id, COUNT(*) AS copies
FROM tgt.orders
GROUP BY order_id
HAVING COUNT(*) > 1;Check 3, totals by month and currency. This is the check that would have caught the rounding change, because counts matched while sums did not. Any row returned is a month and currency where the two sides disagree.
-- Check 3: control totals by month and currency
-- Run it again with src and tgt swapped
SELECT EXTRACT(YEAR FROM order_date) AS yr,
EXTRACT(MONTH FROM order_date) AS mo,
currency_code,
COUNT(*) AS order_count,
SUM(amount) AS total_amount
FROM src.orders
GROUP BY EXTRACT(YEAR FROM order_date),
EXTRACT(MONTH FROM order_date), currency_code
EXCEPT
SELECT EXTRACT(YEAR FROM order_date),
EXTRACT(MONTH FROM order_date),
currency_code, COUNT(*), SUM(amount)
FROM tgt.orders
GROUP BY EXTRACT(YEAR FROM order_date),
EXTRACT(MONTH FROM order_date), currency_code;Check 4, a hash per row. Hashing every column turns a wide comparison into one value per row. Cast each column the same way on both sides, and wrap nullable columns in COALESCE. Otherwise, format differences will show up as false mismatches.
-- Check 4: compare a per-row hash by business key
-- hash_fn() stands for your engine's hash function
WITH s AS (
SELECT order_id,
hash_fn(CAST(order_date AS VARCHAR(10)) || '|' ||
currency_code || '|' ||
CAST(amount AS VARCHAR(40)) || '|' ||
COALESCE(status, '')) AS row_hash
FROM src.orders
), t AS (
SELECT order_id,
hash_fn(CAST(order_date AS VARCHAR(10)) || '|' ||
currency_code || '|' ||
CAST(amount AS VARCHAR(40)) || '|' ||
COALESCE(status, '')) AS row_hash
FROM tgt.orders
)
SELECT s.order_id
FROM s JOIN t ON t.order_id = s.order_id
WHERE s.row_hash <> t.row_hash;How to Read the Results
Every query should return zero rows when the migration is right. Anything else is a lead to investigate, so find the cause before you fix anything.
Check Pass looks like A failure usually means Next step
1. Counts by load date No rows returned A batch failed, was filtered or loaded twice Read that day’s load log, then the reject table 2. Key anti-join and duplicates No rows in either direction Lost rows, test rows or a broken merge key Trace the listed keys through the pipeline 3. Totals by month and currency No rows in either direction A transformation, rounding or filter changed values Run Check 4 on that month’s rows 4. Row hash No rows returned Column values differ, or casts format differently Diff the listed rows column by column
Talk to Kanerika
Want These Checks Run on Your Migration?
Kanerika maps your tables to risk tiers, sets tolerances with your data owners and automates the source-to-target comparisons, so cutover sign-off rests on evidence.
Schedule a Demo → Full Reconciliation, Sampling or Rule-Based Testing: Which Should You Use?
Testing every row of every table is ideal but not always practical. The workable answer is to match the method to the data tier from step 1.
Full reconciliation compares every row and column. Sampling compares a chosen subset of rows instead. Rule-based testing checks aggregate properties, such as totals, ranges and null rates, without comparing rows one by one.
Data tier Method Why Example
Tier 1: financial, regulated, customer-facing Full reconciliation: counts, totals and row hashes on every row One wrong row can mean a misstated report, so nothing is sampled General ledger, billing, patient records Tier 2: operational Full counts and totals, then a sampled row comparison and business rules Errors hurt operations but are found and fixed quickly Inventory, shipments, support tickets Tier 3: archive and reference Counts, control totals and rule checks Low query volume and low impact per error Closed projects, old logs, code tables
For tier 1 data the answer is full reconciliation, every time. Row hashes make that affordable even on large tables, since each row is compared as a single value.
Setting Tolerances by Data Tier
A tolerance is the size of difference you will accept without blocking cutover. The table below is an example policy, not an industry standard, so set your own with data owners and auditors.
Tier Example tolerance Who approves an exception
Tier 1 Zero unexplained differences in counts, totals or row hashes Data owner plus finance or compliance Tier 2 Zero unexplained count differences; documented rounding differences allowed Data owner Tier 3 Counts and control totals match; sampled differences logged and explained Migration lead
Note the word unexplained. A difference with a documented cause, such as test accounts the business chose to drop, is a finding rather than a failure.
Teams handling data migration in banking or insurance data migration show why the strict end exists, since their tier 1 data feeds regulatory reports. For them, a single unexplained difference blocks the cutover.
Data Migration Testing Tools Compared
Data migration tools copy and transform data; testing tools prove the copy is right, and a few products do both.
When you compare cloud-native options, such as the Azure migration tools or AWS DMS, check which ones validate data and which ones only move it. The eight options below cover cloud-native validation, open-source frameworks, platform reconcilers and commercial suites. Capabilities come from each vendor’s own documentation, checked in October 2026.
Eight Data Migration Testing Tools Side by Side
Tool Type What it validates Best fit Watch-out
AWS DMS data validation Built into a cloud migration service Compares each source row with its target row, then validates CDC changes as they occur Migrations into AWS that use DMS Needs a primary key or unique index on every validated table Google Data Validation Tool Open-source Python CLI on Ibis, Apache-2.0 Column (count, sum, avg, min, max, stddev, group by), row, schema and custom-query validation Free, scriptable checks across many databases Row hash validation is not supported for FileSystem connections Databricks Lakebridge ReconcilePlatform reconciler Schema, row and data level comparisons; row level uses hashes and needs no join keyMigrations landing in Databricks Built around a Databricks target dbt data tests In-pipeline tests Built-in unique, not_null, accepted_values and relationships (referential integrity to another model) tests, plus singular and custom generic tests Teams building the target in dbt Tests the target’s models rather than a source-to-target diff Great Expectations (GX Core) Open-source validation framework Declared expectations about a dataset, such as nulls, ranges and uniqueness Ongoing quality checks after go-live Docs cover GX Core 1.24.1 as of October 2026, so pin versions in test code QuerySurge Commercial validation suite Vendor states automated validation across 200+ data sources, AI-generated tests and a migration testing solution Scheduled, logged comparison runs at enterprise scale Vendor claims, so prove them in a pilot on your own sources Datagaps ETL Validator Commercial ETL and ELT testing suite Vendor states it automates ETL and ELT testing and supports migration testing through cutover Teams that want a GUI-driven test suite Vendor claims, so check connector coverage first Custom SQL scripts Hand-built Anything you can query: counts, anti-joins, totals, hashes Small migrations, or gaps in any tool above Maintenance and evidence logging fall on you
Configuring Built-In Validation: AWS DMS and dbt Examples
Two of the tools above start producing evidence with only a few lines of configuration. Both snippets follow the vendor documentation as checked in October 2026.
In AWS DMS, validation lives in the task settings under ValidationSettings, as the DMS data validation task settings page describes. This fragment doubles the default five threads, handles collation differences and delays failure reports by 30 minutes.
{
"ValidationSettings": {
"EnableValidation": true,
"ThreadCount": 10,
"HandleCollationDiff": true,
"RecordFailureDelayLimitInMinutes": 30,
"FailureMaxCount": 10000,
"TableFailureMaxCount": 1000
}
}The two failure limits matter most for testing. FailureMaxCount (default 10,000 records) and TableFailureMaxCount (default 1,000 rows per table) suspend validation once they are reached. To keep validating regardless, AWS says to set the limit above the source’s record count.
Two more settings change how a test runs. A ValidationOnly task compares data without moving any, and that choice cannot be changed after the task is created. From replication engine version 3.5.4, DMS also sets ValidationMode to GROUP_LEVEL on supported paths for faster checks on large datasets.
When the target is built in dbt, the same integrity rules become data tests on each model. The YAML below covers the key, status and customer rules for an orders model.
# models/orders.yml
models:
- name: orders
columns:
- name: order_id
data_tests:
- unique
- not_null
- name: status
data_tests:
- accepted_values:
arguments:
values: ['placed', 'shipped', 'completed', 'returned']
- name: customer_id
data_tests:
- relationships:
arguments:
to: ref('customers')
field: idThe arguments block arrived in dbt 1.10.5; on older versions, put values, to and field directly under the test name. These tests check the target alone, so keep Checks 2 and 3 for the source-to-target diff.
Where Tools Stop and Strategy Starts
A tool confirms the target’s amount column matches the source, but only the mapping document says whether it should have been converted to dollars. Only the tier says whether a one-cent difference blocks cutover. The tool runs the comparisons faster; the tiers, mappings and tolerances still decide what a pass means.
For comparisons that continue after the project, see our guide to data reconciliation tools .
Which Testing Tool Fits Your Migration?
The right pick depends mostly on where the data lands and whether every table has a key. Treat these rules as a starting point, then prove the shortlist in a pilot.
Moving with AWS DMS: turn on DMS validation for tables with a primary key or unique index, and cover keyless tables with Check 3 totals.Landing in Databricks: start with Lakebridge Reconcile, since its row-level hashes need no join key.Many source engines and a small budget: use Google’s Data Validation Tool for column and row checks from one CLI.Target built in dbt: add dbt data tests for keys and rules, and keep Checks 1 to 4 for the cross-system diff.Audited, scheduled runs across dozens of sources: pilot QuerySurge or Datagaps on two of your hardest tables first.A few tables on one engine: the four SQL checks above are enough, provided every result goes to a log table.
Six Questions to Ask Before Choosing a Testing Tool
A short evaluation saves a painful discovery during the first mock run. Put these six questions to every vendor as well as to your own team.
Does it connect to both your source and your target, including files and APIs?
Can it compare rows without a primary key, or does every table need one?
Does it handle CDC windows, or only static snapshots taken before the move?
Will it finish your largest table inside the cutover window?
Does it produce evidence auditors accept, such as timestamped run logs?
Who maintains the tests after go-live, and do they have the skills?
What Drives Testing Effort and Cost
Testing effort depends less on the tool than on five drivers. None of them has a standard price, so estimate each one from your own estate and the results of the first mock run.
Driver Why it adds effort How to reduce it
Data volume Full comparisons take longer and need more compute Hash rows and reconcile tier 1 in full, while sampling lower tiers Transformation complexity Every rule needs its own expected result and test Write expected results into the mapping document before testing Number of sources Each source brings its own formats, keys and quirks Standardize profiling and comparison scripts per source type Retest cycles Every fix triggers a regression run Automate the suite so reruns cost minutes Evidence needs Regulated data needs logged, reviewable results Write results to a log table from day one
Manual vs Automated Data Migration Testing
Automation wins on repetition and scale, while people win on judgment. A good plan uses each where it is strongest.
Script anything you will run more than once, which in a migration with several mock runs means nearly every comparison. Keep people on the work that needs context, such as deciding whether a difference matters, probing edge cases and signing off.
As of October 2026, AI-powered data migration tools draft tests too. Datagaps says its ETL Validator builds tests from mapping documents, and QuerySurge advertises AI-generated tests, but a person still decides which differences matter.
Business acceptance in particular stays human. A finance lead who closes a sample month on the new platform gives evidence no script can.
Task Best done by Why
Counts, anti-joins, totals, hashes Scheduled scripts Repeated on every mock run and after every fix Schema and permission comparison Automated metadata diff Mechanical work that rarely needs judgment Investigating a difference A data analyst Needs business context before judging Edge-case and exploratory tests Testers with domain knowledge Finds cases the mapping document missed Business acceptance and sign-off Business owners Accountability sits with the data owner
Go/No-Go Criteria, Ownership and Test-Data Security
The last week before cutover is for making decisions. Three things keep that week calm: clear owners, safe test data and a written go/no-go checklist.
Each is cheap to set up early and expensive to fix late. Teams that reach cutover week without them spend it arguing about who is allowed to say no while the clock runs.
Who Owns What
Testing fails quietly when nobody owns the result, so name owners early as part of data migration governance . A slimmed-down RACI table, showing who is accountable and who does the work, settles that before the first test runs.
Activity Accountable Responsible
Data tiers and tolerances Data owner QA lead, with input from finance or compliance Test design and automation QA lead Migration engineers, who build the comparison scripts Running loads and fixing defects Migration lead Migration engineers, while QA retests each fix Business acceptance Business sign-off owner Key users, who close a sample period Test-data security Governance team QA lead and engineers, during every rehearsal Go/no-go decision Business sign-off owner Data owners, once the evidence pack is complete
Everyone else is consulted or informed, and the full test plan records who. The point is that each line has exactly one accountable name.
Securing Test Data
Rehearsals copy real data into places that may be less protected than production. NIST SP 800-122 (April 2010) says PII used in a test environment must be protected at the same level as in production.
The same guide notes that random fake data often falls short in testing, because some statistical properties of the real data must be kept. Anonymized substitutes keep those properties without counting as PII. For example, swap ZIP codes between customer records and replace names with generated ones, so counts, totals and key joins still behave like production.
Use data masking or anonymized substitutes in lower environments wherever the test allows it.
Some tier 1 tests need real values. For those, restrict access to named people during the rehearsal window and log every query. Delete the copies when the rehearsal ends, and record that you did.
Go/No-Go Checklist
This list covers testing evidence only. For the full migration plan, from scoping to decommissioning, use our data migration checklist .
All tier 1 tables reconcile with zero unexplained differences.
Tier 2 and tier 3 results sit within approved tolerances.
No open severity 1 defects, and every open defect has an owner and a sign-off.
The last mock migration finished inside the cutover window.
Rollback has been rehearsed and timed.
Top reports and semantic model measures match on both platforms.
Business owners have signed acceptance for their data.
Post-go-live reconciliation jobs are scheduled before cutover.
If any line is unchecked, the default answer is no-go. A short delay costs far less than a rollback in front of customers.
Checklist
Data Migration Checklist
The full migration plan in one checklist, from scoping and mapping to cutover, validation and decommissioning, to sit alongside your testing evidence.
Get the Checklist → Common Data Migration Testing Challenges and Mistakes
The mistakes that hurt migrations tend to be ordinary ones, repeated because they look like shortcuts. The table lists six, with the symptom you will see and the fix. For the wider catalog of what can go wrong, see our guide to data migration risks .
Mistake Symptom Fix
Signing off on row counts only Counts match, but totals and reports do not Add key anti-joins, control totals and row hashes Testing against a moving source Differences appear and vanish between runs Freeze a baseline, then track changes from the snapshot No rollback rehearsal Nobody knows how long a retreat takes Time a rollback during a mock migration Skipping reports and the semantic layer Tables reconcile while dashboards disagree Compare top reports and measures on both platforms Sampling tier 1 data A rare but costly error slips past the sample Reconcile tier 1 in full with row hashes Testers without business context Tests pass on data the business knows is wrong Pair QA with data owners and encode business rules
The common thread is false confidence. Each mistake produces a green result that measures the wrong thing. That is how the opening story’s counts matched while its revenue did not.
Ownership is the other thread. When a test fails and nobody owns the table, the defect waits, and the cutover date arrives anyway.
How Kanerika Tests Data Migrations
At Kanerika, testing is part of every data migration engagement rather than a phase added at the end. Our FLIP platform pairs automated schema conversion with built-in validation, and its modules include Migrate and Reconcile.
FLIP compares data across systems, flags mismatches automatically and logs every comparison in an audit trail. Each workload stays in a parallel run until its validation passes.
The business then receives an evidence pack, with reconciliation results by tier, the defect log, rehearsal timings and the rollback plan. That pack is what the go/no-go meeting reviews, and it also stays with the client as an audit record.
A retail client shows the method on a live estate. The team moved PostgreSQL and Cassandra workloads to Databricks in three phases. Legacy systems and Databricks ran in parallel, and each application was redirected individually after validation.
The Databricks migration case study reports zero downtime with no production interruption and 100% of legacy infrastructure decommissioned. It also reports 100% centralized governance, lineage and data access. Our migration accelerators apply the same validate-then-redirect pattern to other platform moves.
Case Study
Zero-Downtime Databricks Migration for Retail Analytics
PostgreSQL and Cassandra workloads moved to Databricks in three phases, with legacy and new platforms in parallel and each application redirected only after validation.
Read the Case Study → The Bottom Line on Data Migration Testing
A migration is only as trustworthy as the evidence behind it. Start with the strategy: rank data by risk, write expected results and freeze a baseline.
Cover every test type, from completeness through CDC to the reports at the end of the chain. Pick a tool that fits your target, then run the four SQL checks on every tier 1 table.
If you do one thing this week, freeze a snapshot of your highest-risk table and run Check 3 against the target. One row back from that query tells you more about readiness than any green load job.
Frequently Asked Questions
What is data migration testing?
Data migration testing is the process of proving that data moved from a source system to a target system arrived complete, accurate, consistent and usable. It compares counts, keys, totals and individual rows before, during and after cutover. It also checks that reports and downstream systems still produce the same results, so business owners can sign off with evidence.
What is the difference between data migration testing and ETL testing?
ETL testing checks a recurring pipeline that loads data every day, focusing on transformation rules, load logic and error handling. Data migration testing checks a one-time move and its cutover, focusing on completeness, accuracy and business sign-off. Both rely on reconciliation, but migration testing also covers rollback rehearsals, parallel runs and go/no-go decisions.
What are the entry and exit criteria for data migration testing?
Entry criteria usually include approved data tiers and tolerances, a frozen source baseline, finished mapping documents with expected results and a ready test environment. Exit criteria include tier 1 tables reconciled with zero unexplained differences, other tiers within tolerance, no open severity 1 defects, a timed rollback rehearsal, matching reports and signed business acceptance.
Who is responsible for data migration testing?
Responsibility is shared. The QA lead designs and runs the tests, migration engineers fix load and mapping defects, and data owners set tiers and tolerances. Business users perform acceptance testing and approve the go/no-go decision for their data. Governance teams own test-data security, so masked data and access controls are in place during rehearsals.
How long does data migration testing take?
Duration depends on data volume, the number of sources, transformation complexity, how many retest cycles defects trigger and how much evidence regulators expect. Automated comparisons shorten every cycle, while manual checks lengthen each one. The most reliable estimate comes from the first mock migration, which shows how long a full test run takes on real volumes.
What are the main types of data migration testing?
The main types are completeness and reconciliation testing, schema and mapping testing, transformation testing, referential integrity and business-rule testing, and incremental or CDC testing. Performance, security and rollback testing confirm the cutover itself. Downstream testing then checks that reports, semantic model measures and machine learning features return the same results on the new platform.
How do you test data after a migration?
Start by comparing row counts per load date, then run key anti-joins to find missing, extra and duplicate records. Compare control totals by month and currency, and hash each row to catch changed values. Finally, run old and new systems in parallel, compare top reports, and have business owners close a sample period before sign-off.
Should data migration testing be automated?
Yes, for every comparison you will run more than once. Counts, anti-joins, totals and row hashes repeat on every mock migration and after every fix, so scripting them keeps retests cheap and consistent. People should still investigate differences, explore edge cases and give business acceptance, because those tasks need context and accountability that automation cannot provide.
What are common data migration testing mistakes?
Common mistakes include signing off on row counts alone, testing against a source that keeps changing, and skipping a rollback rehearsal. Teams also miss broken reports and semantic models, sample tier 1 financial data instead of reconciling it in full, and assign testers without business context. Each mistake produces a passing result that measures the wrong thing.
When should data migration testing start?
Testing should start before any data moves. Profile the source and freeze a baseline of counts, control totals, key lists and checksums at a fixed snapshot time. Write expected results into the mapping document during design. Then validate each load and CDC window during the move, and run parallel and acceptance tests after cutover.
What tools are used for data migration testing?
Options include AWS DMS data validation, Google’s open-source Data Validation Tool, Databricks Lakebridge Reconcile, dbt data tests and Great Expectations. Commercial suites such as QuerySurge and Datagaps ETL Validator add scheduling and reporting. Many teams also keep custom SQL scripts. The right choice depends on your target platform, data volume, CDC needs and evidence requirements.
What SQL queries are used in data migration testing?
Four queries cover the core checks. Compare row counts per load date between source and target. Use a left join anti-join to find keys missing from the target, plus a grouped count to find duplicates. Compare totals by month and currency with EXCEPT. Then compare a hash of each row by business key to catch changed values.
How do you test large-volume data migrations?
Rank tables by business risk and reconcile tier 1 data in full, using one hash per row so large tables compare quickly. Use counts, control totals and sampled row comparisons for lower tiers. Validate each batch as it loads instead of waiting for the end, and time mock migrations to confirm the tests finish inside the cutover window.
