Try it here
Data Warehousing - Schemas

Star, SnowFlake and Galaxy Schema


Schema is a logical description of the entire database.
A database uses relational model, while a data warehouse uses Star, Snowflake, and Fact Constellation schema.

Star Schema

The star schema is the simplest type of Data Warehouse schema. It is known as star schema as its structure resembles a star. In the Star schema, the center of the star can have one fact tables and numbers of associated dimension tables. It is also known as Star Join Schema and is optimized for querying large data sets.

Benefits of Star Schema:

  • Each dimension in a star schema is represented with only one-dimension table.
  • The dimension table should contain the set of attributes.
  • The dimension table is joined to the fact table using a foreign key
  • The dimension table are not joined to each other
  • Fact table would contain key and measure
  • The Star schema is easy to understand and provides optimal disk usage.
  • The dimension tables are not normalized.
  • The schema is widely supported by BI Tools

Snowflake Schema

A Snowflake Schema is an extension of a Star Schema, and it adds additional dimensions. It is called snowflake because its diagram resembles a Snowflake.
The dimension tables are normalized which splits data into additional tables.

Benefits of Snowflake Schema:

  • The main benefit of the snowflake schema it uses smaller disk space.
  • Easier to implement a dimension is added to the Schema
  • Due to multiple tables query performance is reduced
  • The primary challenge that you will face while using the snowflake Schema is that you need to perform more maintenance efforts because of the more lookup tables.
  • Easy to maintain, due to zero redundancy
  • Enhances overall performance because smaller tables are coupled

Disadvantage of snowflake schemas:

  • 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.

Fact Constellation Schema(Galaxy schema)

A fact constellation has multiple fact tables. It is also known as galaxy schema.
The schema is viewed as a collection of stars hence the name Galaxy Schema.
It is also possible to share dimension tables between fact tables,shared dimensions are called Conformed Dimensions.

Benefits of Fact Constellation Schema:

  • The dimensions in this schema are separated into separate dimensions based on the various levels of hierarchy.
  • Moreover, it is possible to build this type of schema by splitting the one-star schema into more Star schemes.
  • The dimensions are large in this schema which is needed to build based on the levels of hierarchy.
  • This schema is helpful for aggregating fact tables for better understanding.

Key differences between Star and Snowflake Schema

Star Schema Snow Flake Schema
Hierarchies for the dimensions are stored in the dimensional table. Hierarchies are divided into separate tables.
It contains a fact table surrounded by dimension tables. One fact table surrounded by dimension table which are in turn surrounded by dimension table
In a star schema, only single join creates the relationship between the fact table and any dimension tables. A snowflake schema requires many joins to fetch the data.
Simple DB Design. Very Complex DB Design.
Denormalized Data structure and query also run faster. Normalized Data Structure.
High level of Data redundancy Very low-level data redundancy
Single Dimension table contains aggregated data. Data Split into different Dimension Tables.
Cube processing is faster. Cube processing might be slow because of the complex join.
Offers higher performing queries using Star Join Query Optimization. Tables may be connected with multiple dimensions. The Snow Flake Schema is represented by centralized fact table which unlikely connected with multiple dimensions.

See also ETL Introduction

Writer profile pic

Uk01 on Oct 21, 2020 at 12:10 am

If you like and would like to contribute, you can write your article here or mail your article to . See your article appearing on the main page and help others to learn.

Post Comment

Comments( 0)


Forgot Password

Please enter your email address below and we will send you information to change your password.