When To Use Star Schema and snowflake Schema
Dimensional modeling lies at the core of every successful data warehouse and business intelligence (BI) project. By organizing data into fact and dimension tables, you enable efficient aggregation, intuitive reporting, and enhanced analytical capabilities.
Two of the most widely adopted dimensional models are the Star Schema and the Snowflake Schema. Before diving into their differences and use cases, it is important to first understand the foundational concepts.
What is schema ?
Schema is nothing but a proper way of arranging your fact and dimension table
To fully grasp this, we must first define
Fact Table :
Fact table actual Contains measurable, quantitative business data such as sales, Quantity, revenue, Profit. These are the metrics used for analysis.
Dimension table
Dimension table is nothing but a lookup data which Stores descriptive, categorical information like product, category, country, region
Now we have some ideas about fact table and Dimension table
Let’s understand with this Superstore data, here all data both facts and dimensions
may initially reside in a single flat reporting table.
To create a proper star schema:
Separate customer-related data into a Customer Dimension Table.
Organize product details into a Product Dimension Table.
Extract geographical data into a Region Dimension Table.
Consolidate all measurable data (e.g., Sales, Quantity, Discount, Profit) into a Sales Fact Table
In Power BI We can achive this in Power Bi query editor by selecting relevant columns and removing others.
we will select only those columns which you want to keep and remove other columns
After seperating dimension and fact table. then we establish relationship between fact and Dimension table
For that go to model view and in home tab select manage relationship
There you can create a new relationship between two tables using common keys.
(e.g., Customer ID, Product ID).
Here Star Schema comes into focus
Understanding the Star Schema
Star Schema is a data modeling technique used to simplify data access and improve performance in data warehouses.
Here in the star schema every dimension table is connected to fact table.
Key Characteristics:
Each dimension table is directly linked to a central fact table.
Fact tables hold foreign keys that relate to primary keys in dimension tables.
Dimension tables are typically denormalized for fast querying.
Filtering a dimension table filters the fact table a process known as filter propagation.
Structure Example:
Fact Table: Sales Fact with keys like Customer ID, Product ID, Region ID.
Dimension Tables: Customer, Product, Region each containing primary keys and descriptive data.
Here in above diagram we can see there is one fact table and dimension table
As we have 4-dimension tables, the fact table must contain 4 foreign keys
And every dimension table will have one primary key like customer table have customer id,Product table have product id and so on and this primary key act as foreign key in our fact table and we join that with all our dimensions table using this common key
Data in the dimension tables is typically denormalized for speed of query execution.
Snowflake schema
Snowflake Schema builds upon the star schema by normalizing dimension tables into multiple related tables,reducing redundancy and improving data integrity.
Snowflake schema is one of the data modeling techniques followed in data warehousing for representing data into structured form that gets optimized to handle
plenty of data to execute queries efficiently.
In snowflake schema fact table connected to dimension table like star schema but here
we normalize data further by splitting dimension table into sub dimension table to reduce redundancy which means centralized fact table connected to multiple dimensions into multiple levels for flexibility and potentially better data management. This also allows more detailed analysis but leads to a more complex schema for data storage.
The snowflake structure comes into picture when the size of a star schema is determined and highly structured with multiple levels of relationship and the child tables have more than a single parent table. The snowflake effect only affects the dimension tables and not the fact tables.
Features of Snowflake Schema
Efficient storage due to reduced redundancy. Less disk space is used by the snowflake schema
Slower query performance compared to star schema.
Flexible modeling of complex hierarchies.
Complex relationships due to multi-level dimensions.
Improved data consistency through normalization.
Dimension table has two or more sets of attributes that define information at different grains.
The attribute sets of the same dimension table are loaded by different source systems.
Galaxy Schema
A Galaxy Schema consist of multiple fact tables. These are more than two tables that share the commondimension table this schema. Its designed for complex data models that support multi-fact analysis. This Schema is also known as Fact Constellation Schema
It is viewed as a collection of stars and hence its name is Galaxy
Using this technique, organizations can carry out multi-dimensional analysis on complex datasets.Fact Constellation Schema, or Galaxy Schema, is an advanced data modeling technique used in data warehouse design. Compared to other simpler models like the Star Schema and Snowflake Schema,
Use Case: Ideal for enterprise-level BI solutions requiring cross-functional data analysis (e.g., combining sales and
inventory data).
Now what is the difference between Star Schema and Snowflakes Schema
Sr no | Star Schema | Snowflake Schema |
1 | In Star schema we have fact table and dimension | While in snowflake schema fact table, dimension table as well as sub dimension table are also present |
2 | Star schemas use more space | While it uses less space |
3 | It takes less time for the execution of queries | While it takes more time than the star scheme for the execution of queries |
4 | Its design is very simple | While its design is complex |
5 | The query complexity of star schema is low | While query complexity of snowflake schema is higher than star schema |
6 | It has less no of foreign keys | While it has more number of foreign keys |
7 | It has high data redundancy | While it has low data redundancy |
When to use which schema ?
Star schema
You need maximum query performance for power bi dashboard
The dimension tables are relatively small in size
You prefer simple and fast analytics over storage optimization.
Snowflake schema
Storage efficiency is a priority.
your dimensions includes hierarchies (like product-category-sub category)
You are comfortable working with complex ETL processes.
The overall message is that the optimal schema choice depends on the specific needs of the data warehousing project, specifically the anticipated query types and volume of data.
