SQL 114 🛢️ Data Warehousing Concepts: Star Schema and Snowflake Schema

The choice between a star schema and a snowflake schema is a trade-off between query simplicity and storage normalization, and the trade-off is almost always decided in favor of the star schema for analytical workloads. A star schema has a single fact table surrounded by denormalized dimension tables, one per dimension. A snowflake schema normalizes those dimension tables into hierarchies, splitting a product dimension into product, subcategory, and category tables. The snowflake saves storage and enforces referential integrity; the star saves joins and makes the schema navigable by business analysts who write their own SQL. The storage savings from normalization are negligible compared to the size of the fact table, which is why the dimensional modeling literature generally discourages snowflaking .

The performance difference is measurable but workload-dependent. A star schema requires fewer joins for the same aggregation, so it tends to be faster for hierarchy-traversing queries. At low row counts, the difference is negligible. At high row counts with insufficient work_mem, the snowflake’s additional joins increase the likelihood of hash tables spilling to disk, and the performance gap widens . The snowflake’s advantage is data integrity: foreign-key constraints prevent inconsistent dimension hierarchies, and normalization reduces update anomalies when a dimension attribute changes . The practical recommendation from modern data engineering is a hybrid: use a star schema in the gold layer for BI consumption, and keep normalized or Data Vault structures in the silver layer for governance and auditability .

This chapter covers the star and snowflake schemas, their table structures, the query and storage consequences of each, and the decision criteria that determine which pattern fits a given subject area. It assumes familiarity with basic dimensional modeling concepts and SQL joins.

Key point: The star schema is the default for analytical consumption because it minimizes joins and matches how BI tools and analysts expect to query. The snowflake schema is for dimensions with deep, static hierarchies where referential integrity matters more than query simplicity. The storage savings from normalization are negligible compared to the fact table, so the decision should be driven by query complexity and governance, not by disk space.

---

Why the two schemas exist as distinct patterns

Dimensional modeling needs a shape that BI tools can navigate. A star schema has one fact table and one table per dimension. A query that aggregates sales by product category joins the fact table to the product dimension and filters on the category attribute. The join is a single hop, the dimension is wide but flat, and the query is easy to write and easy for the optimizer to execute . This is the shape that most BI tools and most analysts expect.

Normalization addresses redundancy, but the redundancy is in the dimension, not the fact. A product dimension with columns for product, subcategory, and category repeats the category name for every product in that category. Normalizing into three tables eliminates the repetition. The storage savings are real but small: a product dimension with 20,000 rows and two VARCHAR(100) attributes uses roughly 4 MB. Normalizing into a category table and a subcategory table reduces that to under 50 KB, which is less than one percent of total storage for a schema with a properly sized fact table . The fact table, which stores integer foreign keys in both schemas, dominates the storage.

The snowflake enforces hierarchy integrity. In a star schema, the relationship between product, subcategory, and category is implicit. The ETL job ensures that valid combinations are loaded, but the schema itself does not prevent an invalid combination . In a snowflake schema, the subcategory table has a foreign key to the category table, and the product table has a foreign key to the subcategory table. The database enforces the hierarchy. This matters in regulated domains where auditability and referential integrity are requirements .

The trade-off. The snowflake adds joins to every query that traverses the hierarchy. A query that aggregates by category must join the fact table to product, then product to subcategory, then subcategory to category. Each join adds cost, and the cost is borne by every query, not just the ones that need the hierarchy. The star schema pays the storage cost of denormalization once and amortizes it across all queries. The dimensional modeling literature consistently recommends the star schema for this reason, while acknowledging that snowflaking is common in practice because developers find it closer to the ER models of source systems .

---

a. The star schema: one fact, flat dimensions

A star schema has a central fact table containing foreign keys to each dimension and the numeric measures that the business aggregates. The dimension tables are denormalized: each attribute of a dimension lives in the same table, including hierarchical attributes like category and subcategory.

