[WEBINAR] Application Acceleration: Faster Performance for End Users

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

Techopedia Deals

Connect with us

Techopedia on Linkedin
Techopedia on Linkedin
"Techopedia" on Twitter

Sign up for Techopedia's Free Newsletter!

Email Newsletter

Join thousands of others with our weekly newsletter

Free Whitepaper: The Path to Hybrid Cloud
Free Whitepaper: The Path to Hybrid Cloud:
The Path to Hybrid Cloud: Intelligent Bursting To Amazon Web Services & Microsoft Azure
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.