How to build ETL pipelines in Databricks

Summary

• Build ETL in Databricks with three components: Lakeflow Connect or Auto Loader to ingest, Spark Declarative Pipelines (SDP) to transform, and Lakeflow Jobs to schedule and orchestrate.
• Create a pipeline from New > ETL Pipeline, pick a default catalog and schema, and write streaming tables and materialized views in SQL or Python. The pipeline works out dependencies and run order for you.
• Put data quality rules in the table definition with expectations. Each rule can warn, drop invalid rows, or fail the update.
• Use Dry run to validate code without updating data, then schedule from the editor's Schedule button, which creates a job.
• Monitor with the Issues panel and the pipeline event log, and deploy to production as code with Declarative Automation Bundles.

How to build ETL pipelines in Databricks

You build an ETL pipeline in Databricks by ingesting data into Unity Catalog tables, declaring transformations with Spark Declarative Pipelines (SDP) in SQL or Python, and scheduling that pipeline with Lakeflow Jobs. The fastest path is the editor: New > ETL Pipeline, declare your tables, click Run pipeline, then Schedule. Databricks recommends developing new pipelines on serverless compute.

The steps at a glance

Step	What you do	Databricks component
1. Ingest	Land raw data from databases, SaaS apps, files, or event streams	Lakeflow Connect, Auto Loader
2. Transform	Declare streaming tables and materialized views, bronze to gold	SDP
3. Enforce quality	Attach expectations that warn, drop, or fail	SDP expectations on Databricks
4. Validate and run	Dry run, run a file, or run the whole pipeline	Pipeline editor
5. Orchestrate	Schedule or trigger the pipeline alongside other tasks	Lakeflow Jobs
6. Monitor and deploy	Read errors and metrics, deploy as code	Event log, Declarative Automation Bundles

Step 1: Ingest the source data

Lakeflow Connect offers managed connectors for databases (change data capture), SaaS applications, enterprise file storage, and message buses. Its pipelines write to Unity Catalog streaming tables on serverless compute. Connectors are in various release states.
For files in cloud object storage, use Auto Loader. It processes new files incrementally as they arrive, detects schema drift, and rescues data that would otherwise be lost. Databricks recommends Auto Loader inside SDP, where schema and checkpoint locations are managed for you.

Step 2: Declare the transformations

Create the pipeline with New > ETL Pipeline, set a default catalog and schema, and choose SQL or Python. A common layout follows the medallion pattern: a streaming table for raw (bronze) data, then materialized views for cleaned (silver) and aggregated (gold) data. This SQL follows the Databricks ETL tutorial:

CREATE OR REFRESH STREAMING TABLE orders_raw
AS SELECT *
FROM STREAM read_files(
  '/Volumes/main/landing/orders/',
  format => "csv",
  header => "true");

CREATE OR REFRESH MATERIALIZED VIEW orders_clean(
  CONSTRAINT valid_order_id EXPECT (order_id IS NOT NULL) ON VIOLATION DROP ROW)
AS SELECT order_id, customer_id, amount, order_date
FROM orders_raw;

CREATE OR REFRESH MATERIALIZED VIEW daily_revenue
AS SELECT order_date, SUM(amount) AS revenue
FROM orders_clean
GROUP BY order_date;

In Python, the same pattern uses from pyspark import pipelines as dp with @dp.table for streaming tables and @dp.materialized_view for materialized views. You don't write orchestration code: the pipeline analyzes dependencies between tables and orders execution. For change data capture, the AUTO CDC APIs in SDP on Databricks automate computing slowly changing dimensions (SCD) Type 1 and Type 2 from a CDC feed or database snapshots.

Step 3: Enforce data quality

Expectations are constraints you attach to a table. The default action, warn, writes invalid records to the target. ON VIOLATION DROP ROW drops them, and ON VIOLATION FAIL UPDATE stops the update. In Python, the matching decorators are @dp.expect, @dp.expect_or_drop, and @dp.expect_or_fail.

Step 4: Validate and run

In the pipeline editor, Dry run validates the pipeline without updating any data. Run file runs the table definitions in the current file, and Run pipeline runs the full update. Genie Code, the agent in the editor, can build a medallion pipeline from a prompt and fix failures, and it asks for your approval before running code.

Step 5: Orchestrate with Lakeflow Jobs

Click Schedule, then Add schedule. The default is a daily run; Advanced sets a specific time and More options adds notifications. This creates a job. In Lakeflow Jobs, you can also run the pipeline on a table update, on file arrival in a Unity Catalog storage location, or continuously, and combine it with other tasks in a multi-task job.

Step 6: Monitor and deploy to production

When an update fails, start at the Issues panel: failed tables are highlighted on the pipeline graph, and View details opens the matching event log entry. The event log contains pipeline progress, data quality checks, and lineage, and you can query it with event_log(<pipelineId>). Configure job notifications for start, success, or failure, sent to email addresses or webhook-based services.
For production, define the pipeline and job in a databricks.yml file with Declarative Automation Bundles (formerly Databricks Asset Bundles), keep it in Git, and deploy with databricks bundle deploy.

FAQs

Should I write ETL pipelines in SQL or Python?

Either. A single pipeline can mix Python and SQL source files, but each file can contain only one language (pipelines concepts).

What's the difference between a streaming table and a materialized view?

A streaming table processes each record exactly once, assuming an append-only source, which fits ingestion. A materialized view recomputes its results as needed to reflect the current state of the data, which fits transformations and aggregations.

Can a pipeline run continuously or in real time?

Yes. On Databricks, Spark Declarative Pipelines run in triggered or continuous mode. For streaming ETL, Databricks points to micro-batch mode, the default Structured Streaming trigger. For operational work that needs an immediate response, such as fraud detection, use Spark Real-Time Mode (RTM), which is also available in SDP on Databricks. Databricks states that "RTM achieves strict P99 latencies between 40ms and 300ms across demanding operational workloads."