Snowflake schema

A snowflake schema is a dimensional model in which dimension tables are broken into multiple, normalized tables connected by keys — a product dimension might split into separate product, subcategory and category tables — rather than repeating that hierarchy as flat columns in one table. Drawn out, the branching sub-dimensions around the central fact table resemble a snowflake, which gives the pattern its name.

This differs from a star schema, where each dimension is a single denormalized table with all its hierarchy flattened into columns. Snowflaking reduces storage and enforces that an attribute like "category name" is stored once, avoiding update anomalies, but it costs extra joins at query time, since retrieving a fact by category now means traversing several dimension tables instead of one.

Most cloud warehouses today favor the star schema, or even a single one big table design, because storage is cheap and join-heavy queries are slower than flat scans, so snowflaking is chosen mainly when a hierarchy is large, frequently updated, or shared across many fact tables and the normalization genuinely prevents inconsistency. A common misreading is assuming "snowflake" refers to the Snowflake data warehouse product; the schema pattern predates and is unrelated to that vendor.