-- Star schema: sales fact with flat product dimension.
CREATE TABLE fact_sales (
    sale_id        BIGINT PRIMARY KEY,
    product_key    INT REFERENCES dim_product(product_key),
    date_key       INT REFERENCES dim_date(date_key),
    store_key      INT REFERENCES dim_store(store_key),
    quantity       INT NOT NULL,
    revenue        DECIMAL(12,2) NOT NULL
);

-- Denormalized product dimension: hierarchy in one table.
CREATE TABLE dim_product (
    product_key     INT PRIMARY KEY,
    product_name    VARCHAR(100),
    subcategory     VARCHAR(50),
    category        VARCHAR(50),
    brand           VARCHAR(50)
);

A query that aggregates revenue by category is a single join:

SELECT p.category, SUM(f.revenue)
FROM fact_sales f
JOIN dim_product p ON f.product_key = p.product_key
GROUP BY p.category;

The optimizer performs one hash join. The dimension is small relative to the fact table, so the hash table fits in memory, and the fact table is scanned once. This is the pattern that star schemas are designed to produce, and it is why they are the preferred shape for read-heavy analytical workloads .

---

b. The snowflake schema: normalized dimensions

A snowflake schema normalizes the dimension tables into a hierarchy. The product dimension becomes three tables: dim_product, dim_subcategory, and dim_category. Each table has a foreign key to the next level.

-- Snowflake schema: normalized product hierarchy.
CREATE TABLE dim_category (
    category_key    INT PRIMARY KEY,
    category_name   VARCHAR(50)
);

CREATE TABLE dim_subcategory (
    subcategory_key INT PRIMARY KEY,
    subcategory_name VARCHAR(50),
    category_key    INT REFERENCES dim_category(category_key)
);

CREATE TABLE dim_product (
    product_key     INT PRIMARY KEY,
    product_name    VARCHAR(100),
    brand           VARCHAR(50),
    subcategory_key INT REFERENCES dim_subcategory(subcategory_key)
);

The same aggregation now requires three joins:

SELECT c.category_name, SUM(f.revenue)
FROM fact_sales f
JOIN dim_product p ON f.product_key = p.product_key
JOIN dim_subcategory s ON p.subcategory_key = s.subcategory_key
JOIN dim_category c ON s.category_key = c.category_key
GROUP BY c.category_name;

The query is longer, and the optimizer has more join ordering decisions to make. At high row counts, the additional joins increase the chance that intermediate hash tables exceed work_mem and spill to disk. Increasing work_mem narrows the gap, but the gap exists because the snowflake shape requires more work for the same result .

---

c. The decision criteria and the hybrid pattern

The choice is not binary across an entire warehouse. Different subject areas can use different patterns. A sales mart might use a star schema because the product hierarchy is shallow and the queries are frequent. An inventory mart might use a snowflake because the location hierarchy is deep and the dimension is updated frequently .

Factor	Favors Star	Favors Snowflake
Query simplicity	Fewer joins, analyst-friendly	More joins, developer-friendly
Storage	Denormalized dimension uses more space	Normalized dimension saves space
Hierarchy depth	Shallow (1–2 levels)	Deep (3+ levels)
Dimension size	Small to medium	Large with high-cardinality attributes
Update frequency	Rarely updated dimensions	Frequently updated hierarchies
Governance	Lower regulatory burden	Referential integrity required

The modern recommendation is a hybrid: keep a normalized or Data Vault structure in the silver layer for lineage and auditability, and build star-schema marts in the gold layer for BI consumption . The star schema is the consumption interface; the normalized structure is the governance layer. This pattern gives analysts the flat tables they need without sacrificing the integrity of the underlying data.

---

Complete Example Session

