Snowflake Schema Design
(Note: The "Snowflake Schema" is a data modeling concept invented decades ago. It has nothing to do with Snowflake the cloud platform, other than sharing the same name!)
While the Star Schema focuses on denormalization (keeping dimensions flat and wide for query speed), the Snowflake Schema focuses on normalization (breaking dimensions down into smaller sub-dimensions to save storage space).
Topics Covered
- Star Schema vs Snowflake Schema
- Normalizing Dimension Tables
- When Normalization Helps vs Hurts
- Performance Implications in the Cloud
1. Star Schema vs Snowflake Schema
To understand the Snowflake Schema, you must understand what it is fixing.
In a traditional Star Schema for an Indian retail store, the DIM_STORE table is fully denormalized. It contains the store name, the city name, the state name, and the region name all in one row.
| STORE_ID | STORE_NAME | CITY | STATE | REGION |
|---|---|---|---|---|
| 1 | Metro Mart | Mumbai | Maharashtra | West |
| 2 | Smart Bazaar | Pune | Maharashtra | West |
Notice the repetition? The strings "Maharashtra" and "West" are stored redundantly on every single row for every store in that state.
In a Snowflake Schema, you "normalize" this dimension. You break it into three separate tables: DIM_STORE, DIM_CITY, and DIM_STATE.
The DIM_STORE table only holds a CITY_ID. The DIM_CITY table holds a STATE_ID. This creates a structure that looks like a snowflake—a central fact table branching out into dimensions, which branch out into sub-dimensions.
2. Hands-On: Normalizing a Dimension
Let's build a mini Snowflake Schema for our retail store.
Database Setup
(Run this in your Snowflake worksheet)
USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS BEEMS_RETAIL_DB;
USE SCHEMA BEEMS_RETAIL_DB.PUBLIC;
-- 1. Create the outermost sub-dimension (State)
CREATE OR REPLACE TABLE DIM_STATE (
STATE_ID INT,
STATE_NAME STRING,
REGION STRING
);
-- 2. Create the intermediate dimension (City)
CREATE OR REPLACE TABLE DIM_CITY (
CITY_ID INT,
CITY_NAME STRING,
STATE_ID INT -- Foreign Key to DIM_STATE
);
-- 3. Create the innermost dimension (Store)
CREATE OR REPLACE TABLE DIM_STORE (
STORE_ID INT,
STORE_NAME STRING,
CITY_ID INT -- Foreign Key to DIM_CITY
);
-- 4. Create the Fact Table
CREATE OR REPLACE TABLE FACT_SALES (
ORDER_ID INT,
STORE_ID INT, -- Foreign Key to DIM_STORE
AMOUNT NUMBER(10,2)
);
-- Insert Mock Data
INSERT INTO DIM_STATE VALUES (1, 'Maharashtra', 'West'), (2, 'Karnataka', 'South');
INSERT INTO DIM_CITY VALUES (10, 'Mumbai', 1), (20, 'Bengaluru', 2);
INSERT INTO DIM_STORE VALUES (100, 'Metro Mart', 10), (200, 'Smart Bazaar', 20);
INSERT INTO FACT_SALES VALUES (1000, 100, 5000), (1001, 200, 12000);
Querying the Snowflake Schema
To answer a simple business question like "What is the total revenue for Maharashtra?", an analyst must now execute a massive 4-table join.
SELECT
st.STATE_NAME,
SUM(f.AMOUNT) AS TOTAL_REVENUE
FROM FACT_SALES f
JOIN DIM_STORE s ON f.STORE_ID = s.STORE_ID
JOIN DIM_CITY c ON s.CITY_ID = c.CITY_ID
JOIN DIM_STATE st ON c.STATE_ID = st.STATE_ID
WHERE st.STATE_NAME = 'Maharashtra'
GROUP BY st.STATE_NAME;
3. When Normalization Helps vs Hurts
In the 1990s, when hard drive storage cost thousands of dollars per gigabyte, the Snowflake Schema was brilliant. By removing redundant string values (like "Maharashtra") and replacing them with tiny integers (STATE_ID), companies saved millions of dollars in storage costs.
However, in modern cloud architectures like Snowflake, this is an anti-pattern.
Why the Snowflake Schema Hurts in the Cloud
- Storage is Cheap, Compute is Expensive: In the Snowflake Data Cloud, storage costs ~$23 per Terabyte per month. Saving a few megabytes by normalizing strings is financially meaningless. However, executing 4-table joins requires significant compute power. You are burning expensive Compute Credits to save pennies on Storage.
- Columnar Compression: Modern columnar databases inherently compress redundant strings. If a column contains "Maharashtra" one million times, the database compresses it to a tiny dictionary map automatically. Normalizing it manually is unnecessary.
- Analyst Frustration: Writing queries against a Snowflake Schema is incredibly tedious. Business analysts using Tableau or PowerBI prefer wide, flat tables (Star Schemas) over deep hierarchies.
Summary
- A Snowflake Schema normalizes dimension tables into smaller sub-dimensions (e.g., separating Store, City, and State into different tables).
- It minimizes data redundancy and saves storage space.
- However, it requires complex, multi-table
JOINqueries. - In modern columnar databases (like the Snowflake Data Cloud), the Star Schema is strongly preferred over the Snowflake Schema because storage is cheap, compression is automatic, and joins are expensive.