An end-to-end data engineering and analytics project tracking order fulfillment
performance for a wholesale distributor serving retail buyers across North America.
Flow/
├── data/
│ └── raw/
│ ├── orders.csv
│ └── order_logs.csv
├── scripts/
│ ├── generate_data.py
│ └── etl_pipeline.py
├── sql_queries/
│ └── transformations.sql
├── outputs/
│ └── gold_order_performance.csv
├── operations.db
├── dashboard.py
└── viz_requirements.txt
Or with comments:
Flow/
├── data/raw/
│ ├── orders.csv # 2,000 synthetic wholesale orders
│ └── order_logs.csv # ~8,000 status-change events
├── scripts/
│ ├── generate_data.py # Synthetic data generation (seed=42)
│ └── etl_pipeline.py # ETL + SQL transformations to SQLite
├── sql_queries/
│ └── transformations.sql # CTEs, LEAD() window functions
├── outputs/
│ └── gold_order_performance.csv # Gold layer export
├── operations.db # SQLite database
├── dashboard.py # Streamlit dashboard
└── viz_requirements.txt # Python dependencies
pip install -r viz_requirements.txt
python scripts/generate_data.py
python scripts/etl_pipeline.py
streamlit run dashboard.py
###Data Generation
-
2,000 wholesale orders across 5 regions and 4 fulfillment teams
-
~8,000 status-change log events with realistic inter-stage delays
-
Intentional bottleneck in Team C (Processing → Picking stage)
-
~15% backorder rate, ~5% cancellation rate
-LEAD() window function to calculate time-in-status per stage per order
CTEs for bottleneck detection and SLA tier evaluation
Gold-layer fact_order_performance table for dashboard consumption
-
On-Time Delivery Rate by SLA tier (Critical <24h, Expedited <48h, Standard <72h)
-
Average order cycle time (Received → Delivered)
-
Backorder rate and cancellation rate
-
Stage-level bottleneck analysis by fulfillment team