Get in Touch
 Duration 21 hours

Course Outline

1  Pipeline and warehouse concepts

  • Sources, staging, transformations and targets; ETL and ELT workflow differences.
  • Transactional and analytical systems; where tests belong in a data pipeline. 

2  Requirements and test design

  • Read source-to-target mappings and identify business rules and acceptance criteria.
  • Prepare positive, negative and boundary scenarios, test data and a test matrix. 

3  SQL reconciliation

  • Compare row counts and control totals at the correct business grain.
  • Use joins, aggregates and set comparisons to locate missing, extra or mismatched records. 

4  Transformation and data quality checks

  • Verify calculations, lookups, filtering, formats and conversions against expected results.
  • Detect nulls, duplicates, invalid codes and broken relationships; check rejected records. 

5  Full and incremental load testing

  • Check initial loads and subsequent inserts, updates and agreed deletion behaviour.
  • Design rerun scenarios to identify accidental duplication or missed records. 

6  Warehouse history and reporting checks

  • Validate fact and dimension relationships and reconcile selected reporting totals. 
  • Check overwrite and history-preserving dimension changes using supplied expected results. 

7  Repeatable tests and defect investigation

  • Create reusable SQL assertions and retain failed rows as evidence; introduce automated data tests. 
  • Review load logs and timing; distinguish a data defect from a mapping or execution problem.

8  Practical assessment and reporting

  • Test a sample sales pipeline against a supplied mapping and acceptance criteria.
  • Submit the reconciliation queries, failed examples, defect descriptions and regression results.

Lab setup

Use one agreed database platform with prepared source, staging and target tables, sample files and a runnable sample pipeline. SQL-based checks form the core lab. A client-specific ETL interface or dbt demonstration can be added when the agreed environment supports it; access and any licence or cloud cost must be confirmed in advance.

Requirements

Participants should be able to filter, join and aggregate relational data with SQL. Familiarity with test cases and expected results is helpful. Add SQL foundation training first if participants cannot yet perform these queries.

Testimonials (2)

Related Categories