Case Study

Background

A leading insurance company recognized significant issues within its data testing process. To enhance this process and prevent instances of bad data, they engaged RTTS to gain insights into best practices for successful data warehouse and ETL testing. With RTTS’ guidance, they implemented an automated data validation process, significantly reducing their testing cycle, resource hours, and overall costs.

Challenges

The insurance firm’s manual data testing process involved using Microsoft Excel and Microsoft Access to perform data comparisons between source flat files and XML files and the target IBM DB2 and Microsoft SQL Server data warehouses. The process required 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 mandated 100% data testing, making sampling (comparing a limited dataset) an unviable option. 

Strategy

After reviewing the manual data testing process, RTTS suggested automating the process. RTTS trained the insurance team on automated ETL testing tool QuerySurge, and together, they devised and implemented a plan to convert manual data validation tests into automated ones. 

Solution

The insurance firm collaborated with RTTS test engineers to implement QuerySurge, quickly gaining proficiency in creating data validation tests. 

Benefits

A single test that previously took over an hour now takes just 10 minutes using the automated data validation tool. MS Excel and MS Access were no longer needed for data comparison. With hundreds of tests to execute and the ability to run them during off-hours, the time savings were dramatic. The team reduced the testing time from 6 months as a manual process to less than 2 weeks of automated testing and analysis while increasing the volume of data validated. Following the initial test team’s success, the data validation tool was quickly adopted by other teams within the organization. This success prompted upper management, who recognized the improvements and ROI in their data testing process, to expand the use of this automated approach company-wide. These teams now rely on the automated ETL testing tool as an integral part of their daily data quality process, uncovering bad data that, if left unchecked, could have potentially cost the insurance firm millions of dollars in regulatory fines.