Part 5 of 6 · 3 min read
Star and snowflake schemas
Star schemas and snowflake schemas
Think of a star schema as a wheel: one fact table in the hub, with each dimension table as a spoke attached directly to it. A snowflake schema is the same wheel, except some spokes branch into smaller spokes of their own.
In plain words: A star schema is a fact table surrounded directly by its dimension tables, each joined to the fact table by exactly one foreign key, with no further joins needed to describe a dimension. A snowflake schema breaks a dimension table itself into further levels; for example, splitting book_dim so that genre moves into its own small genre_dim table, referenced by book_dim. Snowflaking reduces repeated text and keeps each dimension table smaller, at the cost of extra joins on every analytical query. Most warehouses favor the star shape because analytical queries run faster with fewer joins, and the extra space used by repeating a genre name on every book row is cheap compared to query speed.
A worked example. In a star schema, finding total sales by genre joins sales_fact to book_dim once and groups by genre. In a snowflake version, the same query must also join book_dim to genre_dim, an extra join that adds cost on every run, for a modest saving in stored space.
The key lines.
-- Star schema: one join from fact to dimension
SELECT b.genre, SUM(f.quantity * f.price_dollars) AS revenue
FROM sales_fact f
JOIN book_dim b ON f.book_id = b.book_id
GROUP BY b.genre;
-- Snowflake schema: an extra join through a genre dimension
SELECT g.genre_name, SUM(f.quantity * f.price_dollars) AS revenue
FROM sales_fact f
JOIN book_dim b ON f.book_id = b.book_id
JOIN genre_dim g ON b.genre_id = g.genre_id
GROUP BY g.genre_name;
Star schema, snowflake schema
A star schema is a warehouse design with one central fact table joined directly to a set of dimension tables, each by a single foreign key. A snowflake schema is the same design with one or more dimension tables further broken into their own smaller dimension tables, adding levels and extra joins.
Example: splitting book_dim's genre column into a separate genre_dim table, as shown above, turns a star schema into a snowflake schema.
Quick notes
- A data warehouse copies data from multiple operational sources into one unified schema, kept separate so analytics does not compete with live transactions.
- ETL extracts from sources, transforms (cleans, deduplicates, converts) the data, and loads it into the warehouse; ELT loads first and transforms afterward.
- Warehouse data is usually refreshed on a schedule, so it lags behind the live systems by some hours.
- A fact table holds events, split into measure attributes (aggregated numbers) and dimension attributes (foreign keys to dimension tables).
- A star schema joins the fact table directly to each dimension table; a snowflake schema adds extra levels of dimension tables, trading smaller storage for more joins per query.