Importance of Migration Testing during an Implementation

Why Testing is Important?

Testing a system is always an important part of any implementation. As the saying goes, ‘seeing is believing’, similarly we can say that any system is working fine not when data has been migrated successfully or the new system has been installed successfully, but when we see that the system is behaving as per business requirement without causing inconvenience to its clients/customers.

The existing/legacy system will be replaced with new CBS by transferring data from the former to the later.

Data migration is the process of transferring data between storage types, formats, or computer systems. It is a key consideration for any system implementation, upgrade, or consolidation. Data migration occurs for a variety of reasons, including server or storage equipment replacements, maintenance or upgrades, application migration etc…

When the data is migrated, extensive amount of testing should be done to ensure that the migrated data does not impact the business of the bank by suspending/stopping the functionality which was working earlier in the previous version of CBS or Legacy system.

Data is a key element during any migration. Most banks use a mapping study to analyse available legacy data versus required data in the core banking system.

This helps uncover issues with data quality and will have to be cleaned.

Testing the deliverables of data capture or migration tools helps verify the availability and quality of required data during migration.

The Migration Process

To model a data migration effort, most banks extract data from legacy systems using a data capture tool, process it through a migration tool, and deliver output in a flat file format that can be uploaded to the migration staging area. Validation is run on the staging data to check that it is correct.

Migration tool testing and data validation checks are used in this phase to minimize defects. To mitigate data-related risks, it is crucial to perform exhaustive data testing and validation.

Database migration testing is needed when you move data from the old database(s) to a new database. Database migration may be done manually but it is more common to use an automated ETL (Extract-Transform-Load) process to move the data. In addition to mapping the old data structure to the new one, the ETL tool may incorporate certain business-rules to increase the quality of data moved to the target database.
What should be the Scope of Testing?
Here are some of the things that you may want to test –

1. All the live (not expired) entities e.g. customer records, order records are loaded into the target database. Each entity should be loaded just once i.e. there should not be a duplication of entities.
2. Every attribute (present in the source database) of every entity (present in the source database) is loaded into the target database.
3. All data related to a particular entity is loaded in each relevant table in the target database.
4. Each required business rule is implemented correctly in the ETL tool.
5. The data migration process performs reasonably fast and without any major bottleneck.Possible Challenges during Migration Testing

1. Here are some of the challenges that you might face –The data in the source database(s) changes during the test.
2. Some source data is corrupt.
3. The mappings between the tables/ fields of the source databases(s) and target database are changed by the database development/ migration team.
4. A part of the data is rejected by the target database.
5. Due to the slow database migration process or the large size of the source data, it takes a long time for the data to be migrated.

Activities during Migration Testing
The test approach for database migration testing consists of the following activities:

I. Design the validation tests
In order to test database migration, you need to use SQL queries. Need to create the validation queries to run against both the source as well as the target databases.  It is common to arrange the validation queries in a hierarchy e.g. you want to test if all the Orders records have migrated before you test for all OrderDetails records. Put logging statements within your queries for the purpose of effective analysis and bug reporting later.

II. Set up the test environment
The test environment should contain a copy of the source database, the ETL tool (if applicable) and a clean copy of the target database. You should isolate the test environment so that it does not change externally.

III. Run your validation tests
Depending on your test design, you need not wait for the database migration process to finish before you start your tests.

1. Report the bugs
   You should report the following data for each failed test:
   a. Name of the entity that failed the test
   b. Number of rows or columns that failed the test
   c. If applicable, the database error details (error number and error description)
   d. Validation query
2. User account under which you run your validation test
   f Date and time the test was runFinally

Here are a few tips below to refine your test approach –

• You should take a backup of the current copies of the source and target databases. This would help you in case you need to re-start your test. This would also help you in reproducing any bugs.
• If some source data is corrupt (e.g. unreadable or incomplete), you should find out if the ETL tool takes any action on such data. If so, your validation tests should confirm these actions. The ETL tool should not simply accept the corrupt data as such.
• If the mappings between the tables/ fields of the source and target databases are changed frequently, you should first test the stable mappings.
•  In order to find out the point of failure quickly, you should create modular validation tests. If your tests are modular, it may be possible for you to execute some of your tests before the data migration process finishes. Running some tests while the data migration process is still running would save you time.
• If the database migration process is manual, you have to run your validation queries externally. However, if the process uses an ETL tool, you have the choice to integrate your validation queries within the ETL tool.