Overview
A mid-size manufacturer with operations across three countries had 8 separate systems managing different parts of the supply chain: an ERP for production orders, a WMS for warehouse movements, a TMS for logistics, separate supplier portals for 4 key suppliers, and a spreadsheet-based demand forecast. None of these systems talked to each other. The operations director was compiling a weekly performance report manually — a 6-hour process every Friday — and even then the figures were questioned at every leadership meeting because no one could verify where they came from.
This was a full-stack engagement: discovery and requirements workshops, source system analysis, ETL pipeline development in Python, dimensional model design and build in SQL Server, and a suite of Power BI dashboards covering inventory health, logistics KPIs, and supplier performance. The project ran for 8 weeks from kickoff to live deployment.
Key Deliverables
- Python ETL pipeline connecting to all 8 source systems via REST APIs, SFTP file drops, ODBC, and direct SQL queries
- Supply chain dimensional model with fact tables for Inventory Movements, Purchase Orders, Shipments, and Production Runs
- Conformed dimensions for Date, Supplier, Product, Location, and Carrier shared across all facts
- Inventory aging report showing stock by age band, location, and SKU with automatic reorder alerts
- Logistics KPI dashboard covering on-time delivery rate, lead times, freight cost per unit, and carrier performance
- Supplier scorecard with delivery reliability, quality reject rate, and pricing variance vs. contract
- Board summary page with 6 headline metrics, traffic-light RAG status, and 13-week trend lines
Challenges & Solutions
Two of the supplier portals had no API — data was only available as daily CSV exports emailed to a shared inbox. I built an email-monitoring Python script using IMAP that checked the inbox every 30 minutes, detected new CSV attachments from the known supplier domains, downloaded and validated them, and fed them into the main pipeline. The suppliers have no idea their data is being consumed programmatically.
Aligning shipment data across the TMS and the WMS was the single hardest data quality challenge — the two systems used different shipment reference formats and neither matched the ERP purchase order numbers. A fuzzy-matching reconciliation layer (Python, pandas, and record linkage) resolved 94% of shipments automatically; the remaining 6% were flagged in a data quality dashboard for manual review rather than silently dropped.