AWS Streaming Pipeline

AWS Streaming Pipeline

ABOUT THE PROJECT

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.

COLLABORATOR / SPONSOREnterprise Architecture BoardCloud & Data Governance
COLLABORATOR / SPONSORShakeel Ahamed KCloud Data Architect & Distributed Systems Engineer
01

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.

Fig 5.1 — High-level event-driven streaming topology from RDS through MSK to S3.
Fig 5.1 — High-level event-driven streaming topology from RDS through MSK to S3.
02

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.

Fig 5.2 — Transaction log configuration table showing zero query locking on RDS.
Fig 5.2 — Transaction log configuration table showing zero query locking on RDS.
03

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.

Fig 5.3 — Multi-AZ Kafka broker cluster running within private AWS VPC subnets.
Fig 5.3 — Multi-AZ Kafka broker cluster running within private AWS VPC subnets.
04

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.

Fig 5.4 — MSK Connect task telemetry streaming JSON payloads into Amazon S3.
Fig 5.4 — MSK Connect task telemetry streaming JSON payloads into Amazon S3.
05

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.

Fig 5.5 — PrivateLink VPC endpoints routing data entirely off the public internet.
Fig 5.5 — PrivateLink VPC endpoints routing data entirely off the public internet.
06

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.

Fig 5.6 — Terminal validation recording sub-250ms row mutation events arriving in S3.
MEASURED VELOCITY & IMPACT
EVENT REPLICATION SPEED

< 250ms

Physical row mutation in SQL Server to structured JSON/Parquet arrival in the Amazon S3 Data Lake.

OLTP DATABASE OVERHEAD

0% Query Lock

Eliminated destructive scheduled table scans by reading the database transaction log via Debezium CDC.

NETWORK CONFINEMENT

100% Private VPC

End-to-end data flow confined to private subnets using AWS VPC Endpoints and strict IAM roles.

ARCHITECTURAL RETROSPECTIVE

What did I learn?

01

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.

02

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.

03

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.

Let's work together!

Partnering with ambitious founders and businesses to design, build, and scale world-class digital products.

Whether you're taking a traditional business online, launching an ambitious new product, or fixing operational bottlenecks with intelligent software, let's talk directly at

Keep scrolling or click to go next project

Churn Retention Dashboard

Churn Retention Dashboard

Churn Retention Dashboard