Business Challenge
In the world of Data warehouse, storage and query performance optimization are the major concerns. SnowFlake schema was used to build Data Warehouse, and as a result, when they use to query on the Data Warehouse, there were a lot of joins in the query.
The major challenges were -
-
Understanding the schema and then denormalizing the lookup tables to reduce the number of joins in the query.
-
Aggregation queries were too slow so we need to migrate our data into our new data warehouse that can give the response in seconds.
Solution Offered
-
Denormalization - In collaboration with SRP Solutions team, we started understanding schema from conceptual models to physical models and then we understood the granularity of our snowflake schema.
-
Data Migration - The other challenge was to reduce the latency of aggregate queries. So we migrated our data to Druid Data Warehouse in which we defined our aggregation policy for every table.