Data Warehouse Interview Questions and Answers - 2

11. What is the main difference between Star and Snowflake Star Schema?

In dimensional model, all the data is stored in two types of tables - Facts table and Dimension table. Measures or numbers are stored in fact table and dimensions which describes the measures are stored in dimension table.

In this model, we have two popular schemas available. They are Star Schema and Snowflake Schema. (Schema is nothing but arrangement of tables or database structure)

Star Schema

Star schema is the simplest among the data warehousing schemas. It consists of data in the form of facts and dimensions. It is called a star schema because diagram resembles a star where the fact table is surrounded by multiple de-normalized dimension tables.

Due to de-normalization in the star schema, you have redundant or duplicate data that calls for more maintenance. The queries are simpler with lesser joins.

Snowflake Schema

A Snowflake Schema is an extension of a Star Schema. It is called snowflake because its diagram resembles a Snowflake. Unlike Star schema, some dimension tables in the Snowflake schema can be normalized.

Due to normalization in the Snowflake schema, the redundancy is reduced and therefore, it becomes easy to maintain and the save storage space.