A Practical Guide to ETL and ELT with Databricks

By Eduardo Sobrino, BI/EDW Data, Analytics and AI Practice Manager, Estrada Consulting Inc.

Introduction

As organizations continue to amass data at unprecedented volumes and velocity, the ability to reliably move, transform, and prepare that data for analysis has become mission-critical.

ETL (Extract, Transform, Load) and ELT (Extract, Load, Transform) processes lie at the heart of this challenge, acting as the foundational pipelines that feed downstream analytics, reporting, and machine learning workflows.

Yet, traditional ETL/ELT systems often struggle with complexity, scalability, and governance—leaving data teams to grapple with performance bottlenecks, compliance risks, and operational overhead. Overcoming these challenges is a core focus of our Data & Analytics services.

Databricks addresses these fundamental concerns with a cloud-native architecture that decouples management from execution through its control plane and data plane model. This separation ensures that sensitive data stays securely within an organization’s cloud environment, while orchestration and interface layers remain centrally managed. It’s a structure designed to support compliance-driven industries like finance and healthcare, where data sovereignty, auditability, and fine-grained security controls are paramount.

Equally important is the platform’s ability to scale and adapt. With built-in support for both batch and streaming workloads, Databricks empowers organizations to respond to real-time data needs without sacrificing throughput. Features like autoscaling Spark clusters, the high-speed Photon engine, and tools like Delta Live Tables (DLT) allow teams to build pipelines that are both performant and resilient.

Whether transforming millions of records in a nightly batch or ingesting event data continuously, Databricks provides the flexibility and reliability that modern analytics demands.

Beyond infrastructure, Databricks emphasizes collaboration, reusability, and governance. Through shared notebooks, Git integration, and orchestrated workflows, data engineers and analysts can work together to build modular pipelines that are versioned, tested, and monitored end-to-end. Tools like dbt, Auto Loader, and structured streaming support a wide range of ETL/ELT patterns while maintaining clarity and control over transformation logic. The result is not just operational efficiency, but a stronger, more agile foundation for enterprise data strategies.

The Core Architecture: The Role of the Control and Data Planes in ETL/ELT

Databricks’ control plane and data plane architecture plays a critical role in optimizing ETL/ELT pipelines by separating platform management from data processing. This separation improves scalability, security, performance, and operational efficiency in modern data workflows. Databricks operates using two main components:

• Control Plane: This is managed by Databricks and includes backend services like the web application, REST APIs, and account management. It handles metadata, job orchestration, and user interfaces. Essentially, it manages the “brains” of the operation, ensuring everything runs smoothly.
• Data Plane: This is where the actual data processing happens. It operates within your cloud account (e.g., AWS, Azure, or GCP) and handles tasks like running clusters, executing jobs, and storing data. The Data Plane ensures that your raw data remains within your cloud environment, maintaining security and compliance.

How This Architecture Enhances ETL/ELT

1. Separation of Duties & Security

• Data never leaves your cloud environment: The data plane resides in your VNet or VPC, so sensitive data remains fully under your control.
• Tight governance: You can apply your own IAM, network security rules, and auditing on the data processing environment.
• Private Link support (e.g., Azure Private Link): Isolates data processing from public internet access.
• Benefit: Enables secure, compliant ETL/ELT pipelines—essential for regulated industries (e.g., healthcare, finance).

2. Scalability and Performance

• The data plane scales independently based on the workload.
• Databricks optimizes Spark clusters dynamically (with autoscaling) to handle spikes in ETL/ELT demand.
• The Photon engine in the data plane delivers massive speed-ups for SQL-based ELT transformations.
• Benefit: Large batch ETL and real-time ELT jobs run faster and more cost-efficiently.

3. Centralized Orchestration and Job Management

• The control plane manages jobs, notebooks, workflows, and integrations (e.g., with Airflow, Azure Data Factory, or AWS Step Functions).
• Features like job clusters and parameterized workflows streamline the creation and monitoring of complex pipelines.
• Benefit: Simplifies pipeline development and makes ETL orchestration robust and maintainable.

4. Collaborative Development Environment

• Control planes provide notebooks, version control (via Git), and access control to promote collaborative development.
• Supports Delta Live Tables, which abstracts complex ETL logic into declarative pipelines.
• Benefit: Encourages shared ownership and modular, testable pipeline code across data engineers and analysts.

