eScholar Selects QuerySurge to Automate their Data Testing Efforts
eScholar connects data, solves problems, and empowers decision making to support all learners and stakeholders. Educational data processed by eScholar must adhere to a particular format to ensure accuracy and privacy conditions are met. Given the amount of data processed, existing manual testing efforts were impacting delivery times and an automated solution was required to improve efficiency. After researching several products, eScholar chose QuerySurge because of its ease of use and structured design.
eScholar processes data for EDFacts; a U.S. Department of Education (ED) initiative to collect, analyze, and promote the use of high-quality, pre-kindergarten through grade 12 data. This information is collected through text and Excel files after extraction from a variety of sources. These files are then transformed and loaded into a data mart for analysis, where they will ultimately be exported into a format established by EDFacts business rules and guidance.
The eScholar QA team was performing their data validations manually, as many organizations currently do. Complex transformations based on look-up tables split individual source data sets into hundreds or sometimes thousands of target records. Verifying all the transformation logic on large data sets manually proved time consuming and error prone. To meet delivery deadlines, the team utilized the sampling method, which could only verify a subset of their total data set. This method provides a minimum amount of testing coverage and led to incidents of bad data late in the testing cycle.
QuerySurge provided an automated solution that verified business-critical processes by confirming datasets went through all transformation logic accurately. Utilizing QuerySurge’s flat file and Excel drivers allowed the QA team to write SQL queries against their source files, which could then be compared to corresponding tables in the database. QueryPairs that were focused on specific functions in the transformation process pinpointed issues and helped to reduce defect triage time.
QuerySurge’s data staging feature was used to test all permutations generated by the various look-up table scenarios. Creating data staging tables directly in QuerySurge allowed for joining of disparate data sources, such as Excel and flat files, with the look-up table rules located in the database. This helped the QA team to quickly identify missing data and logic issues within the source data files. With over 1,000 QueryPairs (automated tests) and 1,000 staging queries, the team continues to build out new tests each cycle and incorporates existing QueryPairs to aid in regression efforts.
QuerySurge helped eScholar reduce their testing cycle time while also expanding their data coverage. What used to require two weeks’ worth of effort, now takes the team three days to verify. The ability to organize their tests within Test Suites has facilitated the scheduling of test runs in an automated fashion. The QA team is now able to verify the end-to-end ETL process of loading data into their data warehouse from a variety of source files while also validating the EDFacts output files. With QuerySurge, bad data is being captured at an earlier stage in the testing cycle resulting in a significantly improved data validation effort.
After the deployment of QuerySurge as the key Automation tool for all our ETL processes, we noticed that the return on investment was quite apparent in the efficient utilization of resources and higher quality of data validation and report analysis, leading to an overall improved deployment process.
QuerySurge is the 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.