Real-Time CDC-Based Data Pipeline for Cloud Databases: Architecture, Implementation and Performance Evaluation in E-Gaming CRM Systems

The goal of this project was to create a system that could manage real-time data pipelines in the cloud for PostgreSQL databases. The software system described in this paper takes data from a source, uses Debezium to capture changes to the source, and sends those changes via an Apache Kafka message broker to a Kafka consumer for further processing. We utilized FastAPI as a framework for building REST APIs to receive messages from the Kafka consumers and perform data transformations, manage connections to database instances, and deliver analytics including RFM customer scoring. We also developed a method for measuring performance of the data pipeline based on the amount of time it took to complete the entire data pipeline process from start to finish. We measured the average latency through the data pipeline at 389ms (215x faster than batch ETL processes with 1-minute latency), sustained 2,300 records/second throughput, over 99.96% consistent data transfer, and up to 84% decrease in API response times when Redis caching was added. The system supports scaling to 500 concurrent users with negligible failures, confirming that there is sufficient performance when using this method to synchronize data in real-time with cloud databases for latency-sensitive applications. Some potential avenues for future work include: performing the above evaluation in a multi-cloud manner by deploying the same data pipeline across multiple cloud providers (e.g. AWS RDS, Azure Database for PostgreSQL, Google Cloud SQL) to quantify provider-specific differences in performance; incorporating machine-learning based anomaly detection into the pipeline to detect real-time data quality and pipeline failures; investigating auto-scaling methods for Kafka consumers and FastAPI workers based on observed consumer lag and API response time; and evaluating the ability of Debezium to handle schema evolution scenarios (adding or removing columns, changing data types) and how well it would perform in production-like settings. Reproducibility. All source code, Docker Compose configurations, Debezium connector definitions, synthetic data generators, and benchmark scripts used in this study are publicly available at: https://github.com/kananbunyad/CDC-Pipeline-Research. The repository includes a one-command setup script (setup.sh) and a master benchmark runner (run_all.sh) that reproduce all reported measurements.

[1]       M. Armbrust, A. Fox, R. Griffith, A. D. Joseph, R. Katz, A. Konwinski, G. Lee, D. Patterson, A. Rabkin, I. Stoica, and M. Zaharia, "A view of cloud computing," Communications of the ACM, vol. 53, no. 4, pp. 50-58, 2010. [2]       A. Corbellini, C. Mateos, A. Zunino, D. Godoy, and S. N. Schiaffino, "Persisting big-data: The NoSQL landscape," Information Systems, vol. 63, pp. 1-23, 2017. [3]       R. Kimball and M. Ross, The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling, 3rd ed. Indianapolis, IN: Wiley, 2013. [4]       M. Kleppmann, Designing Data-Intensive Applications: The Big Ideas Behind Reliable, Scalable, and Maintainable Systems. Sebastopol, CA: O'Reilly Media, 2017. [5]       N. Narkhede, G. Shapira, and T. Palino, Kafka: The Definitive Guide: Real-Time Data and Stream Processing at Scale. Sebastopol, CA: O'Reilly Media, 2017. [6]       PostgreSQL Global Development Group, "Logical Replication," PostgreSQL 15 Documentation, 2024. [Online]. Available: https://www.postgresql.org/docs/current/logical-replication.html [7]       PostgreSQL Global Development Group, "Write-Ahead Logging (WAL)," PostgreSQL 15 Documentation, 2024. [Online]. Available: https://www.postgresql.org/docs/current/wal-intro.html [8]       Debezium Community, "Debezium Documentation: Connectors for PostgreSQL," 2024. [Online]. Available: https://debezium.io/documentation/reference/connectors/postgresql.html [9]       Amazon Web Services, "AWS Database Migration Service User Guide," 2024. [Online]. Available: https://docs.aws.amazon.com/dms/latest/userguide/ [10]    G. Wang, J. Koshy, S. Subramanian, K. Paramasivam, M. Zadeh, N. Narkhede, J. Rao, J. Kreps, and J. Stein, "Building a replicated logging system with Apache Kafka," Proc. VLDB Endowment, vol. 8, no. 12, pp. 1654-1655, 2015.