Star Schema vs Snowflake Schema

Difference between Star Schema vs Snowflake Schema

In this article, we will discuss star schema vs snowflake schema differences in detail. Schema is the way of logically describing the entire databases or data warehouses using relational databases. The description may include the name and details of the records of any type with all aggregates and data items. Data warehouses use Star, Snowflake, Fact Constellation and Galaxy schema.

Star Schema

It is the simplest data warehouse schema. As its name suggests, its structure is like a star. In this schema, the fact table is present in the center and the number of dimension tables is associated with it through foreign key and the dimension table contains the set of attributes. In this schema, every dimension is represented with a single dimension table only but the dimension tables are not joined with each other. Its advantages include slicing down, easy understanding of data, optimal disk usage, and performance increase. Let us see an example for better understanding.

Example:

Consider a refrigerator manufacturing company and we need to create a schema for the sales of this refrigerator manufacturing company. Sales will have the following dimensions:

Item
Location
Branch
Time

The schema has a fact table at the center for sales which would contain keys to associate with each dimension, having two measures i.e. units sold and dollars sold.

Snowflake Schema

Snowflake Schema is the extension of the star schema. It adds additional dimensions to it. As its name suggests, it looks like a snowflake. In this schema, the dimension tables are normalized i.e. data is split into additional tables. Splitting the table reduces redundancy and memory wastage. It has the hierarchical form of dimensional tables. The dimension and sub-dimension tables are associated with the primary and foreign keys in the fact table. It is easier to implement and uses less disk space. As it has multiple tables the performance of the query is reduced. More maintenance is required because there are more lookup tables. Let us see an example for better understanding.

Example:

Considering the same example as above of refrigerator manufacturing company, in the snowflake schema the fact table is the same as in star schema but the major difference is in the definition or layout of dimension tables.

In this schema, the single dimension table of the item has been normalized and has been split and a new supplier table has been created including information on the type of supplier. Similarly, the dimension table of location is normalized and data is split into a new city table containing details of the particular city.

Head to Head Comparison between Star Schema and Snowflake Schema (Infographics)

Below are the top 9 differences between Star Schema vs Snowflake Schema.

Key Differences Between Star Schema vs Snowflake Schema

Let us see some major differences between Star Schema vs Snowflake Schema.

Star Schema:

Hierarchies of dimension in star schema are stored in dimension table.
It contains a central fact table encircled by dimension table.
In this, a single join associated the fact table with a dimension table.
It has a simple design.
The data structure is denormalized.
The query executes at a faster rate.
In this cube, the processing is faster.
It has more redundant data.
It uses simple queries.
Star schema is easy to understand.
In a star schema, more space is consumed.

Snowflake Schema:

Hierarchies in a snowflake schema are stored in separate tables.
It also contains a fact table surrounded by dimension table and these dimension tables are further surrounded by a dimension table.
In this schema, many joins are necessary for fetching the data.
It has a complex design.
The data structure is normalized in the snowflake schema.
The query executes comparatively slower than star schema.
In the snowflake schema, cube processing is slower.
It contains less redundant data.
It uses complex queries.
The snowflake schema is comparatively difficult to understand that star schema.
In the snowflake schema, less space is consumed.