Structuring Data for Analysis
Once data lands in the warehouse, you need a logical organization that makes queries fast and intuitive. Two dominant patterns exist: the Star Schema and the Snowflake Schema. Both are built around the same core idea — separating what happened (facts) from who or what was involved (dimensions).
The Star Schema
The Star Schema, popularized by Ralph Kimball in the 1990s, remains the industry standard for analytical data modeling. It has two types of tables:
Fact Table: The center of the star. Contains measurable events — one row per business event (a sale, a page view, a payment). Fact tables are wide and extremely long — billions of rows is normal. They store:
- Numeric metrics (the things you measure): amount_sold, duration_seconds, quantity
- Foreign keys pointing to dimension tables: product_id, store_id, date_id
Dimension Tables: The points of the star. Contain descriptive context about the entities involved in each event. They are narrow and short — thousands to millions of rows. Examples:
- dim_products: product_id, product_name, category, brand
- dim_stores: store_id, store_name, city, region, country
- dim_dates: date_id, day_of_week, month, quarter, is_holiday
The philosophy is deliberate de-normalization. We store region directly in dim_stores even though "North America" appears 500 times. We accept this redundancy because it eliminates joins. Every analytical query becomes a simple pattern: join the fact table to one or more dimension tables, filter on dimension attributes, aggregate a metric.
-- "Total sales by product category in Q4 2023"
SELECT
p.category,
SUM(f.amount_sold) AS total_revenue
FROM fact_sales f
JOIN dim_products p ON f.product_id = p.product_id
JOIN dim_dates d ON f.date_id = d.date_id
WHERE d.quarter = 4 AND d.year = 2023
GROUP BY p.category
ORDER BY total_revenue DESC;
This query is readable, predictable, and fast. The optimizer knows exactly which columns to scan.
The Snowflake Schema
The Snowflake Schema normalizes the dimension tables, breaking them into hierarchies. Instead of storing category inside dim_products, you create a separate dim_categories table and join to it.
fact_sales → dim_products → dim_categories → dim_subcategories
Claimed benefit: Eliminates redundancy, saves storage space.
Actual trade-off in practice:
- Storage savings are negligible. A dim_products table with 1 million rows and a 30-character category column uses ~30 MB. That is rounding error in a petabyte warehouse.
- Every query that needs category information now requires an additional JOIN through the hierarchy. On a fact table with 10 billion rows, extra joins add measurable latency.
- Query complexity increases. Analysts must understand the full join path to write correct queries.
Verdict: In modern cloud warehouses where storage costs pennies per GB, the Snowflake Schema's storage savings are irrelevant. Star Schema is the default choice for performance, simplicity, and analyst ergonomics. Use Snowflake Schema only when you have a genuine, measured storage problem — which is rare.