A star schema keeps the measurements of a business process in one fact table and the descriptive context around them, such as the date, the customer or the sales channel, in dimension tables that the fact joins to by key. A snowflake schema is the same model with one or more dimensions normalised into further tables, which adds joins without adding information. Every fact table has a grain, the one thing a single row stands for, and many dimensional modelling mistakes start with a grain nobody wrote down.
Facts, dimensions and keys
Kimball Group describes a star schema as fact tables linked to their dimension tables through primary and foreign keys. A fact table holds the numeric measures of a real business event, such as the value of an order, plus a foreign key for each dimension that gives the event its context. A dimension table is wide and flat: one primary key and many descriptive attributes that reports filter and group by.
Declaring the grain comes before choosing a fact table's dimensions or measures, because each of them has to be true at that grain. The lowest grain a process captures is its atomic grain. A summary grain is quicker to query but assumes the questions its readers will ask.
A dimension's primary key should belong to the warehouse rather than the source. Kimball Group recommends an anonymous integer surrogate key for every dimension, because a natural key such as a customer number repeats once the dimension keeps history and can differ between source systems. The date dimension is the stated exception: its key can stay meaningful.
A snowflake schema splits a dimension's hierarchy into separate tables joined by keys. Kimball Group advises against snowflaked dimensions because business users find them harder to understand and navigate. They can also slow queries, while a flattened dimension holds exactly the same information.
Worked example
The flagship retail-orders pipeline builds one fact table, orders_fact, with one current row per order. It keeps each order's date and channel on the fact row instead of in dimension tables of their own. Its order_id is what Kimball Group calls a degenerate dimension: a dimension key with no attributes left to hold, stored in the fact itself.
The retail-orders model: one fact per order, then a daily summary
The flow the flagship pipeline runs. The order fact is the centre of the star; its order date and channel are the keys a date dimension and a channel dimension would join on.
Diagram, 3 stages
Select a stage to see its details. The diagram explains the pipeline and does not run it; the full explanation follows below.
Accepted order events
Outputs: order_id, source_version, order_date, channel, amount_cents
Each valid, unique source event. One order can have several, because the source sends a new version when an order is corrected.
Why Events are not the model's grain: an order with two versions has two events, so nothing is summed at this stage.
Inputs and outputsAccepted order events: inputs and outputs
- Receives
- Nothing: the flow starts here
- Outputs
- order_id; source_version; order_date; channel; amount_cents
- Hands on
- the winning version of each order, to Order fact
Hands on to Order fact: the winning version of each order
Order fact
Outputs: one row per order
orders_fact: one current row per order, the lowest grain this model keeps.
Why The winning version of each order replaces the order's row, so a correction changes one fact instead of adding a second one.
Inputs, outputs and schemaOrder fact: inputs, outputs and schema
- Receives
- the winning version of each order, from Accepted order events
- Outputs
- one row per order
- Hands on
- current facts, grouped by date and channel, to Daily channel mart
order_id- VARCHAR Degenerate dimension: the order's own key, with no table of its own
order_date- DATE The key a date dimension would join on
channel- VARCHAR The key a channel dimension would join on: web or store
amount_cents- BIGINT The measure: the order's value in whole cents
Hands on to Daily channel mart: current facts, grouped by date and channel
Daily channel mart
Outputs: one row per order date and channel
daily_channel_mart: one row per order date and channel, with the number of orders and their summed cents.
Why It groups the current facts, not the events, so a corrected order moves its whole amount from its old group to its new one.
Inputs, outputs and schemaDaily channel mart: inputs, outputs and schema
- Receives
- current facts, grouped by date and channel, from Order fact
- Outputs
- one row per order date and channel
- Hands on
- Nothing: the flow ends here
order_date- DATE Group key
channel- VARCHAR Group key
order_count- BIGINT Orders in the group
amount_cents- HUGEINT The group's orders summed, in whole cents
Drawn as a star, the fact sits in the middle with a date dimension and a channel dimension around it. The pipeline does not build those two dimension tables, so the table below is a sketch of the star the fact implies, not output the site produces.
| Table | One row per | Joins on | Holds |
|---|---|---|---|
orders_fact |
Order | order_date, channel |
order_id, amount_cents |
| Date dimension | Calendar day | order_date |
Month, weekday |
| Channel dimension | Sales channel | channel |
web or store |
In the fact, order_date and channel are the dimension keys, order_id is the degenerate dimension and amount_cents is the measure.
Here are the same orders at two grains, from the saved run of the flagship's follow-up batch. At order grain each row is one current order:
order_id |
order_date |
channel |
amount_cents |
|---|---|---|---|
| o100 | 2026-01-02 | web | 12000 |
| o101 | 2026-01-01 | store | 2500 |
| o102 | 2026-01-02 | web | 7000 |
| o105 | 2026-01-02 | store | 0 |
| o106 | 2026-01-02 | store | 3000 |
At date and channel grain, the daily mart groups those five facts into three rows:
order_date |
channel |
order_count |
amount_cents |
|---|---|---|---|
| 2026-01-01 | store | 1 | 2500 |
| 2026-01-02 | store | 2 | 3000 |
| 2026-01-02 | web | 2 | 19000 |
Both tables hold 24500 cents. Five order rows became three group rows: only the total carries across the two grains, not the row count. Order o100 sits on 2 January because a later version of the order moved it there, and the late-arriving data note follows that kind of change through both tables.
Where it goes wrong
Adding counts across grains
Five fact rows and three mart rows are not eight of anything. Each count answers a question at its own grain, which is why the flagship note reads each count at its own grain. Kimball Group's grain rule says the same of tables: different grains must not be mixed in one fact table.
Joining a fact to a dimension with several rows per key
When a dimension keeps one row for each version of a customer, a join on the customer key alone repeats each fact once per version and the measure grows. The dbt snapshots note shows revenue rising from 43000 to 76000 cents this way on its three-customer baseline. The slowly changing dimensions hub covers the history that causes it.
Snowflaking a dimension readers must join
Splitting a dimension into a chain of tables gives every report more joins to write, and each join is one more place to match on the wrong key or at the wrong grain. Note coming, marked 19 October 2026: a field note on star schema vs snowflake schema, showing where those joins fan out.
Keying a history-keeping dimension on the source's key
A dimension that keeps history repeats the source's key, customer_id say, once per version, so that key cannot be the dimension's primary key. A Kimball-style dimension gives each version its own surrogate key, and each fact carries the key of the version that was current when it happened. A dbt snapshot gives each version a dbt_scd_id, but facts do not usually carry it, so a join to a snapshot uses the source's key and compares the fact's date with each version's validity dates. The dbt snapshots note's as-of join does exactly that.
Questions
What is the difference between a fact table and a dimension table?
A fact table holds the measurements of a business event at one declared grain with a key for each dimension, while a dimension table holds the descriptive attributes that reports filter and group by.
What is the difference between a star schema and a snowflake schema?
In a star schema each dimension is one flat table joined straight to the fact, while a snowflake schema splits a dimension's hierarchy into further tables that hold the same information behind more joins.
What is the grain of a fact table?
The grain is what one row of the fact table stands for, such as one order or one channel on one day, and it is declared before the table's dimensions or measures are chosen.