Course Outline
1 Pipeline and Warehouse Fundamentals
- Understanding sources, staging, transformations, and targets; exploring the differences between ETL and ELT workflows.
- Distinguishing between transactional and analytical systems; identifying the appropriate placement for tests within a data pipeline. [E1, T1]
2 Requirements and Test Strategy
- Interpreting source-to-target mappings to identify business rules and acceptance criteria.
- Developing positive, negative, and boundary scenarios, along with test data and a comprehensive test matrix. [E1, E2]
3 SQL Reconciliation Techniques
- Comparing row counts and control totals at the appropriate business grain.
- Utilizing joins, aggregates, and set comparisons to pinpoint missing, extra, or mismatched records. [E1]
4 Transformation and Data Quality Verification
- Validating calculations, lookups, filtering, formats, and conversions against expected results.
- Detecting nulls, duplicates, invalid codes, and broken relationships; reviewing rejected records. [E2, T3]
5 Testing Full and Incremental Loads
- Verifying initial loads as well as subsequent inserts, updates, and agreed-upon deletion behaviors.
- Designing rerun scenarios to detect accidental duplication or missed records. [E2, T1]
6 Warehouse History and Reporting Validation
- Confirming fact and dimension relationships and reconciling selected reporting totals. [E1]
- Reviewing overwrite versus history-preserving dimension changes using provided expected results. [T4]
7 Repeatable Testing and Defect Analysis
- Creating reusable SQL assertions and retaining failed rows as evidence; introducing automated data tests. [T3]
- Analyzing load logs and timing to differentiate between data defects and mapping or execution issues. [T1]
8 Practical Assessment and Reporting
- Testing a sample sales pipeline based on a provided mapping and acceptance criteria.
- Submitting reconciliation queries, examples of failures, defect descriptions, and regression test results.
Laboratory Environment Setup
The lab utilizes a single agreed-upon database platform equipped with prepared source, staging, and target tables, sample files, and an executable sample pipeline. SQL-based checks form the core of the practical session. A client-specific ETL interface or dbt demonstration may be included if the agreed-upon environment supports it; however, any required access, licensing, or cloud costs must be confirmed in advance.
Requirements
Learners should possess the ability to filter, join, and aggregate relational data using SQL. While a basic understanding of test cases and expected results is beneficial, it is not strictly required. If participants are unable to perform these fundamental queries, it is recommended to undertake SQL foundation training prior to enrolling in this course.
Testimonials (2)
The variety of the information shared and the clarity to explain terms in plain English.
Arisbe Mendoza - Fairtrade International
Course - GDPR Workshop
It's a hands-on session.
