Testing the Data Warehouse

This course will provide attendees with an end-to-end understanding of how data warehouse (DWH) testing can be successfully accomplished in a planned and disciplined manner.

We introduce attendees to each phase of ETL (Extract-Transform-Load) testing and validation. Students will learn to develop a testing strategy which leads to effective and complete testing.

In order to assure that the ETL development process, ETL tools for extraction, business rules for data transformation and data loads are correct, it is essential to carefully prepare test plans and test cases. Project managers can gain confidence in their data warehouse and BI development only after efficient and successful testing of the entire ETL process - one that is well planned and executed by a team of test experts who are proficient in ETL testing.

The course demonstrates how to choose the types of testing you'll want to include in your DWH quality assurance plan so that you can flexibly redefine a test plan as needed to grow from a simple data mart effort all the way to enterprise warehousing projects that require multiple releases.

ETL quality assurance is addressed from several perspectives: why test, what to test, tester qualifications, when and how to test. We identify how to choose the types of testing you'll want to include in your test plan so that you can adaptably define a test plan as needed to grow from a simple data mart effort to enterprise warehousing projects that require multiple releases.

Course Objectives

• Upon completion of this course, students will:
  • Know DWH testing processes in support of ETL development and deployment.
  • Acquire analysis techniques to capture data warehouse requirements, including those for source data, data transformations, data quality, and historical data.
  • Understand ETL designs for data loading, including source-to-target mapping, source data capture, data transformation and cleansing.
  • Know DWH test strategies, test plans and test cases - what they are and how to develop them specifically for data warehouses.
  • Create effective DWH test cases and scenarios based on business and user requirements.

Target Audience

• The course is appropriate for both novice and experienced IT staff:
  • ETL and BI architects
  • ETL developers
  • ETL test engineers
  • Program and project / test managers
  • Project / test leads

Participants are expected to have a basic knowledge of modern database technologies. Details of DWH structures and concepts are not specifically taught in this class since there is a large variety of such.

Course Outline

• Concepts and components of data warehouses
• Database, DWH terms (field, records, etc.)
• How DWH testing is different from most testing
• Measures of DWH quality and how to test them
• Recommendations for ETL testing challenges
• Highlights of common test tools
• DWH tester considerations
  • QA staff Interviews and skills
  • Reviewing the resume
  • Questions for interviewees
• Risk analysis for DWH testers
• Components of DWH test estimates
• Developing the DWH test strategy
  • Testing goals and purpose
  • Testing metrics and measures
  • Testing roles
  • Test planning templates
• Reviewing test-related artifacts for completeness (test plans, test cases)
• Best practices for each phase of testing
• Verification of the database objects: Tables, views, keys (integrity constraints), indexes, clusters
• Data profiling sources and targets
  • Importance
  • Tools and techniques
• Understanding and using ETL-related requirements documents
  • Business requirements documents
  • ETL design documents
• Understanding data models and data mapping sheets
• Source and target data
  • Working with relational targets
  • Working with flat file targets
  • Source and target schema validations
• Testing loads of DWH slowly changing dimension
  • SCD types 1, 2 and 3
• Types of DWH testing
  • Requirements testing
  • Data completeness testing
  • Data transformation testing
  • Data quality testing
  • Initial load / full load testing
  • Incremental load testing
  • Presentation layer testing / BI testing / report testing
  • Integration testing / system integration testing
  • Load and performance testing
  • UAT testing / user acceptance testing
  • Regression testing
• DWH test cases
  • Test checklists for missing data, data truncation, type mismatch, null fields, misplaced data, added and dropped records, logic issues, duplicate records, numeric precision, undocumented requirements, transformed data, cleansed data
  • Test case components
  • Sample test cases
• SQL queries for DWH testing
  • Basic select statements
  • Creating sub-queries
  • Using joins
• Test execution - executing the tests
• Common defects found in ETL testing
• Reporting defects found in ETL testing