Data Migration Testing concept and checklist for testing

Data Migration Testing

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

The scope of data migration is much more than what the term suggests. Data migration activity typically will include 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).

Consequently it involves more activities than only migration of existing data as the name suggests.

In a typical scenario it can include following activities:-

A. Migration of existing data to the new database.

B. The new database can have some 'Master Tables'. And Master tables need to be populated with data first to account for various referential constraints. In such cases data migration includes the activity of setting up (master) data in master tables.

C. The new database can have some tables or attributes which are not present in the existing database. Thus a completely new set of data will be setup in these tables.

While data migration is different, first understand the data migration process. Data migration is the process of shifting the whole production data from one database to other new database. This process might be done by using data migration tool which is developed as per the data migration requirements.

During this data migration process 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 gets currepted during migration.

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

When we are speaking database migration testing. We can say ETL testing. Database migration is extracting the database from source and loads the data to target database.

Two types of Loading:

1. Direct Loading.

2. Incremental Loading.

Direct Loading:

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

Incremental Loading:

The Incremental loading, when loading the data from source to target, applying some transformations. Cleaning the data or load the data with specific conditions.

Challenge is two different databases have their own structures so just simple table/record import export function will not work. We need a tool (say ETL) in between source database and destination database where we will write the transformation logic, done by the Dev team. Testing team will validate that all those business rules in ETL are actually working in migration testing

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.

Different types of migrations are listed below. These are the migrations which were encountered during testing of data migration project(s). There could be some more types of migration and presently are not part of the below list.

1. Change in datatype.

a. Data in the source table may be present as a character, but in the target table the mapped column is an integer.

2. Concatenation of two or more columns.

3. Mathematical transformations (e.g. summing up of fields etc)

4. Migration of values depending on other fields/tables present in the source database.

c. Target Generated Data:

This is the data that is generated at the target database. These values will not be present the source database but will be generated each time an insertion or an update takes place on a table. These columns generally are the primary key of a table.

d. Reference at target system generated.

This type of migration deals with data that has to comply with referential integrity constraints. This data is generated at the new database but the data comes from the parent attribute. A reference key in the target database is usually encountered when a table in the source database is split up into two or more tables in the target.

e. Manual setup data in the target database.

Certain tables have to be populated before the migration takes place. These tables can be termed as ‘Master Tables’. These tables are the parent tables to the various referential constraints that are to be implemented in the child tables. The data for these tables may not come from the source database but will be setup manually in the target database.

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

Useful links:

http://www.datamigrationpro.com/data-migration-articles/2009/11/30/how-to-implement-an-effective-data-migration-testing-strateg.html

http://www.agiledata.org/essays/databaseTesting.html