Part of the Analytify BI Glossary, clear definitions of the business intelligence, analytics, and modern data stack terms that matter.
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.
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.
A star schema has two types of tables:
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.
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.
2026 data modelling tools that produce star schemas:
Industry-standard transformation tool for building star schemas in cloud warehouses.
Looker explores assume star schema patterns; LookML models map them to a semantic layer.
Headless BI / semantic layer that consumes star schemas and exposes metrics via APIs.
Code-based semantic layer modelling on top of star schemas.
Reads star-schema dbt models for SaaS embedded analytics with multi-tenant security.
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.
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.
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).
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.
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.
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.