Data Warehousing Course

Lesson 1 • Core DW Architectural Layers

Introduces staging, integration, and presentation layers as the structural backbone of a warehouse. Students 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. Students align technical decisions with business requirements.

Lesson 3 • What Is a Data Warehouse

Defines a data warehouse and its primary business purpose. Grounds students 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. Students understand upstream and downstream dependencies.

Lesson 1 • Inmon vs. Kimball Approaches

Compares the enterprise data warehouse and dimensional modeling philosophies. Students 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. Students apply these patterns to complex enterprise scenarios.

Lesson 3 • Scalability and Performance Architecture

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

Lesson 4 • Layered Zone Design

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

Lesson 1 • Advanced Dimensional Patterns

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

Lesson 2 • Star and Snowflake Schemas

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

Lesson 3 • Slowly Changing Dimensions

Covers SCD types 1 through 6 for tracking historical attribute changes. Students 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. Students 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. Students 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. Students 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. Students diagram a complete pipeline from source to target.

Lesson 4 • Data Transformation and Cleansing

Addresses data standardization, deduplication, and business rule application during transformation. Students 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. Students 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. Students 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. Students map governance responsibilities to organizational roles.

Lesson 1 • Query Optimization Techniques

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

Lesson 2 • Window Functions in Depth

Teaches ranking, offset, and aggregate window functions for row-level analytical calculations. Students 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. Students 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. Students 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. Students 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. Students select and implement the correct index type for a given query pattern.

Lesson 3 • Materialized Views and Aggregates

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

Lesson 4 • Partitioning and Pruning

Teaches range, list, and hash partitioning to reduce data scanned per query. Students 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. Students 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. Students 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. Students 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. Students map platform capabilities to architectural requirements.