What is the difference between the Snowflake schema and the Star schema in data warehousing?

Question

Accepted Answer

Snowflake schema has normalized dimension tables while Star schema has denormalized dimension tables.

Snowflake schema has a centralized fact table connected to multiple dimension tables normalized into sub-dimension tables.
Star schema has a denormalized structure with dimension tables directly connected to the fact table.
Snowflake schema requires more joins for querying data compared to Star schema.
Star schema is easier to understand and query but may lead to data redundancy.
Example: A Snowflake schema may have a 'customer' dimension table normalized into 'customer details' and 'customer address' sub-dimension tables.
Example: A Star schema may have a 'customer' dimension table directly connected to the fact table.

Top PolicyBazaar Senior Database Developer interview questions & answers