Star Schema
One fact table in the middle, dimensions one join away on every side. The shape that makes queries short, joins predictable and grain visible.
Who needs this, what one row is, and why the obvious build breaks
Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.
Why is a central fact table surrounded by single-join dimensions the dominant analytical shape, and when is it the wrong one?
An analyst who has to write the query, a BI tool that has to generate it without understanding it, and a query planner that has to execute it. All three benefit from the same property: every dimension is exactly one join away from the fact, so the join graph has no depth to reason about.
The fact at the centre carries the grain of the whole star — one order, one order line, one account-day. Each dimension is one row per entity or per entity version. The star is well formed exactly when every dimension join is many-to-one from that grain (Grain: What Does One Row Represent?).
Skip the shape and let each analyst join whatever they need, wherever it lives. Modern engines are good at joins, storage is cheap, and imposing a schema pattern feels like ceremony from an era of expensive hardware.
Four analysts write four different join paths to get country onto revenue — via the order's billing address, via the customer's current address, via the shipment, via a region lookup — and produce four defensible numbers (Two Dashboards, Two Numbers).
- Four analysts write four different join paths to get country onto revenue — via the order's billing address, via the customer's current address, via the shipment, via a region lookup — and produce four defensible numbers (Two Dashboards, Two Numbers).
- The BI tool generates SQL from a model it inferred. Without a declared star it guesses the join path, picks one with a many-to-many hop, and every measure on that dashboard is inflated (Grain: What Does One Row Represent?).
- A query joins three fact tables to answer one question. Each join is one-to-many, the row count grows multiplicatively, and the number that comes out is unrelated to anything (Grain: What Does One Row Represent?).
- Nobody can say which tables are facts and which are dimensions, so nobody can say which columns are safe to sum (Fact Tables).
- A new analyst needs a week to learn the join graph, which is the real cost of an unstructured warehouse and the one that never appears in any cost report.
What is actually happening
- A star schema is one fact table with foreign keys to several dimension tables, each exactly one join away. Drawn out, the fact is the centre and the dimensions are the points — hence the name. There is no hop from dimension to dimension.
- The property that matters is join predictability. Fact-to-dimension is many-to-one on a unique dimension key, so the join adds columns and never adds rows. A query touching five dimensions still returns exactly as many rows as the fact had, which means the analyst does not have to reason about fan-out at all (Joins: INNER, LEFT, RIGHT, FULL, CROSS, SELF).
- The second property is query brevity. Every question has the same shape: select from the fact, join the dimensions you need, filter, group by dimension attributes, aggregate measures. That uniformity is what lets a BI tool generate correct SQL, and what lets a human check it by reading.
- The third is engine friendliness. Dimensions are small, so an engine can broadcast them to every worker and join without a shuffle; only the fact is partitioned across workers. Filters on dimension attributes can often be turned into filters on the fact's key column and pushed into the scan (Broadcast Joins, Predicate Pushdown).
- The star is a denormalisation of dimensions, deliberately.
dim_productcarries category and subcategory as flat columns rather than pointing at a category table. That costs repetition inside a small table and buys one fewer join in every query (Denormalization on Purpose). - Multiple facts can share dimensions. That is what makes cross-process analysis possible — orders and support tickets sliced by the same customer dimension — and it is the reason conforming dimensions is worth organisational effort (Dimension Tables).
The shape
The picture is the lesson. One fact in the middle, dimensions around it, every arrow one hop. There is no path from dim_customer to dim_region that does not go through the fact, and that absence is the design.
What the picture encodes is a promise about joins: because each dimension key is unique and each join is many-to-one, adding a dimension to a query adds columns and never rows. An analyst can join all five without thinking about cardinality, which is the property that makes the shape teachable.
It also encodes what the star cannot do. There is no place to put an attribute that varies within an order, because the fact's grain is the order. There is no way to express a many-to-many relationship without a bridge table, which is the one construct that breaks the fan-out promise and therefore has to be handled explicitly.
Every question has the same shape
The practical payoff of the star is that a business question translates mechanically into SQL, and that the translation is the same every time: pick the fact, join the dimensions whose attributes you need, filter on those attributes, group by them, aggregate the measures.
That uniformity is worth more than it looks. It means a query is checkable by reading — you can see which dimensions are joined and be confident the row count is unchanged. It means a BI tool can generate the SQL. It means a new analyst is productive in a day rather than a week. And it means two people answering the same question write the same query.
The three queries below are three genuinely different business questions, and the difference between them is which dimensions appear. Nothing about join cardinality changes, nothing about grain changes, and no query needs a CTE or a subquery to be correct.
1-- "Revenue by country and month, last year, excluding cancellations."2SELECT c.country, d.year_month, SUM(f.revenue) AS revenue3FROM fct_orders f4JOIN dim_customer c USING (customer_key)5JOIN dim_date d USING (date_key)6WHERE d.year = 2025 AND f.order_status <> 'cancelled'7GROUP BY 1, 2;8 9-- "Average order value by customer segment, business days only."10SELECT c.segment, AVG(f.revenue) AS aov, COUNT(*) AS orders11FROM fct_orders f12JOIN dim_customer c USING (customer_key)13JOIN dim_date d USING (date_key)14WHERE d.is_business_day15GROUP BY 1;16 17-- "Discount rate by sales area and fiscal quarter."18SELECT r.sales_area,19 d.fiscal_year, d.fiscal_quarter,20 SUM(f.discount_amount) / NULLIF(SUM(f.revenue), 0) AS discount_rate21FROM fct_orders f22JOIN dim_region r USING (region_key)23JOIN dim_date d USING (date_key)24GROUP BY 1, 2, 3;25-- the ratio is computed from two additive measures at query time,26-- which is why it is correct at EVERY grouping ([[fact-tables]]).27 28-- What none of them needed:29-- a CTE, a subquery, a window function, a DISTINCT,30-- or any reasoning about whether the join changed the row count.31-- Each query returns exactly as many fact rows as the filter selected,32-- because every join is many-to-one on a unique dimension key.The third query is the one to keep. discount_rate is not a column anywhere — it is derived from two additive measures at query time, so it is correct whether you group by quarter, by sales area, or by nothing at all. A stored discount_percent column would have been wrong at every grouping except the one it was computed at (Grain: What Does One Row Represent?).
Where the cost actually is
People argue about star schemas as though the joins were the expensive part. In a columnar warehouse with broadcastable dimensions they are close to free, and the cost of a star query is dominated by how many bytes of the fact table the engine has to read.
That reframes the optimisation work entirely. Denormalising a dimension to remove a join saves almost nothing. Partitioning the fact by date so a month-long query reads a month rather than five years saves almost everything (Partition Pruning).
The one join-shaped cost that is real is the broadcast threshold. While a dimension fits in worker memory, the join is local and cheap. Once it does not — usually because versioning multiplied its row count — the engine switches to a shuffle join and every query in the star gets slower at once, with no schema change to blame it on.
Decided by partitioning, clustering and which columns the query projects. This is where essentially all the tunable cost lives, and it has nothing to do with the star shape.
A step function, not a gradient. Below the threshold the join is local and near free; above it every worker exchanges data on every query touching that dimension.
Columnar storage reads only what you ask for, so SELECT * on a wide fact costs many times a targeted query — the single most common self-inflicted cost in analytics.
Dimensions are small by construction. Reading all of dim_customer costs a fraction of reading one partition of the fact, which is why flattening a hierarchy to save a join is rarely the lever people think it is.
Nearly flat while dimensions are broadcastable. Adding a fifth dimension to a query changes the plan far less than narrowing the date filter by one day.
Relative weights, not measurements. The ordering is what transfers between platforms; the magnitudes do not, and this domain never publishes a price.
Relative weights for a columnar warehouse with small dimensions, shown to establish an ordering rather than as measurements. The ordering is the teaching: layout dominates, join count barely registers, and the only join-shaped cliff is the broadcast threshold.
Broadcast thresholds, automatic join-strategy selection, join elimination using declared keys, and materialised-view rewriting are all optimiser features that differ between engines and change between versions. Whether your engine broadcasts a given dimension, and at what size it stops, is a documentation question rather than a modelling one.
How to build it
Most important first.
- One fact per business process, at one declared grain, with keys to every dimension it needs. Resist the urge to make one fact serve two processes (Grain: What Does One Row Represent?).
- Every dimension exactly one join from the fact. If an attribute needs two hops, flatten it into the dimension rather than leaving the hop (Snowflake Schema).
- Conform dimensions across facts: one
dim_customer, one key space, one meaning. This is what turns several stars into a platform rather than several silos (Data Ownership). - Never join two fact tables directly. Aggregate one to the other's grain in a CTE first (Grain: What Does One Row Represent?).
- Keep the fact narrow and numeric. Descriptive attributes belong in dimensions where they can be restated (Dimension Tables).
- Partition the fact by its event date and let dimension filters push down where the engine supports it. The star's query performance is mostly a layout property, not a schema one (Partitioning).
What this actually promises
Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.
- The star guarantees grain preservation across dimension joins provided each dimension key is unique. That uniqueness is a test, not an enforced constraint, in most analytical engines (Database Constraints).
- It guarantees a single join path between any fact and any dimension, which is what removes the "which route did you take" class of disagreement.
- It guarantees nothing about completeness, freshness or measure correctness. A perfectly formed star can be missing a day of facts and will report it as a quiet day (Reconciliation).
- It does not guarantee that two stars are comparable. Two facts sharing a dimension by name but not by key space produce a join that returns rows and means nothing (Dimension Tables).
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- Assert uniqueness on every dimension key. One duplicate anywhere in the star fans out every query that touches that dimension, so this test protects the whole model rather than one table (Data Tests).
- Assert that the fact's row count is unchanged by the dimension joins in the build. A star whose join preserves grain is a star whose measures are meaningful (Grain: What Does One Row Represent?).
- Assert referential integrity in both directions: every fact key resolves, and track dimension rows that no fact references — the second is not an error but a useful signal that a key space has drifted.
- What these miss: whether the star answers the question people think it does. A well-formed star at order grain will happily answer a product-mix question with a number, and the number will be wrong because the grain cannot express it (Grain: What Does One Row Represent?).
- The star's freshness is the freshness of its slowest component. A fact updated hourly joined to a dimension refreshed nightly gives hourly facts described by yesterday's attributes, which is a different thing from hourly data (Freshness Monitoring).
- Dimension loads must precede fact loads within a run, which puts a serial dependency into the schedule and is a real freshness cost (Task Dependencies).
- Because facts are append-oriented, the newest partition can often be published independently of a full dimension refresh — which is the design that keeps a star fresh without rebuilding it (Incremental Processing).
- Adding a dimension is additive: a new key column on the fact, backfilled or defaulted to the unknown member for history (Dimension Tables).
- Adding a dimension attribute is invisible to existing queries and immediately available to new ones. This is the star's main evolutionary advantage over a wide flat table, where the same change is a rewrite (Schema Evolution).
- Removing a dimension breaks every query grouping by it, loudly. That is the good case, and it is what makes impact analysis worth having before the change rather than after (Impact Analysis).
- Changing the fact's grain is not evolution of the star; it is a new star. Build it alongside and migrate consumers (Breaking Schema Changes).
- A star built deterministically from retained raw data is fully rebuildable. Rebuild dimensions first, then facts, into a new location, validate, then swap (Atomic Publish).
- Rebuild order matters: facts reference dimension keys, so a dimension rebuild that changes keys orphans facts. Deterministic key generation is what makes the rebuild safe (Surrogate Keys).
- Partition-level fact repair is the normal case and is cheap. Full-star rebuilds should be rare enough that their runtime is a known number (Planning a Backfill).
What can go wrong
- A dimension with duplicate keys, fanning out every query in the star at once.
- A BI tool inferring a join path through a bridge or a second fact, producing multiplied measures with no SQL for anyone to review (Dashboards Built Around Questions).
- Two facts joined directly because both had a
customer_key, multiplying rows by the product of their per-customer counts. - Dimensions that grew past the broadcast threshold, turning every star query from a cheap local join into a shuffle without any change to the schema (Broadcast Joins).
- The mitigation failing: a conformed dimension that two teams both write to with different late-arriving rules, so it is conformed in name and divergent in content.
- "Star schemas are a legacy of expensive hardware." The join-cost argument was never the main one. Predictable grain, a single join path and a browsable model are properties about people and correctness, not about disks.
- "A star means one table per source." A star is one fact per business *process*. Several sources often feed one fact, and one source often feeds several (Analytical Data Modeling).
- "We have a star, so our numbers are right." The shape guarantees that a correct query is expressible and that joins do not fan out. It guarantees nothing about whether the rows loaded are complete (Reconciliation).
- "Dimensions are small, so their quality matters less." A dimension defect propagates into every query in the star. Small table, maximum blast radius (Dimension Tables).
Operating it
- Query logs grouped by which dimensions are joined. This tells you which dimensions are load-bearing and which exist for nobody (Data Discovery).
- Fact row count before and after the dimension joins in the build, per run. Equality is the invariant (Pipeline Metrics).
- Dimension row counts over time, with the engine's broadcast threshold marked, so the day a dimension crosses it is a known event rather than a mystery slowdown (Volume Anomalies).
- At 10x facts, partitioning and file layout decide query feasibility; the star shape itself is unaffected (Physical Data Layout).
- At 100x, or once dimensions are versioned, the broadcast threshold becomes the thing to watch. A versioned customer dimension can be an order of magnitude larger than the unversioned one (SCD Type 2 in Practice).
- More facts sharing conformed dimensions scales the governance problem, not the query one. The technical shape is unchanged; the agreement about what
customer_keymeans is what gets harder (Data Ownership).
- The fact dominates storage and scan cost; the dimensions are rounding errors on both. Cost work in a star is almost entirely fact-layout work (Scan Cost).
- Join cost is a step function rather than a gradient: broadcastable dimensions are nearly free, and a dimension that no longer fits forces a shuffle on every query (The Shuffle).
- Denormalising dimension hierarchies costs repeated strings in a small table and saves one join in every query. That trade is almost always worth taking (Snowflake Schema).
- A star costs a transformation layer, a set of tests and an irreversible grain decision. It buys short queries, predictable joins, a shared vocabulary and a shape BI tools can generate against safely.
- Flattened dimensions cost repetition and the loss of a single place to correct a hierarchy. They buy one fewer join in every query and a model a human can browse.
- Conforming dimensions costs cross-team agreement, which is slow. It buys cross-process questions, which are the ones executives ask.
Modeling lab — one grain, ten questions
Change an input and watch which number moves — and which one does not. Everything here comes from a model in this repository, not from a measurement.
| Business question | At this grain | Why |
|---|---|---|
What was revenue by country last month? Wants: One order, or one order line — either works, provided the measure is additive at that grain and is not summed twice. | answered | The measure is additive at this grain and each order is counted once. |
What is the average order value? Wants: One order. An average over lines answers a different question entirely. | answered | The denominator is orders, which is exactly what one row is. |
What was revenue by customer country at the time of each order? Wants: One order, joined to the version of the customer that was current when the order was placed. | WRONG no history | With a Type 1 dimension every historical order is attributed to the customer's current country. A customer moving from Poland to Germany silently rewrites last year's regional reports, and last month's report no longer reproduces. |
What is net revenue after refunds? Wants: One order, with refunds either netted into the measure or held as a separate signed fact at the same grain. | answered | Refunds net into the measure, or sit beside it as a signed fact at the same grain. |
What was the total account balance on each day last year? Wants: One account-day. A balance is a state, not an event, and cannot be reconstructed by summing transactions unless every transaction since account opening is retained. | WRONG | Summing transactions per day gives the daily change in balance, not the balance. The chart has the right shape and the wrong y-axis. |
What is month-three retention by signup cohort? Wants: One user-month of activity, joined to the user's signup month. | WRONG | Users who were active but did not buy are invisible, so retention is understated by exactly the non-buyers. |
What share of sessions ended in a purchase? Wants: One session — which requires a session window over events, because no source system emits a session. | unanswerable | No source system emits a session. Without a session window over events there is no denominator to divide by. |
Which products are most often bought together? Wants: One order line, with the order key retained so lines can be grouped back into baskets. | unanswerable | The most instructive failure in this lab: the model is not wrong, it is at the wrong resolution, and no query can recover what was aggregated away. |
What was yesterday's revenue, asked at 06:00 this morning? Wants: One order, in a period that is not yet closed. | with care | The grain is right and the period is not closed. Orders that happened yesterday and arrive later today are still missing at 06:00. |
What was global revenue, across markets that bill in different currencies? Wants: One order, with both the transaction amount and the converted amount stored, plus the rate and the date the rate applied. | WRONG no history | Converting at query time with today's rate makes every historical report change daily. Converting once with no record of the rate makes the number unreproducible. Both pass every type check there is. |
Where this applies
Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.
- GENERALThe shape and its grain-preservation property are arithmetic, not vendor behaviour, and hold in every SQL engine. What varies is how much the engine rewards it — join elimination and aggregate rewriting are optimiser features, not properties of the schema.
- WAREHOUSE-SPECIFICEngines that broadcast small dimensions make a five-dimension join nearly as cheap as a single-table scan; engines that shuffle both sides pay network cost per join, which is why the same star performs very differently on a distributed warehouse and on a single-node engine reading remote Parquet.
- TOOL-SPECIFICBI tools differ in how they infer join paths from a schema: some require an explicit model where each dimension is declared one hop from the fact, others guess from foreign-key names and will happily route through a second fact table, which produces multiplied measures with no SQL anyone reviews.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — Distributed Systems owns why a broadcast join is cheap and a shuffle join is not — the underlying question is how much data crosses the network and how it is repartitioned to do so.