Star And Snowflake Schema

While in snowflake schema the fact tables dimension tables as well as sub dimension tables are contained. Kimball usually advises that it is not a good idea to expose end users to a physical snowflake design because it almost always compromises understandability and performance.

Https Encrypted Tbn0 Gstatic Com Images Q Tbn 3aand9gcr Us0xohcjbstnxkuic2ypbww7ycy Fpjuwevn3ktw3r0fjpgr Usqp Cau

A snowflake schema is an extension of a star schema and it adds additional dimensions.

Star and snowflake schema. It is called snowflake because its diagram resembles a snowflake. Star and snowflake schemas are the most popular multidimensional data models used for a data warehouse. Snowflake schema is the extension of the star schema.

Although redundancy is reduced in a normalized snowflake more joins are required. The star schema is the simplest type of data warehouse schema. It is known as star schema as its structure resembles a star.

While it uses less space. Data is split into additional tables. While it is a bottom up model.

Star schema is a top down model. In this schema the dimension tables are normalized i e. As its name suggests it looks like a snowflake.

Star schema snowflake schema. Snowflake schema example click to enlarge the main difference when compared with the star schema is that data in dimension tables is more normalized. Ralph kimball recommends that in most of the other cases star schemas are a better solution.

It adds additional dimensions to it. Star schema uses more space. In star schema the fact tables and the dimension tables are contained.

For example instead of storing month quarter and day of the week in each row of the dim date table these are further broken out into their own dimension tables. The crucial difference between star schema and snowflake schema is that star schema does not use normalization whereas snowflake schema uses normalization to eliminate redundancy of data.

Snowflake Schema Data Warehouse Tutorial Intellipaat Com