Published November 14, 2026 · By Henry Palma

Databricks Auto Loader Tutorial: Ingest Only New Files into a Bronze Delta Table

Use Databricks Auto Loader with a checkpoint and availableNow to load only new files into a bronze Delta table, rescue bad values, and prove reruns add nothing.

Auto Loader lets you load only the files that are new since the last run, so a rerun never duplicates rows. This post follows the video's lesson on Databricks Free Edition: land small CSV files in a Unity Catalog volume, ingest them into a bronze Delta table with availableNow, add a third file, and prove that each file is loaded exactly once.

Watch on YouTube

The notebook, source file and cheat sheet are in the lesson folder on GitHub. Use a Python notebook on Serverless compute and run it from the top. Everything uses small fictional CSV files.

Why incremental ingestion

In real projects, files keep arriving. Reloading every file on every run wastes compute and can duplicate rows. Auto Loader discovers new files in a folder and loads them into a Delta table. A checkpoint records which files are already processed, so each file is ingested once even when you rerun the job.

The destination is a bronze table: the raw landing layer of the medallion architecture, kept close to how the data arrived, plus lineage columns. Cleaning happens later, in silver.

Create a schema and two volumes

The first cell creates a schema named btingest plus a random suffix, and two managed Unity Catalog volumes. A volume is governed file storage under /Volumes.

spark.sql(f"CREATE VOLUME {schema_fqn}.{qi('raw_files')} COMMENT 'Landing zone for fictional CSV files'")
spark.sql(f"CREATE VOLUME {schema_fqn}.{qi('pipeline_state')} COMMENT 'Auto Loader checkpoint storage'")

volumeroot = f"/Volumes/{catalogname}/{demoschema}" sourcedir = f"{volumeroot}/rawfiles/orders/" checkpointdir = f"{volumeroot}/pipelinestate/bronzeorders/_checkpoint" ```

rawfiles is the landing zone. pipelinestate holds the checkpoint. Keep the checkpoint out of the folder Auto Loader scans.

Land the first batch

The notebook writes two CSV files with dbutils.fs.put and overwrite=False, so an existing file is never silently replaced. File A has three orders and file B has two.

Define the Auto Loader stream

Auto Loader is a Structured Streaming source with the format cloudFiles:

def bronze_stream(run_label):
    return (spark.readStream.format("cloudFiles")
        .option("cloudFiles.format", "csv")
        .option("header", "true")
        .option("rescuedDataColumn", "_rescued_data")
        .schema(order_schema)
        .load(source_dir)
        .select("*",
                F.col("_metadata.file_path").alias("source_file_path"),
                F.col("_metadata.file_name").alias("source_file"),
                F.current_timestamp().alias("ingested_at_utc"),
                F.lit(run_label).alias("load_run")))

Key parts:

An explicit schema: orderid and quantity as integers, store as a string, unitprice as a decimal with two places.
rescuedDataColumn: when a value cannot be parsed, the typed column becomes null and the original text is kept as JSON in rescueddata. Bronze keeps the evidence instead of dropping the row.
Lineage columns from _metadata for the file path and name, plus an ingestion timestamp and a run label.

Write with availableNow and a checkpoint

query = (bronze_stream(run_label).writeStream
    .option("checkpointLocation", checkpoint_dir)
    .outputMode("append")
    .trigger(availableNow=True)
    .toTable(bronze_fqn))
query.awaitTermination()

trigger(availableNow=True) processes every available file and then stops, which suits serverless compute. awaitTermination() makes the cell finish only after the load is done.

Run 1, then a new file, then run 2

The first run loaded 5 rows from 2 files, with nothing in the rescued column.

Then a third file arrived. In it, order 107 used the word two instead of the number 2 for quantity.

The second run used exactly the same source, checkpoint and table. Auto Loader read its checkpoint, skipped files A and B, and loaded only file C: three new rows. The table reached 8 rows with no duplicate order IDs. That is incremental ingestion.

The rescued data column in action

Order 107 stayed in bronze. Its quantity is null, unitprice is 4.00, and rescued_data holds the original text two plus the source file path. Bronze does not throw the row away. Silver is where you decide to fix, quarantine or reject it.

Prove a rerun adds nothing

A third run with no new files left the table at 8 rows, and no row carried the third run's label. The progress helper in the notebook printed a non-zero number, because it sums recent progress. The unchanged table count is the real proof of idempotency, not the progress print.

Verify with SQL

The final SQL summary on the actual run showed:

| Check | Value | |---|---| | Total rows | 8 | | Files ingested | 3 | | Rescued rows | 1 | | Distinct orders | 8 | | Valid revenue | 63.50 |

Revenue skips order 107 because its quantity is null. DESCRIBE HISTORY showed version 0 as the table creation, then two streaming updates for the two loads that appended data.

Auto Loader or COPY INTO?

COPY INTO is a SQL command that also loads files and skips ones already loaded, tracking them in the target table's metadata. Auto Loader tracks progress in its checkpoint and is built for growing file counts, schema options and the rescued data column. A simple rule of thumb from the lesson: COPY INTO fits straightforward scheduled loads, and Auto Loader fits larger or evolving folders. The notebook shows a COPY INTO example as text only.

Practice challenge

A fourth file lands with two rows: order 109, NORTH, quantity 3, price 2.50, and order 110, SOUTH, quantity 1, price free. You run the ingestion with a new run label. Predict the results, then check:

Total rows in bronze: 10
Rows from the new run: 2
Rescued rows: 2 (orders 107 and 110)
Valid revenue: 71.00
Running it again: adds nothing

Key takeaways

Land files in a Unity Catalog volume and keep the checkpoint in a separate location.
Use cloudFiles with an explicit schema and a rescued data column.
Add file metadata and an ingestion timestamp for lineage.
availableNow plus a checkpoint gives incremental, run-once-per-file loads on serverless.
Prove idempotency with the table count, and verify the stored table with SQL.