IBM Consulting Delivers Comprehensive Data Validation for a Leading Annuity and Life Insurance Company
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.
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.
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.
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.
Working closely with QuerySurge’s Premium Services support team, the IBM Consulting team developed a customized solution using the DevOps for Data add-on. This solution facilitated the extraction of critical data from QuerySurge’s Staging Tables, which could then be used for end-to-end verification purposes. The extracted back-end data from QuerySurge was effectively utilized to verify the web application’s UI values with a third-party testing tool, ensuring comprehensive validation across the entire platform.
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
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.
|Before Implementing QuerySurge
|After Implementing QuerySurge
|Manual process for file validation to a single resource up to 8 hours to complete
|Automated file validation completing in less than 1 hour
|Frequent corruption issues due to improperly formatted input files
|Corruption issues virtually eliminated
|Inadequate data coverage
|100 % data validation coverage
|Nonintegrated testing process
|Testing process integrated with pipelines in the DevOps workspace
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 IBM Consulting:
IBM Consulting is the catalyst for business transformation. With deep industry expertise spanning strategy, experience design, technology, and operations, we have become the trusted partner to over 3,000 of the world’s most innovative and valuable companies. Our 160,000 consultants embrace an open way of working, bringing a diverse set of voices, experiences and technologies like Hybrid Cloud and AI together to accelerate business transformation. Supported by IBM Garage, our proven co-creation method, we bring speed and scale to innovation with an enduring ecosystem of technology leaders to deliver solutions for some of the world’s most complex challenges. We collaborate closely, ideate freely, and apply breakthrough innovations that drive sustainable change and re-invent how business gets done. Find out more at https://www.ibm.com/consulting
About RTTS & QuerySurge:
RTTS, the developer of QuerySurge, is the premier pure-play QA & Testing organization that specializes in test automation. Headquartered in New York, RTTS has had 1,000+ successful engagements at over 700 corporations since 1996.
QuerySurge is RTTS’ smart data testing solution that automates the data validation and ETL testing of Big Data, Data Warehouses, Business Intelligence Reports and Enterprise Applications with full DevOps/DataOps functionality for continuous testing.