Understanding Snowflake and Star Schema
Snowflake Schema
It has multiple fact tables to avoid complexity and create a more normalised structure.
Star Schema
It consists of a single fact table connected to a dimension table visualised as a Star. A single link is established between the fact table and the dimension table.Snowflake Schema extension of Star Schema?
- Large-dimensional Tables are normalised into multiple sub-dimensional tables.
- Every dimension table is associated with a sub-dimension table and has multiple links.
- A Snowflake schema is a Star schema structure normalised 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 Data warehouse world, storage and query performance optimisation 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 denormalise the lookup tables to reduce the number of joins in the query.
- To implement Data Migration into the new data warehouse to give a response in seconds because of slow aggregation queries.
Solution Offerings for Data Warehouse and Data Migration
- Denormalisation - Understand schema from conceptual models to physical models, followed by the granularity of Snowflake schema.
- Data Migration—To reduce the latency of aggregate queries, migrate data to the Druid data warehouse, defining an aggregation policy for every table.
Getting Started with Data Warehousing
A Data WareHouse stores massive data, a central repository of information analysed to make better informed decisions. Latest Data Warehouse architectures include -- 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 the database, aggregate tables, index usage, Vertical and Horizontal Partitioning, Denormalisation, server tuning.
- Delivering Reports: Network traffic and server setups can delay report delivery. Implement Performance Tuning to avoid this.
More Ways to Explore Us
Snowflake Cloud Data Warehouse Architecture
FinOps for Snowflake
How Snowflake Empowers Multimodal AI Solutions
