Azure Synapse Orders ETL Pipeline 🚀

Welcome to my Azure Synapse ETL project! This project showcases how I built a data pipeline from scratch using PySpark and Dedicated SQL Pools to turn raw e-commerce orders and user data into actionable business insights.

Think of it as a journey: Raw data → Cleaned & enriched data → Business-ready insights.

---

Project Overview 🎯

The goal here was simple:

• Bring in raw JSON data from Azure Data Lake.
• Clean it, enrich it, deduplicate it — basically make it trustworthy.
• Generate Silver and Gold layers for analytics.
• Make the data queryable in SQL so we can answer business questions fast.

By the end of this pipeline, we can see metrics like Daily Revenue, Category Revenue, and Average Order Value, all ready to drive decisions.

---

Architecture (Medallion Style) 🏛️

This project follows the Medallion Architecture, which is just a fancy way of saying: layered, organized, and efficient.

Figure: Raw → Silver → Gold data flow

Layers in a Nutshell

1. Raw Layer

   • Where it all starts. All the messy JSON data lives here — untouched.
   • It’s our source of truth, the unfiltered story of every order and user.
     

2. Silver Layer (Clean & Enriched)

   • Removes nulls, adds timestamps (ingested_at, order_ts, dt), and computes totals.
   • Deduplicates orders by order_id (keeps latest timestamp).
   • Stored as Delta tables, partitioned by date (dt).
     

3. Gold Layer (Analytics-Ready)

   • Aggregates business metrics and enriches orders with user info.
   • Includes Daily Revenue, Category Revenue, and Enriched Orders.
   • Optimized for querying in Dedicated SQL Pool.
     
     

---

Dedicated SQL Pool Integration

The Silver layer data is exposed in SQL via Dedicated SQL Pool:

• External tables connect to Silver Delta tables in ADLS.
• Queries like Average Order Value by category give business insights.

---

Conclusion

This project demonstrates a complete ETL pipeline in Azure Synapse Analytics, following the Medallion Architecture:

• Raw Layer: Ingested unprocessed JSON data from ADLS.
• Silver Layer: Cleaned, enriched, and deduplicated orders for reliable analytics.
• Gold Layer: Aggregated business metrics and enriched orders ready for reporting.

By leveraging PySpark for transformation and Dedicated SQL Pools for querying, this pipeline ensures:

• Scalability: Handles large datasets efficiently using Delta tables.
• Data Quality: Only clean, deduplicated data flows to Gold layer.
• Business Insights: Enables metrics like Daily Net Revenue, Category Revenue, and Average Order Value.
• Maintainability: Clear separation of layers makes the pipeline easy to extend and manage.

This ETL pipeline provides a robust foundation for building analytics and BI solutions in Azure Synapse, following best practices for modern data engineering.

Thanks to Data Engineering YT.