Data warehousing star & snowflake: Explain the difference between star and snowflake schemas.

Explain the difference between star and snowflake schemas.

A snow flake schema design is usually more complex than a start schema. In a star schema a fact table is surrounded by multiple fact tables. This is also how the Snow flake schema is designed. However, in a snow flake schema, the dimension tables can be further broken down to sub dimensions. Hence, data in a snow flake schema is more stable and standard as compared to a Star schema.

e.g. Star Schema: Performance report is a fact table. Its dimension tables include performance_report_employee, performance_report_manager

Snow Flake Schema: the dimension tables can be broken to performance_report_employee_weekly, monthly etc.

Explain the difference between star and snowflake schemas.

Star schema: A highly de-normalized technique. A star schema has one fact table and is associated with numerous dimensions table and depicts a star.

Snow flake schema: The normalized principles applied star schema is known as Snow flake schema. Every dimension table is associated with sub dimension table.


- A dimension table will not have parent table in star schema, whereas snow flake schemas have one or more parent tables.
- The dimensional table itself consists of hierarchies of dimensions in star schema, where as hierarchies are split into different tables in snow flake schema. The drilling down data from top most hierarchies to the lowermost hierarchies can be done.

Differences between star and snowflake schema.

A snowflake schema is a more normalized form of a star schema. In a star schema, one fact table is stored with a number of dimension tables. On the other hand, in a star schema, one dimension table can have multiple sub dimensions. This means that in a star schema, the dimension table is independent without any sub dimensions.
Data warehousing view: What is the difference between view and materialized view?
What is the difference between view and materialized view? - A view is created by combining data from different tables. Hence, a view does not have data of itself....Materialized view usually used in data warehousing has data........
Data warehousing Cube and Linked Cube: What is a Cube and Linked Cube?
What is a Cube and Linked Cube - A data cube stores data in a summarized version which helps in a faster analysis of data. Where as linked cubes use the data cube.........
Data warehousing junk dimension: What is junk dimension?
Data warehousing junk dimension - In scenarios where certain data may not be appropriate to store in the schema, this data (or attributes) can be stored in a junk dimension..........
Post your comment
Discussion Board
Spelling erros and mistakes
The Star schema is been misspelled as "Start" schema at many places. Also in the last answer it is written that the star schema has sub-dimension table which is wrong.
Ujjwal Jain 10-4-2016