Dimensional modeling for the warehouse
Lesson 1 of 5 in Data Modeling and Schema Evolution.
A dimensional model answers one shape of question — this measure, by these descriptions, over this period — and it answers it so well that forty years of alternatives have not displaced it. Two kinds of table, and the distinction is not stylistic.
A fact table holds measured events: numeric measures plus foreign keys to the things that describe them. It grows without bound, is almost always append-only, and is the table whose physical layout matters most. A dimension table holds the descriptive context a fact is sliced by — customer, product, store, date. It is small, wide, denormalised, and it is where attribute change has to be handled.
Before either of them, declare the grain: what exactly does one row of this fact table mean? "One row per order line per shipment" is a grain. "Sales data" is not. Everything downstream depends on it — which dimensions can attach (only those that are single-valued at the grain), whether a measure is additive, and whether a join will fan out and inflate a total. Mixing grains in one table is the defect that no amount of careful SQL repairs, because an order-header discount stored beside order-line amounts is multiplied by the number of lines the moment anyone sums it.
The surrogate key is the other non-negotiable. A meaningless warehouse-generated identifier per dimension row does two jobs: it decouples the warehouse from a source system whose business key may be reissued, reformatted or merged with another company's, and — the job that gets tested — it is what makes versioned dimension history possible at all, because the same customer must be able to have several rows and the natural key is identical on every one of them.
The four decisions, in the order that keeps them honest
- Choose the business process — what activity are we measuring?
Not a department, not a report, not a dashboard — an activity that generates measurements: orders being placed, shipments leaving, claims being paid, pages being viewed. Choosing a process rather than a report is what makes the model reusable, because one process supports the eleven reports somebody will ask for next quarter as well as the three they asked for today.
The giveaway that this step was skipped is a table named after its consumer —
finance_monthly_extract— which is a report with a schema, and will need another one beside it the moment a second team asks a question. - Declare the grain — what does one row mean, exactly?
State it as a sentence with no "and/or" in it: one row per order line per shipment. This is the single most consequential decision in the model and the one most often left implicit.
Finer grain costs storage and buys questions you have not thought of yet; coarser grain forecloses them permanently, because you cannot recover detail you never stored. And the discipline this step enforces is that everything after it is checkable: a dimension either is or is not single-valued at the declared grain, and a measure either is or is not additive across it.
- Identify the dimensions — how will people want to slice it?
Every dimension that is single-valued at the grain can attach. One that is not — "the sales representatives on this order", plural — is not a dimension of this fact; it is a bridge table or a sign that the grain is wrong.
This is also where conformed dimensions earn their keep: the same
dim_dateanddim_customerused verbatim by sales, support and billing is what makes those three subject areas comparable at all. Two teams each building their own customer dimension is how an organisation ends up with two different answers to "how many customers do we have". - Identify the facts — what numbers belong at this grain?
Only measures that are true at the declared grain, and preferably additive — quantities and amounts you can sum along every dimension. Semi-additive measures (a balance, which sums across accounts but not across time) and non-additive ones (a ratio, a unit price) need naming as such, because a tool that sums them will produce a confident, wrong number.
Store the components rather than the result wherever you can: keep
revenueandcostand let the ratio be computed, because a stored margin percentage cannot be re-aggregated and a derived one always can.
A star schema keeps each dimension as one flat table holding its whole hierarchy, so every analytical query is exactly one join deep. A snowflake schema normalises those dimensions into sub-dimensions, so dim_product points at dim_subcategory points at dim_category.
On a columnar warehouse the star is the default, and the reason is physical rather than aesthetic. Denormalisation was historically resisted because repeated values waste space — but a repeated category name in a columnar store compresses to almost nothing under a dictionary encoding, so the storage argument that justified normalisation in an operational database barely applies. What does apply is the join: grouping by department in a snowflake means joining three tables instead of one, and on a distributed warehouse at most one of those joins can be collocated.
Which brings the model to the physical layer, where Amazon Redshift makes three choices that follow directly from the star:
The fact table and its largest, most-joined dimension share a distribution key, so that one join is local. A fact table has only one distribution key, so this is a choice of which dimension — made on how often it is joined and the size of the filtered rows, not on table size alone.
Small dimensions take ALL distribution, so a complete copy sits on every node and every join against them is local whatever the fact table is distributed on. Storage multiplied by the node count is cheap for a 900-row dimension and expensive for a 90-million-row one.
The fact table's sort key leads on the date column every query filters by, so zone maps skip blocks whose range cannot satisfy the predicate. And because skew has to hold after filtering, a distribution key that looks even across the whole table can still concentrate a narrow query on a handful of slices — which is why the measurement that matters is the real workload rather than the row count.
| Star schema | Snowflake schema | |
|---|---|---|
Join depth per query | one — fact to each dimension | two or more to reach an outer hierarchy level |
Dimension shape | one flat table per dimension, whole hierarchy denormalised | normalised into sub-dimension tables |
Storage | repeated attribute values — compressed to almost nothing by columnar encoding | genuinely smaller, by an amount columnar compression had already mostly recovered |
Collocation on Redshift | fact collocates with one dimension on a distribution key; small dimensions use ALL | only one join in the chain can be collocated, so the rest redistribute |
Maintenance | an attribute change touches one table — and versioning it is a bigger row | an attribute change touches one small table, which is the real argument for it |
Choose it when | the default — nearly always, on a columnar MPP warehouse | a dimension is genuinely enormous, or an outer level is owned and updated by someone else |
Key terms: Star schema, Snowflake schema, Fact table, Dimension table, Grain, Surrogate key
Interactive checkpoint quiz (2 questions) — open this page in a browser to take it.