Considerations to advance compliance maturity through a digital validation shift
Kick-start software quality and compliance across the enterprise, and learn the most important elements to include on your roadmap for CSV modernization.
Editor’s note: Wayne Yaddow is an independent consultant with over 20 years’ experience leading data migration/integration/ETL testing projects at organizations including J.P. Morgan Chase, Credit Suisse, Standard and Poor’s, AIG, Oppenheimer Funds, IBM, and Achieve3000. Additionally, Wayne has taught IIST (International Institute of Software Testing) courses on data warehouse, ETL, and data integration testing. He continues to lead numerous ETL testing and coaching projects on a consulting basis. You can contact him at firstname.lastname@example.org.
Each data warehouse contains large amounts of data that the business uses for strategic decision making. Source data is gathered from heterogeneous internal and external data sets, then populated and queried to provide for business intelligence reporting.
To ensure the integrity of this data and the decisions that rely upon it, data warehouse testing should be first planned project-wide then carefully implemented. Such testing is critical for removing the inconsistencies that occur due to data being collected from widely different sources and formats. If left uncorrected, these inconsistencies are likely to affect the resilience of the many ETL processes.
DWH/BI testing typically falls into the following categories:
In this writing, I focus on only “component and integration testing” from the above categories. Component and integration testing typically verifies the compatibility of DWH modules in end-to-end ETL streams.
Component and integration testing is often accomplished in incremental order consisting of multiple stages after integration of every component to ensure that they work together. The process of incremental integration testing of components continues until the full functional system is obtained as required.
Integration tests often involve running end-to-end ETL routines that may invoke dozens of components. To ensure that all component processing completed as expected, the developers and QA team will want to determine whether the correct number of data mapping ETL’s executed and whether key business rules were properly applied. In other words, they will want the integration test to repeat many of the unit and individual component tests.
DWH component and integration testing can include the following types of tests:
The challenges of ETL testing are substantially different from conventional software testing. For example, top challenges include:
A dedicated QA team with extensive ETL test process and tool knowledge can help your team address these challenges as they plan and implement the above types of testing. As the graphic below shows, the QA team would be developing the test strategy, categories of important tests, planning and implementing test automation tools and process, test scenarios, test cases, and more. Doing so, business analysts, data analysts, developers, etc., will be freed to carry out their assigned critical tasks.
Improving the quality of reported data and meeting the business intelligence requirements for your project is essential for building trust in the tested data warehouse and business intelligence reports created from it.