Auto Loader
Databricks · Data Ingestion
Theory
Auto Loader is Databricks' incremental file ingestion engine. You point it at a folder in a volume or cloud storage with the cloudFiles source in Structured Streaming, and on every run it picks up only the files it has not processed before. It remembers which files are done in a checkpoint, so you never load the same file twice. It can infer the schema, save it in a schema location, evolve it when new columns appear, and put values that do not match the schema into a _rescued_data column instead of failing.
Example: shop systems drop orders_0001.csv, orders_0002.csv and so on into /Volumes/main/sales/raw/orders/. An Auto Loader stream with trigger(availableNow=True) runs every hour, loads only the new files into main.sales.orders_bronze, then stops. In pipeline SQL, the same engine is used when you write STREAM read_files(...).
Why we use it
Listing millions of files and tracking what is already loaded is hard to do by hand. Auto Loader does the file discovery, exactly-once tracking, schema inference and schema evolution for you, and scales from a few files a day to millions per hour.
When to use it
Use Auto Loader for files that keep arriving in a volume or cloud storage, especially at high volume or with changing schemas. For a small one-off or a simple batch folder load, COPY INTO is fine. For SaaS apps or databases, use Lakeflow Connect instead.
How it works
- New files land in /Volumes/main/sales/raw/orders/
- Auto Loader discovers files (file events or directory listing)
- Is the file already recorded in the checkpoint? Yes: Skip it. No: Read it with the saved schema.
- Does it bring new columns? Yes: Evolve schema (stream restarts and picks them up). No: Keep the current schema.
- Append rows to main.sales.orders_bronze and update the checkpoint
- Stop (availableNow) or wait for more files
Syntax
spark.readStream.format("cloudFiles")
.option("cloudFiles.format", "csv|json|parquet|avro|...")
.option("cloudFiles.schemaLocation", "<path>")
.load("<source path>")
.writeStream.option("checkpointLocation", "<path>")
.trigger(availableNow=True).toTable("<catalog.schema.table>")
Pipeline SQL: SELECT * FROM STREAM read_files('<path>', format => 'csv')Example
# Python notebook or job task
(spark.readStream
.format("cloudFiles")
.option("cloudFiles.format", "csv")
.option("header", "true")
.option("cloudFiles.schemaLocation", "/Volumes/main/sales/raw/_schemas/orders")
.option("cloudFiles.schemaEvolutionMode", "addNewColumns")
.load("/Volumes/main/sales/raw/orders/")
.writeStream
.option("checkpointLocation", "/Volumes/main/sales/raw/_checkpoints/orders")
.trigger(availableNow=True)
.toTable("main.sales.orders_bronze"))
-- Same idea inside a Lakeflow pipeline (SQL)
CREATE OR REFRESH STREAMING TABLE orders_bronze AS
SELECT * FROM STREAM read_files('/Volumes/main/sales/raw/orders/', format => 'csv');Key points
- format("cloudFiles") is what turns a normal stream into Auto Loader
- The checkpoint stores which files were processed; never share it between streams
- schemaLocation keeps the inferred schema so later runs do not re-infer it
- Unexpected values land in _rescued_data instead of breaking the load
- File events mode (cloudFiles.useManagedFileEvents) scales better than directory listing
- trigger(availableNow=True) processes the backlog then stops, so it runs well as a job