Data Warehouse Concepts

1) What is Dimensional Modeling in Data Warehouse?

Dimensional Modeling (DM) is a data structure technique optimized for data storage in a Data warehouse. The purpose of dimensional modeling is to optimize the database for faster retrieval of data, which consists of “fact” and “dimension” tables.

A Fact Table contains - Measurements/facts, Foreign key to the dimension table.

Dimension Table - A dimension table contains dimensions of a fact, They are joined to the fact table via a foreign key. Dimension tables are de-normalized tables.

2) Snowflake schema -

The snowflake schema is an extension of the star schema. In a snowflake schema, each dimension is normalized and connected to more dimension tables.

3) Star Schema -

The star schema architecture is easy to design. It is called a star schema because the diagram resembles a star.

The center of the star consists of the fact table, and the points of the star are dimension tables.

The fact tables are in a star schema which is the third normal form whereas dimensional tables are de-normalized.

4) What is 3NF?

The third normal form (3NF) is a database schema design approach for relational databases which uses normalizing principles to reduce the duplication of data, avoid data anomalies, ensure referential integrity, and simplify data management. 

5) Difference between Star and Snowflake Schema - 

Star schema                         snowflake schema

Star schema is a top-down model.                 While it is a bottom-up model.

Star schema uses more space.                 While it uses less space.

It takes less time for the execution of queries         While it takes more time than star schema for the execution of queries.

In a star schema, Normalization is not used         While in this, Both normalization and denormalization are used

Its design is very simple.                 While its design is complex.

The query complexity of the star schema is low. While the query complexity of snowflake schema is higher than star schema.

It has fewer foreign keys.                         While it has more foreign keys

It has high data redundancy.                 While it has low data redundancy.

6) Types of Dimensions in Data Warehouse-

Role-playing Dimension

Junk Dimension

Conformed Dimension

Degenerate Dimension

Outrigger Dimension

Shrunken Dimension

Swappable Dimension

Step Dimension

6) What is the purpose of NORMALIZATION?

 Reduces data redundancy and eliminates undesirable characteristics like Insertion, Update, and Deletion Anomalies. 

Normalization rules divide larger tables into smaller tables and link them using relationships.

The purpose of Normalization in SQL is to eliminate redundant (repetitive) data and ensure data is stored logically.

7) Difference between Normalization and  Denormalization?

Normalization is used to remove redundant data from the database and to store non-redundant and consistent data into it. Normalization uses optimized memory and hence faster in performance.

 Denormalization is used to combine multiple table data into one so that it can be queried quickly. ...