How to Develop a Master Test Plan for Your Data Warehouse Project

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 wyaddow@gmail.com.

Although Agile testing tends to deprioritize test planning, teams working on data warehouse projects would be remiss to overlook the long-standing motives and rationale for a project-wide data warehouse master test plan.

A data warehouse “Master Test Plan” represents the plan of action and processes designed to accomplish quality assurance from the beginning to the end of a data warehouse development lifecycle. It describes all planned testing for each SDLC phase and how QA will be managed across all levels of testing (ex., unit, component, integration, system testing, etc.). The MTP provides a high-level view of the quality assurance policies and strategies to be implemented (based on IEEE Standard 829).

Such a plan is often developed using the data warehouse project’s “Data Warehouse Project Vision” document, business and technical requirements, data dictionaries, data models for source and target schemas, data mappings, and ETL and BI/analytics application specifications. It’s essential for purging the data warehouse of the most serious and disruptive bugs as effectively and efficiently as possible. And the sooner data quality/testing objectives are defined, the better your chances of exposing issues early—when they’re easier, faster, and least costly to fix.

What’s a Master Test Plan?

A data warehouse Master Test Plan should describe the testing strategy/approach for the entire data warehouse and BI software development cycle. The MTP should help the project team plan and carry out all test activities, evaluate the
quality of test activities, and manage those test activities to successful completion.

The MTP should be published and distributed for approval by business and technical stakeholders to inform everyone about key areas of the planned testing process:

• Testing and quality objectives
• Scope and constraints of planned testing
• Test environments
• Test data sources
• Testing methods
• QA tools, processes, and schedules
• Testing resources required for the project

The MTP should also summarize the test team’s objectives for:

• Work products
• Testing procedures
• Testing assumptions
• Project risks
• QA entry and exit criteria
• Testing roles and responsibilities (including those of business analysts, developers, users, etc.)
• Defect tracking and reporting processes
• Change control processes

Categories of data warehouse testing

The types of quality assurance verifications conducted during unit, integration, system, and acceptance tests (described in the data warehouse MTP) should be the following:

• Business requirement verifications
• ETL testing
• Data warehouse testing (record counts, data quality, performance, etc.)
• System integration testing
• Functional and nonfunctional business requirements
• Technical requirements testing

Another focus of the data warehouse MTP is the end-to-end data warehouse test process, including validating:

• The loading of all required data warehouse tables
• Correct execution of all data transformations according to business rules and reporting requirements
• Successful completion of data cleansing operations

Be sure to identify which categories of testing are targets for automation.

Skills and experience needed for data warehouse testing

The MTP should demonstrate that data warehouse testing is a unique endeavor that requires specific knowledge and skills:

• A firm knowledge of data warehouse structures, analytics and database concepts
• Advanced expertise with database queries
• Expert data profiling methods & tool skills
• Experience with MS Excel data analysis functions
• Skills to develop data warehouse /BI test plans

Testing challenges anticipated for the data warehouse project

The MTP should also describe known testing challenges, and an approach to address each challenge. This will serve as an important aid in test planning. Common challenges include:

• Lack of detail in requirements documents
• Frequently-changing business and technical requirements
• Heterogeneous, complex, and massive data movement
• Missing and duplicate data to be identified, corrected, and tested
• Data that must be transformed and cleansed (often resulting in complex testing)
• Lack of access to commercial or open-source data warehouse testing tools

An MTP should document major priorities for reducing business and technical risks associated with the development,
deployment, and operation of the data warehouse. Using appropriate test cases and allocating appropriate testing resources will significantly reduce risks and make testing more effective and efficient. The MTP will likely enable the project teams to develop a superior product and lay a strong foundation for each iteration.

Conclusion

An effective data warehouse Master Test Plan is the cornerstone of the entire data warehouse verification effort. The MTP guides test engineers as they develop tests for unit, ETL, integration, performance, security, end-to-end, and BI testing. A good MTP will help stakeholders understand what will be tested (and how), as well as assure them that quality was carefully considered. Of course, each project has different needs and thus each data warehouse project may require a different MTP template.