Databricks Medallion Architecture Pipeline

Overview

This project implements an end-to-end data engineering pipeline using the Medallion Architecture pattern in Databricks with Delta Lake and Azure Data Lake Storage (ADLS).

The pipeline processes raw data through three layers:

• Bronze Layer → Raw ingestion
• Silver Layer → Cleaned and transformed data
• Gold Layer → Business-ready KPIs and star schema

The solution also includes:

• Incremental processing using watermarks
• Delta Lake optimizations
• Secure ADLS authentication using Service Principals
• VACUUM maintenance jobs
• Mount-based storage access

---

Project Structure

.
├── 01_bronze_ingest.ipynb      # Raw ingestion into Bronze Delta tables
├── 02_silver_transform.ipynb  # Cleansing and transformation into Silver layer
├── 03_gold_kpi.ipynb          # KPI generation and Gold star schema
├── vacuum.ipynb               # Delta table cleanup and maintenance
└── README.md

---

Architecture

                ┌─────────────────────┐
                │   Source Files      │
                │ CSV / JSON / Parquet│
                └──────────┬──────────┘
                           │
                           ▼
                ┌─────────────────────┐
                │   Bronze Layer      │
                │ Raw Delta Storage   │
                └──────────┬──────────┘
                           │
                           ▼
                ┌─────────────────────┐
                │   Silver Layer      │
                │ Cleaned & Validated │
                └──────────┬──────────┘
                           │
                           ▼
                ┌─────────────────────┐
                │    Gold Layer       │
                │ KPIs & Star Schema  │
                └─────────────────────┘

---

Technologies Used

Technology	Purpose
Databricks	Data engineering platform
Apache Spark	Distributed data processing
Delta Lake	ACID-compliant data lake tables
Azure Data Lake Storage (ADLS Gen2)	Cloud storage
PySpark	ETL transformations
Databricks Secrets	Secure credential management
Delta VACUUM	Storage optimization

---

Notebook Details

1. Bronze Layer Ingestion

File

01_bronze_ingest.ipynb

Purpose

Loads raw source data into Bronze Delta tables.

Features

• Spark session initialization
• Databricks widgets for runtime configuration
• Secure authentication using Service Principal
• ADLS mount creation
• Canonical column mapping
• Raw data ingestion into Delta tables
• Connectivity validation

Key Concepts

• Raw immutable storage
• Schema standardization
• Incremental ingestion
• Delta format storage

---

2. Silver Layer Transformation

File

02_silver_transform.ipynb

Purpose

Transforms Bronze data into clean, validated Silver tables.

Features

• Reads Bronze Delta tables
• Watermark-based incremental processing
• Explicit schema enforcement
• Data cleansing and transformation
• Deduplication
• Data quality handling
• Writes curated Delta tables

Key Concepts

• Incremental ETL
• Data quality validation
• Transformation logic
• Standardized schemas

---

3. Gold Layer KPI Generation

File

03_gold_kpi.ipynb

Purpose

Creates business KPIs and star schema tables from Silver data.

Features

• Reads curated Silver data
• Builds dimension tables
• Builds fact tables
• Generates KPI tables
• Creates Gold analytical datasets
• Optimized for BI/reporting

Star Schema Components

Dimension Tables

• Date Dimension
• Customer Dimension
• Product Dimension
• Location Dimension

Fact Tables

• Sales Fact
• Transaction Fact
• KPI Aggregates

Key Concepts

• Dimensional modeling
• Star schema
• Business metrics
• Analytical optimization

---

4. Delta Maintenance

File

vacuum.ipynb

Purpose

Performs Delta Lake cleanup and storage optimization.

Features

• Weekly scheduled execution
• Delta VACUUM operations
• Removal of obsolete files
• Storage optimization
• Delta table maintenance

Benefits

• Reduces storage usage
• Improves query performance
• Cleans stale Delta files

---

Authentication & Security

The project uses:

• Azure Service Principal authentication
• Databricks Secret Scopes
• OAuth-based ADLS access

Required Secrets

Store the following in Databricks Secret Scope:

Scope: adls-scope

Keys:
- client-id
- client-secret
- tenant-id

---

Storage Mounting

The notebooks dynamically:

• Check existing mounts
• Create mounts if missing
• Connect to ADLS containers
• Reuse mounts across sessions

Example mount paths:

/mnt/bronze
/mnt/silver
/mnt/gold

---

Incremental Processing

The Silver layer implements watermark-based incremental processing.

Workflow

1. Read last processed timestamp
2. Filter new Bronze records
3. Process only new data
4. Update watermark

Benefits

• Faster processing
• Reduced compute cost
• Scalable ETL execution

---

Delta Lake Features Used

Feature	Usage
ACID Transactions	Reliable updates
Time Travel	Historical recovery
Schema Enforcement	Data consistency
VACUUM	Storage cleanup
Delta Format	Optimized analytics

---

How to Run the Pipeline

Step 1: Configure Secrets

Create Databricks secret scope and store:

• Client ID
• Client Secret
• Tenant ID

---

Step 2: Attach Cluster

Attach all notebooks to a Databricks cluster.

Recommended:

• Runtime: Databricks Runtime 13+
• Spark 3+

---

---

Step 3: Scheduling

Notebook	Frequency
Bronze Ingestion	Daily
Silver Transformation	Daily
Gold KPI	Daily
VACUUM	Weekly

---

Performance Optimizations

The project includes:

• Incremental processing
• Delta Lake optimizations
• Partition-aware design
• Efficient storage layout
• VACUUM cleanup
• Z-Ordering
• OPTIMIZE commands
• Unity Catalog integration

---

Key Learnings

This project demonstrates:

• Medallion architecture implementation
• Delta Lake best practices
• Incremental ETL design
• Secure cloud storage access
• Databricks notebook orchestration
• Dimensional modeling
• Production-style data engineering workflows