I had the opportunity to work on my very first data migration project a few days ago. The objective of our project was to ensure the correctness and completeness of data migrated from a source system with Oracle as its database to a target system with MS SQL as its database. I learned a lot while working on this project. I got an opportunity to hone my SQL query writing skills and got well versed with the major checkpoints and activities performed during database migration testing. I wanted to share my learning’s and experience in this post.
23 million data sets had to be migrated from Oracle to SQL. In order to do this, we divided the activities into two phases:
Our job was to test and verify the correctness and completeness of around 23 million data sets at the end of each activity. The real challenge was to perform this job manually without automation. What made it even more interesting was that there’s a chance of up to 5% data loss during data migration.
To achieve our goal, we divided our tasks into 4 major Phases:
The experience I gained while working on this project can be divided into 3 parts:
- Basic skills required for database migration testing
- Major checkpoints for database migration testing
- Major activities performed during database testing
Basic skills required for database migration testing
Other than testing skills required for all types of testing, one needs following skills for data migration testing
- SQL query writing for legacy and target database
- Knowledge of the data migration testing tool (if any) being used or advance knowledge of Excel features for data comparison
Major checkpoints for database migration testing, data profile matching
- Data profiling
- Table count
- Data type matching of columns
- Identifying different classes of records from business point of view.
- Performing Check Sum on column holding numerical data
- Row count matching of legacy and target database
- Column count matching of legacy and target database
- Matching of total Null values per column of legacy and target database
- Matching of Not Null count per column
- Checking the Distinct count per column
- Checking the Group By count per column
- Matching of Summation of numeric fields
- Checking the Min value per numeric column
- Checking of Max value per numeric column
- Matching of Average data value in numeric columns
- Checking the biggest values in non-numeric columns
- Checking the shortest values in non-numeric columns
2. Checking data redundancy
3. Matching control tables to verify the exact transfer of relationships among tables
4. Functional testing on migrated data
5. Random sampling- Picking and matching data from corresponding tables randomly.
Major activities performed during database testing
- Identification and matching of the number of tables in legacy and target databases.
- Identification and matching of the data types of columns in corresponding tables.
- Identification and matching of the relationship between tables in legacy database and target database.
- Identification and matching of business rules in legacy database.
- Identification of primary key attribute for each table
- Identification of columns with Not Null property
- Identification of numeric fields for data profiling
- Query writing for both databases to identify all data profiling checkpoints:
- Row count
- Column count
- Null values count per column
- Not Null count per column
- Distinct values count per column
- Group By count per column
- Summation of numeric fields
- Min value per numeric column
- Max value per numeric column
- Average value of numeric fields
- Biggest values in non-numeric columns
- Shortest values in non-numeric columns
- Matching the data profiling results of both databases
- Query writing for random sampling to ensure that data has been migrated correctly and completely
- Using migrated data with the related application to test its functional use
Migrating data is a challenging activity. Users expect it to be fast and efficient, without loss of data. Thorough data migration testing has to be performed to reduce risk and ensure that data has been migrated as per requirements. I believe that using the points outlined above, you can refine your test approach and ensure a better data migration testing process.
Mayank Raj | Trainee Test Analyst | Zen Test Labs