Real-Time Transaction CDC Pipeline

A production-grade, event-driven data pipeline that ingests financial transactions through a REST API, captures every database change via CDC (Change Data Capture), streams and transforms the data in real-time, and delivers it to a query-ready data lake. This project replicates how companies like Razorpay, Stripe, and Capital One process millions of payment events daily — from the moment a customer taps "Pay" to the point where analysts query settlement trends across cities and merchants.

---

Architecture

---

Tech Stack

Layer	Service	Purpose
Ingestion	API Gateway	REST API endpoint — receives transaction requests over HTTP
Ingestion	AWS Lambda (Ingestion)	Validates payload, generates server-side fields, writes to database
Storage	DynamoDB	Low-latency NoSQL database for live transaction records
CDC	DynamoDB Streams	Captures every insert and update as a real-time change event
Routing	EventBridge Pipes	Serverless connector — routes stream events to Kinesis
Streaming	Kinesis Data Streams	Real-time data highway for high-throughput event delivery
Delivery	Kinesis Firehose	Managed delivery — batches, transforms, and writes to S3
Transformation	AWS Lambda (Transform)	Flattens DynamoDB format, adds computed fields for analytics
Data Lake	Amazon S3	Scalable, durable storage for historical transaction data
Cataloging	AWS Glue (Crawler + Data Catalog)	Auto-discovers schema and registers table metadata
Analytics	Amazon Athena	Serverless SQL engine — queries S3 data directly
Simulation	Python (requests)	API client script simulating real-time transaction traffic

---

End-to-End Workflow

Phase 1 — Ingestion

• The client sends a POST /transactions request with payment details. API Gateway forwards it to the Ingestion Lambda, which validates required fields, generates a UUID transaction_id and ISO 8601 timestamp server-side, resolves the merchant_category from a lookup table, and writes the complete record to DynamoDB with status pending.

Phase 2 — CDC and Streaming

• DynamoDB Streams automatically captures the INSERT event the moment the record is written. EventBridge Pipe reads from the stream and forwards each event into Kinesis Data Stream. Firehose consumes from the stream, buffers records into micro-batches, and invokes the Transformation Lambda.

Phase 3 — Transformation

• The Transformation Lambda decodes the base64-encoded DynamoDB stream records, flattens the typed attribute format ({"S": "value"}) into clean JSON, and adds computed fields — transaction_date, amount_category, and event_type (INSERT or MODIFY).

Phase 4 — Data Lake and Analytics

• Firehose writes the transformed NDJSON files to S3, automatically partitioned by year/month/day/hour. Glue Crawler scans the S3 path, discovers the schema, and registers the table in the Glue Data Catalog. Athena queries the cataloged table using standard SQL.

Phase 5 — CDC in Action

• After a short delay, the client sends an update for the same transaction_id, changing status from pending to success or failed — simulating bank confirmation. The Lambda performs an update_item on DynamoDB, which triggers a MODIFY event in Streams. The MODIFY event flows through the same pipeline and lands in S3 alongside the original INSERT — demonstrating full Change Data Capture.

---

Key Engineering Features

• Full CDC (Change Data Capture): Every database change — both inserts and updates — is captured, streamed, and delivered to the data lake. The pipeline tracks the complete transaction lifecycle: pending → success/failed.

• Server-side field generation: The client never generates IDs or timestamps. The Ingestion Lambda generates transaction_id (UUID), timestamp (server clock), and merchant_category (lookup) — following how real payment APIs separate client concerns from server logic.

• Schema-on-read architecture: Data lands in S3 as raw JSON. The schema is applied only at query time by Athena through the Glue Data Catalog. This allows schema evolution without pipeline changes.

• Automatic partitioning: Firehose partitions S3 data by year/month/day/hour automatically. Athena leverages this to skip irrelevant partitions during queries, reducing scan cost and improving performance.

• Weighted realistic data distributions: Transaction amounts follow a log-normal distribution (most small, few large). Payment methods, statuses, and currencies use weighted probabilities matching real-world patterns (e.g., 85% success, 10% failed, 5% pending).

• Graceful error handling: The Transformation Lambda handles missing or malformed fields without crashing. Failed records are marked as ProcessingFailed and returned to Firehose for retry or dead-letter routing.

---

Analytics

Daily Transaction Volume and Revenue

SELECT transaction_date, currency, COUNT(*) AS total_transactions,
       ROUND(SUM(transaction_amount), 2) AS total_amount
FROM financial_transactions_db.transactions
WHERE event_type = 'INSERT'
GROUP BY transaction_date, currency
ORDER BY transaction_date DESC, currency;

Top Merchants by Revenue

SELECT merchant_name, merchant_category, currency, COUNT(*) AS txn_count,
       ROUND(SUM(transaction_amount), 2) AS total_revenue
FROM financial_transactions_db.transactions
WHERE transaction_status = 'success' AND event_type = 'MODIFY'
GROUP BY merchant_name, merchant_category, currency
ORDER BY total_revenue DESC LIMIT 10;

City-wise Transaction Breakdown

SELECT city, currency, COUNT(*) AS txn_count,
       ROUND(SUM(transaction_amount), 2) AS total_amount
FROM financial_transactions_db.transactions
WHERE event_type = 'INSERT'
GROUP BY city, currency
ORDER BY total_amount DESC;

---

Repository Layout

realtime-transaction-cdc-pipeline/
├── ingestion_lambda.py          # Lambda behind API Gateway — validates, enriches,
│                                  generates transaction_id/timestamp, writes to DynamoDB.
│                                  Handles both CREATE (put_item) and UPDATE (update_item).
│
├── transformation_lambda.py     # Firehose transform Lambda — decodes base64 DynamoDB
│                                  stream records, flattens typed attributes to clean JSON,
│                                  adds computed fields (transaction_date, amount_category,
│                                  event_type, updated_at).
│
├── simulate_transactions.py     # API client script — simulates real-time payment traffic.
│                                  Creates transactions as "pending", then settles them to
│                                  "success" or "failed" to demonstrate full CDC lifecycle.
│
├── athena_queries.sql           # Glue database setup + 5 production-ready analytical
│                                  queries (daily volume, payment method analysis, top
│                                  merchants, hourly patterns, city breakdown).
│
├── requirements.txt             # Python dependencies (requests library for API client).
│
├── screenshots/                 # AWS Console screenshots for architecture and Athena results.
│
└── README.md                    # This file.

---

Author

Osama Mustafa

• LinkedIn
• GitHub