Star Schema

A star schema is the foundational pattern of dimensional modeling for analytics. A central fact table holds measurable business events (sales, clicks, shipments) and foreign keys to surrounding dimension tables that describe the context of those events (product, customer, date, store). Drawn out, the fact table sits at the center with dimensions radiating outward like points of a star.

How It Works

The fact table stores numeric, additive measures plus dimension keys, and is typically long and narrow with many rows. Each dimension table is wide and short, denormalized so all attributes of an entity live in one table — a product dimension carries name, category, brand, and supplier together rather than splitting them across normalized tables. Queries join the fact to the dimensions, filter and group by dimension attributes, and aggregate the measures. The deliberate denormalization minimizes joins, which makes queries fast and easy for BI tools and analysts to write. A surrogate integer key per dimension row decouples the warehouse from source-system keys and supports history tracking.

When to Use It

Use a star schema for analytical and reporting workloads: data warehouses, BI dashboards, and OLAP cubes. It is ideal when query patterns are aggregation-heavy, when business users write queries, and when read performance and clarity matter more than write efficiency or storage normalization.

Trade-offs

Denormalized dimensions duplicate data and can become inconsistent if not maintained through controlled ETL. Star schemas are built for reads, not transactional writes, so they are unsuitable as operational stores. Very large or hierarchical dimensions can grow unwieldy; normalizing them yields a snowflake schema, which saves space but reintroduces joins. Handling changing dimension attributes over time requires explicit slowly-changing-dimension strategies. Grain (the level of detail of the fact table) must be chosen carefully, as it is hard to change later.

Related Patterns

Star schemas rely on slowly-changing-dimension techniques to track history, are often served via materialized-view roll-ups, and form the gold layer of a medallion-architecture.

Example

A retail warehouse has a fact_sales table with one row per line item, carrying quantity and revenue plus keys to dim_date, dim_product, dim_store, and dim_customer. An analyst computes monthly revenue by product category with a single fact-to-dimension join and a group-by, and the BI tool renders it instantly.