Data Warehousing and Dimensional Modeling

A complete data warehousing and dimensional modeling course covering analytical requirements, grain, facts and dimensions, star schemas, conformed dimensions and bus architecture, slowly changing dimensions, bridge/snapshot patterns, surrogate keys, source profiling, ETL/ELT, CDC, orchestration, physical/columnar design, semantic layers, marts, governance, testing, observability, performance, cloud/lakehouse architecture, migration, and production delivery.

Browse curriculum Back to all courses

30chapters

150lesson paths

Intermediate → Advancedlearning level

Publishedcourse state

Coverage baselineVendor-neutral dimensional modeling and modern warehouse engineering principles spanning classic Kimball-style patterns, ELT/cloud warehouses, semantic metrics, governance, testing, and lakehouse coexistence

Course brief

Turn operational data into trustworthy analytical models by controlling grain, history, semantics, data quality, lineage, loading behavior, and physical performance.

This syllabus deliberately separates foundations, modeling, querying, internals, reliability, security, performance, operations, and production design so advanced material is not compressed into generic catch-all chapters.

By the end

You will be able to

Translate business processes and analytical questions into explicit grain, facts, dimensions, conformed models, and semantic metrics
Model history correctly with SCDs, snapshots, bridges, late-arriving data, surrogate keys, and temporal policies
Design reliable ETL/ELT and CDC pipelines with idempotency, data quality, reconciliation, orchestration, and observability
Choose physical warehouse patterns—partitioning, clustering, columnar storage, materialization, marts, and workload isolation—from measured workloads
Govern and operate analytical platforms with lineage, security, testing, cost/performance controls, DR, migration plans, and production SLAs

Complete syllabus

30 chapters · 150 lessons.

Every lesson path was reserved in the planned syllabus and is now linked because its lesson HTML is available. The sequence moves from foundations through advanced implementation, architecture, operations, reliability, security, tuning, and a production capstone.

Chapter 1

Data Warehouse Foundations: OLTP vs OLAP, Analytical Workloads, Architecture, and Lab Dataset

5 lessons

Operational vs Analytical Systems: Transaction Shape, History, Concurrency, Query Complexity, and Data FreshnessPublished lesson · Chapter01/Lesson1.html

Data Warehousing and Dimensional Modeling

Turn operational data into trustworthy analytical models by controlling grain, history, semantics, data quality, lineage, loading behavior, and physical performance.

You will be able to

30 chapters · 150 lessons.

Data Warehouse Foundations: OLTP vs OLAP, Analytical Workloads, Architecture, and Lab Dataset

Requirements Engineering: Business Processes, Questions, Metrics, Dimensions, and Grain

Dimensional Modeling Foundations: Facts, Dimensions, Star Schemas, and Query Semantics

Fact Table Design: Additive, Semi-Additive, Non-Additive Measures, and Factless Facts

Dimension Design: Descriptive Context, Hierarchies, Attributes, and Analytical Usability

Conformed Dimensions, Enterprise Bus Architecture, and Cross-Process Analytics

Slowly Changing Dimensions: Types 0–7, History, and Effective Dating

Special Dimension Patterns: Date/Time, Role-Playing, Junk, Degenerate, Mini, and Inferred Members

Bridge Tables and Many-to-Many Analytical Relationships

Transaction, Periodic Snapshot, and Accumulating Snapshot Fact Tables

Surrogate Keys, Late-Arriving Facts/Dimensions, Deletes, Restatements, and History Corrections

Source System Profiling, Data Contracts, Lineage, and Ingestion Readiness

Data Quality Engineering: Validation, Standardization, Matching, Reconciliation, and Quarantine

ETL vs ELT Architecture: Staging, Raw, Integration, Presentation, and Transform Ownership

Incremental Loading, CDC, Watermarks, High-Water Marks, and Idempotency

Orchestration, Dependencies, Scheduling, Backfills, SLAs, and Failure Recovery

Physical Warehouse Design: Schemas, Tables, Constraints, Partitioning, Clustering, and Distribution

Columnar Storage, Compression, Encoding, Vectorized Execution, and Analytical Scan Economics

Materialized Views, Aggregate Tables, Cubes, and Precomputation

Semantic Layers, Metrics, Dimensions, Measures, and BI Contracts

Data Marts, Domain Data Products, Self-Service Analytics, and Ownership

Security, Privacy, Row/Column Policies, Masking, and Least-Privilege Analytics

Metadata, Data Catalogs, Lineage, Documentation, Ownership, and Governance

Testing Analytical Pipelines: Unit, Schema, Quality, Reconciliation, and Regression Tests

Observability and Data Reliability: Freshness, Volume, Schema, Lineage, and Incident Response

Performance Engineering and Workload Management: Scan Reduction, Joins, Aggregations, and Concurrency

Cloud Warehouses and Serverless Analytics: Separation of Storage/Compute, Elasticity, and Cost

Lakehouse Coexistence: Open Table Formats, Medallion Layers, Warehouse Serving, and Federation

Migration and Modernization: Legacy EDW, Mart Consolidation, Cloud Moves, and Semantic Preservation

Production Capstone: Deliver a Governed, Testable, Performant Analytical Warehouse