5. High Availability & Fault Tolerance

• The control plane ensures high availability of jobs and metadata.
• The data plane uses Spark’s resilient architecture to recover from worker failures during transformations.
• Benefit: Increases the reliability of long-running ETL/ELT jobs.

Real-World Example: ETL Using This Model

• Trigger Job: A scheduled job is initiated from the control plane.
• Launch Cluster: The job spins up a Spark cluster in your data plane.
• Ingest + Transform: Raw data from cloud storage is read and transformed using Spark SQL or Python.
• Write Output: Cleaned and structured data is written back to Delta Lake, Parquet, or a data warehouse.
• Monitor: Control plane logs and alerts notify the team of success or failure.

Databricks ETL/ELT Options

Databricks offers a rich ecosystem for building ETL (Extract, Transform, Load) pipelines, supporting both batch and streaming data. Here’s a breakdown of the main ETL options available in Databricks:

1. Delta Live Tables (DLT)

Best for: Declarative, managed ETL pipelines with built-in data quality and lineage.

Languages: SQL or Python

Features:

• Auto-managed infrastructure
• Data quality checks with EXPECT
• Built-in monitoring and lineage
• Supports both batch and streaming

Examples:

# dlt_table.py  import dlt  from pyspark.sql.functions import col    @dlt.table(    comment="Raw data from the source system"  )  def raw_orders():    return spark.read.format("json").load("/databricks-datasets/retail-org/orders")    @dlt.table(    comment="Cleaned and filtered orders"  )  def clean_orders():    return (      dlt.read("raw_orders")      .filter(col("amount") > 0)      .withColumnRenamed("customer_id", "cust_id")    )

-- Create a materialized view for daily sales  CREATE OR REFRESH LIVE TABLE daily_sales_mv  AS  SELECT    date_trunc('DAY', timestamp) AS sale_day,    SUM(amount) AS total_sales,    COUNT(*) AS order_count  FROM LIVE.clean_orders  GROUP BY date_trunc('DAY', timestamp);

-- Define a streaming live table using Auto Loader  CREATE OR REFRESH STREAMING LIVE TABLE raw_orders_stream  COMMENT "Streaming ingestion of raw order data from cloud storage"  AS SELECT *  FROM cloud_files(    "/mnt/data/streaming/orders",  -- Replace with your path    "json",    map(      "cloudFiles.inferColumnTypes", "true",      "cloudFiles.schemaLocation", "/mnt/schemas/raw_orders_stream"    )  );

 

Feature	Tool	Key Benefit
Live Tables	DLT	Declarative ETL with lineage
Streaming Tables	Auto Loader + Delta	Real-time ingestion
Materialized Views	SQL + DLT	Fast query performance on aggregates

2. Structured Streaming

Best for: Real-time ETL with low-latency data ingestion.

Languages: PySpark, Scala, SQL

Features:

• Exactly-once semantics
• Watermarking and windowing
• Scalable and fault-tolerant

from pyspark.sql.functions import *  # Define schema  schema = "order_id STRING, customer_id STRING, amount DOUBLE, timestamp TIMESTAMP"  # Read streaming data  streaming_df = (      spark.readStream.format("cloudFiles")      .option("cloudFiles.format", "json")      .schema(schema)      .load("/mnt/data/streaming/orders")  )  # Write to Delta table  streaming_df.writeStream \      .format("delta") \      .option("checkpointLocation", "/mnt/checkpoints/orders") \      .outputMode("append") \      .table("streaming_orders")

3. Notebooks (PySpark, SQL, Scala, R)

Best for: Custom ETL logic, ad hoc transformations, and data exploration.

Languages: Python, SQL, Scala, R

Features:

• Full control over logic
• Easy to schedule with Jobs
• Integrates with ML workflows

4. Auto Loader

Best for: Incremental ingestion from cloud storage (S3, ADLS, GCS).

Features:

• Schema inference and evolution
• Scalable file discovery
• Works with Structured Streaming

5. Databricks Workflows (Jobs)

These are automated pipelines that allow you to schedule and orchestrate tasks in Databricks. You can use workflows to integrate various tasks, such as data ingestion, transformation, and analysis, into a single pipeline. They support features like monitoring, notifications, and artifact archiving.

