Data Engineering System
CDC E-commerce Analytics Pipeline
Simulated near real-time analytics for order status, inventory movement, customer behavior, and revenue tracking without repeatedly polling source tables.
- PostgreSQL WAL
- Debezium
- Kafka
- SCD2 + facts
Problem
E-commerce orders, inventory, and customer records change throughout the day, making scheduled extracts too slow for operational analytics.
Solution
Built a CDC pipeline that captures PostgreSQL WAL changes with Debezium, streams them through Kafka, and processes them into analytics tables with Python.
Results
Simulated near real-time analytics for order status, inventory movement, customer behavior, and revenue tracking without repeatedly polling source tables.
Solution
The pipeline captures row-level source changes from PostgreSQL, publishes entity-specific events into Kafka topics, and transforms them into dimensional outputs, snapshots, and fact tables for dashboards.
Architecture
PostgreSQL source database, WAL-based Debezium connectors, Kafka topics, Python CDC consumer, SCD Type 2 dimensions, fact tables, snapshots, and analytics dashboards.
Scalability Considerations
Scale with Kafka topic partitioning, consumer groups, offset management, bounded retention, and separate consumers for high-volume entities.
Engineering Decisions
- Used CDC to avoid heavy polling against transactional tables.
- Modeled change events around business entities: orders, customers, products, and inventory.
- Used SCD Type 2 patterns where historical attribute changes matter.
- Separated event capture from transformation so ingestion can keep running while analytics logic evolves.
Tradeoffs Considered
- CDC reduces source database load, but it introduces connector monitoring, offset management, and schema-change handling.
- SCD Type 2 preserves history, but it increases table complexity and requires careful effective-date logic.
- Kafka enables independent consumers, but topic retention and replay strategy must be designed upfront.
Results
- Enabled near real-time visibility into operational source changes.
- Reduced pressure on the transactional database.
- Created a dimensional foundation for inventory, order, customer, and revenue analytics.
What I Would Do In Production
Add connector health monitoring, schema-change alerts, reconciliation checks, dead-letter topics, backfill workflows, exactly-once handling where required, and CI/CD for consumers.