Star Schema vs Snow Flake Schema in SQL Server

Back to: SQL Server Tutorial For Beginners and Professionals

Star Schema vs Snow Flake Schema Design in SQL Server

In this article, I am going to discuss the Star Schema vs Snow Flake Schema Design in SQL Server with Examples. This is a continuation part of our previous article, so please read our previous article before proceeding to this article where we discussed Database Database De-Normalization in detail. As part of this article, we are going to discuss the following pointers.

1. What are Star Schema and Snow Flake Schema Design in SQL Server?
2. What is the difference between Star Schema and Snow Flake Design in SQL Server?
3. When to use Star Schema and when to use Snow Flake Design in SQL Server?

Before understanding the Star Schema and Snow Flake design, first, we need to understand four important terms i.e. Dimension, Measures, Fact Table, and Dimension Table.

Understanding Dimension, Measures, Fact Table and Dimension Table:

The main goal of the OLAP system is the analysis of historical data and the most important thing in the analysis are numbers. So, in OLAP applications, we need to get those numbers, we need to forecast them; we need to analyze them for better business. These numbers can be total sales, total products, total amounts, total customers, etc. Again these numbers are technically termed Measures. Measures are mostly stored in the Fact table. Dimensions describe what these measures are actually. As you can see in the below image, we have two measures 50000 units and 10000 $. One dimension is Product wise sales and the other dimension is Age-wise sales. The Dimensions are stored in the Dimension table.

So, measures are numbers and dimensions describe those numbers.

Star Schema Design in SQL Server:

The Star Schema Design is the simplest database design technique for OLAP systems. The Star Schema Design Consists of Fact and Dimension tables. Please have a look at the following diagram for a better understanding. The Fact table has measures i.e. the numbers such as Total Sales, Total Quantity, Total Products, etc. and the Dimension table gives more context to the Fact table. As you can see in the below image, we have four dimension tables such as Country, City, Employees, and Products. And each dimension table representing the Fact table for measure values.

Snow Flake Schema Design in SQL Server:

The Snow Flake Schema design is very much similar to the Star Schema Design. The only difference is the Dimension tables. Here, the Dimension Tables are normalized. Please have a look at the following image. Here, you can see, the Product table and the Vendor table are normalized based on the Primary key and Foreign Key relationships.

Difference between Star Schema and Snow Flake Schema Design in SQL Server:

The difference between Start Schema and Snow Flake Design are as follows:

1. Normalization: The Snow Flake design can have normalized dimension tables (Product and Vendor) while the Star Schema design has pure de-normalized dimension tables.
2. Maintenance: The Snow Flake Design has less redundancy so less maintenance while the Star Schema has more redundancy due to pure de-normalized dimension tables so more maintenance.
3. Queries: We need to write complex queries in Snow Flake Design due to normalized dimension tables whereas we need to write simple queries in Star Schema Design due to de-normalized design.
4. Joins: More joins in Snow Flake Design due to normalized tables whereas fewer join in Star Schema Design due to de-normalized tables.
5. Usage: If you are concern about data integrity and duplication, then simply go with Snow Flake Design, on the other hand, if you are more concerned about speed and performance rather than data integrity then go with Star Schema Design.

That’s it for today. In the next article, I am going to discuss How to Schedule a Job in SQL Server using SQL Server Agent. Here, in this article, we discussed what are Star Schema and Snow Flake Design and what is the difference between them. I hope you enjoy this Star Schema vs Snow Flake Schema Design in SQL Server with Examples article and understand Star Schema vs Snow Flake Schema Design in SQL Server.