Home  >  Blog  >   Snowflake  > 

Star schema and Snowflake schema in QlikView

Rating: 5
  1. Share:
Snowflake Articles

Star schema and Snowflake schema - Overview

In QlikView, the relational database schemas are of two types. One is the Star schema and the other is the Snowflake schema. But, before going to learn the two different schemas, first, we should know what kinds of tables were used to store the data?

So, there are two types of tables: 

  • Fact table 
  • Dimension table

Fact table: 

The Fact table is a table that contains numeric data or measurable attributes of data such as ID, keys, etc, that are connected with the dimension table across the data model. The fact table is placed at the center of the Star schema or the Snowflake schema bounded by dimension tables in a data model.

If you would like to become QlikView Certified professional, then visit Mindmajix - A Global online training platform: " QlikView Certification Training Course ". This course will help you to achieve excellence in this domain.

Dimension table: 

The Dimension tables are those tables that consist of descriptive or textual attributes of data such as Product ID, Product name, Manager ID, Manager name, etc., Each dimension table describes the data of that particular table. For e.g., If a dimension table gives information about a product, it provides the data particularly on that product itself. 

In the figure, the highlighted ones are the Dimension tables and the one which is connected with all the dimension tables is the Fact table. As a title of this article, we will learn What is a Star schema? What is a Snowflake schema? And What are the difference between them? Let’s get started.

Star schema and Snowflake schema in QlikView - Dimension table


Related Article: QlikView Interview Questions and Answers

What is Star Schema?

A star schema has a single fact table that connects with all the dimension tables based on their links. By its name, it resembles a star having a fact table at its center and surrounded by all dimension tables. It is known as a  star schema because it represents the entity-relationship diagram between a Fact table and dimension tables that is where one fact table is connected with multiple dimension tables. In this schema, every dimension table contains a primary key but they will not have any parent table.

Data model: 

A star schema data model consists of one main Fact table that is connected with multiple dimension tables through the primary keys. This type of schema is commonly used for Online Analytical Processing (OLAP) which provides high speed. The resulting star schema has a spoke, or hub, or a Star-like representation.

Simply, a star schema is a structure where through dimension tables are connected with the center situated Fact table. The fact table consists of foreign keys or the primary keys of all the dimension tables.

As shown in the figure, the center table (Sales details) represents the Fact tables, and tables connected across the fact table are the dimension tables (Product details, Place details, customer details, order details). 

Star schema and snowflake schema in QlikView - Data model

E.g. Star schema with details of sales as Fact table and other details regarding sales like Product details, place details, Customer details, and Order details as Dimension tables.

Advantages of Star schema

The advantages of the star schema are as follows:

  • A star schema reduces the time required to load a large amount of data into the database

  • It provides efficient navigation through data yet the dimension tables are joined to the fact table

  • Queries run faster in a star schema

  • It is designed to enforce the relation between accuracy and consistency of loaded data

Disadvantages of star schema

The disadvantages of the star schema are as follows:

  • It is not flexible in terms of analytical requirements as a normalized data model

  • Data integrity is not executed as well as it is a highly normalized database

MindMajix YouTube Channel

What is a Snowflake Schema?

A Snowflake schema is an enhancement of a star schema where every point of a star multiplies into several points. As we know, in star schema each dimension is represented by a single dimension table, But in Snowflake schema, that dimension table is standardized into numerous lookup tables. That means a dimension table is further linked to the Sub dimension table through multiple links. It is used when the dimension table becomes very big. 

In this schema, a dimension table will have one or more parent tables. The hierarchies are divided into distinct tables and these hierarchies assist to drive down the data from the top-most hierarchy to the bottom-most hierarchy.

Related Article: Snowflake Basics    

Data model:

The Snowflake schema data model consists of one or more fact tables that are connected with multiple dimension tables as a star schema, and these dimension tables are further connected with the Sub dimension tables depending on the data scaling. 

As shown in the figure below, the fact table is connected with all the dimension tables based on their primary keys and some of the dimension tables are further linked to the Sub dimension table.

Unlike star schema, the Snowflake schema organizes the data inside the database in order to eliminate the redundancy and thus helps to reduce the amount of data. This kind of schema is commonly used for multiple fact tables that were a more complex structure and multiple underlying data sources.

Star schema and snowflake schema in QlikView - Data Model

E.g. the fact table is connected with the Location table (Dimension table) and the Location table is further connected to the Location details table (Sub dimension table).

Related Article: Data Warehousing Tool

Advantages of the Snowflake schema

The advantages of snowflake schema are as follows:

  • It helps to increase flexibility

  • It improves query performance due to minimized disk storage requirement and connecting small lookup tables

  • It is easy to maintain

Disadvantages of the Snowflake schema

  • The disadvantages of the Snowflake schema are as follows:

  • Due to multiple tables query performance might be reduced

  • It is required to perform more maintenance efforts because of more lookup tables

Explore QlikView Sample Resumes! Download & Edit, Get Noticed by Top Employers!

Difference between Star schema and Snowflake schema

Description Star schema Snowflake schema
Data model Top-down approach Bottom-up approach
Normalization/ Denormalization The fact table and Dimension tables are in the Denormalized form. The fact tables are in Denormalized form, but Dimension tables are in normalized form.
Ease of use Easy to understand and low query rate. It is a complex structure and not as easy to understand.
Ease of maintenance It has redundant data and is less easy to maintain and change. No redundancy. Hence, Snowflake is easier to maintain and change.
Dimension table It contains only a single dimension table for each dimension. It contains more than one dimension table for each dimension depending on the data.
Query performance Less number of foreign keys and takes less time for execution. More foreign keys and takes a long time for execution.
Joins Less number of joins More number of joins
Application We can prefer the star schema when the dimension table has fewer rows. We can prefer the snowflake schema when the dimension table is relatively big. So, this schema helps to reduce the size of the data.


Enroll and Get Certified Snowflake Training In Hyderabad Now!!


In QlikView, the preferred schema is the star schema as it provides queries that run faster. The structure allows us to load large batches of data quickly. It is a flexible model and deals with complex scripts easily. It also provides good RAM consumption. But selecting a suitable schema depends on the quantity of data. In this article, we have learned the core details of Star schema and Snowflake schema. Hope you find relevant information.

Related Articles:

Join our newsletter

Stay updated with our newsletter, packed with Tutorials, Interview Questions, How-to's, Tips & Tricks, Latest Trends & Updates, and more ➤ Straight to your inbox!

Course Schedule
Snowflake TrainingJun 06 to Jun 21
Snowflake TrainingJun 10 to Jun 25
Snowflake TrainingJun 13 to Jun 28
Snowflake TrainingJun 17 to Jul 02
Last updated: 04 June 2023
About Author
Remy Sharp
Vinod Kasipuri

Vinod Kasipuri writes about various IT platforms such as QlikView, Qlik Sense, and Perl Scripting, at Mindmajix. He loves to explain the concepts he writes in simple terms. He is also engaged in researching trends in AngularJS and LabView. Reach out to him via LinkedIn and Twitter.

Recommended Courses

1 /10