Azure Retail Data Pipeline
End-to-end analytics pipeline on Microsoft Azure
Project Overview
Built a complete data engineering pipeline simulating a retail company analytics workflow. Raw CSV datasets are ingested and cleaned with Python (pandas), loaded into Azure SQL Database via SQLAlchemy, enriched with SQL views (vw_SalesSummary, vw_CustomerLifetimeValue, vw_ProductPerformance), and visualized in a multi-page Power BI dashboard covering Executive Overview, Customer Insights, and Product Performance.
The Problem
Retail businesses generate large volumes of transactional data across multiple entities that is difficult to query and visualize without a structured pipeline and data model.
The Solution
Built a Python ETL pipeline using pandas and SQLAlchemy to load 360K+ raw CSV records into a normalized Azure SQL Database schema, then created SQL reporting views and a multi-page Power BI dashboard for business intelligence consumption.
Key Highlights
- Full ETL pipeline: CSV → pandas → SQLAlchemy → Azure SQL Database
- Normalized relational schema across customers, products, orders, stores, and suppliers
- Three named SQL reporting views: vw_SalesSummary, vw_CustomerLifetimeValue, vw_ProductPerformance
- Executive Overview page: Total Revenue, Orders, Customers, Products, Monthly Trend, Revenue by Category/City
- Customer Insights page: Average Order Value, Orders per Customer, Customer Lifetime Revenue, Top Customers
- Product Performance page: Average Revenue, Top Products, Revenue by Category
- Secure credential management via .env environment variables
Data / Request Flow
System Architecture
Code Spotlight
Real snippets from the codebase demonstrating key engineering decisions.
1import pandas as pd
2from sqlalchemy import create_engine, text
3
4# Load and transform raw CSV data
5df = pd.read_csv('retail_sales.csv', parse_dates=['order_date'])
6df['revenue'] = df['quantity'] * df['unit_price']
7df['month'] = df['order_date'].dt.to_period('M').astype(str)
8df['is_repeat_customer'] = df.groupby('customer_id')['order_id'].transform('count') > 1
9
10# Push to Azure SQL Database
11engine = create_engine(AZURE_SQL_CONNECTION_STRING)
12df.to_sql('sales_fact', engine, if_exists='replace', index=False, chunksize=5000)
13
14# Create reporting views
15with engine.connect() as conn:
16 conn.execute(text("""
17 CREATE OR REPLACE VIEW vw_SalesSummary AS
18 SELECT month, SUM(revenue) AS total_revenue,
19 COUNT(DISTINCT customer_id) AS unique_customers
20 FROM sales_fact GROUP BY month
21 """))Challenges & How I Solved Them
Handling referential integrity across large datasets during bulk inserts
Designing DAX measures that perform efficiently against Azure SQL
Securing database credentials properly using .env and .gitignore
Managing Azure SQL resource costs during active development
Results
- →Fully functional analytics pipeline processing 360,000+ retail records
- →3-page Power BI dashboard: Executive Overview, Customer Insights, Product Performance
- →Three reusable SQL reporting views simplifying all dashboard queries
- →Clean Python ETL codebase with reproducible setup via requirements.txt