What is the difference between star schema and snow flake schema ?and when we use those schema’s?
Star Schema : Star Schema is a relational database schema for representing multimensional data. It is the simplest form of data warehouse schema that contains one or more dimensions and fact tables. It is called a star schema because the entity-relationship diagram between dimensions and fact tables resembles a star where one fact table is connected to multiple dimensions. The center of the star schema consists of a large fact table and it points towards the dimension tables. The advantage of star schema are slicing down, performance increase and easy understanding of data.
Snowflake Schema : A snowflake schema is a term that describes a star schema structure normalized through the use of outrigger tables. i.e dimension table hierachies are broken into simpler tables.
In a star schema every dimension will have a primary key.
# In a star schema, a dimension table will not have any parent table.
# Whereas in a snow flake schema, a dimension table will have one or more parent tables.
# Hierarchies for the dimensions are stored in the dimensional table itself in star schema.
# Whereas hierachies are broken into separate tables in snow flake schema. These hierachies helps to drill down the data from topmost hierachies to the lowermost hierarchies.
Explore posts in the same categories: Data WareHousing Interview Questions