Advanced Data Warehouse Testing using SQL
Course Summary
This one day course of lectures and hands-on training is designed to provide students with advanced techniques necessary for testing data warehouses. The course covers advanced SQL transformations and the challenges these issues cause in testing a data warehouse.
Intended Audience
- Data Quality Teams
- Data Warehouse Analysts
- Automation Engineers
- Quality Assurance Analysts
- Project Managers
- anyone involved with providing software quality for data warehouses
- Course Objectives
- Course Outline
- Prerequisites
At the end of the course, you will be able to:
- understand data warehouse structures and architectures
- implement a successful process for data warehouse testing
- create and execute more sophisticated transformation tests
- utilize regular expressions for data comparisons
- create and utilize subqueries
- work with derived tables and inlined views
- take advantage of advanced techniques for data warehouse testing
Data Warehouse Overview
- Understanding Data Warehouse Architecture
- Understand the challenges of Data Warehouse Testing
- Understanding ETL Mapping Documents
- Overview of Transformation Types
- Data Warehouse Testing Comparison Methods
Calculated Fields Transformation Test
- Aggregate Functions with Group By statement
- Compare Calculated Source fields with grouping to target field.
Derived Fields Transformation Test
- Discuss the differences between calculated and derived fields
- Implement variations of SubQueries (Nested, Scalar, Correlated, Non-Correlated, Inline)
- Compare a target field from a derived field from the source data.
Field Length Limits Transformation Test
- Discuss DATA_LENGTH field from the ALL_TABS_COLUMN table.
- Calculate maximum size of Field Mergers
- Calculate maximum size of Field Splits
- Validate maximum size of source data split into separate fields into the target database
Field Padding Transformation Test
- String Padding Functions
- SQL Regular Expression Functions
- Verify erroneous source data has been padded correctly in target table
XML Transformation Test
- Usage of the Extract function
- Discuss relevance of XPATH
- Database specific casting functions
- Utilizing XMLSequence to form result set from XML content
- Compare source tables to XML content in a target table
Transpose Transformation Test
- Utilization of Self Joins
- Compare transposed source data to a target table
Match and Merge Transformation Test
- Utilization of Unions
- Discuss differences between Union and Union ALL and how they are used for matching
- Compare multiple source records that need to be matched and then merged into a target table.
- Understanding of basic ETL testing processes
- Basic SQL knowledge or have taken Introduction to Data Warehouse Testing using SQL
Students also registered for...
Data Warehouse Testing Immersion
Learn More
Introduction to Data Warehouse Testing using SQL
Learn More