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

  1. New files land in /Volumes/main/sales/raw/orders/
  2. Auto Loader discovers files (file events or directory listing)
  3. Is the file already recorded in the checkpoint? Yes: Skip it. No: Read it with the saved schema.
  4. Does it bring new columns? Yes: Evolve schema (stream restarts and picks them up). No: Keep the current schema.
  5. Append rows to main.sales.orders_bronze and update the checkpoint
  6. 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

Databricks topics

Databricks Fundamentals

Compute

Notebooks & Development

Apache Spark on Databricks

Delta Lake

Unity Catalog & Governance

Data Ingestion

Declarative Pipelines (Lakeflow)

Lakeflow Jobs

Databricks SQL & BI

Architecture, Performance & Cost

Machine Learning & AI

Certification & Interview

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Databricks

The lakehouse platform from the ground up: Apache Spark, Delta Lake, Unity Catalog, Lakeflow pipelines and jobs, Databricks SQL and MLflow. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice SQL / Spark SQL Sharpen the SQL you use in Databricks SQL and Spark SQL: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Databricks  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Databricks needs a real workspace — there's no in-browser engine for it. Click Copy above, then paste it into a notebook or the SQL editor in Databricks Free Edition (free, no credit card) and run it on serverless compute.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic