Why it is shaped that way
each analytical question has the same form: *aggregate some measure, sliced by some fields, filtered by others*. Revenue by category by month for one region. Units sold by store by weekday.
In a star schema each such query is the same join: fact table to the dims the question mentions, group by their fields, aggregate the fact's measures. Run the variants above and they're all that shape.
That uniformity has three consequences worth having.
Query planners handle it well. The pattern is recognisable enough that analytical engines have a dedicated *star join* optimisation: filter the small dims first, use the result to restrict the fact-table scan, and never materialise a large mid.
Analysts can write the queries. Adding a slice means adding a join and a group-by column. No investigation of a normed graph is needed.
BI tools make them. Tableau, Looker and Power BI all assume this model, and give good SQL against it and poor SQL against any of it else.
The date dimension
A table of dates looks redundant. A DB has date functions. It earns its place anyway, because it holds things no function knows: your fiscal calendar, public holidays in the markets you sell in. This Weeks were promotion periods, whether a day was a weekday in the local sense.
WHERE d.is_holiday and GROUP BY d.fiscal_quarter are then plain joins rather than a growing pile of CASE expressions.
Grain
The most consequential choice is the grain: what just one fact row shows.
"One row per order line" and "one row per order" are different grains, and mixing them corrupts each aggregate — sum a per-order total across order lines and you've times'd the revenue by the number of lines. This is the most common defect in real warehouses, and it's silent.
State the grain in one sentence before creating the table, and make each measure steady with it.
Slowly changing dimensions
A product moves category. Do historical facts belong to the old category or the new one?
Type 1: overwrite. Simple, and history is rewritten — last year's report changes.
Type 2: add a new dimension row with validity dates and point new facts at it. History is preserved and the dim grows. This is the usual choice, and it's why dim tables have surrogate keys rather than using the natural business key.
Type 3: keep a previous_category column. Enough for one level of history, rarely enough .
Star against snowflake
Norming the dims — splitting product into product, category and supplier tables — gives a snowflake schema. It drops redundancy and adds joins.
For warehouses the star is often preferred, because dims are small enough that the redundancy costs little, the extra joins cost real query time, and the denormalised version is far easier to read. The exception is a truly large dim — tens of millions of customers — where the duplication starts to matter.
Mixed grain, shown inflating a total
"Times'd the revenue by the number of lines" is the most costly silent bug in warehousing, so it's worth watching happen. Three orders of $100 each, with 3, 2 and 1 line items, joined fact-to-lines and summed the wrong way:
| query | result |
|---|
SUM(total) over orders | $300 (correct) |
SUM(total) after joining order lines | $600 (wrong) |
The join repeats each order's total once per line. So The three-line order counts its $100 three times, and the total comes out just doubled — here 2×, but the factor is the mean number of lines per order, what that happens to be. Nothing errors, the number is plausible, and it's wrong on each run. This is what "state the grain in one sentence" blocks: a measure at order grain must be summed at order grain, and mixing it with a per-line join silently times it.
Text belongs in the dimension, and here is the cost
"Text in the fact table" sounds harmless until you note which table is huge. The fact table has one row per event. Hundreds of millions of them — while dims are small. Storing a 20-byte product name on each fact row, instead of a 4-byte key pointing at the product dim, widens the largest table in the warehouse:
| on a 100M-row fact table | storage |
|---|
| a 20-byte text column | ~2.0 GB |
| a 4-byte foreign key to a dimension | ~0.4 GB |
Five times the space, on the table each analytical scan has to read, for data that is duplicated millions of times. The same product name written on each one of its sales. The dim stores it once. And because columnar warehouses scan the columns a query touches, a bloated fact table isn't just disk; it's read on each query that goes near it. Narrow facts, wide dims isn't an aesthetic preference — it's keeping the big table small and the redundancy in the place where it's cheap.
Questions people ask about Star Schema
What is a star schema? A central fact table of measurable events surrounded by dimension tables that describe them, such as product, customer and date.
What is the grain of a fact table? What one row represents, for example one line item on one order. Settle it first, and keep it consistent.
What is the difference between a fact and a dimension? Facts are numbers you add up. Dimensions are the descriptive attributes you filter and group by.
Star schema or snowflake schema? A star keeps dimensions flat, with fewer joins. A snowflake normalises them, saving space but adding joins. Star is the usual default.
What is a slowly changing dimension? A dimension whose attributes change over time, such as a customer's city. You choose whether to overwrite, or keep history with new rows.
Why have a date dimension? It lets you group by week, quarter or holiday without computing them in every query, and it keeps date logic in one place.
Where it goes wrong with Star Schema
Mixed grain. Silent, and it inflates each total.
Text in the fact table. It belongs in a dim. It makes the largest table wider for no gain.
No date dim. Fiscal calendars and holidays end up as CASE expressions copied between queries.
Type 1 all over. History quietly rewrites itself, and last quarter's report no longer copies.