Snowflake Schema

Definition - What does Snowflake Schema mean?

In computing, a snowflake schema refers a multidimensional database with logical tables, where the entity-relationship diagram is arranged into the shape of a snowflake. It is often depicted by a centralized fact table linked to multiple and different dimensions. The dimension tables are divided into various dimension tables, which are generally normalized to clear out redundancy. For example, a product table is divided into tables of products and sub-products.

A snowflake schema resembles a star schema. However, the dimensions of a star schema are normalized, so that an individual table depicts each dimension.

Free Techopedia White Paper | The Ultimate Guide to Applying AI in Business > Download Now

Techopedia explains Snowflake Schema

Complex snowflake patterns appears when there are elaborate snowflake schema dimensions. In order for the schema to be the shape of a snowflake, it must consist of various relationship levels, and child tables should include several parent tables. The fact tables are not affected by the "snowflaking" effect.

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