A snowflake schema is a dimensional model variation in which dimension tables are partially or fully normalised — their attributes are split across multiple related tables rather than stored flat in a single dimension table. Where a star schema stores all geography attributes (city, region, country, continent) in a single flat Geography dimension, a snowflake schema stores them in a hierarchy of tables: City joins to Region, Region joins to Country, Country joins to Continent. The result resembles a snowflake rather than a star in the entity-relationship diagram. The distinction that matters most: a snowflake schema is not a performance optimisation. For most BI query patterns, it is a performance trade-off — reduced storage footprint in exchange for additional join hops that can slow query execution if the query engine does not optimise them effectively.
How It Works
| Characteristic | Star Schema | Snowflake Schema |
|---|---|---|
| Dimension structure | Single flat table per dimension | Normalised hierarchy of related tables |
| Join complexity | One join per dimension from fact | Multiple joins per dimension hierarchy |
| Storage | Higher redundancy in dimension tables | Lower redundancy; attributes stored once |
| Query performance | Generally faster for typical BI patterns | Depends on query engine optimisation |
| Maintenance | Simpler ETL; fewer tables to manage | More complex ETL; more tables to maintain |
| Best for | Most analytical BI workloads | Very large dimensions with deep hierarchies; strict normalisation requirements |
Modern columnar query engines — Snowflake, BigQuery, Oracle ADW — are better at optimising multi-table join patterns than earlier MPP databases, which has made snowflake schemas more practical. But the query optimiser’s effectiveness depends on statistics and execution plan choices that are not always predictable; the star schema remains the safer default for finance BI environments where query performance consistency is more important than storage efficiency.
Design Decisions and Trade-offs
The valid case for a snowflake schema in finance BI is a dimension with a very large attribute set and a deep, active hierarchy — an account dimension with thousands of members and five or six hierarchy levels that are all used in BI filtering. Storing all hierarchy levels flat in the dimension table produces a very wide dimension with redundant hierarchy node values on every row. Normalising the hierarchy into a parent-child or level-based sub-dimension table reduces this redundancy and makes hierarchy maintenance more manageable when the account structure changes.
Common Implementation Errors
The error most specific to snowflake schema implementations is creating normalised sub-dimension joins that are not pre-joined in the semantic layer — leaving the BI tool to handle the multi-hop join at query time. When Power BI or OAC must traverse three dimension tables to reach a hierarchy level used in a visual, it may execute that traversal inefficiently, producing slow report load times that appear to be infrastructure problems when they are actually schema design and semantic layer configuration problems. Pre-joining snowflake dimensions into a flat view at the semantic layer — so the BI tool sees a star-like interface even when the underlying storage is snowflaked — resolves this without changing the physical storage model.
How Loop Wise Solutions Designs for This
We use star schemas as the default for finance BI dimensional models and evaluate snowflake normalisation only where a specific, documented case justifies it — very large dimensions with active, deep hierarchies. Where we do implement snowflake patterns, we pre-join the dimension hierarchy at the semantic layer so that BI consumers see a flat dimension interface and the query engine handles the join optimisation at the warehouse layer, not at the BI tool layer.
