Star Schema vs Snowflake Schema: Which to Use for BI and Product Analytics

A star schema is a data warehouse model in which one central fact table of measurable events, such as orders, joins directly to a set of flat, denormalized dimension tables, such as customer, product and date. A snowflake schema starts from the same fact table but normalizes the dimensions into further tables, so product points to a separate category table, which may point to a department table, and each arm of the star branches out like a snowflake.

The short answer to which one to use: for BI and governed metrics on a modern cloud warehouse, a star schema is the default, and a snowflake schema is the exception for deep, shared hierarchies. For product analytics, neither is the whole answer, because funnels and retention need the raw event table. This guide walks through the anatomy, a worked e-commerce example in both shapes, a comparison table, what changes with columnar warehouses and dbt, and where event data fits.

Anatomy of a star schema

The star schema comes from dimensional modeling, the approach Ralph Kimball set out in The Data Warehouse Toolkit in the 1990s. It has four parts, and the last two matter as much as the first two.

• Fact table. One row per business event or measurement: an order line, a payment, a page view. It holds foreign keys to the dimensions and numeric measures you aggregate, such as quantity, net revenue and cost. Fact tables are long and narrow, and they grow every day.
• Dimension tables. One row per thing that describes a fact: a customer, a product, a date. They hold the attributes people group and filter by, such as country, plan, category and brand. In a star schema each dimension is a single flat table, so a product row carries its category and department name directly.
• Grain. The exact meaning of one row in the fact table, stated before anything else. “One row per order line” and “one row per order” are different tables that produce different metrics. Mixing grains in one table is the most common source of double counting.
• Keys. Each dimension has a primary key, often a surrogate key generated in the warehouse rather than the ID from the source system, and the fact table stores it as a foreign key. Every join from fact to dimension is many-to-one. Surrogate keys also let a dimension keep history: when a customer changes plan, a new row with a new key records the change (a type 2 slowly changing dimension), and old facts still point to the old plan.

A star schema example: e-commerce orders

Take an online store that wants net revenue, orders, average order value and margin by month, country and category. The star schema has one fact table and three dimensions. The grain of the fact table is one row per order line, meaning one product in one order, because product is one of the dimensions and an order can contain several products.

Every question is the fact table plus one join per dimension it needs. Net revenue, orders, average order value and gross margin by month and country:

SELECT
  d.month_start,
  c.country,
  SUM(f.net_revenue)                        AS net_revenue,
  COUNT(DISTINCT f.order_id)                AS orders,
  SUM(f.net_revenue)
    / NULLIF(COUNT(DISTINCT f.order_id), 0) AS avg_order_value,
  SUM(f.net_revenue - f.cost_amount)        AS gross_margin
FROM fact_orders AS f
JOIN dim_customer AS c ON f.customer_key = c.customer_key
JOIN dim_date     AS d ON f.date_key     = d.date_key
WHERE d.year = 2026
GROUP BY d.month_start, c.country
ORDER BY d.month_start, net_revenue DESC;

The grain decides the formula. Because the table has one row per order line, orders must be COUNT(DISTINCT order_id), and average order value must be total revenue divided by distinct orders. AVG(net_revenue) would return the average order line, a smaller and wrong number that looks plausible on a chart. Writing that rule down once, next to the table, is the point of a governed metric, which we come back to below.

The same model as a snowflake schema

In the snowflaked version, the fact table does not change. The product dimension is normalized: brand and category move into their own tables, and category points to a department table. Each name is stored once instead of on every product row.

Renaming a category is now a one-row update, and a category can carry its own attributes without repeating them on thousands of products. The cost lands on every query: revenue by department walks fact to product, product to category and category to department, three joins where the star schema needed one (JOIN dim_product p ON f.product_key = p.product_key, then group by p.department). Anyone writing SQL, and any BI model built on top, has to know that path.

Star schema vs snowflake schema: side-by-side comparison

