Skip to Content

Star Schema Vs Snowflake Schema Vs Fact constellation Schema

Data Warehousing Schemas

  1. Star Schema
  2. Snowflake Schema
  3. Fact Constellation

Star Schema

  • A single large central fact table and one table for each dimension
  • Every fact points to one tuple in each of the dimensions and has additional attributes
  • Does not capture hierarchies directly.


Snowflake Schema

  • Variant of star schema model.
  • A single, large and central fact table and one or more tables for each dimension.
  • Dimension tables are normalized split dimension table data into additional tables.


       Fact Constellation:

  • Multiple fact tables share dimension tables.
  • This schema is viewed as collection of stars hence called galaxy schema or fact constellation.
  • Sophisticated  application requires such schema.


Case Study:

  • Afco Foods & Beverages is a new company which produces dairy, bread and meat products with production unit located at Baroda.
  • There products are sold in North, North West and Western region of India.
  • They have sales units at Mumbai, Pune , Ahmadabad ,Delhi and Baroda.
  • The President of the company wants sales information.

Sales Information

  • Report: The Number of units sold.
  • 113
  • Report : The Number of units sold over Time.


Building Data Warehouse

     Data Selection

  • Data Preprocessing
    1. Fill missing values
    2. Remove inconsistency
  • Data Transformation & Integration
  • Data Loading

Data in warehouse is stored in form of fact tables and dimension tables.


Thanks & Regards,

Vishakha Nigam

Be the first to leave a comment
You must be Logged on to comment or reply to a post.