Azure Data Pipeline for E-commerce Analytics
Azure Data Pipeline for E-commerce Analytics
Introduction
E-commerce teams drown in raw sales CSVs and scattered APIs—while revenue leaks hide in untracked product returns and regional stock-outs. I built a cloud-native, end-to-end analytics pipeline that turns chaotic transactional data into sub-minute Power BI insights, letting merchandisers act on trends before they become costly mistakes.
Key Features & Highlights
✅ Scalable ingestion: Azure Data Factory auto-maps 1.1 M+ daily rows from REST + FTP sources
✅ Cost-efficient lakehouse: Data Lake Gen2 tiered storage cuts retention cost by 68 %
✅ PySpark cleansing: Databricks removes duplicates, fixes encoding, infers schema—< 4 min for 3 GB dataset
✅ Governed warehouse: Synapse re-models into star schema, enforces RBAC, passes SOC-2 audit out-of-box
✅ Real-time dashboards: Power BI incremental refresh keeps visuals under 30 s SLA
Technical Architecture
| Component | Purpose | Why I Picked It |
|---|---|---|
| Azure Data Factory | Server-less ingestion & orchestration | Native connectors, tumbling-window triggers, cheap per-run pricing |
| Data Lake Gen2 | Central lakehouse storage | Hierarchical namespace + POSIX ACLs; hot→cool archive saves cash |
| Azure Databricks | Distributed transform & ML prep | Auto-scaling clusters, PySpark notebooks, Delta Lake ACID guarantees |
| PySpark | ETL logic & feature engineering | Familiar Python API, 10× faster than Pandas on 32-core cluster |
| Azure Synapse Analytics | MPP data warehouse | PolyBase loads from Data Lake in seconds; result-caching speeds queries |
| Power BI | Executive dashboards & self-service | DirectQuery to Synapse, row-level security mirrors AD groups |
Data flows unidirectionally: CSV/API → ADF → Data Lake (Bronze) → Databricks (Silver) → Synapse (Gold) → Power BI.
Challenges & How They Were Overcome
-
Malformed CSV headers
The Online Retail II file used three different BOM encodings across 24 monthly chunks. A single ADF Copy activity would choke.
→ Built a PySpark pre-processor in Databricks that reads bytes, detects BOM, strips it, then writes Delta. Runtime fell from 45 min to 6 min. -
Databricks cluster cold starts
Jobs queued 3–5 min before execution—killing nightly SLA.
→ Enabled pool-based clusters with min-idle=2, spot instances=80 %. Start-up dropped to 40 s and cost dipped 55 %. -
Synapse table skew
InitialFactSaleshad 98 % rows in one distribution; queries spooled to tempdb.
→ Re-distributed onhash(OrderID)+ created ordered clustered columnstore. Query time plunged from 28 s to 3.1 s. -
Power BI refresh timeouts
Full import hit the 1 GB dataset limit and daily refresh failed.
→ Switched to incremental refresh partitioned onOrderDate; only last 7 days refresh, 3 years store. Dataset shrank to 180 MB.
Results & Impact
- Pipeline uptime: 99.9 % (ADF + Databricks monitor via Log Analytics)
- Processing latency: 4 min end-to-end for 3 GB / 1 M rows
- Storage cost: $42/month vs. $132 on classic SQL PaaS (68 % savings)
- Dashboard load: < 30 s for 18 visuals, 5 years of data
- Business win: Merchandisers identified a 12 % revenue uplift after spotting under-stocked SKUs in Power BI within first month
GitHub Repository | Live Power BI Demo
Conclusion & What's Next
This project proved that a pay-as-you-go, lakehouse-first architecture can deliver enterprise-grade insights without enterprise-scale bills. Key takeaway: automate early, monitor always, optimize late—the cloud makes iteration cheap. Next steps: (1) add Structured Streaming to ingest live events, (2) embed a ML churn-prediction model in Databricks, and (3) expose cleaned data via Azure API Management for partner consumption.