Insurance Company Utilizes QuerySurge to Improve its Data Testing Practices

Background

A leading Insurance company realized it had significant issues with its data testing process. The team investigated the possibility of attending training courses focused on data testing and registered for Data Warehouse and ETL Testing Fundamentals, a data warehouse testing course at RTTS (the parent company of QuerySurge). A QuerySurge demo was provided by the instructor at the end of the course and the students downloaded the 15-day trial a few days later. After completion of the trial, the Insurance company decided QuerySurge was a fit for their data testing initiative and purchased the solution to hopefully reduce their testing cycle, resource hours, and ultimately, their costs.

Challenges

The Insurance firm’s manual data testing process was using Microsoft Excel and Microsoft Access to perform data comparisons between source flat files and xml files to the target IBM DB2 and Microsoft SQL Server data warehouses. The process took extensive time to import query results from a SQL analyzer into MS Excel or MS Access for data validation. Excel and Access were not designed to compare large volumes of data. The insurer had to slice and dice the data due to the limitations of these tools. Federal regulations require them to test 100% of their data, so sampling of data (comparing a limited size) was not an option.

Implementation

The Insurance firm purchased QuerySurge and was able to quickly get up-to-speed on creating data validation tests (Query Pairs).
QuerySurge assisted them with:

• creating automated, reusable tests quickly
• grouping tests in logical test suites to obtain coverage over problematic areas
• executing tests nightly, automatically after ETL processes have completed
• receiving email notifications on completion of tests and pass/fail results
• pinpointing potential data issues that can be solved by the development team
• validating 100% of their data quickly, therefore improving overall data quality

Benefits

A single test that used to take over 1 hour now takes 10 minutes using QuerySurge. MS Excel and MS Access were no longer needed for data comparison. With hundreds of tests to execute and the ability to run them at off-hours, the time savings was dramatic. The team was able to reduce the testing time from 6 months as a manual process to less than 2 weeks of automated testing and analysis while increasing the amount of data validated. After the initial test team’s success, QuerySurge quickly propagated to other teams within the organization. This prompted upper management, who had noticed the improvements and ROI in their data testing process, to purchase additional licenses of QuerySurge to expand its use to additional teams. These teams now utilize QuerySurge as an integral part of their daily data quality process to uncover bad data that, had the data gone invalidated, could have potentially cost the Insurance firm millions of dollars in regulatory fines.