Automate Data Migration Testing with iceDQ
10 min read
Test your data migration to big data or to cloud Redshift, Vertica, Snowflake, Azure and more…
Data Migration is the process of moving the data from one system to another. Companies do data migration because they are combining applications or want to retire a legacy database.
Currently, most of the companies are starting data migration projects to move their on-prem databases and applications to cloud databases and applications. This migration is helping them save costs and transform their ecosystem in the long term.
Database migration and Application migration are two different types of data migrations.
Database Migration
Moving the data from one database to another is called Database Migration.
The majority of Database Migrations are so to speak “Lift and Shift” migrations. Meaning their data structure is the same; data is the same only thing that might change is datatype. An excellent example of this is Netezza to Snowflake migration.
Application Migration
The process of replacing one business application with another is called Application Migration.
In application migration, the underlying data is the same, but the data structure between both the systems can be completely different. One example of this is migrating from an on-prem CRM system to Salesforce.
An organization can certify that the data migration is successful only if the data between the source and target are matching. Therefore an organization must test its data migration process continuously.
What is Data Migration Testing?
The process of checking the structure and the data for issues between the source and target data source is called Data Migration Testing.
Data Migration Testing Challenges?
|
Testing data across
|
Data Migration involves moving from one system to another, so Testing data across different systems is very time-consuming, manual, and error-prone. Because the users will have to either eyeball the data or bring it in excel to compare. |
|
Testing millions and
|
Organizations have to migrate years’ worth of data to a new system. And it is critical to validate all of the data to certify that Data Migration is a success. But manually testing millions and billions of rows to certify the data migration is way too challenging. |
|
Inability to accelerate
|
In any Data Migration 1000’s of tables are moved into the new system. The Inability to accelerate Test creation and execution for 1000’s of tables in a short period increase the project timeline. |
|
Delay in Identifying
|
Lack of test automation causes a delay in identifying data issues early on in the data migration process lifecycle. Therefore, the cost of fixing the data issues late in the cycle becomes high. |
Types of Data Migration Testing
Below are distinct types of checks done for Data Migration Testing.
Schema Compare Tests
Make sure that the data model or schema structure is matching between the source and target system. Users can easily query the metadata tables to pull the information for validating.
- Check if the table and column name is the same between source and target.
- Datatype mapping between source and destination should be correct. Example source column with INT datatype should be NUMERIC in the target system.
- Verify the views, primary keys, and indexes are also matching.
Row Count Tests
The most basic type of check is to make sure the count is for a table between source and target is matching.
- One time Row Count checks for the initial loads of all the tables.
- Row Count checks for delta loads of all or specific tables.
Data Comparison Tests
Compare the data in all of the tables, rows by row and column by column. This will certify that data migration was a success.
- Check the first name column in the source, and the target is the same.
- Ensure the date value is matching even though the format is different between the source and the target.
Data Aggregation Tests
Organizations can perform aggregated checks for really high volume tables between source and target. This is necessary as row by row comparison for billions of rows in a table can be costly.
- Verify that summation for all the numeric columns in a table is matching between source and target.
What is a must in a Data Migration Testing tool?
Data Validation Engine
A comparison and validation engine is the core of any Data Migration Testing tool. As the data sits in different data sources, where one can be on-prem, and others can be in the cloud, the testing of data becomes streamlined. Not only that, but the engine should be effective and efficient enough to compare any volume of data for any data source and format.
Test Creation in bulk
Organizations give very short timelines to the teams to successfully implement a Data Migration project. And giving users the ability to accelerate the testing of these Data Migration processes helps them shorten that timeline easily. Therefore a feature to create tests for 1000’s of tables with a click of a button addresses this pain point.
Scheduling & Integration
“How do I automate the test?” is always a question users ask. So a tool needs to give the ability to schedule the runs for the tests. But also allow organizations to integrate the test execution as part of the CICD pipeline through plugins or Rest API’s.
Reporting
A Data Migration Testing tool should give all of its users the facility to generate any report either out of the box or through external reporting tools. Managers will be able to see how much automation is done, and engineers can look at the actual data issues by runs.
Accelerate Data Migration Testing with iceDQ
- Use our in-memory rules engine to compare data across any data source be it on-premises or cloud.
- With the help of our bulk test creation add-on quickly create Rules for 1000’s of tables in iceDQ.
- Integrate with any external scheduling tool or orchestration tool to automate the execution as part of your data pipeline.
