Training on Data Warehouse Testing

Course Description and Objectives

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


Class participants are introduced to each phase of ETL (Extract-Transform-Load) testing and validation. They will learn to develop and execute a testing strategy that 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 using best methods, processes, and tools. The QA team and project managers, along with business and other IT participants, will 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 categories 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 categories of testing you'll want to include in your planning so that you can flexibly define a test plan as needed to grow from a simple data mart effort to enterprise warehousing projects that require multiple releases.

 

Target Audience

The course is appropriate for novice and experienced IT staff. The typical student will be a data warehousing practitioner, database developer, or database tester although it is not uncommon to have purely business individuals or people new to this space on the course.

  •     Project managers
  •     QA managers and test leads
  •     ETL test engineers
  •     Acceptance test analysts
  •     ETL developers
  •     ETL and BI architects
  •     Data and business analysts
  •     Business Intelligence (BI) program and project managers

 

Recommended Prerequisite Knowledge / Experiences

Knowledge of databases and their interaction is a distinct advantage as technical terms are often used.

  1.   Basic data and database technologies (ex., Relational database concepts, XML files, flat files)
  2.   Principals and methodologies of data warehousing
  3.   Experience with Excel data functions (ex., sorting, filtering) **
  4.   SQL programming skills for database queries **
  5.   Experience with DB editors (ex., Toad, SSMS) for query execution **
  6.   Familiarity with principals of data profiling **

Course Duration: Up to eight hours

Training Delivery: Instructor lecture with Q & A 

 

What You Can Expect from This Course

At the end of this course, class participants will:

  •     Identify the testing challenges unique to DWH and data integration in order to develop an effective testing approach.
  •     Comprehend data models and data mapping documents and how to create test cases from them
  •     Understand the role of the data warehouse QA process as a key component of the software development lifecycle whether agile or traditional
  •     Identify methods for effective test planning and test execution
  •     Craft effective test scenarios based on business and user requirements for the data warehouse
  •     Acquire new  testing methods and the circumstances where each is most effective
  •     Generate the ability to develop DWH QA strategies, test plans and test cases; what they are and how to develop them, specifically for DWH and data integration projects
  •     Learn  how to test data warehouse, data migration and data integration systems throughout the development lifecycle from requirements to deployment
  •     Be capable of estimating testing resources as well as conducting a risk assessment specific to DWH testing
  •     Learn typical ETL tester job descriptions with an understanding of the skills the QA team needs

 

Post-Course Follow-up Service

After the course, the instructor will answer any follow-up questions and provide additional related advice, papers, presentations, sample test plans and test scenarios from a large collection resulting from years of research into ETL and BI testing. Attendees are encouraged to take advantage of this valuable support available for free 5 days after the course. 

 

COURSE SYLLABUS

Following is the course syllabus describing session topics, learning objectives and agenda.

The Introduction and Concepts topic provides basic concepts as a common basis for each of the following topics. Other topics basically follow the order of planning, resourcing, and execution of DWH QA ramp-up. 

Introduction and Concepts

Learning objectives and agenda:  At the end of the presentation, learners should be able to understand the terms that will be used throughout the course, the basic flow of data and testing on an ETL QA project, and the ability to comprehend DWH requirements as expressed in data mapping documents.

  1. Overview of data warehousing terminology
  2. Data warehouse architectural patterns
  3. Front-end vs. backend (data) testing
  4. Data models, data mapping overviews

Challenges of DWH Testing

Learning objectives and agenda: As a result of this subject matter, participants will understand the variety of test planning and execution challenges to be addressed before detailed planning and resourcing is implemented. Root causes of several challenges will be described so that they can be appropriately addressed.

  1. Tester and testing challenges  
  2. Business process, IT process, and tool challenges
  3. Importance and process of preparing for challenges

Planning for DWH Tests

Learning objectives and agenda: Based on information in this topic, participants will gain an understanding of how to initiate the test planning process based on goals and a standard master test plan template. Through observations of a DWH planning template and associated checklists, participants will be able to derive from these to create effective plans of their own. Attendees will learn the importance of test plan static reviews among the QA team and others on the project.

  1. Starting points for DWH test planning
  2. Common goals for the testing plan
  3. Contents of the master test plan and testing approaches
  4. Importance of the “DWH Master Test Plan”
  5. Examples of test plans
  6. ETL goals and objectives as checklists for test coverage
  7. Recommendations for DWH QA from the trenches
  8. Examples of defects commonly found during ETL testing
  9. Recommended ETL test scenarios and test cases
  10. Conducting ETL test plan reviews 

  11. Estimating DWH test resources and schedules 
  12. Source to target data evaluations – what to look for
  13. Assessing test plans and readiness

DWH Test Scenarios 

Learning objectives and agenda: Students will be exposed to examples of numerous DWH test defects and the phases of verification in which they may be found. In addition, they will be able to write test cases which are likely to expose those issues. Basic SQL queries and data profiling techniques are presented so that participants can delve more deeply into learning these key skills which are essential for DWH testers.

  1. Formal QA Entry and Exit Criteria for each data build deployment
  2. Examples and walkthroughs of ETL test scenarios
  3. Examples of defects; writing test cases to find them
  4. Functional testing: ex., Security, performance, ETL error logs, regression testing guidelines

Test Data Planning and Management

Learning objectives and agenda: Class participants learn how to address the difficulties often encountered when planning, then selecting, test data. The variety of choices for data selection, and challenges associated with each, are discussed so that testers can make wise choices. 

  1. Challenges for test data planning
  2. Developing a test data management strategy
  3. Options for test data selection

QA Risk Management Assessments

Learning objectives and agenda: Students are presented with overviews of risk assessments for the ETL test planning and test execution phases. With each potential risk, strategies are presented for mitigations based on their probability of occurrence and the forecasted impact. As a conclusion, participants will experience a list of DWH QA risk management best practices.

  1. How to conduct risk and QA assessments of your ETL QA plan and process 
  2. Components of the risk assessment and QA plan assessment

Test Automation using ETL Validator and BI Validator

Learning objectives and agenda: Since most DWH projects are different from one another and require diverse test support tools, class participants are exposed to names and descriptions of tools that are most often needed… no cost tools and commercial tools. Students will learn which ETL tests are most amenable to automated testing and they will learn how to select types of tests to automate and which tools may be most helpful and cost effective.

  1. Key Features of ETL Validator

  2. Examples of ETL test automation using ETL Validator
  3. Key Features of BI Validator
  4. Examples of BI test automation using BI Validator
  5. Considerations for DWH test automation
  6. Examination and assessment of test automation alternatives
  7. Additional Resources on ETL Validator & BI Validator

Tester Skills for DWH Testing

Learning objectives and agenda: Staffing and training the QA team is among the most important steps in the test planning process. Tester skills and learning goals are presented in such a way that job descriptions can be prepared and candidates effectively interviewed. Key DWH QA abilities are outlined so that QA team members are prepared for all facets of testing.

  1. What testers need to know and accomplish
  2. Important tester skills, sample job description
  3. Sample SQL queries for a variety of common validations
  4. Verifying stored procedures used in the ETL process
  5. How to conduct profiling of data sources and targets
  6. Using DB editors, Excel and MS Access for analysis of test query results
  7. Gaining support of project team for QA testing

DWH QA Best Practices

Learning objectives and agenda: A comprehensive list of best practices is discussed so that participants can take with them what has been learned and successfully accomplished by experienced ETL testers.

  1. Examination and discussion of QA best practices learned from the trenches
  2. How to promote and implement DWH QA best practices
  3. Books and websites addressing DWH testing