Azure End-to-End Data Engineering Project

📌 Overview

End-to-end data engineering pipeline built on Azure using a Medallion Architecture (Bronze → Silver → Gold) to process and analyse retail sales data. Ingests 50M+ records/day from multiple source systems into a curated analytics layer powering executive dashboards.

Business Problem: A large retail organisation had data siloed across SQL Server, Salesforce, and third-party APIs. Reports were taking 6+ hours to refresh. This pipeline reduced that to under 15 minutes with real-time quality checks.

---

🏗️ Architecture

┌─────────────────────────────────────────────────────────────────────┐
│                         DATA SOURCES                                │
│  ┌──────────┐  ┌─────────────┐  ┌─────────┐  ┌─────────────────┐  │
│  │SQL Server│  │  Salesforce │  │REST APIs│  │  Azure Event Hub│  │
│  └────┬─────┘  └──────┬──────┘  └────┬────┘  └────────┬────────┘  │
└───────┼───────────────┼──────────────┼─────────────────┼───────────┘
        │               │              │                 │
        ▼               ▼              ▼                 ▼
┌─────────────────────────────────────────────────────────────────────┐
│                  AZURE DATA FACTORY (Orchestration)                 │
│   Copy Activity │ Data Flows │ ForEach Loops │ Triggers             │
└────────────────────────────┬────────────────────────────────────────┘
                             │
                             ▼
┌─────────────────────────────────────────────────────────────────────┐
│                    ADLS Gen2 — BRONZE LAYER                         │
│   Raw Parquet / CSV / JSON — Partitioned by ingestion date          │
└────────────────────────────┬────────────────────────────────────────┘
                             │
                             ▼
┌─────────────────────────────────────────────────────────────────────┐
│           AZURE DATABRICKS — SILVER LAYER (PySpark)                 │
│   Cleansing │ Deduplication │ Schema Enforcement │ Delta Lake        │
└────────────────────────────┬────────────────────────────────────────┘
                             │
                             ▼
┌─────────────────────────────────────────────────────────────────────┐
│            AZURE DATABRICKS — GOLD LAYER (Business)                 │
│   Aggregations │ Slowly Changing Dimensions │ Business Metrics       │
└────────────────────────────┬────────────────────────────────────────┘
                             │
                             ▼
┌─────────────────────────────────────────────────────────────────────┐
│              AZURE SYNAPSE ANALYTICS / POWER BI                     │
│   Serverless SQL Pool │ Dedicated Pool │ Power BI DirectQuery        │
└─────────────────────────────────────────────────────────────────────┘

---

📁 Repository Structure

azure-data-engineering-projects/
│
├── adf-pipelines/
│   ├── pl_ingest_sql_to_bronze.json        # ADF pipeline - SQL ingestion
│   ├── pl_ingest_api_to_bronze.json        # ADF pipeline - REST API ingestion
│   ├── pl_master_orchestrator.json         # Master orchestration pipeline
│   └── linked_services/
│       ├── ls_sqlserver.json
│       ├── ls_adls_gen2.json
│       └── ls_databricks.json
│
├── bronze-layer/
│   └── ingest_raw_data.py                  # Bronze ingestion utilities
│
├── silver-layer/
│   ├── transform_sales_silver.py           # Sales dimension cleansing
│   ├── transform_customers_silver.py       # Customer data normalisation
│   └── data_quality_checks.py             # Great Expectations / custom DQ
│
├── gold-layer/
│   ├── gold_sales_summary.py               # Daily/monthly sales aggregation
│   ├── gold_customer_360.py               # Customer 360 view
│   └── scd_type2_handler.py               # SCD Type 2 implementation
│
├── synapse-analytics/
│   ├── create_external_tables.sql          # Serverless SQL pool tables
│   └── stored_procedures/
│       └── usp_refresh_gold_views.sql
│
└── config/
    └── pipeline_config.yaml

---

⚡ Key Features

• Incremental loads using watermark-based change tracking on source systems
• SCD Type 2 for customer and product dimension history
• Data quality framework with automated alerting via Azure Monitor
• Unity Catalog governance on all Delta tables (PII tagging, column masking)
• Cost optimisation — auto-scaling Databricks clusters, lifecycle policies on ADLS
• CI/CD via Azure DevOps with separate dev/test/prod environments

---

🚀 Getting Started

Prerequisites

• Azure subscription with Contributor access
• Databricks workspace (Premium tier for Unity Catalog)
• Azure Data Factory instance
• Python 3.9+

Setup

# Clone the repo
git clone https://github.com/donthula9908/azure-data-engineering-projects.git
cd azure-data-engineering-projects

# Install dependencies
pip install -r requirements.txt

# Configure environment
cp config/pipeline_config.yaml.example config/pipeline_config.yaml
# Edit pipeline_config.yaml with your Azure resource details

Deploy ADF Pipelines

# Import pipelines via ADF ARM template or use the JSON files directly
# in ADF Studio → Author → Import from JSON

---

📊 Performance Metrics

Metric	Before	After
Report refresh time	6 hours	14 minutes
Data freshness	T+1 day	T+15 minutes
Pipeline failures	~12/month	< 1/month
Cost (compute)	$8,200/month	$2,100/month

---

🔧 Tech Stack

Component	Technology
Ingestion	Azure Data Factory
Storage	ADLS Gen2
Processing	Azure Databricks (PySpark)
Table Format	Delta Lake
Governance	Unity Catalog
Warehousing	Azure Synapse Analytics
Visualisation	Power BI
Monitoring	Azure Monitor, Log Analytics
CI/CD	Azure DevOps