A star schema is a data warehouse design pattern that organises data into a central fact table containing measurable business events (sales, page views, payments) surrounded by dimension tables containing descriptive attributes (customers, products, time, location), forming a star shape when diagrammed.
Why Star Schema Matters
The star schema, introduced by Ralph Kimball in the 1990s, is still the dominant data modelling pattern for analytical workloads in 2026. The reason: it strikes the best balance between query performance, ease of understanding, and flexibility. BI tools, semantic layers, and AI agents all assume star schema patterns by default.
Without consistent data modelling, every dashboard reinvents its own joins and filters. With a clean star schema, every dashboard and AI query starts from the same well-understood structure.
How Star Schema Works
A star schema has two types of tables:
- Fact tables: Contain measurable business events. Each row is a fact: a sale, a click, a payment. Fact tables have foreign keys to dimensions plus numerical “measures” (revenue, quantity, duration). Fact tables are typically the largest tables in the warehouse.
- Dimension tables: Contain descriptive attributes. Each row describes a “thing”: a customer, a product, a date, a location. Dimensions are typically much smaller than facts and join to fact tables via primary key / foreign key relationships.
The star shape comes from facts in the centre with dimensions radiating outward. A more normalised variant, the snowflake schema, splits dimensions into sub-dimensions but is less common in modern cloud warehouses because joins are cheap.
Modern dbt projects almost universally produce star schemas in their final “marts” layer. The semantic layer then exposes metrics computed against the star schema.
Real-World Example
A SaaS company’s order data is modelled as a star schema. Fact table fct_orders has one row per order, with foreign keys to dim_customer, dim_product, dim_date, and dim_promotion, plus measures: revenue, quantity, discount_amount. The dimensions hold attributes: dim_customer has customer_id, name, country, segment, signup_date. dim_product has product_id, name, category, price. Any analyst can write a query like “monthly revenue by product category” by joining the fact to the dimensions and aggregating.
Common Star Schema Tools and Platforms in 2026
2026 data modelling tools that produce star schemas:
dbt
Industry-standard transformation tool for building star schemas in cloud warehouses.
Looker / LookML
Looker explores assume star schema patterns; LookML models map them to a semantic layer.
Cube
Headless BI / semantic layer that consumes star schemas and exposes metrics via APIs.
Holistics AML
Code-based semantic layer modelling on top of star schemas.
Analytify
Reads star-schema dbt models for SaaS embedded analytics with multi-tenant security.
Frequently Asked Questions About Star Schema
What is the difference between a star schema and a snowflake schema?
In a star schema, dimension tables are flat and denormalised. In a snowflake schema, dimensions are normalised into multiple sub-tables. Star schemas are simpler and faster for typical BI queries; snowflake schemas save storage space but require more joins.
Why use a star schema in modern cloud warehouses?
Star schemas optimise for the most common BI query pattern: aggregating facts grouped by dimension attributes. Cloud warehouses are fast enough to handle either pattern, but star schemas remain easier to understand, model in dbt, and consume from BI tools.
What is a fact table?
A table where each row records a measurable business event (a sale, a click, a payment). Fact tables have foreign keys to dimensions plus numerical measures (revenue, quantity).
What is a dimension table?
A table holding descriptive attributes that contextualise facts (customers, products, dates, locations). Dimensions answer “who, what, when, where, why” about the events in fact tables.
Do I need a star schema if I am using a modern data warehouse?
Yes — even though modern warehouses can query any schema fast, the star schema makes data more understandable, BI tools more performant, and metric definitions in the semantic layer simpler to write.
How is a star schema different from a wide table?
A wide table denormalises facts and dimensions into one table — easier to query but harder to maintain (every dimension change requires updating millions of rows). Star schemas keep dimensions separate so a single update to dim_product affects all historical fact rows automatically.