Schema is a logical description of the entire database.
A database uses relational model, while a data warehouse uses Star, Snowflake, and Fact Constellation schema.
The star schema is the simplest type of Data Warehouse schema. It is known as star schema as its structure resembles a star. In the Star schema, the center of the star can have one fact tables and numbers of associated dimension tables. It is also known as Star Join Schema and is optimized for querying large data sets.
A Snowflake Schema is an extension of a Star Schema, and it adds additional dimensions. It is called snowflake because its diagram resembles a Snowflake.
The dimension tables are normalized which splits data into additional tables.
A fact constellation has multiple fact tables. It is also known as galaxy schema.
The schema is viewed as a collection of stars hence the name Galaxy Schema.
It is also possible to share dimension tables between fact tables,shared dimensions are called Conformed Dimensions.
Star Schema | Snow Flake Schema |
Hierarchies for the dimensions are stored in the dimensional table. | Hierarchies are divided into separate tables. |
It contains a fact table surrounded by dimension tables. | One fact table surrounded by dimension table which are in turn surrounded by dimension table |
In a star schema, only single join creates the relationship between the fact table and any dimension tables. | A snowflake schema requires many joins to fetch the data. |
Simple DB Design. | Very Complex DB Design. |
Denormalized Data structure and query also run faster. | Normalized Data Structure. |
High level of Data redundancy | Very low-level data redundancy |
Single Dimension table contains aggregated data. | Data Split into different Dimension Tables. |
Cube processing is faster. | Cube processing might be slow because of the complex join. |
Offers higher performing queries using Star Join Query Optimization. Tables may be connected with multiple dimensions. | The Snow Flake Schema is represented by centralized fact table which unlikely connected with multiple dimensions. |
See also ETL Introduction
If you like dEexams.com and would like to contribute, you can write your article here or mail your article to admin@deexams.com . See your article appearing on the dEexams.com main page and help others to learn.