Data migration testing: A practical guide to data validation

Data migration testing ensures that data moves between systems without loss, corruption, or rule violations. It validates accuracy, completeness, and consistency across source and target environments during and after migration.

There are several reasons organizations need to migrate data, ranging from system upgrades to application consolidation. Data migration can be performed manually or through dedicated tools, depending on system complexity and data volume. The process becomes more demanding when large datasets or complex applications are involved, where even minor inconsistencies can impact business operations.

Data accuracy is a key factor in any migration test strategy. It must be verified when loading data from a source system into a target environment. QA teams focus on confirming whether data is complete, correctly mapped, and aligned with all defined business rules. These checks address common questions that arise during the migration process, such as whether all records were transferred correctly and whether transformations were applied as intended.

Data validation testing applies the principle of early testing. QA engineers begin by reviewing requirements, business rules, and data structures before executing test cases. Early analysis helps identify defects sooner, reducing rework and lowering overall migration costs.

Typical objectives of data migration testing include:

• Verify if all the required data was transferred according to the requirements.
• Verify if the destination tables are populated with accurate values.
• Verify the absence of data loss unless it is based on requirements.
• Verify the performance of custom scripts.

Migrating data is not simply about transferring large amounts of data from one system to another. It is the complex work of a QA team and it requires skill, expertise, tools, and resources.

So, it’s time to go through the customary steps that should be taken during Data Validation testing within the migration process:

1. Test planning and control

Test planning defines how data migration testing will be organized, executed, and controlled. It sets the scope, approach, risks, and required resources before any testing activities begin.

Test planning is the most important activity in any test project. This step presupposes the following actions:

Identify and agree on data migration requirements, identify the whole list of data cleaning requirements and understand all the dependencies and interactions with other systems.

• Define the scope. It’s necessary to pinpoint and describe all the things that need to be tested (data to be migrated, migration rules, data quality assessment).
• Define migration approach. Select techniques, determine test items and coverage. Decide on single migration (big bang), phased or incremental approach.
• Define risks (business objectives, product, project and technical risks).

Determine the size of the test efforts. It’s very important not to underestimate the required efforts for testing. It should be pointed out when and who is to be involved in testing, set up all the needed supportive hardware and software.

Define the test completion criteria.

Meticulous monitoring and control.

2. Test analysis and design

During test analysis activity, QA will create TCs with an emphasis on how each item of data is mapped and migrated from source to target. It will also include a design of how the migration is validated through testing and define non-functional aspects to test.

A. Review test basis, analyze test items, specification and identify test conditions

Review documentation and check the mapping of each data type in the legacy system and the new system. Make a note that during the migration, based on the transformation rules, some columns may be combined into one column; some column names, tables may change names or tables.

Then, according to the documentation, check the DB scheme to ensure that it includes mandatory fields, field names, field types, data types, etc., for both, the original data source and the destination system and find any mistakes or error areas and correct as much as possible.

A good mapping document is supposed to represent the following data from both source and target fields:

• Table name
• Field name
• Data types and length

Transformation flow: how source tables and files should be joined in the new target data set, transformation logic, and/or business rules

Take into account if any fields that are required in the new application are not required in the old one and make sure that the value of these fields is not equal to NULL.

Mapping is a useful instrument for data loss prevention in case you need to deploy tables or columns that have been renamed. It (mapping with business rules) can be represented in different ways, for example: in a simple table (excel) or in a graphical view.

B. Create TCs and a set of SQL queries to validate the data before and after migration

The following key points should be considered:

Data completeness

Data completeness includes:

Record count verification.

Record count checks consist of 2 scenarios:

• Record count for inserted Records. Use a requirement document so that you can identify what the expected result is.
• Record count for updated records. The number of records which are updated should match the record count in the source table (also using requirement document).

In order to get a number of a record in a target or source table we can use a simple

SELECT COUNT (*)

FROM table (changed)

Data quality

Data quality ensures that the data is correctly loaded into the destination tables/fields, and that the application rejects, substitutes default values, alters, ignores, and reports invalid data correctly.

Comparing source and target data sets. For example, in order to compare data in source

DB with destination DB two types of queries can be created:

• Query to extract data from the source database. For example:

SELECT t.name, v.vendor_name, p.description, p.brief FROM rewards r INNER JOIN promotions p ON r.id = p.reward_id INNER JOIN vendor v ON r.vendor_id = v.vendor_id

• Query to extract data from the destination database. For example:

SELECT p.name, v.partner_name, p.long_description, p.short_descryption FROM product_entity p INNER JOIN vendor v ON p.partner_id = v.partner_id

