Near-real-time data pipeline using change data capture approach

Abstract

This study focused on designing and developing a near-real-time data pipeline, leveraging the Change Data Capture (CDC) approach to achieve real-time reporting for an online transaction processing business.

The company was required to report the financial transactions in near-real-time to the regulator for auditing and compliance purposes. However, the existing system was constrained, capable only of dispatching reports the following morning, and the reporting feature was coupled with the online transaction system. It was challenging to add the real-time reporting feature to the existing system.

This thesis explored the methodology known as CDC to streamline the transfer of transactional data to an analytical database, enabling the generation and delivery of reports in near-real-time. This approach successfully fulfilled the legal requirements for timely and accurate financial reporting.

Firstly, it investigated different technologies in this domain, including Apache Kafka, Debezium, Kafka Streams, ClickHouse, Prometheus, and Grafana.

Secondly, a production-ready data pipeline solution was built based on the selected platform and framework. This platform achieved 0.6 seconds of average end-to-end latency and about 30 thousand records per second of average throughput, and the performance fulfilled the business requirements of reporting the transactions in 5 minutes and demonstrated stability in its operation.

Potential enhancements and refinements to the system were also identified and discussed.