Advanced SQL Techniques for QuerySurge
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 using QuerySurge. 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 transforation 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
- Understanding of basic ETL testing processes
- Basic SQL knowledge or have taken Introduction to Data Warehouse Testing using SQL
Students also registered for...
QuerySurge Immersion
Learn More
Introduction to SQL for QuerySurge
Learn More