-- ============================================
-- PART 1: STAR SCHEMA — FACT AND DIMENSION
-- ============================================
CREATE TABLE fact_sales (
    sale_id     BIGINT PRIMARY KEY,
    product_key INT REFERENCES dim_product(product_key),
    date_key    INT REFERENCES dim_date(date_key),
    revenue     DECIMAL(12,2) NOT NULL
);

CREATE TABLE dim_product (
    product_key  INT PRIMARY KEY,
    product_name VARCHAR(100),
    subcategory  VARCHAR(50),
    category     VARCHAR(50)
);

-- ============================================
-- PART 2: STAR QUERY — ONE JOIN
-- ============================================
SELECT p.category, SUM(f.revenue)
FROM fact_sales f
JOIN dim_product p ON f.product_key = p.product_key
GROUP BY p.category;

-- One hash join. Dimension fits in memory.
-- Fact table scanned once.

-- ============================================
-- PART 3: SNOWFLAKE SCHEMA — NORMALIZED
-- ============================================
CREATE TABLE dim_category (
    category_key  INT PRIMARY KEY,
    category_name VARCHAR(50)
);

CREATE TABLE dim_subcategory (
    subcategory_key  INT PRIMARY KEY,
    subcategory_name VARCHAR(50),
    category_key     INT REFERENCES dim_category(category_key)
);

CREATE TABLE dim_product (
    product_key     INT PRIMARY KEY,
    product_name    VARCHAR(100),
    subcategory_key INT REFERENCES dim_subcategory(subcategory_key)
);

-- ============================================
-- PART 4: SNOWFLAKE QUERY — THREE JOINS
-- ============================================
SELECT c.category_name, SUM(f.revenue)
FROM fact_sales f
JOIN dim_product p ON f.product_key = p.product_key
JOIN dim_subcategory s ON p.subcategory_key = s.subcategory_key
JOIN dim_category c ON s.category_key = c.category_key
GROUP BY c.category_name;

-- More joins. More optimizer decisions.
-- At scale, hash tables may spill to disk.

-- ============================================
-- PART 5: STORAGE COMPARISON
-- ============================================
-- Star: dim_product 20,000 rows × 2 VARCHAR(100) ≈ 4 MB.
-- Snowflake: dim_category (5 rows) + dim_subcategory (2,800 rows)
--            ≈ 50 KB.
-- Savings: < 1% of total storage for the schema.
-- The fact table dominates in both.

-- ============================================
-- PART 6: INTEGRITY COMPARISON
-- ============================================
-- Star: ETL enforces valid category-subcategory-product combos.
--       Schema does not prevent invalid combos.
-- Snowflake: Foreign keys prevent orphan subcategories.
--            Referential integrity enforced by the database.

-- ============================================
-- PART 7: THE HYBRID PATTERN
-- ============================================
-- Silver layer: normalized 3NF or Data Vault.
--   → Lineage, auditability, governance.
-- Gold layer: star-schema marts.
--   → BI consumption, dashboards, analyst queries.

-- ============================================
-- PART 8: MIGRATION FROM SNOWFLAKE TO STAR
-- ============================================
-- Create denormalized view of the snowflake hierarchy.
-- Validate row counts and values.
-- Create physical star table from the view.
-- Swap the table in a transaction.
-- Drop normalized lookup tables.

-- ============================================
-- PART 9: WHEN SNOWFLAKE IS CORRECT
-- ============================================
-- Deep hierarchy (> 3 levels).
-- Large dimension with high-cardinality attributes.
-- Frequent updates to hierarchy attributes.
-- Regulatory requirement for referential integrity.

-- ============================================
-- PART 10: THE COMPLETE FLOW
-- ============================================
-- 1. Fact table stores keys + measures.
-- 2. Star: flat dimensions, fewer joins.
-- 3. Snowflake: normalized dimensions, more joins.
-- 4. Storage savings are negligible vs fact table.
-- 5. Star for BI consumption, snowflake for integrity.
-- 6. Hybrid: normalized silver, star gold.

---

