Databricks Auto Loader

Auto Loader is a Databricks feature that detects new files in cloud storage and loads them into a table without manual work. A folder that receives new files every hour, day, or minute becomes a steady, automatic data feed. Auto Loader remembers which files it already processed, so no file loads twice and no file gets missed.

The Mailbox Analogy

Picture a mailbox that receives letters all day. A manual process opens the mailbox, rereads every letter, and tries to remember which ones it already handled. Auto Loader works like an assistant who keeps a checklist. The assistant opens only the new letters and ticks them off the list.

MANUAL LOADING VS AUTO LOADER
──────────────────────────────────────────────────────────
MANUAL:
  Day 1: read files 1–100
  Day 2: read files 1–200   (files 1–100 read again)
  Day 3: read files 1–300   (files 1–200 read again)
  Slow, costly, and prone to duplicates.

AUTO LOADER:
  Day 1: read files 1–100   ✓ remembered
  Day 2: read files 101–200 ✓ remembered
  Day 3: read files 201–300 ✓ remembered
  Fast, cheap, and free of duplicates.

A Basic Auto Loader Stream

Auto Loader uses Structured Streaming with the cloudFiles source. The code below reads new JSON files from a folder and writes them into a table.

(spark.readStream
  .format("cloudFiles")
  .option("cloudFiles.format", "json")
  .option("cloudFiles.schemaLocation", "/Volumes/main/shop/meta/orders_schema")
  .load("/Volumes/main/shop/landing/orders/")
  .writeStream
  .option("checkpointLocation", "/Volumes/main/shop/meta/orders_checkpoint")
  .trigger(availableNow=True)
  .toTable("main.shop.orders_raw"))

What Each Line Does

• The cloudFiles format activates Auto Loader.
• The cloudFiles.format option names the file type, such as json, csv, or parquet.
• The schemaLocation folder stores the discovered schema so later runs stay consistent.
• The checkpointLocation folder stores the list of processed files.
• The availableNow trigger processes all waiting files and then stops.
• The toTable call writes the results into a Delta table.

How Auto Loader Finds New Files

Directory Listing Mode

Auto Loader lists the folder and compares it with its checkpoint. This mode needs almost no setup and works well for small and medium folders.

File Notification Mode

Auto Loader subscribes to cloud events, so the storage service announces each new file. This mode scales to very large folders because Auto Loader never scans the whole directory.

TWO DETECTION MODES
──────────────────────────────────────────────────────────
Directory listing:
  Storage folder ◄── Auto Loader checks the list on every run

File notification:
  New file lands ──► cloud event ──► queue ──► Auto Loader reads it

Schema Inference and Evolution

Auto Loader samples your files and infers a schema, then saves that schema in the schemaLocation folder. Source systems change over time, and new columns appear without warning. Auto Loader handles that situation through schema evolution modes.

Mode	Behavior When a New Column Appears
addNewColumns	The stream stops once, adds the column to the schema, and continues after a restart
rescue	The new data goes into a rescue column and the schema stays fixed
failOnNewColumns	The stream fails until someone updates the schema
none	New columns are ignored

The Rescued Data Column

Auto Loader stores values that do not match the schema in a column named _rescued_data. A text value inside a numeric field lands there instead of breaking the load. Engineers review that column to find and fix source problems.

Example: Online Store Order Files

An online store drops one JSON file of new orders into a folder every ten minutes. Auto Loader runs continuously and appends each new file to a raw orders table. The analytics team queries that table all day.

ONLINE STORE FEED
──────────────────────────────────────────────────────────
Store app ──► JSON file every 10 minutes ──► landing folder
                                                  │
                                           Auto Loader stream
                                                  │
                      ┌───────────────────────────┴──────────┐
                      ▼                                      ▼
              main.shop.orders_raw                   _rescued_data
              (valid rows)                           (odd values for review)

Replace the availableNow trigger with a processing-time trigger for a continuous stream. The code below checks for new files every minute.

.trigger(processingTime="1 minute")

Cost and Reliability Tips

• Keep one checkpoint folder per stream and never share it between streams.
• Use the availableNow trigger for scheduled jobs, which lets compute shut down after each run.
• Store the schema and checkpoint folders outside the landing folder.
• Choose file notification mode for folders that hold millions of files.

Key Points

• Auto Loader loads only new files from cloud storage and never repeats old ones.
• The cloudFiles source and a checkpoint folder form the core setup.
• Directory listing mode suits simple cases, and file notification mode suits very large folders.
• Schema evolution modes control how the stream reacts to new columns.
• The _rescued_data column captures values that do not match the schema.
• The availableNow trigger turns a stream into an efficient scheduled batch job.