Data Warehousing Course

Lesson 1 • Core DW Architectural Layers

Introduces staging, integration, and presentation layers as the structural backbone of a warehouse. Learners map data flow across all three layers.

Lesson 2 • Key Stakeholders and Use Cases

Identifies business and technical stakeholders who drive and consume DW solutions. Learners align technical decisions with business requirements.

Lesson 3 • What Is a Data Warehouse

Defines a data warehouse and its primary business purpose. Grounds learners in the vocabulary needed for all subsequent chapters.

Lesson 4 • Business Intelligence Ecosystem

Positions the data warehouse within the broader BI stack including reporting and analytics tools. Learners understand upstream and downstream dependencies.

Lesson 1 • Inmon vs. Kimball Approaches

Compares the enterprise data warehouse and dimensional modelling philosophies. Learners evaluate each approach against project constraints.

Lesson 2 • Hub-and-Spoke and Data Vault

Introduces federated and Data Vault 2.0 architectures for scalable, auditable warehouses. Learners apply these patterns to complex enterprise scenarios.

Lesson 3 • Scalability and Performance Architecture

Addresses partitioning, clustering, and distribution strategies that sustain performance at scale. Learners apply these techniques to architecture blueprints.

Lesson 4 • Layered Zone Design

Details raw, cleansed, and curated zone patterns used in modern DW implementations. Learners design a multi-zone data flow diagram.

Lesson 1 • Advanced Dimensional Patterns

Introduces degenerate dimensions, junk dimensions, and role-playing dimensions. Learners apply these patterns to resolve complex modelling challenges.

Lesson 2 • Star and Snowflake Schemas

Contrasts star and snowflake schema designs and their query performance implications. Learners choose the appropriate schema for given analytical needs.

Lesson 3 • Slowly Changing Dimensions

Covers SCD types 1 through 6 for tracking historical attribute changes. Learners implement the correct SCD type based on business history requirements.

Lesson 4 • Facts, Dimensions, and Measures

Defines the building blocks of dimensional models and their relationships. Learners identify facts and dimensions from business requirements.

Lesson 1 • Loading Strategies and Patterns

Compares insert-only, upsert, and truncate-reload loading patterns and their use cases. Learners implement the correct load pattern for a given SCD type.

Lesson 2 • ETL Error Handling and Recovery

Teaches techniques for detecting, logging, and recovering from pipeline failures. Learners build error-handling logic into an ETL workflow.

Lesson 3 • ETL Pipeline Architecture

Defines the stages of an ETL pipeline and their responsibilities within the DW load cycle. Learners diagram a complete pipeline from source to target.

Lesson 4 • Data Transformation and Cleansing

Addresses data standardisation, deduplication, and business rule application during transformation. Learners write transformation logic for common data quality issues.

Lesson 1 • Metadata Management and Data Catalog

Covers business and technical metadata, lineage tracking, and catalog tooling. Learners populate a data catalog with lineage and glossary entries.

Lesson 2 • Data Quality Rules and Validation

Teaches how to encode business rules as automated validation checks within the pipeline. Students implement rule-based validation gates in an ETL workflow.

Lesson 3 • Profiling and Assessment Techniques

Covers column profiling, pattern analysis, and referential integrity checks. Learners produce a data quality assessment report for a sample dataset.

Lesson 4 • Data Governance Frameworks

Introduces governance roles, policies, and stewardship models that sustain data quality over time. Learners map governance responsibilities to organisational roles.

Lesson 1 • Query Optimisation Techniques

Covers execution plan analysis, index usage, and join optimisation for large analytical tables. Learners rewrite slow queries using optimisation best practices.

Lesson 2 • Window Functions in Depth

Teaches ranking, offset, and aggregate window functions for row-level analytical calculations. Learners apply window functions to solve running totals and ranking problems.

Lesson 3 • Stored Procedures and Scripted Loads

Teaches encapsulating transformation logic in stored procedures for repeatable DW loads. Learners build a stored procedure that implements a full SCD Type 2 load.

Lesson 4 • Analytical Query Patterns

Covers aggregation, grouping sets, and rollup operations for multi-dimensional analysis. Learners write queries that replicate common BI report structures.

Lesson 1 • Workload Management and Concurrency

Covers resource queues, priority classes, and concurrency scaling to manage mixed workloads. Learners configure workload management rules for a multi-user DW.

Lesson 2 • Indexing Strategies for DW

Covers bitmap, B-tree, and columnar indexing approaches suited to analytical workloads. Learners select and implement the correct index type for a given query pattern.

Lesson 3 • Materialised Views and Aggregates

Explains how pre-computed aggregates and materialised views accelerate repeated analytical queries. Learners create and refresh materialised views for a reporting layer.

Lesson 4 • Partitioning and Pruning

Teaches range, list, and hash partitioning to reduce data scanned per query. Learners design a partitioning scheme that eliminates full table scans.

Lesson 1 • Data Lakehouse Architecture

Introduces the lakehouse pattern combining data lake storage with warehouse query semantics. Learners evaluate when a lakehouse replaces or complements a traditional DW.

Lesson 2 • CI/CD for Data Warehouse Pipelines

Covers version control, automated testing, and deployment pipelines for DW code. Learners implement a CI/CD workflow for a dimensional model and its ETL.

Lesson 3 • ELT vs. ETL in the Cloud

Contrasts traditional ETL with cloud-native ELT patterns where transformation occurs inside the warehouse. Learners redesign an ETL pipeline as an ELT workflow.

Lesson 4 • Cloud DW Platform Capabilities

Surveys elastic compute, separation of storage and compute, and serverless query features. Learners map platform capabilities to architectural requirements.