GuideBeginner
Data Modeling For Analytics
Picks up the flat model — one fact and two dimensions — that `data-engineering-foundations-guide` deliberately left incomplete and takes it to real dimensional modeling, on the same Kiosko case. You learn Kimball's four-step process (business process, grain, dimensions, facts), how to decide with real criteria between a star schema, a snowflake schema, and a wide One Big Table by comparing join cost with `EXPLAIN`, how to historize a changing dimension with SCD type 1 and type 2 using `MERGE INTO`, how to join facts with historical dimensions at the exact point in time (the most expensive mistake in a dimensional model), how to deduplicate repeated rows with `ROW_NUMBER()`/`QUALIFY`, and how to build cumulative patterns: Kimball's accumulating snapshot for a session funnel and Zach Wilson's cumulative table design with array-type columns. Everything runs as SQL executed directly against DuckDB, with Python as minimal glue code. It closes with a capstone that integrates a complete Kiosko analytics warehouse: a star with a historized dimension, an accumulating snapshot, cumulative activity, and a wide table for the BI team.
- 64
- lessons
- 8
- modules
- English · Spanish
- available in
- Yes
- certificate
- Free
- access
Outcomes
What you'll be able to do
- Apply Kimball's four-step process and formally declare the grain of an existing fact table
- Build a complete star schema with surrogate keys, a conformed calendar dimension (`dim_date`), and a bus matrix map
- Compare star schema, snowflake, and One Big Table with `EXPLAIN`, measuring join cost against size and repeated columns
- Implement SCD type 1 and type 2 with `MERGE INTO`, deciding per column when to historize and when to overwrite
- Write the correct point-in-time join (`BETWEEN valid_from AND valid_to`) and explain why joining on the natural key alone corrupts historical revenue
- Deduplicate repeated rows from a source with `ROW_NUMBER()`/`QUALIFY` and detect changes with anti-joins
- Model an accumulating snapshot fact table for a session funnel, and a cumulative table design with array-type columns and 7- and 30-day rolling windows
- Formalize degenerate dimensions and junk dimensions, and validate schema contracts between bronze, silver, and gold across a domain with more than one fact
Before you start
What you need to bring
It's for you if...
- Anyone who already completed `data-engineering-foundations-guide` (or has an equivalent flat fact-and-dimension model) and needs to bring it to a real analytics warehouse
- Analytics engineers and data engineers designing dimensional models for BI who need to justify star vs. snowflake vs. OBT with real criteria
- Devs who already implemented SCD without understanding why their historical join returned wrong numbers
- Anyone preparing for interviews that ask about dimensional modeling, SCD type 2, or accumulating snapshots
Requirements and materials
- `data-engineering-foundations-guide` completed (or equivalent: a working flat fact-and-dimension model, with basic Python and SQL)
- Intermediate SQL: `SELECT`, `JOIN`, `GROUP BY`
- A personal laptop with Python 3 and DuckDB installable (`pip install duckdb`) — everything runs locally at $0
Content
The syllabus, module by module
Open any of them to see its lessons.
- 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
- 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
- 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
- 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
- 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
- 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 Zach Wilson pattern
- Rolling windows with array columns
- Computing 7- and 30-day actives per store
- Mini-project: Kiosko's funnel and cumulative activity
- 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
- 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
Common questions
What people usually ask
No limit. It's a free guide: come in whenever you like, as often as you like.
No. Modules run from easier to harder, but you can jump to the one you need. Progress is saved per lesson.
Whatever is needed is listed under “What you need to bring”, above. If nothing is listed there, you can start from zero.
In the Club's WhatsApp group, and every two weeks there's a live with an instructor where questions get worked through.
Yes. It's issued automatically once you finish every lesson, with a verifiable code you can share on LinkedIn.
Start whenever you like
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!