🎧 Spotify End-To-End Azure Data Engineering Project

📌 Project Overview

This project demonstrates a complete end-to-end Data Engineering pipeline built on Microsoft Azure + Databricks ecosystem using Medallion Architecture (Bronze → Silver → Gold).

The pipeline ingests data from Azure SQL Database & GitHub, processes it using Azure Data Factory, transforms and models data using Databricks & Delta Live Tables, and finally exposes curated datasets through a Data Warehouse layer.

🧰 Tech Stack

☁️ Cloud & Storage

• Azure Cloud
• Azure Data Lake Storage (ADLS Gen2)

🔄 Data Ingestion & Orchestration

• Azure Data Factory
• Logic Apps
• Incremental Pipelines
• Backfilling Pipelines
• Loop-based Dynamic Pipelines

⚡ Data Processing

• Azure Databricks
• Spark Structured Streaming
• Databricks Autoloader
• PySpark
• Python Utilities
• Metadata Driven Pipelines (Jinja2)

🧱 Data Modeling

• Unity Catalog
• Star Schema
• Slowly Changing Dimensions (SCD Type 2)
• Delta Live Tables (DLT)

🚀 DevOps & CI/CD

• GitHub
• Databricks Asset Bundles
• Git Branch Collaboration

✨ Project Features

• ✅ Incremental Data Processing
• ✅ Backfilling Support
• ✅ Real-time Stream Processing
• ✅ Metadata Driven Code
• ✅ Dynamic Pipeline Execution
• ✅ Star Schema Data Modeling
• ✅ CI/CD Integration
• ✅ Custom PySpark Utilities

Project Architecture

This project includes Data ingestion using Azure Data factory where I have build realtime dynamic pipeline with backfilling capabilities. I have used Azure Databricks and Spark structured streaming and Autoloader for Big data processing within Python utilities. I have build slowly changing dimensions and star schema using DLT and lakeflow pipelines. I have also used Databricks Asset Bundles to Push and deploy my code to GitHub.

📊 Data Flow Explanation

🔹 Data Sources

• Azure SQL Database
• GitHub (Static Files)

🥉 Bronze Layer (Raw Data)

👉 Implemented using Azure Data Factory

• Incremental ingestion from SQL Database
• Backfilling support
• Dynamic pipeline execution
• CDC-based loading
• Stored data in ADLS in Parquet format

🥈 Silver Layer (Cleaned & Transformed Data)

👉 Implemented using Azure Databricks

• Unity Catalog & Metastore configuration
• External locations & credential setup
• Spark Structured Streaming using Autoloader
• PySpark transformations
• Metadata-driven joins using Jinja2

🥇 Gold Layer (Business Ready Data)

👉 Implemented using Delta Live Tables

• Star Schema implementation
• SCD Type 2 implementation
• Final curated datasets

🏬 Warehouse Layer

• Exposes analytical datasets
• Provides endpoints for downstream applications

---

☁️ Azure Infrastructure Setup

🔹 Resources Used

• Access Connector for Azure Databricks
• Azure Databricks
• Azure Data Factory
• Logic Apps
• Azure SQL Database
• Azure SQL Server
• Azure Data Lake Storage(ADLS) Gen2
• Outlook API Connection

---

🗄️ Storage Layer (ADLS Gen2)

Containers

Bronze Layer Structure

---

🔄 Azure Data Factory Implementation

Git configuration with Azure Data Factory

Linked Services for connecting ADF with SQL database and ADLS

---

Dynamic pipeline to incrementally load the raw data from SQL Database to ADLS(Bronze Layer)

Pipeline Features

• Incremental Loading
• Backfilling Support

Datasets Used

1. azure sql - To define the location of data present in SQL Database
2. json_dynamic - To dynamically define the location of json data present in ADLS
3. parquet_dynamic - To dynamically define the location of parquet file format data present in ADLS

Pipeline Parameters

Pipeline Variables

---

Clone of same Pipeline with Loop and Alerts (By using Logic App)

Pipeline Parameters

Pipeline Variables

---

Logic Apps Integration

Used for sending notifications via Outlook when pipeline failed.

Setting the trigger - When an HTTP request is received

Setting the action - Send an Email(V2)

---

Databricks Implementation

Workspace Overview

Access connector Configuration

Role Assignment

In ADLS I have added a role assignment (Storage Blob Data contributor) to the managed Identity ("AccessConnectorForDatabricks_SpotifyProject"). So that databricks can access the ADLS data with this access connector.

---

Unity Catalog Setup

Created a dedicated container named "databricksmetastore" in ADLS for Databricks

Deleted the default metastore and created a new metastore

Workspace associated to the metastore

Catalog creation in Databricks workspace

---

External Locations & Credentials

Created a credential and external locations for ADLS bronze, Silver and Gold layer.

---

🔄 Streaming & Transformations

Databricks Asset Bundle

---

Autoloader + PySpark Transformations

Created a notebook - Used Autoloader to load the data in Stream and pyspark transformations to transform the data

---

Custom Utility Functions

Created a Utility File to create a reusable method to delete columns

---

Silver Layer Tables

written the processed table in the databricks catalog inside silver schema.

---

🧠 Metadata Driven Pipelines (Jinja2)

created another notebook named "jinja_notebook" and used Jinja to dynamically apply joins between FactStream table , dimuser table and dimtrack table present in the databricks Unity catalog in Silver Schema.

---

🥇 Gold Layer Implementation (DLT)

Star Schema + SCD Type 2

Created a new ETL pipeline in databricks using DLT for creating star schema and slowly changing dimension

---

DAG View for the ETL Pipeline

---

SCD Type 2 Output

SCD Type 2 Successfully implemented in the gold layer data

---

Gold Schema Tables

All tables has successfully transferred to the Gold schema in the catalog

---

🚀 CI/CD Using Databricks Asset Bundles

Deployed the asset bundle in the dev environment and it created.bundle

Bundle Configuration - databricks.yml

Bundle Deployment

---

🎯 Final Outcome

• Fully automated Azure Data Engineering pipeline
• Real-time streaming ingestion
• Enterprise-grade medallion architecture
• Production-ready CI/CD implementation
• Scalable metadata-driven framework

---

👨‍💻 Author

Kunal Kumar Das