Here’s something nobody tells you about working on big, complex projects: you can spend years building production pipelines at scale and still lose touch with the basics. When everything is abstracted behind platform tooling, internal frameworks, and team conventions, it’s easy to forget why things work the way they do — you just know that they do.
I wanted to fix that. So I built this project from scratch. No internal tooling, no pre-built abstractions, just me, a blank repo, and a problem to solve.
The honest trigger? I bombed a take-home technical interview. The task wasn’t even that complex, but I hadn’t built a streaming pipeline end-to-end from zero in a while, and it showed. I used it as motivation to get back to basics and sharpen my craft. This project is the result of that. I had a lot of fun with it! Here’s a walkthrough of what I built, why I made certain design decisions, and what I’d do differently next time.
The Problem
A company needed a platform where logistics leaders could monitor orders and their details with minimal latency. The requirements were clear:
- Handle millions of records
- Support both historical and current-state queries
- Use a dimensional data model suitable for business reporting
- Make data available as soon as possible after changes occur
The business questions they needed to answer: how many open orders exist by delivery date and status? Which delivery dates have the most open orders? How many pending items are there per product? Who are the top customers with pending orders?
Architecture Overview
The pipeline follows a classic Lambda-inspired streaming architecture:
Postgres (source) → Debezium (CDC) → Kafka → Spark Structured Streaming → Analytics Schema → FastAPI
Each component plays a distinct role:
Postgres is the operational database — the source of truth for orders, order items, products, and customers. Write-Ahead Logging (WAL) is enabled to support Change Data Capture.
Debezium is a CDC tool that listens to Postgres’s logical replication stream and publishes every insert, update, and delete as a JSON event to Kafka. This means no polling, no batch jobs — changes propagate the moment they happen.
Kafka acts as the durable event bus between the source database and the streaming layer. Each table gets its own topic (e.g. finance_db.operations.orders).
Spark Structured Streaming consumes from Kafka, parses the Debezium JSON envelope, handles type conversions (epoch timestamps, variable-scale decimals for prices), and writes results to the analytics schema via JDBC.
Analytics schema — two layers live here: fact tables that store both the full event log and the current state of each entity, and mart views that pre-aggregate answers to the four business questions.
FastAPI is a thin serving layer that queries the mart views and exposes the business metrics as REST endpoints.
The Data Model
One of the more interesting design decisions was how to handle the dual requirement of historical data and current-state queries.
I ended up with two types of fact tables for orders:
analytics.fact_orders is an append-only event log — every CDC event lands here. This supports historical audit, time-travel queries, and rebuilding state from scratch if needed.
analytics.fact_orders_current keeps only one row per order ID — the latest known state. This is what the mart views query, since you don’t want to scan millions of events every time a logistics manager refreshes their dashboard.
For order items and products, only the current state table exists since historical versions weren’t required.
On top of the fact tables sit four mart views in SQL: open orders by delivery date, top delivery dates by order count, pending items by product, and top customers by open order count. Business logic lives in SQL, keeping the API thin and the data layer self-documenting.
How the Streaming Works
Spark reads from Kafka in micro-batches. Each message contains a Debezium envelope with the operation type (r for read/snapshot, c for create, u for update, d for delete) and the before/after state of the row.
For the current-state tables, I used a staging + merge pattern: each micro-batch is deduplicated to the latest row per key, written to a *_staging table, then merged into the *_current table using PostgreSQL’s INSERT … ON CONFLICT DO UPDATE. This is necessary because Spark’s JDBC writer doesn’t support native upserts.
Upsides
Near real-time propagation. CDC means changes flow through the pipeline within seconds of hitting the source database — no polling, no scheduled batch jobs, no stale data windows.
Full history preserved. The event log table gives you the complete audit trail. You can reconstruct the state of any order at any point in time, which is valuable for debugging and compliance.
Decoupled architecture. Each component can evolve independently. If you want to replace Spark with Flink, or swap the analytics database, Kafka acts as the buffer and nothing else needs to change.
Thin API layer. Business logic lives in SQL mart views, not in application code. This makes the API easy to maintain and the analytics layer reusable by any consumer — BI tools, notebooks, other services.
Runnable locally. The entire stack runs via docker compose up. No cloud account needed to develop or test.
Downsides and Limitations
Same Postgres instance for OLTP and OLAP. The analytics schema lives in the same database as the operational schema. This is fine for a side project but would be a significant problem in production — analytical queries would compete with operational writes for resources. A real implementation would separate them, likely using a dedicated data warehouse.
Spark over JDBC is not ideal for streaming. Writing to Postgres via JDBC foreachBatch works but introduces latency and connection overhead. A native sink (like writing to Iceberg on S3) would be more scalable and performant.
No schema evolution handling. If the source database schema changes — a new column, a renamed field — the Debezium events change shape and the Spark parser will break. A schema registry (like Confluent Schema Registry with Avro) would handle this gracefully.
No backpressure or flow control. If Kafka accumulates a large lag, Spark will try to process it all at once. Production pipelines need rate limiting and lag monitoring.
Manual stream management. Streams are started manually from a Jupyter notebook. In production, this would be replaced by a proper job scheduler or a containerised Spark application managed by Kubernetes or EMR.
What I’d Do Differently in Production
Replace the Jupyter notebook with a proper Python application deployed as a Docker container. Use Apache Iceberg instead of JDBC writes for the analytics layer — columnar storage, time travel, and schema evolution out of the box. Add a schema registry to handle evolving Debezium payloads. Separate the OLTP and OLAP databases. Add monitoring (Datadog or Prometheus) for Kafka consumer lag, pipeline throughput, and API latency.
Running It Yourself
The full code is on GitHub: https://github.com/triatomo/analytics-data-pipeline-task
You’ll need Docker and docker-compose. Run docker compose up, open the Jupyter notebook at localhost:8888, run the streaming cells, and hit the API at localhost:8000/docs to explore the endpoints.
Wrapping Up
This project was a great exercise in combining CDC, streaming, dimensional modeling, and API serving into a single coherent system. The stack — Debezium, Kafka, Spark, Postgres, FastAPI — represents a realistic subset of what you’d find in a production streaming pipeline at a mid-to-large engineering company.
If you have questions or feedback, feel free to reach out or open an issue on the repo!