Total
42
Done
0
Progress
0%
Left
42
Est. time
~28 h
From business to model
business 0/5 ▾Business processes, not reports
Model the events the business runs (orders, shipments, claims), not the report someone asked for. Reports change; processes don't.
DWT, ch. 2
Requirements meet data realities
Ask the people who make decisions what they need, then check what the source systems really capture. The model lives where both meet.
Normalized vs dimensional
Source systems are normalized for writing; analytics wants stars for reading. Know both, and why you move from one to the other.
DWT, ch. 1
The bus matrix
Processes as rows, dimensions as columns: the plan for the whole warehouse on one page.
DWT, ch. 4
Conformed dimensions
One customer, one product, one calendar shared by every process, so numbers from different facts can be compared.
DWT, ch. 4
Facts
facts 0/8 ▾Declare the grain
One row means exactly one thing. Decide it before any column, and never mix grains in one table.
DWT, ch. 3
Additive, semi-additive and non-additive facts
Sales add up across everything; balances don't add across time; ratios never add. Store the parts and compute the ratio.
DWT, ch. 4
Transaction fact tables
One row per event. The most detailed and the most flexible table you can build.
DWT, ch. 3
Periodic snapshot fact tables
One row per thing per period: inventory, balances, headcount at month end.
DWT, ch. 4
Accumulating snapshot fact tables
One row per pipeline instance (an order, a claim), updated as it passes each milestone. Lags between dates become facts.
DWT, ch. 6
Factless fact tables
Events with nothing to measure, like attendance or promotion coverage. Counting rows is the measure.
DWT, ch. 3
Header and line facts
Order header vs order lines: push header values down to the line grain, allocated, instead of joining two facts.
DWT, ch. 6
Late-arriving facts
A fact shows up after its dimension row has changed. Look up the version that was current when it happened.
Dimensions
dimensions 0/9 ▾Surrogate keys and natural keys
The warehouse key is yours; the source key is theirs. Keep both, and a durable key that survives history.
The date dimension
One row per day with fiscal periods, holidays and flags, so nobody computes a calendar in SQL again.
DWT, ch. 3
Slowly changing dimensions — type 1 and 2
Overwrite, or keep history with a new row and effective dates. Type 2 is what lets you report the past as it was.
DWT, ch. 5
Other SCD types — 3, mini-dimensions and 6
Previous value in a column, fast-changing attributes split out, and the hybrid that gives current and historical views at once.
DWT, ch. 5
Degenerate and junk dimensions
An order number with no table of its own, and a bag of low-cardinality flags in one small dimension.
DWT, ch. 6
Role-playing dimensions
One date dimension playing order date, ship date and due date in the same fact.
DWT, ch. 6
Many-to-many with bridge tables
A patient with several diagnoses, an account with several owners. A bridge with weighting factors keeps totals right.
DWT, ch. 8
Hierarchies
Fixed levels flatten into columns; org charts and charts of accounts with uneven depth need a bridge.
DWT, ch. 7
Late-arriving dimensions
A fact references a customer the source hasn't sent yet. Insert a placeholder row and fill it in later.
Building it with dbt
dbt 0/8 ▾Staging models
One per source table: rename, cast and clean. No joins and no business logic yet.
Intermediate models
The steps between sources and marts: joins, pivots and re-graining, each with one purpose.
Marts: facts and dimensions
Where the star lives. Named after the business, wide, and ready for people and tools to query.
Snapshots for type 2 history
dbt records how a source row changed over time, with valid-from and valid-to columns.
Incremental models
Process only new or changed rows once a fact gets big, and know when a full refresh is the safer choice.
Data tests
Unique and not null on every key, relationships between facts and dimensions, accepted values on flags.
Model contracts
Column names and types enforced at build time, so a change upstream fails loudly instead of breaking a dashboard.
Documentation as part of the model
Describe every model and column where the code lives. People read it, and so do the AI agents that query it.
Semantic layer & metrics
semantic 0/4 ▾Why the same metric gives two numbers
Revenue defined in five dashboards ends up as five numbers. Define it once, next to the model.
Semantic models: entities, dimensions, measures
Describe the star to the semantic layer: how tables join and what can be summed.
Defining metrics
Simple, ratio, cumulative and derived metrics, each with one definition everyone queries.
Who consumes it: BI tools and AI agents
The same definitions serve dashboards and the AI agents that answer questions with the business's data.
Other approaches
other 0/3 ▾Data Vault — hubs, links, satellites
A raw, auditable layer that absorbs source changes well. Stars still sit on top for people to query.
Snowflaked dimensions and outriggers
Normalizing a dimension saves little space and costs every query a join. Know when an outrigger is still worth it.
Wide tables for BI tools
One wide table per use case built on top of the star, not instead of it.
// bibliography
The books, courses and practice sites behind this roadmap.
// books
The Data Warehouse Toolkit ↗
The reference for dimensional modeling. Each chapter is one industry case.
// other resources
Courses for the basics this roadmap doesn't cover.
Select Star SQL ↗
SQL by doing: joins, aggregation, subqueries.
SQL tutorial ↗
From basic queries to window functions and performance.
dbt Fundamentals ↗
Models, sources, tests and docs in a first dbt project.
Practice
practice 0/5 ▾Draw a bus matrix for a business you know
Your company or a shop you use: list its processes and shared dimensions, then pick the first fact to build.
Model retail sales end to end
The classic first case: point-of-sale grain, product, store, date and promotion, plus a factless fact for promotion coverage.
DWT, ch. 3
Build the star in dbt on DuckDB
Staging, intermediate and marts on your laptop, with tests on every key.
Add history and an order pipeline
A type 2 customer with snapshots, and an accumulating snapshot for orders from placed to delivered.
Put metrics on top
Define revenue, orders and average order value in the semantic layer and query them from two tools.