Quick Reference

Schema Comparison

Aspect	Star Schema	Snowflake Schema
Dimension shape	Denormalized (flat)	Normalized (hierarchy)
Joins per query	One per dimension	One per level of hierarchy
Storage	Higher (redundant attributes)	Lower (normalized)
Query simplicity	High	Lower
Referential integrity	Enforced by ETL	Enforced by foreign keys
BI tool support	Broad	Some tools require flattening

Decision Factors

Factor	Star	Snowflake
Shallow hierarchy	✓
Deep hierarchy		✓
Read-heavy analytics	✓
Frequent hierarchy updates		✓
Analyst-written SQL	✓
Regulatory auditability		✓

Storage Reality

Component	Star	Snowflake
Fact table	Integer keys	Integer keys
Dimension	4 MB (20k rows)	50 KB (normalized)
Total storage impact	Negligible	Negligible

---

Best Practices

✅ Do This:

-- Use star schema for BI marts and dashboards.              // ✅
-- Flat dimensions, one join per dimension.

-- Normalize only deep, static hierarchies.                  // ✅
-- 3+ levels with high-cardinality attributes.

-- Keep fact tables with integer foreign keys.               // ✅
-- Both schemas share this design.

-- Use hybrid: normalized silver, star gold.                 // ✅
-- Governance in the lower layer, usability in the upper.

❌ Don’t Do This:

-- Snowflake a shallow dimension "for normalization."        // ❌
-- Adds joins with negligible storage savings.

-- Use a star schema with implicit hierarchy integrity.      // ❌
-- ETL must enforce valid combinations.

-- Assume snowflake is always slower.                        // ❌
-- At low row counts, the difference is negligible.

-- Denormalize everything into one giant dimension.          // ❌
-- Update anomalies; redundant storage.

---

Common Pitfalls

Pitfall	Why It Happens	Fix
Snowflake joins spill to disk	Insufficient work_mem	Raise work_mem or use star
Star schema update anomalies	Redundant attributes not synchronized	ETL validation or snowflake
BI tool cannot traverse hierarchy	Tool expects flat dimension	Create a flattened view
Storage savings negligible	Fact table dominates size	Choose based on query simplicity

---

Real-World Examples

1. Star Fact Table

CREATE TABLE fact_sales (sale_id, product_key, date_key, revenue);

2. Star Dimension

CREATE TABLE dim_product (product_key, name, subcategory, category);

3. Snowflake Hierarchy

CREATE TABLE dim_category (category_key, name);
CREATE TABLE dim_subcategory (subcategory_key, name, category_key);

4. Star Query

SELECT p.category, SUM(f.revenue) FROM fact_sales f JOIN dim_product p USING (product_key) GROUP BY p.category;

5. Snowflake Query

SELECT c.name, SUM(f.revenue) FROM fact_sales f
JOIN dim_product p USING (product_key)
JOIN dim_subcategory s USING (subcategory_key)
JOIN dim_category c USING (category_key)
GROUP BY c.name;

6. Flattened View for BI

CREATE VIEW v_dim_product_flat AS
SELECT p.*, s.name AS subcategory, c.name AS category
FROM dim_product p
JOIN dim_subcategory s USING (subcategory_key)
JOIN dim_category c USING (category_key);

7. Storage Check

SELECT pg_size_pretty(pg_total_relation_size('dim_product'));

8. Work Mem Tuning

SET work_mem = '256MB';

9. Hybrid Layer

-- Silver: 3NF normalized. Gold: star schema mart.

10. Migration View

CREATE VIEW v_flat_dim AS SELECT ... -- Denormalized from snowflake.

---

Visual: Star vs Snowflake

