BEGINNER · 8 MODULES · 64 LESSONS
Data Modeling for Analytics
Turn a flat data model into a real analytics warehouse, with dimensions that keep their history and tables your BI team can query without mistakes.
- For you if
- You already have a facts-and-dimensions model and need to turn it into a warehouse for BI.
- By the end
- You'll know how to choose star, snowflake, or one big table, track history with SCDs, and join facts at the right time.
- You need
- Intermediate SQL, basic Python, and the data engineering foundations guide or equivalent. Everything runs locally.

Curriculum
01From Flat Tables To Dimensional Models
- Module introduction: from flat tables to dimensional models
- What foundations left flat, and why
- Kimball's four-step process
- Step 1: selecting Kiosko's business process
- Step 2: declaring the grain of fact_orders
- Steps 3-4: facts vs dimensions, a precise definition
- What changes when the grain is explicit
- Mini-project: Kiosko's grain declaration
02The Star Schema And Conformed Dimensions
- Module introduction: the complete star schema and conformed dimensions
- Anatomy of a star schema
- Surrogate keys vs natural keys
- Building dim_date: Kiosko's calendar
- Conformed dimensions across processes
- The bus matrix: mapping Kiosko's processes
- Assembling Kiosko's first real star
- Mini-project: Kiosko's star schema in DuckDB
03Star Vs Snowflake Vs One Big Table
- Module introduction: star vs snowflake vs One Big Table
- Normalizing a dimension: the snowflake schema
- Comparing the cost of a JOIN with EXPLAIN
- The wide table (One Big Table) argument
- Building Kiosko's sales OBT
- When the snowflake still wins
- When the OBT still wins
- Mini-project: Kiosko's three shapes compared
04Slowly Changing Dimensions
- Module introduction: when a dimension changes over time
- The problem: dim_product isn't static
- SCD type 1: overwrite and lose history
- SCD type 2: historize with valid_from/valid_to
- Implementing SCD type 2 with MERGE INTO
- Choosing type 1 vs type 2 per column
- SCD type 3 and other variants, briefly
- Mini-project: Kiosko's historized dim_product
05Point In Time Joins And Deduplication
- Module introduction: joining facts against a dimension that has history
- Why joining on product_id alone breaks history
- The point-in-time join pattern
- Late-arriving dimensions
- Where duplicate rows come from
- Deduplicating with ROW_NUMBER and QUALIFY
- Anti-joins to find what changed
- Mini-project: Kiosko's correct historical revenue
06Accumulating And Cumulative Patterns
- Module introduction: two shapes of fact that aren't an order line
- The accumulating snapshot fact table
- Modeling Kiosko's session funnel
- Updating milestones in place
- Cumulative table design: the yesterday + today pattern
- Rolling windows with array columns
- Computing 7- and 30-day actives per store
- Mini-project: Kiosko's funnel and cumulative activity
07Messy Domains And Medallion At Depth
- Module introduction: Kiosko's messy domain, named and under contract
- When one fact and two dimensions is not enough
- Degenerate dimensions: order_id, in depth
- Junk dimensions: bundling low-cardinality flags
- Medallion contracts between bronze, silver, and gold
- Multiple facts, a single conformed calendar
- Schema evolution without breaking gold
- Mini-project: Kiosko's multi-fact gold layer
08Project Kioskos Analytics Warehouse
- Module introduction: the whole guide's capstone
- The brief: Kiosko needs a real warehouse, not seven demos
- Rebuilding bronze and silver from foundations
- Building the star with historized dim_product
- Adding the session funnel and cumulative activity
- Publishing the OBT mart for the dashboard team
- What Kiosko still needs
- Mini-project: Kiosko's first analytics warehouse
Reviews
What students say
These reviews are from enrolled students who completed at least 50% of the course. We moderate reviews only on content grounds (spam, offensive language, personal data), never for being critical or negative.
No approved reviews yet.
Be the first to share your experience!