91% Faster Dashboard Queries with a Snowflake Data Warehouse
7 Source Systems Integrated | 9-Week Implementation | Sub-4-Second Queries
At a Glance
| Industry | Global Retail |
| Markets | 14 |
| Services | Implementation of Snowflake Data Warehouse, Data Integration, Dimensional Modeling, and Orchestration |
| Challenge | With heterogeneous systems, scattered data, and sluggish dashboards, query time averaged 45 seconds. |
| Solution | I have designed and built a new data warehouse in Snowflake from scratch based on dbt to model dimensional data and Airflow to orchestrate the data pipeline and enforce RBAC (role-based access control). |
| Key Result | As a result, I have achieved a reduction of dashboard query time from 45 seconds on average to less than 4 seconds, which is 91% faster, and delivered a scalable and production-ready solution within 9 weeks. |
About the Client
The company is an international retail business that operates in 14 markets. The company's data was scattered across several enterprise and regional systems.
Its analysis relied heavily on data from different ERPs, point-of-sale systems that are specific to the region, and an old-fashioned data warehouse.
The Challenge
In this case, the client was looking for an up-to-date data warehouse that would unify data from seven diverse source systems to the analytics environment that would allow scale.
The main problems were:
- Data scattered across different sources; there were only seven data warehouse systems from which to collect.
- Data from various ERP systems had to be unified in an analytics data model.
- Few local markets use POS platforms as a data source; this is another reason for complicated data integration.
- Apart from the off-prem warehouse where analytics data was available, legacy data has to be connected as well.
- Slow dashboards: Most dashboard queries lasted around 45 seconds, and that was way too long for the company's analysts, so they had little time to run reports based on queries that could take a few minutes.
- Cross-market data access: In order to manage the activities in 14 markets, they have to have a secure way of sharing data within the new platform.
A brand NEW data infrastructure solution was required by the company in order to unify these sources while significantly boosting the analytics performance.
The Solution
Aegis Softtech provided Snowflake consulting and implementation expertise to develop a Greenfield Snowflake data warehouse.
By leveraging Snowflake, we created a centralized system that is now the foundation for enterprise analytics. Besides this, we also did integration for all seven source systems. Dimensional modeling was done by combining Snowflake with the dbt tool.
Data orchestration, on the other hand, was done using Apache Airflow.
Seven-Source Data Integration
Data from seven diverse sources were combined within Snowflake, which included, among others:
- More than an ERP system
- Several POS systems covering regional markets
- An older, on-site data warehouse
All this resulted in one single data store that served as the backbone of the client's analytics activities in 14 countries.
Snowflake Data Warehouse Architecture
The data warehouse was a Greenfield Snowflake implementation that did not build on the previous system architecture
It delivered to the client a truly new cloud data warehouse that was tailored to his analytics and reporting needs.
Dimensional Modeling with dbt
The dimensional modeling layer development was based on dbt.
The modeling technique basically turned integrated source data into well-organized datasets that would later be used to generate reports and for analytics.
Role-Based Access Control
Implementation of role-based access control was an important step in ensuring that access to data within the Snowflake environment was managed properly.
The implementation of such a method of access limitation ensured that users who dealt with the data on an enterprise level throughout different business lines and markets had a controlled way of accessing that data.
Data Orchestration with Airflow
We chose Apache Airflow for orchestration of the data workflows on the new platform as it gave us the means to systematically carry out all movements from the source systems to the warehouse.
Production-Ready Solution Delivered Within 9 Weeks
The company managed to deliver the data warehouse for Snowflake as a production-ready solution within 9 weeks of time.
Within the 9 weeks, the team completed source integration, dimensional modeling, orchestration, security, and production deployment.
The Results
By redesigning and migrating their analytics platform to Snowflake, the client improved their analytics performance substantially and consolidated their data resources.
- The Dashboards Speed-Up is 91%:
The average query time decreased from 45 seconds to less than 4 seconds. - A Full Build Took 9 Weeks:
By developing the warehouse from scratch, the team managed to design the production-ready solution within 9 weeks. - Integration of 7 Sources:
Data from ERP, POS, and legacy warehouse were collected and provided in a single analytics platform. - Covering 14 Markets:
The new environment was designed to offer a unified foundation for the customer's worldwide retail operations. - Cloud-Based Data Warehouse:
The old and split analytics environment was replaced with a modern cloud computing Snowflake data warehouse solution. - Restriction of data to certain roles:
By using role-based access control, permissions across the platform became more structured.
Technology Stack
- Snowflake
- dbt
- Apache Airflow
- Dimensional Data Modeling
- Role-Based Access Control (RBAC)
- ERP Data Integration
- POS Data Integration
- Cloud Data Warehousing
Need to Modernize Your Data Warehouse with Snowflake?
By using Snowflake data warehouses, companies benefit from the ability to consolidate data from disparate sources, thus improving the legacy analytical infrastructure and ensuring greater reporting efficiency.
Aegis can assist you with developing an advanced-level Snowflake environment that is perfectly integrated with your business needs, right from the phases of data integration and modeling till the implementation stages like orchestration, security, and actual production deployment.
Get Expert Snowflake Data Warehousing Consultation
*Client identity is confidential.*