Building a Modern Data Pipeline with Azure Databricks

This comprehensive guide details the 12 essential steps to construct a scalable, robust, and modern data pipeline using Azure Databricks, organized via the Medallion Architecture. This document is structured and ready to be published as a technical blog post.

Step 1: Define Pipeline Requirements
Step 2: Configure Cloud Storage
Step 3: Connect Data Sources
Step 4: Establish Data Governance
Step 5: Ingest Data into Bronze
Step 6: Design the Medallion Architecture
Step 7: Manage Incremental Loads
Step 8: Clean Data in Silver
Step 9: Apply Data Quality Rules
Step 10: Build Gold Data Models
Step 11: Serve Data for Analytics
Step 12: Orchestrate and Monitor

Step 1: Define Pipeline Requirements

Description: Identify sources, business goals, refresh needs, security, and expected insights. This foundational step ensures the pipeline meets business objectives by defining exact needs before architectural implementation.

Category	Details
Tools	Azure DevOps, Databricks Workspace
Output	Defined pipeline scope

Step 2: Configure Cloud Storage

Description: Set up Azure Data Lake Storage (ADLS) Gen2 for scalable and centralized data storage. ADLS Gen2 forms the foundation for your data lakehouse, providing highly scalable and secure storage for big data analytics.

Category	Details
Tools	ADLS Gen2
Output	Central storage foundation

Sample Code (Accessing ADLS Gen2 in PySpark):

spark.conf.set(
"fs.azure.account.key.<storage-account-name>.dfs.core.windows.net",
"<your-storage-account-key>"
)
# Reading from ADLS Gen2
df = spark.read.csv("abfss://<container-name>@<storage-account-name>.dfs.core.windows.net/path/to/data")

Step 3: Connect Data Sources

Description: Connect databases, APIs, files, SaaS platforms, logs, and streams to bring your varied data into the Azure ecosystem. Effective integration minimizes latency and standardizes ingestion.

Category	Details
Tools	Azure Data Factory, Event Hubs, Lakeflow Connect
Output	Integrated data sources

Step 4: Establish Data Governance

Description: Manage permissions, lineage, metadata, and auditing across data assets to ensure a secure and compliant environment via centralized governance principles.

Category	Details
Tools	Unity Catalog, Azure Key Vault
Output	Secure data environment

Sample Code (Unity Catalog setup via SQL):

CREATE CATALOG IF NOT EXISTS prod_catalog;
USE CATALOG prod_catalog;
CREATE SCHEMA IF NOT EXISTS bronze_schema;
GRANT SELECT ON SCHEMA bronze_schema TO `data_engineers`;

Step 5: Ingest Data into Bronze

Description: Land raw batch and streaming data without major transformations. This provides an auditable history of raw incoming data that acts as the single source of truth for downstream processes.

Category	Details
Tools	Auto Loader, Structured Streaming
Output	Auditable raw data

Sample Code (Using Auto Loader in PySpark):

# Ingest JSON files into Bronze using Auto Loader
bronze_df = (spark.readStream
.format("cloudFiles")
.option("cloudFiles.format", "json")
.option("cloudFiles.schemaLocation", "/checkpoints/schema_bronze")
.load("abfss://landing@myadls.dfs.core.windows.net/data/"))

(bronze_df.writeStream
.format("delta")
.option("checkpointLocation", "/checkpoints/bronze_ingest")
.trigger(availableNow=True)
.start("prod_catalog.bronze_schema.raw_table"))

Step 6: Design the Medallion Architecture

Description: Organize data logically into Bronze (raw), Silver (cleansed/filtered), and Gold (business-level aggregates) processing layers to maintain modularity and incremental value.

Category	Details
Tools	Azure Databricks, Delta Lake
Output	Layered pipeline architecture

Step 7: Manage Incremental Loads

Description: Process only new or changed records using checkpoints and Change Data Capture (CDC) to maintain efficient and rapid data ingestion and reduce processing compute costs.

