Real time data sync, secure access and enhanced BI for scalable analytics

When you're running 4,000 individual MySQL databases, monitoring everything is akin to trying to watch a movie through a kaleidoscope. Our enterprise customer needed to pull all that data into Amazon Redshift: the customers' BI and analytics could be run from one trusted warehouse, but they needed nightly updates to ensure the insights were always current, and the migrations could not break production dashboards.

Challenges

• Massive database migration: Consolidating thousands of distinct MySQL instances into one Redshift environment without breaking current reports.
• Continuous data sync: Daily changes had to flow into Redshift automatically to maintain up to date analytics.
• Seamless analytical performance: Users expected queries to stay fast during and after the migration, even as new data kept streaming in.

The Solution

• We built an automated pipeline based on AWS DMS for the bulk migration and Change Data Capture (CDC) for new records and updates entering the database at real time.
• To ensure query performance was optimal, we developed Redshift Materialized Views for their common analytical patterns.
• Cleaning and transforming data was managed with AWS Step Functions and AWS Lambda (Python) scripts, ensuring the data was accurate before loading it.
• Finally, we set granular Redshift permissions to control access to the data for only the appropriate people.

The Impact

• 3x faster migration: Automated replication seemed to finish about 300% faster than manual scripts.
• 100% data accuracy: Integrity checks between source and destination data and auto reruns of processes ensured every row made it correctly.
• Secure and controlled access: Role-based permissions created a balance between governance and analyst productivity.
• Improved BI and analytics: Up to date waiting consolidated data enabled richer reporting and more confidence in decision-making.