E-Commerce Data Platform Rebuild
Mo Rahman
E-Commerce Data Platform Rebuild
A production-grade modern data stack built on the Olist Brazilian e-commerce dataset, demonstrating end-to-end streaming ingestion, warehouse transformation, orchestration, and analytics across 100,000+ orders.
Architecture
Airflow orchestrates the full pipeline on an hourly schedule. Each dbt model is exposed as an individual Airflow task via Astronomer Cosmos, enabling model-level observability and failure isolation.
Lineage Graph
Generated by
dbt docs generate. Green nodes are raw Snowflake source tables. Teal nodes are staging views and intermediate views. Dark teal nodes are materialized mart tables.Dashboard
Five charts powered by
fact_orders and dim_sellers queried live from Snowflake's analytics schema via Metabase:Chart Key Finding Monthly Gross Revenue (BRL) 20× growth from BRL 50K → BRL 1M over 18 months Order Volume by Status ~96K of 99K orders delivered successfully Delivery Performance 91.88% on-time delivery across 96,478 orders Top 10 States by Revenue São Paulo (SP) leads at ~BRL 5M gross revenue Seller Performance Table Top sellers by total revenue with city/state
dbt Documentation
The
docs/ folder contains screenshots of the generated dbt documentation site. Key pages:File Contents
docs/lineage_graph.png Full project lineage from sources to marts docs/fact_orders1–4.png fact_orders details, columns, tests, compiled SQL docs/int_orders_enriched1–4.png Intermediate model with business logic documentation docs/sources.png Raw source tables registered in ecommerce.rawRun locally with:
Project Structure
Key Design Decisions
ELT over ETL. Raw data lands in Snowflake before any transformation is applied. The same raw tables can be re-transformed with updated dbt logic at any time without re-ingesting from the source. Snowflake's compute handles all SQL transformations, eliminating the need for a separate transformation server.
Kafka keying by
order_id. All events for a given order (placed, delivered, reviewed) are routed to the same partition via message key. Kafka only guarantees ordering within a partition, so keying by order ensures related events are consumed in sequence.acks="all" on the producer. Instructs the broker to confirm a write before the producer considers the message sent. Slower than the default but correct for order data where losing a message is worse than incurring latency.Manual Kafka offset commits.
enable_auto_commit=False combined with calling consumer.commit() only after a successful Snowflake write provides at-least-once delivery semantics. A crash between the write and the commit causes messages to be re-processed on restart rather than silently dropped.Cosmos
DbtTaskGroup over a bash task. Running dbt run as a single bash task in Airflow produces a monolithic log and treats the entire transformation as a single pass/fail unit. Cosmos exposes each dbt model as its own Airflow task, enabling model-level logs, automatic dependency enforcement, and partial re-runs that re-execute only failed models and their downstream dependents.Bounded consumer runs in Airflow. The consumer accepts a
--max-runtime-seconds flag that causes it to flush and exit after a fixed duration. This makes the same script compatible with two modes: continuous consumption for local development (no flag) and time-boxed batch consumption for Airflow scheduling (--max-runtime-seconds 60).dbt Test Results
All 21 data tests pass against the production Snowflake analytics schema:
Tests include
unique and not_null on every primary key, accepted_values on order_status (8 valid statuses), not_null on financial and timestamp columns, and a relationships test verifying referential integrity between fact_orders.customer_id and dim_customers.Airflow Failure Handling
Failure isolation was verified by deliberately introducing a bad column reference in
stg_orders.sql and re-triggering the DAG. Results confirmed:stg_orders failed and was retried twice (per retries=2 in default_args) before being marked permanently failedint_orders_enriched and fact_orders were automatically skipped since they depend on stg_ordersstg_customers, stg_sellers, stg_order_items, int_seller_performance, and dim_sellers continued running and succeeded independentlyAfter reverting the fault, clearing only the failed tasks and using Airflow's partial re-run recovered the pipeline to fully green without re-running the ingestion layer
Setup
Prerequisites
WSL2 (Ubuntu) on Windows, or native Linux/macOS
Docker Desktop with WSL2 integration enabled
Snowflake account (free trial at signup.snowflake.com)
Python 3.8+
1. Clone and configure environment
2. Snowflake setup
Run
snowflake/setup.sql as ACCOUNTADMIN in a Snowflake worksheet. This creates the warehouse, database, schemas, and CSV file format.3. Load raw data
4. Verify dbt
5. Start Kafka
6. Stream events
Open two terminals:
7. Start Airflow
8. Start Metabase
Makefile Targets
Summary
E-Commerce Data Platform | Python · Kafka · Airflow · Snowflake · dbt
Rebuilt a 100K+ order analytics pipeline as a production-grade modern data stack, streaming simulated order events through Kafka into a Snowflake warehouse orchestrated by Airflow DAGs
Modeled raw data into a dimensional schema using dbt, including 21 schema tests, generated documentation with full lineage graph, and reusable marts for revenue, retention, and delivery analytics
Reduced pipeline to a fully declarative, version-controlled architecture deployable in a single Airflow run, with Cosmos-powered model-level failure isolation and verified partial re-run recovery
License
MIT
Like this project
Posted Aug 24, 2026
Rebuilt a 100K+ order pipeline with Kafka, Snowflake, dbt, and Airflow; 21 tests passed with model-level failure isolation and partial reruns.
Likes
0
Views
0
Clients
Olist
