The Star Schema

Concepts covered: Star Schema, Snowflake Schema

A star schema places 1 fact table at the centre and a ring of dimension tables around it. At Ridgeline Outfitters the centre is fact_sales, and its grain is 1 row per order line item: 1 product on 1 order, sold to 1 customer on 1 date through 1 channel. Each row carries 4 foreign keys, 2 identifiers and 4 measures: quantity, unit_price, discount_amount and line_amount. Everything descriptive about the customer, the product, the date and the channel lives in the dimension the key points to.

The 2 kinds of table have opposite shapes. fact_sales is tall and narrow: 840 million rows over 3 years and 10 columns, every column a key or a numeric measure. dim_customer is short and wide: 4.2 million rows and 31 columns, among them city, region, loyalty_tier and acquisition_channel. dim_product holds 38,000 rows, dim_channel 16, and dim_date 36,525, 1 per calendar day for 100 years. Facts hold the numbers and dimensions hold the words.

fact_sales at the centre with its 4 dimensions. Every edge is an integer foreign key joined on equality.

fact_sales: order_date_sk int FK, customer_sk bigint FK, product_sk bigint FK, channel_sk bigint FK, order_id bigint, line_seq int, quantity int, unit_price decimal(10,2), discount_amount decimal(10,2), line_amount decimal(10,2)
dim_customer: customer_sk bigint PK, customer_id varchar, first_name varchar, city varchar, region varchar, loyalty_tier varchar, acquisition_channel varchar
dim_product: product_sk bigint PK, sku varchar, product_name varchar, brand varchar, category_name varchar, department_name varchar
dim_date: date_sk int PK, calendar_date date, month_name varchar, calendar_year int, fiscal_quarter varchar, is_holiday boolean
dim_channel: channel_sk bigint PK, channel_name varchar, store_region varchar

fact_sales.customer_sk references dim_customer.customer_sk
fact_sales.product_sk references dim_product.product_sk
fact_sales.order_date_sk references dim_date.date_sk
fact_sales.channel_sk references dim_channel.channel_sk

Why the shape is fast to query

Every analytical query walks the same path: start at the fact table, filter and group through the dimensions, aggregate the measures. Units sold by brand and month during 2025 reads the 2025 partitions of fact_sales, joins dim_product for brand and dim_date for month_name, and sums quantity. The warehouse returns 1,100 rows in 1.8 seconds.

SELECT p.brand, d.month_name, SUM(f.quantity) AS units
FROM fact_sales f
JOIN dim_product p ON p.product_sk = f.product_sk
JOIN dim_date d ON d.date_sk = f.order_date_sk
WHERE d.calendar_year = 2025
GROUP BY p.brand, d.month_name;

Columnar storage: the engine reads the 3 columns the query names out of 10, and never touches the other 7.
Partition pruning: fact_sales is partitioned by order date, so a 2025 filter skips 2 of the 3 years on disk.
Broadcast joins: a 38,000-row dimension is copied to every worker and probed in memory during the fact scan.
Predicate pushdown: a filter on brand becomes a list of product keys applied to fact rows as they are read.

The broadcast join is the mechanism worth holding onto. Joining 840 million fact rows to 38,000 product rows costs about as much as scanning the fact table once, because every worker probes its own in-memory copy of the dimension. Ridgeline's operational schema answers the same question through 11 joins across normalized product, brand, supplier, category and department tables, each resolved before a row can be summed, and runs for 9 minutes.

Star against snowflake

A snowflake schema keeps dimensions normalized: dim_product carries a category_id pointing to dim_category, which carries a department_id pointing to dim_department. A star copies category_name and department_name onto every product row, storing 'Footwear' 6,400 times instead of once for about 50 KB. The snowflake makes any department filter 2 joins deeper, and every analyst has to remember which of 3 tables holds the category name.

Snowflaking earns its place in 2 situations: a fact table that lives at a hierarchy level, such as marketing spend per category, which needs a category table to own category_sk; and a dimension where a repeated 2 KB description column would dominate storage. A 38,000-row product table with a 12-character category name is neither.

Ridgeline's merchandising team renames a category about twice a year. An analyst proposes moving category_name out of dim_product into a new dim_category, so a rename updates 1 row instead of 6,400. Which design serves the warehouse best?

Snowflake it: a rename touches 1 row, and the extra join is small.
Keep category_name on dim_product and let the load rewrite the 6,400 rows when a rename arrives.
Put category_id on fact_sales directly, so renames never touch a dimension.
Keep both: a dim_category table and the copied name on dim_product.

About this lesson

This section is part of the Star Schemas lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.