Snowflake Schema to Star Schema – Storage and Query Performance Optimization

Understanding Snowflake and Star Schema

Snowflake Schema

It has more than one fact table used to avoid complexity and to create a more normalized structure.

Star Schema

It consists of a single fact table connected to the dimension table visualized as a Star. Single link establishes between the fact table and dimension table.

Snowflake Schema extension of Star Schema?

Large Dimension Tables normalized into multiple sub dimensional tables.
Every dimension table associated with the sub a dimension table and has multiple links.
A Snowflake schema is a Star schema structure normalized through the use of outrigger tables. i.e., the dimension table hierarchies broken into more unadorned tables.

Challenge for Implementing Storage and Query Platform

In the world of Data warehouse, storage and query performance optimization are significant concerns. Snowflake schema builds Data Warehouse, and as a result, query on the Data Warehouse results in lots of joins.

The major challenges include -

Understand the schema and then denormalize the lookup tables to reduce the number of joins in the query.
To implement Data Migration into the new data warehouse to give the response in seconds because of slow aggregation queries.

Solution Offerings for Data Warehouse and Data Migration

Denormalization — Understand schema from conceptual models to physical models followed by the granularity of Snowflake schema.
Data Migration — For latency reduction of aggregate queries, migrate data to Druid data warehouse defining aggregation policy for every table.