What changes on a modern columnar warehouse with dbt?

Most star vs snowflake advice was written for row-based databases on expensive storage. Cloud warehouses such as Snowflake, BigQuery, Databricks, Redshift and ClickHouse change three of the trade-offs.

• Storage is cheap and compressed. Columnar storage keeps each column separately and compresses repeated values well. A category column with 40 distinct values across ten million product rows costs very little, which removes the main reason to snowflake.
• Joins are less of a concern, not free. Joining a large fact table to small dimensions is routine for these engines. Joins still cost compute on very large tables, and every extra join is one more thing a person or a tool can get wrong.
• Transformation is code. With dbt, dimensions are rebuilt from staging models on every run, so the update cost of denormalized attributes mostly disappears. Teams often keep normalized tables in staging and flatten them into star-shaped marts.

The same economics made wide, denormalized tables common. One Big Table (OBT) pre-joins the fact table to all its dimensions, so a dashboard or a tool that cannot join reads a single table. An earlier version of this guide called these enriched fact tables: each dimension pre-joined to the fact, so nobody has to research which dimension table holds an attribute. In dbt, it is one model on top of the star:

select
  f.order_line_id,
  f.order_id,
  d.date_day,
  d.month_start,
  c.customer_id,
  c.country,
  c.signup_channel,
  p.product_name,
  p.brand,
  p.category,
  f.quantity,
  f.net_revenue,
  f.cost_amount
from {{ ref('fact_orders') }} as f
left join {{ ref('dim_customer') }} as c on f.customer_key = c.customer_key
left join {{ ref('dim_product') }}  as p on f.product_key  = p.product_key
left join {{ ref('dim_date') }}     as d on f.date_key     = d.date_key

Wide tables answer a known set of questions quickly. They do not replace the star schema as the place where metrics are defined. Each wide table is a copy of the joins at one grain, and teams that build many of them end up with one revenue calculation per table, which is the problem dimensional modeling was meant to solve. A wide table also fixes the dimension attributes at build time, so adding a new attribute means rebuilding it.

The common pattern on a modern stack is a star schema as the governed core, with wide tables generated from it where a consumer needs one.

Where event data fits?

Product analytics adds a different kind of fact table: the event table. Each row is one thing a user did, such as page_viewed, product_added_to_cart or checkout_completed, with a user ID, a timestamp, an event name and properties. In star schema terms, it is a fact table at event grain. The earlier version of this guide used exactly that shape: a page_events fact table with page_visit and element_clicked_on_page events, an item_id foreign key to an items dimension, and a country code foreign key to a countries dimension.

The dimensions around an event table are mostly about who did it: a user dimension (signup date, country, acquisition channel) and, for B2B products, an account dimension (plan, industry, seat count). Product, page or content dimensions join on keys carried in event properties. Modeling events in the data warehouse covers the choice between one wide events table and one table per event type.

What matters is that the event grain stays. A BI fact table is often pre-aggregated, or kept at transaction grain, because the questions are sums over time. Product analytics questions are about sequences per user:

• Funnels need each user's events in order, with timestamps, to check that added to cart came after viewed product and that checkout happened within a conversion window.
• Retention needs each user's first event to place them in a cohort, and every later event to see which weeks they came back.
• Journeys need the full path of events between two points.

A daily table of orders by product cannot answer any of these, because aggregation throws away who did what and when. So a warehouse that serves both BI and product analytics usually has both: star-shaped marts such as fact_orders for KPIs, and the event table at event grain, joined to the same user and account dimensions. Sharing those dimensions, and the customer key between them, is what lets a revenue number be traced back to the users and journeys behind it.

Star schemas and defining metrics once

A star schema makes data easy to join. It does not, on its own, make metrics consistent. As a company grows, the number of fact and dimension tables grows with it, and knowing which table holds an attribute and how to join to it becomes tribal knowledge that slows every ad hoc question. Every analyst who writes average order value from scratch has another chance to use AVG on the wrong grain, or to forget that refunds are subtracted.