Best for: Scheduling and orchestrating ETL pipelines.

Features:

• Task dependencies
• Retry policies
• Alerts and monitoring
• Supports notebooks, JARs, Python scripts, SQL

A sample Databricks Workflow that orchestrates a simple ETL pipeline using notebooks and Delta Live Tables (DLT). This example includes:

• A data ingestion task
• A transformation task
• A DLT pipeline trigger
• A notification task

Sample Databricks Workflow JSON

{
  "name": "Customer ETL Pipeline",
  "email_notifications": {
    "on_failure": [
      "data-team@example.com"
    ]
  },
  "timeout_seconds": 3600,
  "max_concurrent_runs": 1,
  "tasks": [
    {
      "task_key": "ingest_data",
      "description": "Ingest raw customer data from cloud storage",
      "notebook_task": {
        "notebook_path": "/Repos/your_user/etl/ingest_customers"
      },
      "new_cluster": {
        "spark_version": "13.3.x-scala2.12",
        "node_type_id": "Standard_DS3_v2",
        "num_workers": 2
      }
    },
    {
      "task_key": "transform_data",
      "description": "Clean and enrich customer data",
      "depends_on": [
        {
          "task_key": "ingest_data"
        }
      ],
      "notebook_task": {
        "notebook_path": "/Repos/your_user/etl/transform_customers"
      },
      "new_cluster": {
        "spark_version": "13.3.x-scala2.12",
        "node_type_id": "Standard_DS3_v2",
        "num_workers": 2
      }
    },
    {
      "task_key": "run_dlt_pipeline",
      "description": "Trigger DLT pipeline for CDC processing",
      "depends_on": [
        {
          "task_key": "transform_data"
        }
      ],
      "pipeline_task": {
        "pipeline_id": "<your-dlt-pipeline-id>"
      }
    },
    {
      "task_key": "notify_success",
      "description": "Send success notification",
      "depends_on": [
        {
          "task_key": "run_dlt_pipeline"
        }
      ],
      "notebook_task": {
        "notebook_path": "/Repos/your_user/etl/notify_success"
      },
      "new_cluster": {
        "spark_version": "13.3.x-scala2.12",
     &n"node_type_id": "Standard_DS3_v2",
        "num_workers": 1
      }
    }
  ]
}

What You Need to Customize

• Replace `notebook_path` with your actual notebook paths.
• Replace `<your-dlt-pipeline-id>` with your actual DLT pipeline ID.
• Adjust cluster specs as needed.
• Add email addresses for notifications.

How to Deploy

1. Go to Workflows > Create Job in Databricks.
2. Use the UI or click Import JSON to paste the above.
3. Save and run the workflow.

6. dbt on Databricks

dbt is a tool for transforming data in your warehouse using SQL. It helps you build, test, and document your data models. When integrated with Databricks, dbt can be used to automate SQL transformations, monitor tasks, and include them in broader workflows. Together with Workflows, dbt enable seamless integration of data transformation tasks with other processes, such as ETL (Extract, Transform, Load) operations or analytics workflows.

Best for: SQL-based transformation pipelines with version control and testing.

Features:

• Modular SQL models
• Data testing and documentation
• Git integration
• Works with Unity Catalog

7. Partner Tools & APIs

Best for: Integrating with external ETL tools or building custom pipelines.

Examples:

• Apache Airflow
• Informatica, Fivetran, dbt Cloud
• REST APIs for job orchestration

Implementing Smarter ETL/ELT with Change Data Capture (CDC)

Databricks supports Change Data Capture (CDC) through several powerful features and integrations, enabling you to track and process data changes efficiently. In the Databricks platform, CDC transforms ETL/ELT from a resource-heavy batch process into a scalable, real-time, and historically accurate pipeline architecture. It allows data teams to minimize latency, reduce costs, and ensure trust in data—critical requirements for modern analytics and AI initiatives.

Here’s a breakdown of how CDC is handled in Databricks:

1. Delta Lake Change Data Feed (CDF)

Delta Lake provides native support for CDC via Change Data Feed, which allows you to query row-level changes (inserts, updates, deletes) between versions of a Delta table.

Key Features:

• Track changes between two versions or timestamps
• Supports INSERT, UPDATE, DELETE
• Works with batch and streaming

Example:

