Data Migration Testing

Data migration is a process which involves the migration of data from an existing legacy system to a new system. Whenever an organization decides to upgrade or change its database, it will need to transport the existing data to the new database.

Data migration activity includes everything that is required with respect to data to ensure that the new database is up and running without any defects and it also contains all the legacy data ( data that is present in the existing database) , and the data has been migrated to correct table(s) and column(s).

During this data migration there may be possibility of data missing or data corruption. So after the data migration process one has to test whether the whole data is migrated successfully or not, whether something is missing or got corrupted during migration.

So data migration testing is to compare migrated data with original data to find out any discrepancies.

Two types of data Load:

1. Direct Loading.
2. Incremental Loading.

Direct Loading:

The direct loading is copying the data from source to target. The number of rows should be equal among source and target.

Incremental Loading:

Loading the data from source to target by applying some transformations. Cleaning the data or load the data with specific conditions.

Types of Migration

Migration of data from one database to another can be further classified into various subsets.

a. Direct Migration from the source database to the target:
In this type of migration the data elements that are present in the source database is directly transferred into the target database
b. Migration after performing certain transformations on the source data:
This migration requires some transformation of the source data before it is migrated to the new database.
For e.g., a field ‘Priority’ in the source database may allow only one out of the three values High, Medium and Low. But in the target database the same field allows one out of the three values 1, 2 and 3. Thus the a High value in the source database will be transformed to a value 1 and will then be migrated to the target database.

Test Planning

Activities during planning of data migration testing:-

1.       Understanding the Requirements.

2.       Understanding the databases involved.

3.       Estimating the size of the data that is under the scope of data migration.

4.       Identifying the 'Entity Relationship' in the existing and the new databases. This indicates how various attributes are related to each other in the two databases.

5.       Identification of the complexity of migration, like -

6.       a. Data migration (directly migrated from existing to the new database). This can be further    divided into two categories
  i. AS IS transformation: The data from the existing database that is directly migrated to the new database without any transformation, this follows the logic - transform as it is.
  ii. Functional transformation: This is the set of data that undergoes some transformation before it is migrated to the new database. This is done based on the transformation rule as specified in the requirements.
 b. Manually setting up data (manually populating some of the tables in the new database).

c. Data that is generated at the new database. There are some attributes for which values are generated every time any insert into or update on the table occurs.

d. Data that has to comply with referential integrity. This data is generated at the new database but the data comes from the parent attribute.

e. Data that is populated as default value for some of the attributes.

6. Identifying the tests to be automated and the tests that can be automated.

7.       Developing approach and SQL queries. This step includes development of test approaches. Test approaches are developed based on the different types of migration identified in the step 5

Checklist for test Data Migration Testing

1. We can check for the table row count.
2. We have to check database integrity.
3. We have to check the referential integrity.
4. Whether the data are loaded in the target database correctly.
5. Check the constraints.
6. All the entities are transformed or not like tables, SPs, Views, functions etc
7. Check the functionality of your application with target database, it should work as it is supposed to work

Data Migration Testing Life Cycle

The following are the steps to be followed in the process of Data Migration Testing:-
1. Data Migration Requirement Analysis
2. Test Strategizing
3. Creation of POC
4. Test Planning
5. Design of Automated Test Scripts
6. Test Environment Setup
7. Test Execution
8. Result and Defect Analysis 

Risks Involved

1. Before the QA team starts to develop its approach, the requirements - in case of data migration it can also be a data mapping document where different transformation rules and logic and the mapping of attributes in the source and target database are listed, are frozen.

2. The QA team should be provided with the latest requirements (mapping documents) otherwise this will lead to major rework in the approach and SQL queries and automating programs.

3. QA team may not understand the transformation rules correctly incase of functional transformations.

4. The QA team must understand the relation ship between different attributes correctly else the SQL queries and automating programs will not give correct results and may eventually result in improper defect reporting.

Test Automation

When testing large databases, manual testing of the migration may prove to be in-efficient. Sampling of data can be done and a manual verification can be performed on this sample data, but this can lead to in-sufficient testing as a larger part of the database may be left untested. Thus automated tests should be performed to ensure that an error free migration is performed.

Automation involves using scripts which will do the job of comparing the data in the source and the target databases. The candidates for automation would be those fields where the number of records involved are large and also those where there is functional transformation taking place when the data is being migrated from the source to the target database.

Automation can be done by writing test scripts (i.e. programs) that compare the data present in the two databases. The script may be written in any programming language that is comfortable to the tester and which has the functionality to access both the source and target databases. The automated script merely has to retrieve the data present in the two databases and compare the values/records of each column/table of the source and target database with each other. The scripts will contain SQL queries that will retrieve the data present in the database. The complexity of these queries will depend on the database and also on the type of migration being performed. Data retrieved from the source and target databases should be sorted in the same manner. After the script retrieves the data, the script should perform a one to one comparison between the data elements retrieved from the source and target databases. The details of the comparison should be written into a file thus allowing the tester to verify if any errors were encountered during the comparison.