▣ dataarchitect.studio

Pattern

Star Schema

Model analytical data as central fact tables of measurements surrounded by flat, denormalized dimension tables of context.

Intent

Make analytics fast, readable, and consistent by separating measurements (fact tables) from context (dimension tables), keeping each dimension flat and denormalized so queries are simple joins from one central table.

Context

Many people query the same data for analysis. Questions follow a stable shape — aggregate a measure, filtered and grouped by context — and you value consistency and predictable performance over write efficiency. The warehouse is read-heavy: written by a few pipelines, queried by everyone.

Structure

fact_sales measures + foreign keys dim_date dim_customer dim_product dim_store one hop from fact to any context — every query is the same simple join shape

The fact table is long and narrow: one row per measurement event at a declared grain, carrying numeric measures and foreign keys. Dimensions are wide and short: descriptive attributes you filter and group by, kept flat rather than normalized into sub-tables.

Trade-offs

Gains: queries mirror business questions; every metric lives in one place; dimensions are reusable across facts (the basis of conformed dimensions); columnar warehouses compress the redundancy cheaply.

Costs: modelling discipline up front — grain declarations, key management, and slowly changing dimension policies; ETL complexity moves into the dimension maintenance; and it fights you on genuinely document- or graph-shaped data.

When not to use it

Operational OLTP workloads (normalize instead), one-off exploratory analysis (modelling is wasted ceremony), or very small teams querying one flat export — where one big table is honestly sufficient until scale or team growth breaks it.

Common questions

What is a star schema in simple terms?

A central fact table of measurements (one row per event, like an order line) joined directly to flat dimension tables of context (customer, product, date). Every query is the same shape: aggregate measures from the fact, filtered and grouped by dimension attributes.

Should I use a star schema or a snowflake schema?

Default to the star. The only difference is whether dimensions stay flat (star) or are normalized into sub-tables (snowflake), and on modern columnar warehouses the snowflake's storage saving is negligible while its extra joins are permanent. Snowflake a dimension only for a specific, nameable problem.

Is the star schema still relevant in the lakehouse era?

Yes. Storage formats changed; the query shape of analytics didn't. Gold-layer tables in a lakehouse are very often star schemas, and semantic layers compile metrics down to them. The star survives because it matches how business questions are asked.