Case Study

Summary

IBM Consulting provided data validation services to a leading annuity and life insurance provider in the US. IBM oversaw data testing for the client’s ETL process, handling input files from various sources and loading them into databases. They also ensured successful validation of a data migration project involving thousands of tables transferred to a cloud-based solution. The challenges included the large volume of data and the limitations of manual validation, which was time-consuming and lacked coverage.

IBM implemented QuerySurge, the leading automated data validation and ETL testing solution, to overcome these challenges. QuerySurge was integrated into the customer’s existing automation platform, leveraging its vast functionality to streamline the testing workflow. The solution resulted in efficient data validation, reduced time and resource requirements, and a higher data verification rate, eliminating instances of invalid data.

Background

IBM Consulting was entrusted with providing data validation services to one of the leading annuity and life insurance providers in the United States. IBM’s expert resources oversaw the data testing efforts for the client’s ETL process, which manages critical insurance policy information for their valued customers. This comprehensive process involves handling input files sourced from various vendors, legacy COBOL system copybooks, and actuarial data files, which were subsequently transformed and loaded into multiple SAP HANA databases.

IBM Consulting’s extensive expertise was also instrumental in ensuring the successful validation of a substantial data migration project, wherein thousands of tables were transferred from the SAP Cloud to an alternative cloud-based solution. The objective of this migration project was to guarantee the preservation of data integrity, eliminating any potential data corruption or loss of essential records.

Challenges

The amount of data to be validated posed an immense challenge to the team’s testing effort. The client’s ETL process ingested over 500 files daily with some files reaching up to 16 GB in size. Relying solely on manual efforts to validate such an extensive amount of data proved insufficient, resulting in incomplete testing coverage and runtime issues caused by improperly formatted files. Although an analytics platform was utilized to develop supplementary tests, it fell short of meeting the project’s requirements in terms of coverage, pricing, and connection capabilities.

Similar data volume challenges were encountered during the cloud migration project, which involved more than 2000 tables spread across four distinct schemas. Manual validation efforts for such migrations are typically time-consuming, spanning weeks to months, and often fall short of achieving the desired level of testing coverage.

Solution

As a Platinum Alliance Partner within the QuerySurge Partner Program, the IBM Consulting team was able to quickly implement an automated testing solution. QuerySurge’s ability to connect to all data sources within their environment enabled the team to adopt a comprehensive coverage approach. Connections were established with a variety of data formats such as fixed width flat files, transformed copybooks from COBOL systems, and SAP HANA databases allowing them to compare data points across the entire ETL process.

The design-time run feature proved to be beneficial for testers, as it allowed them to develop and finetune their tests using a small subset of data. After verifying their QueryPairs with design-time runs, they were incorporated into test suites where testing of the complete, often substantial, datasets was executed. The team also relied on QuerySurge’s Projects feature, which enabled them to effectively categorize and manage different work efforts within the testing process.

To ensure the integrity of the cloud data migration project, QueryPairs were developed to validate over 2,000 tables across four schemas. The Scenario Results view offered a streamlined and efficient means of identifying problematic tables, simplifying the pinpointing of issues within the migration process. As a result, the team was able to confidently assert that the data moved to the new cloud location achieved a 100% validity rate, ensuring a successful and error-free migration.

To further streamline their testing workflow, IBM experts integrated QuerySurge into their existing automation platform, Worksoft, utilizing the QuerySurge API to trigger test executions. Additionally, they were able to integrate QuerySurge validations into their pipeline workflow using QuerySurge’s Azure DevOps extension and achieve a more efficient and automated testing process.

Average table size validation:

• 350 MB

Largest table size validated:

• 16 GB

Total tests created:

• 9000+ QueryPairs

QuerySurge server specifications:

• Processor Cores: 64
• RAM: 64 GB
• Hard Drive: 1 TB

Benefits

QuerySurge efficiently automated the data validation process, resulting in a significant reduction in time and resource requirements for the project. Previously, the task of file validation would consume a resource for an entire day. With the implementation of QuerySurge, the validation process can now be completed in under an hour. QuerySurge was also able to extend the validation coverage to 100% of the ingested data. As a result, instances of invalid data infiltrating the SAP HANA DB from the input files were nearly eliminated.

The migration project also experienced remarkable benefits thanks to the adoption of QuerySurge. The tool facilitated the comprehensive verification of all tables involved in the migration process, ensuring the integrity of the data transfer. QuerySurge’s Run Dashboard offered organized and readily reviewable results enabling thorough analysis.

IBM Consulting’s expertise paired with QuerySurge is a formula for success, as demonstrated by the remarkable benefits achieved in both the ETL and migration projects. The integration of QuerySurge into the testing process played a pivotal role in ensuring the team’s ability to attain the desired coverage level. Without this integration, the team would have encountered significant challenges and limitations, ultimately jeopardizing the achievement of 100% data validation, a crucial outcome for the projects’ success.

About RTTS & QuerySurge: