Building (and Debugging) a Real-Time Stock Trading Pipeline

Scenario

Real-time trade data needs to be ingested, aggregated, stored, and made visible on a dashboard — without losing events, without blocking on failures, and with the whole thing runnable and re-runnable on a schedule rather than babysat by hand. That’s the same shape of problem behind a lot of production streaming systems (fraud detection, risk monitoring, live pricing), so I built a local, fully containerized version of it end to end for interview prep — with the real debugging stories included, not a cleaned-up tutorial version.

Scope: a producer generates simulated stock trade events, a streaming job aggregates them into per-symbol, per-minute windows, results land in a database, a dashboard reads from it, and an orchestrator ties the whole pipeline together on demand.

Approach

Two versions of the Spark job

• Streaming version — query.awaitTermination(), runs forever, used for a live/manual demo watched via the Spark UI.
• Batch version — trigger(availableNow=True) instead of a continuous trigger. Processes everything currently in Kafka across as many micro-batches as needed, then stops. Required for Airflow orchestration, since Airflow tasks must complete — an infinite streaming job would never let the task finish.

foreachBatch for the Postgres sink

Structured Streaming has no native JDBC/Postgres streaming sink. foreachBatch converts each micro-batch into a regular batch DataFrame, which can then use any standard batch writer.

Keyed producer

Kafka messages are produced with key=symbol — guarantees all events for a given stock land in the same partition, in order.

Airflow uses Postgres, not SQLite, for its metadata DB

SQLite only supports Airflow’s SequentialExecutor (no task parallelism). Postgres enables LocalExecutor, matching how production Airflow deployments actually work.

Airflow controls other containers via the Docker SDK

The Airflow container has /var/run/docker.sock mounted in, and uses the docker Python package to exec commands inside the Kafka and Spark containers. In a real cloud setup, this role would be filled by an SSH operator or a Livy/EMR API call — same shape, different transport.

Tech Stack

• Kafka (apache/kafka:3.7.0) — durable event log, topic stock-trades, 3 partitions keyed by stock symbol
• Spark (apache/spark:3.5.1) — Structured Streaming job: reads Kafka, does windowed aggregation (avg price, trade count, total volume per symbol per 1-minute window), writes to Postgres
• PostgreSQL — sink table stock_price_summary; used my existing local install rather than adding a redundant container
• Grafana (grafana/grafana:11.1.0) — dashboard querying Postgres directly: price trend line chart, volume by symbol, summary stats, trade count over time
• Airflow (apache/airflow:2.11.0) — orchestrates the pipeline as a 4-task DAG: create topic → produce events → run Spark (bounded batch) → verify Postgres row count

Solution

[Producer] --> [Kafka topic: stock-trades] --> [Spark Structured Streaming] --> [Postgres] --> [Grafana dashboard]
                                                                                       ^
                                                                    [Airflow DAG orchestrates all of the above]

All services run via a single docker-compose.yml, on one Docker network, so containers reach each other by service name. Containers reach the host machine’s Postgres via host.docker.internal.

End to end: the producer publishes keyed trade events to Kafka, the bounded Spark job consumes and aggregates them into per-symbol windows and writes results to Postgres, Grafana queries Postgres directly for the dashboards, and the Airflow DAG drives the whole sequence — create topic, produce, run Spark, verify the row count landed — as one re-runnable, schedulable unit instead of a set of manually-run scripts.

The debugging stories (the actually useful part)

• MSK IAM “Access Denied” → “Authorization Failed”: AmazonMSKFullAccess only covers control-plane actions. Data-plane actions (Connect, CreateTopic, WriteData) need a custom policy — and critically, the resource ARN type must match the action (topic/... for topic actions, not cluster/... for everything).
• Watermarking, seen live: aggregated output doesn’t appear until the watermark advances past a window’s end boundary. I watched this happen in real time — data sat correctly in internal state before ever reaching a sink.
• Docker volume mount silently not applied: docker compose restart reuses the existing container and does NOT pick up new docker-compose.yml changes — only down + up (or up --build) actually recreates the container from the current file.
• Postgres 15+ default privilege changes: new users can connect but can’t CREATE TABLE by default — needs explicit GRANT ALL ON SCHEMA public, plus separate grants on sequences.
• Bitnami image deprecation: Bitnami moved most versioned Docker tags to an unmaintained legacy registry mid-project, breaking my bitnami/kafka and bitnami/spark pulls. Switched to official apache/kafka and apache/spark images instead.

Concepts demonstrated

• Kafka: topics, partitions, offsets, consumer groups, keyed partitioning for ordering, delivery guarantees
• Spark Structured Streaming: micro-batch processing, watermarking, windowed aggregation, output modes, checkpointing, foreachBatch
• Orchestration: DAG dependency chains, idempotent task design, bounding a naturally-infinite streaming job for scheduler compatibility
• Infra-as-code: Docker Compose multi-service orchestration, custom image builds, container networking
• Observability: Spark UI for live job monitoring, Grafana for downstream dashboards