Definition - What does Snowflake Schema mean?
A snowflake schema resembles a star schema. However, the dimensions of a star schema are normalized, so that an individual table depicts each dimension.
Techopedia explains Snowflake Schema
Snowflake schemas are made for flexible querying over more complicated relationships and dimensions. It is ideal for one-to-many and many-to-many relationships among dimension levels and is typically associated with data marts and dimensional data warehouses, in which data retrieval speed is more critical than data manipulation efficiency. Snowflake schemas are most commonly used with advanced query tools, which build an abstraction layer between users and raw tables for scenarios that have multiple queries with elaborate specifications.
Benefits of using a snowflake schema are as follows:
- Key benefit is the progression in query performance, as it requires minimal disk storage and joins lookup tables with smaller sizes
- Offers better flexibility for interrelationships between components and dimension levels
- Easy to maintain, due to zero redundancy
- Enhances overall performance because smaller tables are coupled
Disadvantage of snowflake schemas include the following:
- Queries are more difficult because the joining of additional tables is required. This makes understanding queries more difficult.
- Requires more query execution time, due to the use of more tables