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.

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
Share this:

Connect with us

Email Newsletter

Join thousands of others with our weekly newsletter

The 4th Era of IT Infrastructure: Superconverged Systems
The 4th Era of IT Infrastructure: Superconverged Systems:
Learn the benefits and limitations of the 3 generations of IT infrastructure – siloed, converged and hyperconverged – and discover how the 4th...
Approaches and Benefits of Network Virtualization
Approaches and Benefits of Network Virtualization:
Businesses today aspire to achieve a software-defined datacenter (SDDC) to enhance business agility and reduce operational complexity. However, the...
Free E-Book: Public Cloud Guide
Free E-Book: Public Cloud Guide:
This white paper is for leaders of Operations, Engineering, or Infrastructure teams who are creating or executing an IT roadmap.
Free Tool: Virtual Health Monitor
Free Tool: Virtual Health Monitor:
Virtual Health Monitor is a free virtualization monitoring and reporting tool for VMware, Hyper-V, RHEV, and XenServer environments.
Free 30 Day Trial – Turbonomic
Free 30 Day Trial – Turbonomic:
Turbonomic delivers an autonomic platform where virtual and cloud environments self-manage in real-time to assure application performance.