Micro Free Course — 100% Free Learning
All lessons in this module are free to learn. Sign in with Google to save your progress.
Dimensional Modeling in Practice
Master star schemas, fact grain, conformed dimensions, and SCDs through theory and hands-on portfolio submissions.
Module Content
Star Schema — The Default Analytics Layout
One fact table, many dimensions, one query pattern. The shape every analytics-engineering interview asks about. FIND_VIDEO: search 'star schema explained Kimball' — recommended channel: Kahan Data Solutions / Kimball Group. Aim for 11 min or under.
Quiz: Anatomy of a Star Schema
One fact, surrounded by dimensions. Why this layout wins for analytics, and what's in each piece.
Fact Tables — Grain, Additivity, and Measures
The single most important decision in dimensional modeling: what does one row mean? FIND_VIDEO: search 'fact table grain Kimball' — recommended channel: Kahan Data Solutions / Kimball Group. Aim for 11 min or under.
Quiz: Choosing the Right Grain
Grain is the contract of a fact table. Get it wrong and every query is wrong.
SUBMISSION: Case 1 — Multi-Currency Financial Fact & Grain
Architect financial revenue fact tables supporting multi-currency transactions, daily snapshotted FX rates, and consolidated board reporting in USD.
Dimensions — Conformed, Junk, and Role-Playing
The dimension design patterns Kimball named — and you'll see in every real warehouse. FIND_VIDEO: search 'conformed dimensions Kimball role playing' — recommended channel: Kahan Data Solutions / Kimball Group. Aim for 11 min or under.
Quiz: Dimension Design Patterns
Three named patterns: conformed (shared dimensions), junk (catch-all flags), role-playing (same dimension wearing different hats).
SUBMISSION: Case 2 — Conformed Marketing Attribution Dimension
Reconcile disparate marketing ad platforms and tracking tools into a unified conformed channel dimension and omni-channel attribution fact.
Slowly Changing Dimensions (SCDs)
How dimensions change over time — and the three (well, six) strategies for handling history. FIND_VIDEO: search 'slowly changing dimensions SCD type 2' — recommended channel: Kahan Data Solutions / Kimball Group. Aim for 12 min or under.
Quiz: SCD Type 1, 2, 3 With Real Cases
The decision tree for handling dimension change. Overwrite, version, or carry-both — the choice has consequences.
SUBMISSION: Case 3 — SaaS Subscription Lifecycle & SCDs
Model subscription lifecycle events, monthly recurring revenue (MRR) snapshot marts, and Type 2 subscription state dimensions for a B2B SaaS business.
Snowflake Schema — When to Extend Beyond Star
The star schema's normalized cousin. Rarely the right answer — but you'll meet it. FIND_VIDEO: search 'snowflake schema vs star schema' — recommended channel: Kahan Data Solutions. Aim for 10 min or under.
Quiz: Star vs Snowflake Tradeoffs
When normalizing a dimension makes sense — and the (more common) reasons to leave it denormalized.
SUBMISSION: Case 4 — E-Commerce Order Funnel Star Schema
Design the analytics star schema for a high-volume D2C e-commerce platform. Model line-item facts, Type 2 customer/product SCDs, and junk dimensions.