┌──────────────────────────────────────────────┐
│  STAR SCHEMA                                 │
│                                              │
│         dim_date    dim_product              │
│              \        /                      │
│               \      /                       │
│            fact_sales                        │
│               /      \                       │
│              /        \                      │
│         dim_store    dim_customer            │
│                                              │
│  One join per dimension.                     │
│  Dimensions are flat.                        │
│                                              │
│  SNOWFLAKE SCHEMA                            │
│                                              │
│         dim_date    dim_product              │
│                        │                     │
│                   dim_subcategory            │
│                        │                     │
│                   dim_category               │
│                                              │
│  Fact → product → subcategory → category.    │
│  Three joins to reach category.              │
│                                              │
└──────────────────────────────────────────────┘

Storage Reality

┌──────────────────────────────────────────────┐
│  STORAGE COMPARISON                          │
│                                              │
│  Fact table: dominates (millions of rows).   │
│                                              │
│  Star dimension: 4 MB (20k rows, 2 VARCHAR). │
│  Snowflake: 50 KB (normalized).              │
│                                              │
│  Savings: < 1% of total schema storage.      │
│                                              │
│  The join cost is paid on every query.       │
│  The storage savings are paid once.          │
│                                              │
└──────────────────────────────────────────────┘

Decision Flow

┌──────────────────────────────────────────────┐
│  STAR OR SNOWFLAKE?                          │
│                                              │
│  Hierarchy depth?                            │
│       ├── 1–2 levels → Star                  │
│       └── 3+ levels → Consider snowflake     │
│                                              │
│  Query frequency?                            │
│       ├── High (dashboards) → Star           │
│       └── Low (ad hoc) → Either              │
│                                              │
│  Integrity requirement?                      │
│       ├── Regulatory → Snowflake (FKs)       │
│       └── Standard → Star (ETL validation)   │
│                                              │
│  Default for BI: Star.                       │
│                                              │
└──────────────────────────────────────────────┘

---

Summary

Item	Value
Star schema	Flat dimensions, one join per dimension
Snowflake schema	Normalized dimensions, one join per hierarchy level
Storage savings	Negligible vs fact table size
Query cost	Snowflake adds joins; gap widens at scale
Integrity	Snowflake enforces via foreign keys; star via ETL
Default for BI	Star schema
Hybrid pattern	Normalized silver, star gold

Key takeaways:

• The star schema minimizes joins by denormalizing dimensions. Each dimension is a single table, so a query that aggregates by category is one join. This is the shape that BI tools and analysts expect, and it is why the dimensional modeling literature recommends it for analytical consumption .
• The snowflake normalizes dimensions into hierarchies. A product dimension becomes product, subcategory, and category tables. The storage savings are real but small—less than one percent of total schema storage in a typical fact-table-dominated warehouse .
• The snowflake enforces referential integrity. Foreign keys between hierarchy levels prevent orphan subcategories and inconsistent category assignments. This matters in regulated domains where auditability is a requirement .
• The performance gap is workload-dependent. At low row counts, the difference is negligible. At high row counts with insufficient work_mem, the snowflake’s additional joins increase the chance of disk spill and widen the gap .
• The hybrid pattern is the modern recommendation. Keep normalized or Data Vault structures in the silver layer for lineage and governance, and build star-schema marts in the gold layer for BI consumption .
• Snowflaking is discouraged for shallow hierarchies. The additional joins add cost to every query, while the storage savings are negligible. Normalize only when the hierarchy is deep, static, and integrity-critical.

Remember: The star schema and snowflake schema are two shapes for the same data. The star flattens dimensions to minimize joins; the snowflake normalizes dimensions to minimize redundancy and enforce integrity. The storage savings from normalization are negligible compared to the fact table, so the decision should be driven by query simplicity and governance requirements, not by disk space. The star schema is the default for BI consumption because it matches how analysts write SQL and how tools expect to query. The snowflake is for deep, static hierarchies where referential integrity matters more than join count. The hybrid pattern—normalized silver, star gold—gives both: the integrity of normalization where it matters, and the usability of denormalization where analysts live.

---

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!