Success Story

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.

Background

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.

Solution

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.

Benefits

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.