DE Snowflake Schema
The snowflake schema is a variation of the star schema. Both organize data around a central fact table, but the snowflake schema takes dimension tables one step further — it normalizes them by splitting them into multiple related tables. The resulting structure looks like a snowflake rather than a simple star.
How Snowflake Differs from Star
In a star schema, dimension tables are flat. All attributes of a product — name, category, brand, supplier — live in one product dimension table. In a snowflake schema, that product dimension splits into multiple tables: a product table, a category table, and a supplier table, each connected by foreign keys. The hierarchy within the dimension becomes explicit through table relationships.
The Filing Cabinet Analogy
A star schema is like a single thick folder labeled "Products" with every product detail on one page per product. A snowflake schema is like a well-organized filing cabinet: the main product folder references a separate "Categories" drawer and a "Suppliers" drawer. More organized, less duplication — but you open more drawers to get the complete picture.
Side-by-Side Schema Comparison
STAR SCHEMA - dim_product (one flat table): +--------------+--------------+----------+-----------------+ | product_key | product_name | category | supplier_name | +--------------+--------------+----------+-----------------+ | P001 | Laptop Pro | Laptops | TechWorld Ltd | | P002 | Mouse Slim | Mice | PeripheralCo | | P003 | USB Hub | Cables | TechWorld Ltd | +--------------+--------------+----------+-----------------+ SNOWFLAKE SCHEMA - normalized into three tables: dim_product: dim_category: dim_supplier: +-------------+--------+ +-----------+-------+ +-----------+---------------+ | product_key | name | | cat_key | name | | sup_key | supplier_name | | cat_key | | +-----------+-------+ +-----------+---------------+ | sup_key | | | C01 |Laptops| | S01 | TechWorld Ltd | +-------------+--------+ | C02 | Mice | | S02 | PeripheralCo | | P001 | Laptop Pro|C01|S01| | C03 |Cables | +-----------+---------------+ | P002 | Mouse Slim|C02|S02| | P003 | USB Hub |C03|S01|
The Full Snowflake Diagram
[dim_date]
|
[dim_supplier] |
| [fact_sales]--- [dim_product] --- [dim_category]
[dim_customer_city] |
| [dim_store] --- [dim_region]
[dim_customer]
Notice that dimensions branch out further than in a star schema. A dimension like "customer" might connect to a "city" table, which connects to a "country" table. Each level of the hierarchy becomes its own table.
When Snowflake Makes Sense
Deep Hierarchies
Some dimensions have natural multi-level hierarchies. Product belongs to subcategory, subcategory belongs to category, category belongs to department. Flattening this into one row per product repeats category and department information thousands of times. Normalizing into separate tables eliminates this repetition.
Storage Efficiency
When dimension tables are very large and contain many repeated values, normalization significantly reduces storage. If a product dimension has 1 million rows and only 50 unique categories, storing the category name in every row wastes space. A separate category table with 50 rows and a foreign key in the product table is far more compact.
Dimension Maintenance
When a category name changes, a snowflake schema updates one row in the category table. A star schema requires updating every product row that uses that category — potentially thousands of rows.
When Star Is Better Than Snowflake
Consider Star When: Consider Snowflake When: - Dimensions are small - Dimensions are large with many repeated values - Query simplicity matters most - Storage efficiency is a priority - BI tools generate queries - Deep hierarchies need explicit modeling - Analysts write SQL manually - Data maintenance frequency is high - Warehouse storage is cheap - Storage costs are a concern
Query Complexity Trade-off
The snowflake schema requires more joins to answer the same question. The same "electronics revenue in the South" query from the star schema topic now needs to join through the product table, then to the category table — one extra join per normalized dimension level.
Star Schema Query (2 extra joins): JOIN dim_product p ON f.product_key = p.product_key WHERE p.category = 'Electronics' Snowflake Schema Query (3 extra joins): JOIN dim_product p ON f.product_key = p.product_key JOIN dim_category c ON p.cat_key = c.cat_key WHERE c.name = 'Electronics'
Modern query engines and BI tools handle these extra joins efficiently, but the additional complexity matters when analysts write queries manually.
Real-World Usage
Most production data warehouses use a hybrid approach. Some dimensions stay flat (star style) because they are small and simple. Others normalize into multiple levels (snowflake style) where hierarchies exist and repetition becomes costly. A pure star or pure snowflake is rare — pragmatic design mixes both based on the characteristics of each dimension.
Summary
The snowflake schema extends the star schema by normalizing dimension tables into multiple related tables. It eliminates redundancy, saves storage, and handles deep hierarchies cleanly. The trade-off is increased query complexity. Data engineers choose between star and snowflake schemas — or combine both — based on dimension size, hierarchy depth, storage constraints, and the SQL fluency of the team using the warehouse.