Build a Simple Batch ETL Pipeline

Building your first data pipeline often feels like wrestling with a pile of scripts, cron jobs, and fragile copy statements. You know the data needs to move from a landing zone into a clean, queryable table — but the moment you try to connect the pieces, you hit schema drift, silent failures, and no way to recover. In this lesson, you'll learn how to build a simple batch ETL pipeline on Databricks that turns that chaos into a repeatable, observable, production-ready process — one that you can extend and trust.

The problem this lesson solves

When you start working with real data, it rarely sits in a convenient format. It arrives as CSV files dropped into cloud storage, JSON logs from APIs, or tables in a legacy database. You need to extract that data, transform it into something useful, and load it into a place where analysts and dashboards can query it. Without a structured approach, you end up with:

• Copy-paste notebooks that everyone edits differently
• Undocumented steps that break when the data changes
• No way to reproduce a failed run or debug a bad output

A batch ETL pipeline solves this by giving you a clear, sequential workflow: you read from source, apply deterministic transformations, and write to a target — typically a Delta table — on a schedule. The beauty of doing this on Databricks is that your pipeline can scale from a few megabytes to terabytes without rewriting your code, and you get error reporting, retries, and a full history of your data changes for free.

Core concept / mental model

Think of an ETL pipeline as an assembly line for data:

1. Extract — raw materials (files, tables, streams) come in at one end.
2. Transform — each station (Spark transformation) cleans, joins, aggregates, or enriches the data.
3. Load — the finished product is stored in a polished, structured format (Delta Lake) ready for consumption.

In Databricks, you implement this with notebooks or Python scripts that run on a job cluster. You define the pipeline as a sequence of DataFrames: each step takes one input, produces a new output, and passes it to the next stage. The key mental shift is to treat every step as a pure function of its input — no hidden side effects, no writes to random paths, no global variables that change behavior.

Pro tip: Your pipeline code should be idempotent — running it twice should produce the same result. Delta Lake’s overwrite mode and merge operations make this easy, and it saves you from duplicate data disasters.

How it works step by step

Here’s the high-level sequence you’ll follow to build any batch ETL pipeline:

1. Define the source — a path to raw data (e.g., /mnt/raw/sales/)
2. Read the source into a Spark DataFrame
3. Apply transformations — filtering, casting, joins, aggregations
4. Write the result to a Delta table (managed or external)
5. Schedule the job to run on a recurring basis
6. Monitor the run and alert on failures

Each step builds on the previous one. The transformations are the heart of your logic, and the read/write steps are the shell that moves data in and out.

Hands-on walkthrough

Let’s build a real batch ETL pipeline. We’ll create a simple one that reads raw sales data from a CSV, cleans it, aggregates daily totals, and writes to a Delta table. You can run this in a Databricks notebook or as a Python script on a cluster.

1. Set up your environment

First, make sure your cluster has the Databricks Runtime and that you have a directory for raw data. In a notebook, start with:

# Define paths (adjust these to your own mount points)
raw_path = "/mnt/raw/sales"
target_path = "/mnt/delta/sales_daily"

# This is where Spark magic happens under the hood
spark = SparkSession.builder.getOrCreate()

2. Extract: read the raw data

Use the DataFrame reader to load the CSV. Notice that we set inferSchema for convenience, but in production you should define an explicit schema to avoid surprises.

from pyspark.sql.types import StructType, StructField, StringType, DoubleType, TimestampType

# Option 1: Infer schema (quick but fragile)
df_raw = spark.read.option("header", "true").option("inferSchema", "true").csv(raw_path)

# Better: explicit schema
schema = StructType([
    StructField("order_id", StringType(), True),
    StructField("customer_id", StringType(), True),
    StructField("amount", DoubleType(), True),
    StructField("order_timestamp", TimestampType(), True)
])

df_sales = spark.read.option("header", "true").schema(schema).csv(raw_path)

3. Transform: clean and aggregate

Now apply transformations. Drop rows with missing order IDs, filter out negative amounts, and group by date.

from pyspark.sql.functions import col, to_date, sum as _sum

# Clean: remove null / invalid rows
df_clean = df_sales.filter(
    col("order_id").isNotNull() & (col("amount") > 0)
)

# Transform: add a date column and aggregate daily totals
df_daily = df_clean \
    .withColumn("order_date", to_date(col("order_timestamp"))) \
    .groupBy("order_date") \
    .agg(_sum("amount").alias("total_amount"), 
         count("order_id").alias("order_count"))

4. Load: write to Delta

Write the aggregated data to a Delta table. Using mode("overwrite") makes the pipeline idempotent for this simple case.

df_daily.write \
    .mode("overwrite") \
    .format("delta") \
    .save(target_path)

# Or register it as a table for SQL access
spark.sql(f"CREATE TABLE IF NOT EXISTS sales_daily USING DELTA LOCATION '{target_path}'")

5. Run and verify

Run the notebook. You should see output confirming the write, and then query the table:

SELECT * FROM sales_daily ORDER BY order_date DESC LIMIT 10;

Expected output — a DataFrame with columns order_date, total_amount, order_count.

Compare options / when to choose what

There are multiple ways to orchestrate a batch pipeline on Databricks. Here’s a comparison to help you choose:

For a first simple ETL, a notebook triggered by a job is the fastest path. As your logic grows, migrate to Delta Live Tables for maintainability and monitoring.

Pro tip: Start with a notebook, but keep your transformations in separate functions so you can unit-test them later.

Troubleshooting & edge cases

Even a simple pipeline can hit snags. Here are common issues and fixes:

• CSV parser errors — If you see Malformed line in CSV, add .option("mode", "DROPMALFORMED") or pre-validate the source.
• Schema mismatch — When you overwrite a Delta table with a different schema, it may fail. Use .option("overwriteSchema", "true") to overwrite the schema, but be careful in production.
• OutOfMemory errors — Your cluster may be under-provisioned. Increase executor memory or repartition the DataFrame before transformations.
• Duplicate data — If you run a merge incorrectly, you might introduce duplicates. Always test with a SELECT count(*) before and after.
• Job fails silently — Configure alerting on your job clusters, and always check the Spark UI for stage failures.

What you learned & what's next

You now understand how to build a simple batch ETL pipeline on Databricks: you extracted raw data, transformed it in Spark, loaded it into Delta Lake, and scheduled it to run repeatedly. You also learned about idempotency, schema management, and how to choose among orchestration approaches.

In the next lesson, you’ll explore incremental processing with Auto Loader and Delta Live Tables, which lets you handle new data without reprocessing everything — a natural evolution of what you just built.