Near Real-time Reporting and Analytics for a Crypto Casino

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