About Client
One of world’s top crypto casinos in terms of revenue and number of players
Challenge
The client was using MySQL as their backend system for years. It would take the IT team anywhere between 24 to 48 hours to generate reports for different departments.
Being a crypto casino, the organization had to be agile with their decision making abilities. They were in need of a solution that could provide them with continuous updates and insights into their daily/hourly operations.
Our Solution
Data from MySQL was ingested into S3 buckets using AWS Database Migration Services (DMS) for Change Data Capture (CDC), enabling data to be captured and updated in near real-time.- AWS S3 was used as the stage with Snowflake as the data warehouse and operational data store (ODS). AWS Lambda was used for processing data into Snowflake from S3.
- Summary tables consisting of mission-critical business KPIs were created on Snowflake with pre-computed values to allow for quick reporting and optimal performance. This reduced the need to query the data warehouse multiple times.
- Tableau dashboards were used for reporting the data loaded from the summary tables in batches to enable efficient processing of large datasets while minimizing resource usage.
-
AWS DMS: Enables near real-time data replication and ensures that data is up-to-date and readily available for downstream processing without impacting the source database’s (MySQL) performance.
-
Snowflake Data Warehouse with AWS S3 for staging: Snowflake has a cult following in the cloud data warehouse space with many enterprises moving towards the platform for its performance and flexibility. Although Snowflake is great at querying massive amounts of data, data ingestion must be performed to handle large amounts of data, without which, there lies a risk of querying outdated values and returning irrelevant analytics.Hence, S3 was used as the staging area as Snowpipe does not support continuous ingestion yet. Plus, S3 is cost-effective and integrates well with DMS.
-
AWS Lambda: Lambda-S3 event architecture offers a more efficient and cost-effective approach to processing data in near real-time
-
Tableau: The most popular and widey adopted visualization tool in the market known for being the extremely user-friendly and enabling users with powerful capabilities including real-time data exploration
Outcome
A powerful near-real time analytics platform consisting of AWS DMS (for continuous ingestion), AWS S3 (stage), Snowflake (ODS and data warehouse), AWS Lambda (data processing) and Tableau (vizualization) with MySQL as the source for quick decision making
The summary tables and batch processing helped optimize performance and minimize costs. Snowflake’s unique pricing system charges the user based on the queries, reducing the queries using summary tables cut costs significantly as well as optimized the performance of Snowflake and AWS Lambda.- Summary tables also ensured that Tableau dashboards refreshed quickly and provided the decision makers with all necessary information almost instantly.