SnowFlake Schema to Star Schema - Storage and 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

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

• 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.

Getting Started with Data Warehousing

• Amazon RedShift
• Google BigQuery
• Panoply

Data WareHouse Characteristics

• Highly Reliable
• Data Integrity
• Better Storage Performance
• Faster Sequential Reads
• Elimination of Physical Hardware
• Massive Parallel Processing

Data WareHouse Classification

• ETL (Extract, Transform, Load) Processes - Data Warehousing tunes the ETL processing to increase performance and reduce load time.
• Query Processing - Query Optimisation by understanding query execution in database, aggregate tables, index usage, Vertical and Horizontal Partitioning, Denormalization, server tuning.
• Delivering Reports - Network traffic, server setups delay in delivery of reports. Implement Performance Tuning to avoid this.