Banks moving to a new core system, hospitals replacing their records software, retailers shifting to the cloud: every one of these is a data migration, and many large IT service projects in India include one. Data migration testing proves that every record arrived in the new system complete, correct and usable. This guide covers the strategy, process, SQL checks, tools and a checklist you can reuse.
What Is Data Migration Testing?
Data migration testing verifies that data moved from a legacy (source) system to a new (target) system without loss, corruption or unintended change, and that the target application works correctly with the migrated data. It covers the data itself and the business processes that depend on it.
Data Migration Testing vs ETL Testing
| Data migration testing | ETL testing | |
|---|---|---|
| Frequency | Usually one-time (or a few waves) | Ongoing, every load |
| Target | A new operational application | Usually a data warehouse for reporting |
| Main risk | Lost or broken records at go-live | Wrong numbers in reports over time |
| Shared skills | SQL reconciliation, mapping review, data quality checks | Same |
The techniques overlap heavily, which is why ETL testers are often staffed on migration projects. Searches for "ETL testing for data migration" reflect this. See what ETL testing is.
Data Migration Testing Strategy
A good strategy document answers these questions before testing starts:
- Scope: which entities (customers, accounts, transactions), how many years of history, which are out of scope.
- Mapping: source-to-target field mapping, default values and data that will be cleansed or dropped.
- Waves and cut-over: big-bang or phased; mock migrations (rehearsals) before the real one.
- Reconciliation rules: which counts and totals must match, and what tolerance (usually zero for money).
- Sampling vs full comparison: full automated comparison for key fields; sampling for visual checks in the new application.
- Exit criteria and rollback: what blocks go-live, and how to roll back if it fails.
Data Migration Testing Process and Approach
| Phase | What testers do |
|---|---|
| Pre-migration | Profile source data (nulls, duplicates, invalid values), review the mapping, record baseline counts and totals |
| Mock migration runs | Execute the migration in a test environment, run reconciliation, log defects, repeat until clean |
| Migration (cut-over) | Monitor job logs and rejects; run quick count checks per entity |
| Post-migration | Full reconciliation, field-level comparison, business process tests in the new application, performance checks |
| Sign-off | Reconciliation report with evidence; agree on known issues |
Pre-migration profiling is where most surprises are found. See data profiling.
Reconciliation SQL for Data Migration
-- 1. Record counts per entity (run on legacy and new systems) SELECT 'customers' AS entity, COUNT(*) FROM legacy.customers UNION ALL SELECT 'accounts', COUNT(*) FROM legacy.accounts; -- 2. Financial totals must match exactly SELECT SUM(balance) AS total_balance, COUNT(*) AS accounts FROM legacy.accounts WHERE status = 'ACTIVE'; -- 3. Records missing after migration SELECT account_no FROM legacy.accounts EXCEPT SELECT account_no FROM newsys.accounts; -- 4. Field-level differences on migrated records SELECT l.account_no, l.balance AS legacy_bal, n.balance AS new_bal FROM legacy.accounts l JOIN newsys.accounts n ON n.account_no = l.account_no WHERE l.balance <> n.balance OR COALESCE(l.branch_code,'') <> COALESCE(n.branch_code,'');
When legacy and new systems are on different databases, extract both sides to a common database or compare with Python. See Python ETL testing framework.
Data Migration Testing Tools
| Tool type | Examples | Use |
|---|---|---|
| SQL and scripts | SQL clients, Python with pandas | Most projects; full control over reconciliation |
| Data testing tools | QuerySurge, Datagaps ETL Validator | Automated source-to-target comparison at scale |
| Migration/ETL platforms | Informatica, SSIS, Azure Data Factory, AWS DMS | Built-in logs, reject handling and row counts |
| Data quality tools | Great Expectations, dbt tests | Rule-based checks on the target |
More on tools: ETL testing tools and QuerySurge ETL testing.
Data Migration Testing Checklist
- ☐ Source data profiled; known data issues documented
- ☐ Mapping reviewed and signed off, including defaults and transformations
- ☐ Record counts match per entity (or differences explained by agreed rules)
- ☐ Financial and quantity totals match exactly
- ☐ No missing or extra records on business keys
- ☐ Field-level comparison passed for critical fields
- ☐ Relationships intact: no orphan child records
- ☐ Dates, currencies, encodings (including Indian-language text) converted correctly
- ☐ Rejected records reviewed and resolved
- ☐ Business processes tested in the new application with migrated data
- ☐ Performance acceptable with full data volume
- ☐ Rollback plan tested
Data Migration Testing Interview Questions
1. What is data migration testing?
Verifying that data moved from a legacy system to a new system is complete, accurate and usable by the new application.
2. What checks do you run after a migration?
Counts per entity, totals, missing/extra records on business keys, field-level comparison, referential integrity, rejects review and business process tests.
3. What is a mock migration?
A rehearsal of the full migration in a test environment so defects are found before the real cut-over.
4. How do you test when source and target are different databases?
Extract both sides to a common database or files and compare with SQL or Python, or use a tool that connects to both.
5. How is migration testing different from ETL testing?
Migration is usually a one-time move into a new operational system; ETL testing validates recurring loads into a warehouse. The SQL techniques are similar.
Data testing skills: SQL for testers · Power BI testing · big data testing · data quality checks · ETL tools list · ADF & Databricks testing · database testing interview questions
More guides for Indian students: ETL full form · ETL testing with example · ETL testing jobs in India · ETL testing course in India · ETL training in Hyderabad, Bangalore & Pune · best ETL testing course on Udemy · ETL testing roadmap · ETL tester vs ETL developer · interview questions for freshers · free ETL testing mock test · SQL for testers · QA to data engineer · ETL testing kya hai (Hindi)
Frequently Asked Questions
What is data migration testing?
Data migration testing verifies that data moved from an old system to a new one is complete, accurate and works correctly in the new application, using counts, totals, record-level and field-level comparisons.
What is the data migration testing approach?
Profile the source, review the mapping, run mock migrations with full reconciliation, monitor the cut-over, then run post-migration reconciliation and business process tests before sign-off.
Which tools are used for data migration testing?
SQL and Python scripts on most projects, plus tools such as QuerySurge or Datagaps ETL Validator for automated comparison, and the logs of the migration platform itself.
Is data migration testing part of ETL testing?
It is closely related. Both use source-to-target mapping and SQL reconciliation, so ETL testers are often assigned to migration projects.
What skills do I need for data migration testing?
Strong SQL, understanding of the business data, data profiling, reconciliation techniques and clear defect reporting. Python helps when comparing across different databases.
Asim Noaman Lodhi
Certified Google Partner · QA Consultant · 12+ Years IT
QA consultant specializing in ETL testing and data quality. Trained 913+ students to transition into data testing roles through hands-on, real-world instruction.