// fundamentals — data modeling

Data Modeling — Roadmap

Model how a business works so anyone can ask it questions: business processes, grain, facts and dimensions, history, dbt and the semantic layer. Practice comes at the end.
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 ↗
Ralph Kimball & Margy Ross · cited in 24 topics
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 ↗
Zi Chong Kao · free
SQL by doing: joins, aggregation, subqueries.
SQL tutorial ↗
Mode / ThoughtSpot · free
From basic queries to window functions and performance.
dbt Fundamentals ↗
dbt Labs · free
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.