Business Intelligence Dictionary: Star Schema, Snowflake Schema | NSBI
Relational database normalized schema is different from the architecture of a data warehouse. This article will explain what are the two most common design approaches when building a data warehouse - the Star Schema and the Snowflake Schema.
NSBI Tutorials aim to make Business Intelligence (BI) and Data Warehousing (DWH) attractive to non-technical people as well as to those who are now entering the field and are excited by the numerous ways data is changing our world. NSBI Tutorials are written and delivered by Nick Shopov, (BI Software Developer & DWH Consultant).
Star Schema | Definition
See visualization below as an example.
Star schema is designed for faster access to data (less joins) at the cost of further data redundancy.
Snowflake Schema | Definition
The snowflake schema is a hybrid between the star schema and the normalized relational schema where dimensions in the Star Schema are further normalized. This implies that dimensions are no longer directly linked to the Fact Table but may have other tables in between.
See a sample visualization of a Snowflake schema:
Snowflake schema is reducing the effect from the star schema and is normally avoided.