Azure Retail Data Pipeline
A 300K-order Azure pipeline from Data Factory and Azure SQL to Power BI
Project overview
The pipeline covers 50,000 customers, 10,000 products, 300,000 orders, 100 stores, 200 suppliers, 30 categories, and 50 promotions. It uses Python and SQL for transformation, Azure Data Factory for orchestration, Azure SQL for relational modeling and analytical views, and Power BI for revenue, order volume, average order value, margin, store, and customer reporting.
The problem
Large retail datasets become useful only when ingestion, relational modeling, analytical logic, and reporting are connected into a repeatable pipeline.
The approach
Built a cloud flow from structured source data through Azure Data Factory and Azure SQL into reusable analytical views and Power BI reporting.
Implemented systems
- Structured ingestion and transformation across customers, products, orders, stores, suppliers, categories, and promotions
- Azure Data Factory orchestration feeding a relational Azure SQL Database design
- Analytical views: vSalesByMonth, vTopProducts, vCustomerCohorts, and vStorePerformance
- Power BI KPIs for revenue, order volume, average order value, margin, stores, and customer trends
- Python, pandas, SQLAlchemy, and SQL across validation, transformation, loading, and analysis
System flow
- Source DataRetail
- Azure Data FactoryOrchestrate
- Azure SQLModel
- Analytical ViewsSemantic
- Power BIDecisions
Architecture
Engineering challenges
Maintaining relationships and load order across high-volume retail entities
Designing views for recurring KPI reporting and deeper analysis
Keeping orchestration, transformation, storage, and BI responsibilities separated
Current state
- The pipeline covers ingestion, transformation, relational storage, analytical views, and visualization
- The modeled dataset includes 300,000 orders, 50,000 customers, and 10,000 products
- Power BI surfaces commercial, store, product, and customer performance measures