# The Star Schema, Explained > The dimensional-modeling pattern behind most data warehouses - one fact table surrounded by dimension tables - and why it's shaped that way on purpose. --- # The Star Schema, Explained Open up a data warehouse and you'll find tables organized in a way that looks nothing like the app database you're used to. Instead of a web of normalized tables all pointing at each other, you'll find one big table sitting in the middle - sales, orders, events, whatever the business measures - surrounded by a handful of smaller tables describing the "who, what, when, and where" of each row. That shape has a name: a **star schema**, and it's not an accident. It's built specifically to make one kind of question fast: "sum this number, broken down by that category." This guide covers what the two kinds of tables are, why the shape looks like a star, and when you'd reach for it. ## How to read this Read in order. Phase 1 introduces facts and dimensions with a concrete example. Phase 2 explains why dimensions are deliberately denormalized - the choice that makes this shape different from the database schemas you already know. Phase 3 contrasts it with a "snowflake" schema and connects it to the broader OLTP/OLAP split. ## The phases 1. [Facts vs. dimensions](01-facts-vs-dimensions.md) - the two kinds of tables, with a concrete sales example. 2. [Why it's shaped like a star](02-why-a-star.md) - denormalized dimensions, on purpose, for fast reporting. 3. [Star vs. snowflake, and when to use it](03-star-vs-snowflake.md) - a normalized variant, and where this fits in the bigger picture. --- # Facts vs. dimensions A star schema is built from exactly two kinds of tables, and once you can tell them apart, the rest of this guide is filling in the detail. Let's ground it in something concrete: a business selling products, tracking every sale. ## The fact table: what happened The **fact table** holds the events you're measuring - one row per thing that occurred. For a sales business, that's `sales_fact`, and each row is one sale: ```text sales_fact ----------------------------------------------------------- sale_id | customer_id | product_id | date_id | region_id | amount | quantity 1001 | 42 | 501 | 20260701| 3 | 89.99 | 1 1002 | 17 | 233 | 20260701| 1 | 45.50 | 2 1003 | 42 | 501 | 20260702| 3 | 89.99 | 1 ``` *What just happened:* each row is one measurable event - a sale - with a handful of numbers you care about summing or averaging (`amount`, `quantity`), plus a set of IDs pointing out to the "who, what, when, where" of that sale. Those numeric, summable columns are called **measures**. The fact table is deliberately narrow on descriptive detail - it doesn't store the customer's name or the product's category inline. It records "sale 1001 involved customer 42, product 501, on this date, in this region, for this amount," and nothing more. Everything else lives one hop away. ## The dimension tables: the who, what, when, where Each ID in the fact table points to a **dimension table** - a table that describes one axis you might want to slice the facts by. For this example, there are four: ```text customer product date region ------------------------ ------------------------ ------------------------ ------------------------ customer_id | name | tier product_id | name | category date_id | date | month | year region_id | name | country 42 | Amara| gold 501 | Mug | Kitchen 20260701| Jul 1| Jul | 2026 3 | West | USA 17 | Devon| standard 233 | Pen | Office 20260702| Jul 2| Jul | 2026 1 | East | USA ``` *What just happened:* each dimension answers one question about a sale. `customer` answers "who bought it." `product` answers "what did they buy." `date` answers "when." `region` answers "where." None of these tables are large compared to the fact table - you might have millions of sales but only a few thousand customers, a few hundred products, a handful of regions, and one row per calendar date. ## Why the split matters This split creates a shape: one big table full of numbers and ID references, surrounded by several small tables full of descriptive attributes. That's the whole idea. The fact table is where the *measurements* live; the dimension tables are where the *context* for interpreting those measurements lives. ```mermaid flowchart TD F[sales_fact: sale_id, amounts, IDs] C[customer] P[product] D[date] R[region] F --> C F --> P F --> D F --> R ``` *What just happened:* this is the star taking shape - one fact table in the middle, dimensions arranged around it, each connected by a foreign key. It's a plain-language division of labor: facts are *what happened, and how much*; dimensions are *everything you'd use to describe or filter what happened*. > If a column is a number you'd sum or average, it belongs in the fact table. If it's something you'd filter or group by - a name, a category, a date, a region - it belongs in a dimension. Phase 2 gets into why the dimension tables themselves look different from what you'd expect if you've worked with a normalized application database - and why that difference is deliberate, not sloppy design. --- # Why it's shaped like a star If you've worked with an application database, you were probably taught to **normalize**: split data into small tables so each fact is stored exactly once, avoiding duplication. A star schema's dimension tables break that habit on purpose, and understanding why is the core of this phase. ## What a normalized schema is trying to prevent In a normalized, transactional (OLTP) schema, you'd typically split `product` further - categories in their own table, referenced by ID, so that renaming a category means updating one row instead of thousands. That avoids what's called an **update anomaly**: the same fact stored in many places, some of which get updated and some of which don't, leaving the data inconsistent with itself. That design is optimized for a system that's constantly writing - orders coming in, inventory changing, customer details being edited. Keeping each fact in exactly one place keeps those writes cheap and safe. ## Dimensions are denormalized on purpose A dimension table in a star schema does the opposite: it flattens everything about one entity into a single row, categories and all, even if that means the word "Kitchen" is repeated across a thousand rows of the `product` table. ```text -- flattened (star schema style) product_id | name | category | subcategory 501 | Mug | Kitchen | Drinkware -- normalized (OLTP style) would instead split this into: product (product_id, name, subcategory_id) subcategory (subcategory_id, name, category_id) category (category_id, name) ``` *What just happened:* the star schema version repeats "Kitchen" and "Drinkware" on every product row that belongs to them, instead of pointing to a separate category table. That's duplication a normalized schema is designed to avoid - and here, it's intentional. ## Why duplication is the right trade here A data warehouse isn't being hammered with constant small writes the way an application database is. It's typically loaded in batches (nightly, hourly, or streaming in append-only fashion) and then read over and over by people running reports and dashboards. The workload it needs to be fast at is **aggregation**: "sum sales by region by month," "average order value by customer tier," "total revenue by product category last quarter." Denormalizing the dimensions means a query like that touches far fewer tables. Instead of joining `sales_fact` to `product`, then `product` to `subcategory`, then `subcategory` to `category`, you join `sales_fact` to `product` once and every attribute you need - category included - is already sitting right there in that one row. ```mermaid flowchart LR Q["Sum sales by region, by month"] --> J1[Join sales_fact to region] Q --> J2[Join sales_fact to date] J1 --> A[Aggregate] J2 --> A ``` *What just happened:* a typical warehouse query joins the fact table directly to one or two flat dimension tables and aggregates, instead of chaining through sub-tables to reassemble a product's category. Fewer joins - especially on a table with millions of fact rows - means a faster, simpler query. > A star schema isn't "worse" normalization - it's a different design goal. Normalized OLTP schemas optimize for correctness under frequent writes; a star schema optimizes for speed under heavy aggregation reads. Neither one is right for the other's job. This is also why you rarely see anyone running `UPDATE` against a warehouse's dimension tables the way you would against an OLTP database - dimensions are typically reloaded wholesale from the source system. That way, the "what if the category name changes and it's duplicated everywhere" problem is handled by the load process, not the schema. Watch it animated: [a star schema](/explainers/StarSchema.dc.html) ```quiz [ { "q": "Why are dimension tables in a star schema typically denormalized, unlike tables in an OLTP application database?", "choices": [ "Denormalization is a mistake that warehouse designers haven't fixed yet", "Warehouses optimize for fast aggregation reads, and flat dimensions mean fewer joins per query", "Denormalized tables use less disk space", "SQL doesn't support joins in a data warehouse" ], "answer": 1, "explain": "Flattening a dimension avoids joining through several sub-tables just to get one attribute, which speeds up the aggregation queries a warehouse is built for." }, { "q": "What problem does normalization in an OLTP schema primarily prevent?", "choices": [ "Slow aggregation queries", "Update anomalies - the same fact stored in multiple places getting out of sync", "Running out of table names", "Foreign keys pointing to the wrong table" ], "answer": 1, "explain": "Normalization keeps each fact in one place so an update only has to happen once, which matters most under frequent writes." }, { "q": "In the sales_fact example, which of these belongs in the fact table rather than a dimension table?", "choices": [ "The customer's loyalty tier", "The product's category name", "The sale amount", "The region's country" ], "answer": 2, "explain": "The sale amount is a measure - a number you'd sum or average - which is what fact tables hold. The others are descriptive attributes that belong in dimensions." } ] ``` --- # Star vs. snowflake, and when to use it Now that you've seen why dimensions are deliberately flattened, here's a variant that partially undoes that - and when each one earns its place. ## The snowflake schema A **snowflake schema** takes a dimension and normalizes part of it back out into sub-tables - the same splitting you'd do in an OLTP schema. Instead of one flat `product` table carrying `category` and `subcategory` inline, you get: ```text snowflake version: product (product_id, name, subcategory_id) subcategory (subcategory_id, name, category_id) category (category_id, name) star version: product (product_id, name, category, subcategory) ``` *What just happened:* the snowflake version breaks `product` into three linked tables so "Kitchen" is stored exactly once instead of once per product. Drawn out, the fact table's points now branch further into sub-dimensions, which is where the name comes from - it looks like a snowflake's branching arms instead of a plain star's straight points. ```mermaid flowchart TD F[sales_fact] --> P[product] P --> S[subcategory] S --> C[category] ``` *What just happened:* this is one arm of the star growing an extra hop. Where the star schema had `sales_fact → product` with everything already attached, the snowflake has `sales_fact → product → subcategory → category`, each step normalized. ## The trade-off A snowflake schema reduces duplication - genuinely useful if a dimension is large and its attributes change often, since there's only one row to update. The cost is exactly what Phase 2 showed a star schema avoiding: more joins per query. Summing sales by category now means traversing three tables deep instead of reading one flat row. ```text star schema -> more storage duplication, fewer joins, simpler & faster queries snowflake schema -> less duplication, more joins, more query complexity ``` In practice, most data warehouses lean toward star schemas, or something close to it, precisely because query speed and simplicity matter more than storage savings when dimension tables are small relative to the fact table anyway. A `product` dimension with ten thousand rows duplicating a category name costs you almost nothing in storage - but it can save real time on every report that groups by category. > Neither shape is universally "correct." A star schema optimizes for the queries; a snowflake schema optimizes for the storage and update story. Pick based on which one you're actually trading against. ## Where this fits in the bigger picture Everything in this guide has been about how a warehouse organizes tables once data is already there - the OLAP side of things, built for analysis and reporting. That's a different world from the OLTP database your application writes to on every request, and the two get confused constantly. If the distinction between those two systems - and how data gets from one to the other - isn't already solid for you, that's covered in [Data Warehouses vs Lakes, Plainly](/guides/warehouses-vs-lakes), which lays out the OLTP/OLAP split this guide has been assuming throughout.