The fix is to define each metric once, on the fact table it belongs to, with the dimensions it can be broken down by. That is a semantic layer, and a star schema is the shape semantic layers handle best: one fact table at a declared grain, many-to-one joins to dimensions, and no join that can multiply rows and inflate a sum. Net revenue becomes sum of net_revenue on fact_orders; average order value becomes net revenue divided by distinct order_id; country and category become dimensions any metric can use. A snowflake schema works too, but every hierarchy level is another relationship to declare and test.

When to choose which?

• Choose a star schema for BI marts, KPI dashboards and anything with governed metrics. It is the default on a columnar warehouse with dbt, and the shape BI tools and semantic layers expect.
• Choose a snowflake schema for a dimension with a deep hierarchy that several other dimensions share (a product taxonomy or a geography maintained by another team), for a very large dimension where repeated attributes really cost something, or when the source is already normalized and you only need it in staging. You can snowflake one dimension and keep the rest flat.
• Add wide tables or One Big Table on top of the star for a specific dashboard, an export, a machine learning feature set, or a tool that cannot join. Generate them from the star schema so the metric logic stays in one place.
• Keep an event table at event grain for product analytics. Join it to the same user and account dimensions as your marts, and do not replace it with aggregated facts.

Using a star schema in Mitzu

Mitzu is an analytics platform on your data warehouse for BI, product analytics and experiment analysis, where the data team governs the metrics and everyone uses them through dashboards, an AI agent, Slack and their own AI assistants. Your data team defines metrics such as net revenue, orders, average order value and margin once on its own fact and dimension tables, and Mitzu can draft those definitions from the warehouse and dbt models for the team to review. People build KPI dashboards by clicking or by asking the agent, on the same metrics, and drill into the user journeys behind every number, because the event tables sit in the same warehouse. Queries run in your warehouse, and Mitzu reads dbt-modeled tables the same way it reads raw event streams. The business intelligence page shows how it works.

Start free on your warehouse, or book a demo and see it on your own data.

FAQ

Is star schema still relevant?

Yes. Columnar cloud warehouses made storage cheap and joins less costly, which weakened the case for snowflake schemas, not for star schemas. Many dbt projects build star-shaped marts with fact and dimension tables, and semantic layers and BI tools are designed around that shape. What changed is that wide tables are now often built on top of the star for specific dashboards.

Star schema vs one big table: which is better?

They do different jobs. One Big Table pre-joins a fact table to all its dimensions, so it is fast and simple for a known set of questions and for tools that cannot join. A star schema keeps facts and dimensions separate, so new questions, new attributes and history are cheap to add and each metric is defined once. Most teams use the star schema as the governed model and generate wide tables from it where needed.

Can BI tools use a snowflake schema?

Yes. BI tools and semantic layers can model a snowflake schema by declaring each relationship, such as product to category and category to department. It takes more modeling work, more joins at query time and more care to avoid many-to-many paths, and business users see more tables. Many teams flatten snowflaked dimensions in a dbt model before exposing them to a BI tool.

What is the difference between a fact table and a dimension table?

A fact table records events or measurements, one row per order line, payment or user event, with numeric measures and foreign keys. A dimension table describes the things involved, one row per customer, product or date, with the attributes you group and filter by. Facts are long and grow constantly; dimensions are shorter and change slowly.

Is a star schema normalized or denormalized?

A star schema has denormalized dimensions: each dimension is one flat table, so hierarchy attributes such as category and department repeat on every product row. The fact table itself holds keys and measures, not descriptive attributes. A snowflake schema is the normalized alternative, splitting those hierarchies into separate tables.

Is the snowflake schema related to the Snowflake data warehouse?

No. The snowflake schema is a modeling pattern named for the shape of its diagram and is older than the company. The Snowflake data platform runs star schemas, snowflake schemas and wide tables, and star-shaped marts are a common choice on it.