-- Enable CDF on a Delta table  ALTER TABLE customers SET TBLPROPERTIES (delta.enableChangeDataFeed = true);    -- Query changes between versions  SELECT * FROM table_changes('customers', 5, 10);

2. Delta Live Tables (DLT) with CDC

DLT supports CDC using the APPLY CHANGES INTO syntax, which simplifies building slowly changing dimension (SCD) pipelines.

Example: SCD Type 1 with DLT

APPLY CHANGES INTO LIVE.customers_silver  FROM STREAM(LIVE.customers_bronze)  KEYS (customer_id)  SEQUENCE BY updated_at  COLUMNS * EXCEPT (deleted)  STORED AS SCD TYPE 1;

3. Auto Loader + Merge for CDC

You can use Auto Loader to ingest CDC logs (e.g., from Debezium, SQL Server, Oracle) and apply changes using MERGE.

Example:

MERGE INTO target_table AS t  USING source_stream AS s  ON t.id = s.id  WHEN MATCHED THEN UPDATE SET *  WHEN NOT MATCHED THEN INSERT *;

4. Partner Integrations

Databricks integrates with CDC tools like:

• Fivetran
• Debezium
• Qlik Replicate
• Informatica

Managing Historical Data with Slowly Changing Dimensions (SCD)

A Slowly Changing Dimension (SCD) is a fundamental data modeling technique used to manage changes in dimensional data over time. This is particularly important when dealing with business entities such as customers, products, or employees—records that don’t change often, but when they do, those changes must be either tracked historically or updated appropriately. The challenge lies in deciding whether to preserve past values for historical accuracy or simply overwrite them to reflect the latest information.

SCD Type 1 and 2

Databricks supports key SCD strategies, most notably SCD Type 1 and Type 2, through its Delta Lake and Delta Live Tables (DLT) frameworks. In an SCD Type 1 approach, the system overwrites old data with new values, effectively discarding history. This is suitable for scenarios where changes are corrections or where historical data is not relevant.

On the other hand, SCD Type 2 is designed to maintain a complete history of changes. Instead of replacing existing records, a new version of the record is inserted while the older one is marked as inactive or expired. This method is critical for analytical use cases where understanding how an entity evolved over time is essential—such as tracking a customer’s address history or product pricing changes.

Approach

What makes Databricks particularly powerful for implementing SCD logic is its support for declarative, scalable, and streaming-friendly pipelines. Using Delta Live Tables, developers can define SCD Type 2 behavior with simple syntax, letting Databricks handle the heavy lifting—like tracking keys, ordering updates, and managing record versions. These capabilities are seamlessly integrated with Delta Lake’s ACID transactions and schema evolution features, ensuring data consistency even at scale.

In practice, this means organizations can build and maintain accurate, audit-ready data models without the complexity often associated with traditional ETL tools. Whether for compliance, reporting, or advanced analytics, SCDs in Databricks provide a reliable and efficient way to track data evolution over time—empowering teams to trust their data and derive deeper insights from it.

Scenario

Here’s a complete CDC (Change Data Capture) example using Delta Live Tables (DLT) in Databricks, implementing Slowly Changing Dimension (SCD) Type 2 logic. You have a source table `customers_bronze` with CDC data (including inserts and updates), and you want to maintain a historical record in `customers_silver`.

1. Create the Source Streaming Table

CREATE OR REFRESH STREAMING LIVE TABLE customers_bronze  AS SELECT *  FROM cloud_files(    "/mnt/data/customers_cdc",  -- Replace with your path    "json",    map(      "cloudFiles.inferColumnTypes", "true",      "cloudFiles.schemaLocation", "/mnt/schemas/customers_bronze"    )  );

2. Apply CDC with SCD Type 2 Logic

APPLY CHANGES INTO LIVE.customers_silver  FROM STREAM(LIVE.customers_bronze)  KEYS (customer_id)  SEQUENCE BY updated_at  COLUMNS * EXCEPT (deleted)  STORED AS SCD TYPE 2;

Expecting:

• KEYS (customer_id): Identifies the unique record.
• SEQUENCE BY updated_at: Orders change chronologically.
• SCD TYPE 2: Maintains history by closing old records and inserting new versions.

3. Query the Resulting Table

SELECT * FROM LIVE.customers_silver  ORDER BY customer_id, effective_date DESC;

