Snowflake Schema

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.

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

Related Terms

Margaret Rouse
Technology Expert

Margaret is an award-winning technical writer and teacher known for her ability to explain complex technical subjects to a non-technical business audience. Over the past twenty years, her IT definitions have been published by Que in an encyclopedia of technology terms and cited in articles by the New York Times, Time Magazine, USA Today, ZDNet, PC Magazine, and Discovery Magazine. She joined Techopedia in 2011. Margaret's idea of a fun day is helping IT and business professionals learn to speak each other’s highly specialized languages.