Verification Data validation. It includes the following:

• Data validation for updated records.
• Data validation for inserted records.
• Duplicate records check. Can be done using the next query:

SELECT column1, COUNT (column2)

FROM table

GROUP BY column1

HAVING COUNT (column2) > 1

Verification of the deleted records. Based on the requirements, some data can be deleted.

Verification of Distinct Values. For example, a requirements document specified that a column in the target table should have distinct columns. Then, the next query can be used:

SELECT DISTINCT <Column Name> FROM <Table Name>.

Verification of many source rows into one target row. A Requirements Document can specify that some rows should be combined into one. An example of the query:

SELECT s.srcCode, s. srcName, t. Name

FROM (SELECT Code, CONCAT(col1, ‘-’, col2) as Name FROM src_table) s

INNER JOIN (SELECT Code, Name FROM dest_table) t ON s.Code = t.Code

WHERE s.Name <> t.Name

Inspection of correct handling of invalid data types in the fields.

Inspection of the correct handling of the NULL values.

Data Transformation and Data Integrity

• Check if the data transformation works according to the requirements and business rules.
• Check if new tables are created and if new columns are created with the proper data types (as specified in the design).
• Check the referential integrity between tables.

B. Create TCs and a set of SQL queries to validate the data before and after migration

Learn about data validation testing techniques and approaches to help you ensure the data you deal with is correct and complete.

3. Execute test cases

Once migration has been performed, QA should start running TCs that are created during the design phase and compare results. How do you compare huge data files? There are a lot of free or commercial tools for that. Data can be compared in SQL Data Compare or Red Gate. Also, the exported files can be compared with ‘Total Commander’ or Excel.

Data validation confirms that data migration aligns with business rules and mapping requirements. However, successful validation does not guarantee that the target environment operates correctly in real conditions. Therefore, further testing is required beyond data comparison to ensure system stability and functional correctness.

That is why the following testing types should be performed:

• Non-functional testing, which includes the following:
• Security testing in order to make sure that no security vulnerabilities were opened up.
• Performance testing like load and stress tests to ensure system stability.
• Functional Application testing. Running the critical business processes in the destination environment is more than important, it’s vital. It often happens that there are still some issues that need to be resolved before the migration process is completed.

4. Evaluating exit criteria and reporting

This phase confirms whether data migration testing objectives have been achieved and whether the migration can be considered complete and stable. It focuses on verifying the reliability of the migration process and the quality of the data in the target system before final approval.

This phase presupposes checking the following:

• Whether determined exit criteria are met.
• Determination if more tests are needed.
• Summary of the test results and report creation.

The most widespread issues during data migration testing

You know that testing is a live procedure. So, any professional might come across a whole range of issues. Here, I’ll touch upon the most common ones. Those include:

• Process.
• Underestimated scope.
• Resources proficiency: hard and soft skills, experience, etc.
• Data inconsistency.
• Loss of Data.

FAQ

When should data migration testing start in a project lifecycle?

Data migration testing should start during the early planning phase, before the actual migration begins. Early involvement allows QA teams to review data requirements, define a test plan, and identify risks linked to data fields, dependencies, and system interactions.

What are the most common risks in data migration projects?

Common risks include incomplete data transfer, mismatched data fields, system incompatibilities, and issues caused by complex data structures. Poor planning can also lead to data loss or corruption, especially when large volumes of data are moved between systems.

How does test environment setup impact data migration testing?

A properly configured test environment ensures that migration activities reflect real production conditions. Without it, validation results may be inaccurate, making it harder to identify issues in data quality, performance, or data integrity during migration testing.

What is reconciliation testing in data migration?

Reconciliation testing is the process of comparing source and target data to ensure that all records have been transferred correctly. It helps verify data consistency, detect missing or duplicate records, and confirm that transformation rules were applied correctly.

Can data migration testing be fully automated?

Automation can significantly improve efficiency, especially when validating large datasets or repetitive test cases. However, not all migration scenarios can be automated, particularly when business rules or complex data relationships require manual validation.

Conclusion

As it’s more and more about data and its quality nowadays, businesses need to drive maximum value by utilizing it. Migration of data is not just the process of physically moving data from one storage to another. When data is extracted from one source, transformed, and finally loaded into the destination place, it is the best time to check its quality. Data migration testing is a viable instrument which capacitates organizations to get the best insights from their data, but it requires specialized skills, expertise, tools, and resources.

Want to ensure your data migration is seamless and accurate? Contact us and let’s make sure it’s successful with meticulous planning, validation, and testing.