Saaim Abdullah
Email me
ProjectsJune 20, 2026

Real-time e-commerce ETL pipeline

Saaim Abdullah
View source
Built with
  • Apache Kafka
  • PySpark
  • Apache Airflow
  • PostgreSQL
  • Parquet
  • S3-compatible storage
  • Docker Compose

The problem

E-commerce reporting fails in a specific, expensive way. The dashboard, the finance export and the ops spreadsheet each compute "revenue" from a slightly different slice of the same raw events, and nobody can say which one is right. The fix is not a better dashboard. It is one modelled layer every consumer reads from, fed by a pipeline that can be replayed when the definitions change. Orders arrive continuously, so a nightly batch leaves operations blind for the whole working day.

Constraints

Built solo as a deep engineering project, so the constraint set was chosen deliberately: everything runs locally on Docker Compose, reproducible from a clone with one command, with no cloud account required. The design still had to be honest about scale, which is why the write-up includes a reference architecture mapping every component to its managed AWS equivalent rather than pretending Compose is production. Two more constraints came from the data rather than from the setup. Raw events are messy and the schema drifts, so the pipeline has to survive that without losing anything. And every transformation has to be re-runnable from source, because business definitions change after launch.

How it's built

A producer publishes orders to a Kafka topic called orders, on a broker running in KRaft mode so there is no ZooKeeper to operate. PySpark Structured Streaming consumes that topic and writes raw Parquet to bronze on S3-compatible object storage. Two batch jobs follow: bronze_to_silver.py validates timestamps, drops null and invalid records and deduplicates, then silver_to_gold.py builds the dimensional model in PostgreSQL. Gold is a star schema of four tables: dim_customer, dim_product, dim_date and a central fact_sales keyed to all three. dim_date carries the calendar attributes analysts otherwise recompute in every query, including a is_weekend flag.
The shipped pipeline: a producer writes orders to a Kafka topic, PySpark Structured Streaming consumes it and writes raw Parquet to a bronze layer on object storage, and two Airflow-scheduled batch jobs promote bronze to silver and silver to a star schema in PostgreSQL.
What I actually built. Kafka, PySpark, Airflow and Postgres, running on Docker Compose.
The same pattern at enterprise scale is a different set of components doing the same jobs, and mapping one onto the other is most of what moving this to a managed platform involves. I drew that mapping out separately, as a design exercise rather than as a deployment.
Reference architecture, not the shipped system
An enterprise AWS reference architecture for the same medallion pattern: Amazon MSK and Kinesis ingesting from web, mobile, IoT and CDC sources, Spark on EMR and Glue for streaming and batch transforms, MWAA orchestrating, an S3 data lake split into bronze, silver and gold layers, Redshift as the serving warehouse, and QuickSight, Tableau, Power BI and SageMaker consuming the gold layer, all across two availability zones with IAM roles, CloudWatch, CloudTrail and Lake Formation governance.
The pipeline I built runs Kafka, PySpark, Airflow and Postgres on Docker Compose. This is the reference architecture I designed for the same pattern at enterprise scale on AWS, mapping each component: MSK for Kafka, EMR for Spark, MWAA for Airflow, Redshift as the serving warehouse.
What Airflow does and does not own. The DAG orchestrates the batch half of the pipeline and nothing else. The producer and the Structured Streaming job are long-running processes started outside Airflow, because a scheduler built around tasks that finish has no good way to own a task that never does. I tried modelling the streaming job as a scheduled task first. It works until the first run overruns its interval, at which point Airflow is either running two consumers against the same checkpoint directory or refusing to run any. The honest fix was to stop pretending the streaming job is a scheduled task and run it as a service, with Airflow owning only the hourly bronze_to_silver >> silver_to_gold chain. The README states the split rather than hiding it. Measured on the Compose stack:
  • Sustained throughput: 2,800 events/sec through Structured Streaming
  • End-to-end latency, Kafka to gold: 4.7 minutes
  • Full rebuild of silver and gold from bronze: 8 minutes
  • Gold star schema: 4 tables, 3 dimensions around 1 fact, queryable from any BI tool without a join guide
  • Whole stack runs from one docker compose up: Kafka in KRaft mode, Airflow, Spark

Trade-offs I made

Medallion layering over a single transform. Bronze keeps raw immutable Parquet, silver cleans and conforms, gold serves the star schema. The cost is duplicated storage and more moving parts than one script writing straight to final tables. The benefit is the reason the layers exist: when the definition of revenue changes, gold is rebuilt from silver, and nothing is re-ingested. Streaming ingest, batch transforms. Kafka and Spark Structured Streaming land events continuously, while silver and gold are batch stages on a schedule. Full streaming end to end would cut latency but multiply the failure modes and the operational surface. The dashboards this feeds do not need second-level freshness, so the complexity was not bought. A star schema at gold, not a normalised one. Normalised would have been tidier and would have pushed a join-writing tax onto every consumer, permanently. Analysts and BI tools join dimensions to a fact table without help, so the denormalisation is paid once at write time rather than every read.

What I'd do differently

The Airflow metadata database is SQLite, which forces the sequential executor and means the DAG cannot run tasks in parallel even where the dependency graph allows it. It was the right call for a laptop and the wrong one to leave in place, because it silently sets a ceiling that has nothing to do with the pipeline. PostgreSQL as the metadata backend is an afternoon of work and removes the ceiling entirely. I would also add data-quality assertions between silver and gold from the start. I caught a broken dimension join by reading output, which does not scale past a handful of tables.

Links

Related projects

Machine learning

Hybrid recommender, built as a service

A recommender with the parts that usually get skipped: cold start handled by design, training and serving separated by an artifact, hot model reload without a deploy, and an evaluation that reports its own sample size.

The hard partSwapping a retrained model without dropping traffic. Hot reload inside the running service, version skew and reload safety handled there rather than by a deploy.

Read case study
RAG / AIMulti-tenant

Multilingual, multi-tenant RAG engine

Several tenants on one deployment with fully isolated retrieval. The tenant claim lives in the JWT rather than the request, because cross-tenant retrieval is a security bug rather than a quality one. Local embeddings, grounded answers with citations.

The hard partCross-tenant retrieval is a security incident, not a bad answer. The tenant claim had to come from the verified JWT, so a client can never ask for someone else's documents.

Read case study
Edge ML

Autoencoder compression for edge devices

Bandwidth, not compute, is the binding constraint on a remote sensor. The encoder runs on the microcontroller through TensorFlow Lite Micro at 7.8:1, and a Flask decoder on EC2 reconstructs the signal server-side.

The hard partThe encoder had to fit in flash beside the application on microcontroller-class hardware and still cost less power to run than the bytes it saves to send.

Read case study

Want to dig into this one?

I’m open to full-time backend, platform and data engineering roles, and happy to walk through any decision on this page in more detail than it deserves.
Get in touch