Decades after it was popularized, the star schema is still the default choice for a reason: it is fast to query, easy for analysts to understand, and forgiving of the way real reporting questions are actually asked. The design work is not in the shape — it is in deciding what belongs in the fact table and what belongs in the dimensions.

Facts are the measure, dimensions are the context

A fact table holds the numbers you aggregate — amounts, quantities, durations — plus the foreign keys that give them context. A dimension table holds the descriptive attributes you filter and group by — customer, product, date, store. The single most common design mistake is putting a descriptive attribute in the fact table because it is "convenient." That quietly breaks the grain and makes every later join more expensive.

Get the grain right, or nothing else matters

The grain is the single most important decision in the model: what does one row in the fact table represent? "One row per order line" is a different grain from "one row per order," and mixing them in one table is how warehouses end up with double-counted revenue. Write the grain down in one sentence and check every column against it.

  • State the grain explicitly before writing any DDL.
  • Keep additive measures in the fact table; derived ratios in the reporting layer.
  • Use a dedicated date dimension — it pays for itself on every single report.
  • Prefer a denormalized star over a snowflake unless a dimension is genuinely huge.
A star schema is a contract between the data team and the analysts. Get the grain right and the rest is detail.

When to deviate

Snowflake a dimension only when it is very large and rarely queried in full — the extra joins are a real cost. Reach for a data vault or a hybrid when you have many slowly-changing dimensions and strong lineage requirements. But for the large majority of enterprise reporting workloads, a clean, well-grained star schema on a modern columnar engine (Snowflake, BigQuery, Databricks) is still the fastest path to answers analysts actually trust.

If you are designing a new warehouse or untangling an existing one, our team in Bucharest models fact and dimension tables for banks and retailers across Europe — with the grain, conformed dimensions and reporting layer all documented from day one.