This will show the full history of changes for each customer.

Optional: Add Data Quality Checks

CREATE OR REFRESH LIVE TABLE validated_customers  CONSTRAINT valid_email EXPECT (email LIKE '%@%')  AS SELECT * FROM LIVE.customers_silver;

Requirements

• Delta Live Tables Enabled.
• Cloud storage path for CDC data.
• JSON or CSV files with CDC format (e.g., from Debezium, Fivetran).

Stronger Together: Integrating ADF, ADLS, and Databricks

Integrating Azure Databricks with Azure Data Factory (ADF) and Azure Data Lake Storage (ADLS) creates a powerful, end-to-end data platform that unifies orchestration, storage, and scalable data processing.

ADF serves as the control center, orchestrating the flow of data from diverse sources into ADLS, where it is securely and cost-effectively stored in its raw or curated form. This separation of concerns allows for flexible pipeline design, centralized control, and automation of complex workflows with minimal manual intervention.

Databricks complements this architecture by providing the processing and transformation engine. It reads data directly from ADLS, applies complex business logic or machine learning models using high-performance Spark clusters, and writes transformed outputs back to the data lake.

ADF seamlessly triggers Databricks notebooks or pipelines as part of broader ETL/ELT workflows, enabling smooth coordination between data movement and processing tasks. This modular, cloud-native approach scales with demand and supports both batch and streaming data processing. As a Microsoft Gold Partner, we specialize in architecting these powerful, integrated cloud solutions.

ADF + ADLS + Databricks Integration

Component	Primary Role	Key Capabilities	How It Integrates with Others	Benefits of Working Together
Azure Data Factory (ADF)	Orchestration & ETL/ELT pipeline management	– Data ingestion from 100+ sources – Scheduling & monitoring – Control flow logic – Parameterization – Triggering compute jobs	– Loads data into ADLS – Triggers Databricks notebooks via activities – Passes parameters to notebooks	– Central control of data flow – Decouples logic from orchestration – Simplifies cross-service coordination
Azure Data Lake Storage (ADLS)	Centralized data lake for storage	– Scalable, secure object storage – Delta Lake support – Fine-grained access control (ACLs) – Native Azure AD integration	– Stores raw and processed data for Databricks – Serves as input/output for ADF pipelines	– Unified storage layer – Enables raw-curated-refined data zoning – Cost-effective and durable
Azure Databricks	Scalable data transformation & analytics engine	– Distributed compute with Apache Spark – Delta Lake transactions – Machine Learning & AI integration – Real-time and batch processing	– Reads/writes directly to ADLS (via mounted paths or ABFS) – Triggered by ADF for processing steps – Returns output paths for downstream steps	– High-performance transformations – Advanced analytics & ML-ready – Unified processing for batch/streaming

Why They Work Better Together

Integration Point	Description	Resulting Benefit
ADF → ADLS	ADF stages raw or staged data into ADLS from external/internal sources	Enables centralized, scalable, and secure data storage
ADF → Databricks	ADF pipeline activity triggers Databricks notebooks/jobs for transformation logic	Separates orchestration from computation; supports modular architecture
Databricks ↔ ADLS	Databricks reads/writes to Delta/Parquet/CSV in ADLS directly via high-performance IO	Provides scalable, schema-evolving storage optimized for analytics
Databricks → ADF (optional feedback loop)	Databricks can log outputs or status that ADF uses for conditional branching or error handling	Supports dynamic and intelligent pipeline flows

End-to-End Use Case Example

Step	Tool	Action
Ingest external data	Azure Data Factory	Pulls data from SQL Server/SaaS/API to raw zone in ADLS
Transform data into clean format	Databricks	Processes raw data using Spark or SQL; applies business rules; writes to silver zone in ADLS
Curate analytical models	Databricks	Generates aggregates, dimensions, and facts; stores in Delta Lake format in gold zone
Load into analytics layer	ADF / Databricks	Moves final datasets to Synapse, Power BI, or ML model inputs for consumption

Modernize Your Data Strategy with Estrada

Navigating the complexities of modern ETL/ELT and building a scalable data foundation on platforms like Databricks requires deep expertise. If you’re ready to move beyond theoretical guides and implement a robust data strategy that drives real business value, our team of data engineering experts is here to help.

Contact us today to schedule a consultation.