Star Schemas and Tracking Changes Course

Lesson 1 • Data Warehousing Core Concepts

Introduces the purpose, history, and architecture of data warehouses. Establishes vocabulary used throughout the course.

Lesson 2 • Data Warehouse Architecture Patterns

Surveys Inmon, Kimball, and hybrid architectural approaches. Students select appropriate patterns based on organisational requirements.

Lesson 3 • OLTP vs. OLAP Systems

Contrasts transactional and analytical systems across workload, design, and query patterns. Clarifies why separate analytical stores are needed.

Lesson 1 • Star Schema Validation and Review

Provides a structured checklist for reviewing star schema designs before implementation. Students apply the checklist to sample schemas and identify defects.

Lesson 2 • Designing Fact Tables

Covers grain declaration, measure selection, and additive vs. non-additive facts. Correct grain definition is emphasised as the most critical design decision.

Lesson 3 • Designing Dimension Tables

Teaches attribute selection, surrogate keys, and natural keys for dimensions. Students understand how dimension richness drives analytical flexibility.

Lesson 4 • Anatomy of a Star Schema

Breaks down the central fact table and surrounding dimension tables. Establishes the visual and structural pattern students will build throughout the course.

Lesson 1 • SCD Type 3 and Hybrid Types

Introduces limited history via prior-value columns and blended SCD strategies. Students evaluate when hybrid approaches outperform pure types.

Lesson 2 • SCD Type 2 Full History Tracking

Teaches row versioning with effective dates and current-row flags as the most widely used SCD method. Students implement Type 2 logic on sample dimensions.

Lesson 3 • SCD Type 0 and Type 1

Covers fixed attributes and overwrite strategies as the simplest SCD approaches. Students identify when each type is appropriate and its trade-offs.

Lesson 4 • SCD Strategy Selection Framework

Provides a decision framework mapping business requirements to SCD types. Students apply the framework to case studies and justify their selections.

Lesson 1 • Testing and Validating SCD Loads

Defines test cases for completeness, accuracy, and idempotency of Type 2 ETL jobs. Students build a reusable test suite for dimension load validation.

Lesson 2 • Handling Late-Arriving Data

Addresses source records that arrive after their effective date, requiring retroactive dimension updates. Students apply correction patterns without corrupting history.

Lesson 3 • Change Detection Techniques

Covers hash comparison, checksum, and column-by-column diffing to detect changed rows in source data. Accurate detection is the prerequisite for correct SCD loads.

Lesson 1 • Multi-Valued Dimension Relationships

Solves the many-to-many relationship between facts and dimensions using bridge tables. Students design bridge tables and write correct aggregation queries.

Lesson 2 • Transaction, Snapshot, and Accumulating Facts

Distinguishes the three fundamental fact table types by grain and update behaviour. Students match each type to the correct business process.

Lesson 3 • Accumulating Snapshot Deep Dive

Focuses on pipeline and workflow tracking using accumulating snapshots with milestone dates. Students implement update logic for in-progress and completed rows.

Lesson 1 • Incremental Load Strategies

Covers watermark, CDC, and full-diff approaches for loading only changed data. Students select and implement the appropriate strategy for each source type.

Lesson 2 • Pipeline Scheduling and Dependency Management

Covers job scheduling, dependency graphs, and SLA monitoring for warehouse loads. Students design a dependency-aware schedule for a multi-table schema.

Lesson 3 • ETL Architecture for Dimensional Loads

Maps the extract, transform, and load stages to star schema requirements. Students sequence dimension loads before fact loads to maintain referential integrity.

Lesson 4 • Error Handling and Pipeline Auditing

Establishes error logging, row-count auditing, and restart checkpoints in ETL jobs. Students implement audit tables that support operational monitoring.

Lesson 1 • Aggregation and Materialised Views

Uses pre-aggregated summary tables and materialised views to accelerate common queries. Students identify which aggregations provide the highest performance gain.

Lesson 2 • How Query Engines Process Star Schemas

Explains join ordering, filter pushdown, and star join optimisation in analytical engines. Students read query execution plans to identify bottlenecks.

Lesson 3 • Query Rewriting and Anti-Pattern Avoidance

Identifies common slow-query anti-patterns and rewrites them using dimensional best practices. Students benchmark before-and-after query performance.

Lesson 4 • Partitioning Fact Tables

Applies range, list, and hash partitioning to large fact tables to reduce scan costs. Students design partition schemes aligned with common query filters.

Lesson 1 • Validation, Testing, and Optimisation

Executes the full test suite, resolves defects, and applies query optimisations to the completed schema. Students document test results and performance benchmarks.

Lesson 2 • Schema Design and Documentation

Produces a complete star schema design including all fact and dimension tables with SCD types assigned. Students create a bus matrix and data dictionary.

Lesson 3 • Project Presentation and Peer Review

Students present their schema design and ETL pipeline to peers and receive structured feedback. Presentation skills and design justification are evaluated.

Lesson 4 • ETL Pipeline Implementation

Builds the full ETL pipeline for the designed schema, including SCD Type 2 logic and audit tables. Students run the pipeline against a realistic sample dataset.