Category	Details
Tools	Delta Lake, Change Data Feed (CDF)
Output	Efficient data ingestion

Sample Code (Enabling and Querying CDF):

-- Enable Change Data Feed on a Delta Table
ALTER TABLE prod_catalog.bronze_schema.raw_table SET TBLPROPERTIES (delta.enableChangeDataFeed = true);

-- Query changes from a specific version
SELECT * FROM table_changes('prod_catalog.bronze_schema.raw_table', 2);

Step 8: Clean Data in Silver

Description: Standardize formats, remove duplicates, and resolve missing or inconsistent values to create a dependable source of truth for exploratory analytics and data science.

Category	Details
Tools	PySpark, SQL, Lakeflow Pipelines
Output	Clean and consistent data

Sample Code (Data Cleaning in PySpark):

from pyspark.sql.functions import col, to_timestamp

silver_df = (spark.read.table("prod_catalog.bronze_schema.raw_table")
.dropDuplicates(["user_id", "transaction_id"])
.filter(col("transaction_amount").isNotNull())
.withColumn("transaction_date", to_timestamp(col("timestamp_str"), "yyyy-MM-dd HH:mm:ss")))

silver_df.write.format("delta").mode("merge").saveAsTable("prod_catalog.silver_schema.cleansed_transactions")

Step 9: Apply Data Quality Rules

Description: Validate accuracy, completeness, uniqueness, and required business conditions so that downstream analytics can trust the datasets implicitly.

Category	Details
Tools	Lakeflow Expectations (Delta Live Tables), PySpark Tests
Output	Trusted datasets

Sample Code (Using Delta Live Tables Expectations):

import dlt

@dlt.table(
name="trusted_users"
)
@dlt.expect_or_drop("valid_user_id", "user_id IS NOT NULL")
@dlt.expect_or_fail("valid_age", "age > 0 AND age < 120")
def get_trusted_users():
return spark.readStream.table("prod_catalog.silver_schema.users_silver")

Step 10: Build Gold Data Models

Description: Create dimensions, fact tables, KPIs, aggregates, and business-ready datasets tailored for business intelligence (BI) reporting and executive dashboards.

Category	Details
Tools	Delta Lake, Databricks SQL
Output	Analytics-ready data models

Sample Code (Creating a Gold Aggregation via SQL):

CREATE OR REPLACE TABLE prod_catalog.gold_schema.daily_sales_kpi AS
SELECT
DATE(transaction_date) AS sales_date,
store_id,
SUM(transaction_amount) AS total_revenue,
COUNT(DISTINCT user_id) AS unique_customers
FROM prod_catalog.silver_schema.cleansed_transactions
GROUP BY sales_date, store_id;

Step 11: Serve Data for Analytics

Description: Expose Gold data to dashboards, applications, machine learning, and AI workloads using efficient serving layers like warehouses and endpoints.

Category	Details
Tools	Databricks SQL Warehouse, Power BI, MLflow
Output	Governed data products

Step 12: Orchestrate and Monitor

Description: Schedule workflows, automate deployments, monitor performance, failures, quality, and costs to ensure a reliable and observable production-ready pipeline.

Category	Details
Tools	Lakeflow Jobs, Asset Bundles, Azure Monitor
Output	Production-ready pipeline

Sample Code (Databricks Asset Bundle YAML snippet for a Job):

resources:
jobs:
daily_pipeline_job:
name: "Daily Medallion Pipeline"
schedule:
quartz_cron_expression: "0 0 2 * * ?"
timezone_id: "UTC"
tasks:
- task_key: "ingest_bronze"
notebook_task:
notebook_path: "/Workspace/Pipelines/01_bronze_ingest"
- task_key: "process_silver"
depends_on:
- task_key: "ingest_bronze"
notebook_task:
notebook_path: "/Workspace/Pipelines/02_silver_transform"

Conclusion: Following these 12 methodical steps ensures your data architecture on Azure Databricks is scalable, strictly governed, and delivers compounding business value consistently.

Data bricks

Building MDS Databricks (Modern Data Stacks)