Eliminating batch latency with zero-impact distributed event streaming
When enterprise reporting depends on recurring daily or hourly queries against live production databases, the operational core suffers: table locks stall customer checkouts, server CPUs spike, and business decisions are made on stale data.
We set out to engineer a modern, cloud-native streaming pipeline that synchronizes operational relational databases (OLTP) with analytical data lakes (OLAP) in real time—without writing a single invasive query against active application tables.
By deploying Debezium's Change Data Capture (CDC) engine on AWS MSK Connect, every row-level modification in Microsoft SQL Server is captured at the transaction log level. Events are published as partitioned messages into an Amazon MSK cluster and pushed continuously into Amazon S3 storage buckets using an S3 Sink Connector.
The architecture is air-gapped inside private AWS subnets via VPC Endpoints and governed by fine-grained IAM authentication, giving enterprise engineering teams a resilient, audit-ready data stream ready for real-time dashboards, machine learning, and fraud detection.
The Batch Query Bottleneck
The hidden operational cost of running analytics on production databases
Traditional data warehousing relies on periodic SQL dumps that strain live production servers. During high-traffic hours, scheduled analytical queries cause lock contention and increase transaction latency for end users. The project solved this by decoupling analytical ingestion from transactional operations.

Zero-Impact Change Data Capture (CDC)
Capturing row mutations directly from the SQL Server transaction log
Configured Change Data Capture (CDC) on Amazon RDS Microsoft SQL Server. Instead of scanning tables, Debezium reads raw transaction log files directly—instantly capturing INSERT, UPDATE, and DELETE operations with zero execution overhead on active production workloads.

Amazon MSK Distributed Message Backbone
Scalable event brokering with AWS Managed Streaming for Apache Kafka
Provisioned a high-throughput, fault-tolerant Amazon MSK cluster partitioned across availability zones. Configured automated topic management following strict namespace patterns (`database.schema.table`) with IAM-based client authentication and encrypted data-at-rest protocols.

MSK Connect & S3 Data Lake Sink
Continuous object delivery to Amazon S3 without intermediate compute
Deployed a managed Amazon S3 Sink Connector on MSK Connect. The connector consumes messages directly from Kafka topics and writes partitioned, structured data files to Amazon S3 in near real time, forming the foundational raw layer of a cloud data lake.

Air-Gapped Private VPC Security
Confining enterprise data flow to private AWS network endpoints
Engineered network boundaries using AWS VPC Endpoints (PrivateLink) and security groups. The MSK cluster, RDS instance, and S3 buckets communicate exclusively across private internal routing tables, preventing sensitive corporate financial and transactional data from traversing the public internet.

End-to-End Real-Time Verification
Kafka CLI validation on EC2 and sub-second mutation audits
Verified pipeline latency using a dedicated EC2 consumer node running Kafka CLI tools. Executing row updates in SQL Server demonstrated immediate schema-aware change events landing in Amazon S3 in under 250 milliseconds, providing an audit-ready event trail for downstream BI and analytics.
< 250ms
Physical row mutation in SQL Server to structured JSON/Parquet arrival in the Amazon S3 Data Lake.
0% Query Lock
Eliminated destructive scheduled table scans by reading the database transaction log via Debezium CDC.
100% Private VPC
End-to-end data flow confined to private subnets using AWS VPC Endpoints and strict IAM roles.
What did I learn?
Log-based Change Data Capture always beats batch query polling.
Analytical teams should never run bulk `SELECT` queries against live production databases. Reading the low-level transaction log via CDC allows continuous data ingestion without causing table locks or slowing down customer transactions.
Distributed message brokers decouple production from downstream failure.
Routing transactions through Apache Kafka topics ensures that downstream outages (such as an analytical data lake slowdown) never back up or crash core operational payment systems.
Cloud data pipelines must be isolated from the public internet.
Enterprise database replication demands zero-trust networking. Utilizing AWS VPC Endpoints allows MSK Connect and S3 to communicate entirely over Amazon's private network backbone, eliminating exfiltration risks.
