Case Study: Large Online Retail Customer – Change Data Capture (CDC) & Database Replication Across Cloud Platforms

By intelia | @intelia | October 24

intelia implemented a templated, flexible and scalable process to transfer data and real-time CDC changes from OLTP databases in AWS to an analytics warehouse in GCP (BigQuery) to improve business reporting, performance and maintain history.

The challenge

The Customer was facing a challenge in business reporting where critical reports were out of sync and in some cases were taking too long to load causing operational issues. The existing OLTP database on AWS was unable to handle the rising demands of real-time reports. Separating the databases used for reporting operations from the transactions and utilising the power of BigQuery was chosen.

The solution

intelia designed a process to stream CDC data in real-time as well as batch fill historical data in BigQuery that serves as the reporting database as well as a historical backup of database records and aids in disaster recovery. The solution uses Debezium for CDC data along with Pub/Sub, Dataflow, Cloud Storage and Cloud Functions to automatically create replicas of the OLTP tables.

The results

The dashboards that use the BigQuery tables are real-time and the business reports load faster while being immune to database outages. The organisation also has historical records of all tables that can be used for further analytical processing.

About the customer

The customer is a Sydney-based, Australian online fashion and sports retailer.

Industry: Retail & Wholesale

Primary project location: Australia