Databricks DE · Topic 4.1

Auto Loader (cloudFiles)

Incrementally ingests only the new files landing in cloud storage — a Structured Streaming source with checkpointing, schema evolution, and a rescued-data column. This deep dive walks each with the code.

cloudFilesexactly-onceschema evolution_rescued_datafile events

The one-liner

Auto Loader (format("cloudFiles")) streams in only new files as they arrive — checkpointed for exactly-once, with automatic schema inference/evolution and a _rescued_data column for mismatches.

🎢 Analogy: a conveyor belt that only picks up new boxes. Files keep landing in a folder; Auto Loader grabs just the ones it hasn't seen — never re-handling old boxes, even after a restart.

Incremental ingest — only new files

▸ "land" new files, then run Auto Loader; restart-safe via checkpoint

No files yet.

1 · Core setup — cloudFiles source + checkpoint

trigger(availableNow=True) processes all currently-available files then stops (ideal for scheduled jobs); omit for an always-on stream. Throttle with maxFilesPerTrigger/maxBytesPerTrigger.

PySpark

df = (spark.readStream.format("cloudFiles")
        .option("cloudFiles.format", "json")
        .option("cloudFiles.schemaLocation", "/Volumes/cat/sch/vol/_schema")
        .load("/Volumes/cat/sch/vol/landing"))

(df.writeStream
   .option("checkpointLocation", "/Volumes/cat/sch/vol/_ckpt")   # exactly-once state
   .trigger(availableNow=True)                                   # all new, then stop
   .toTable("cat.sch.bronze_events"))

SQL — read_files streaming TVF

CREATE OR REFRESH STREAMING TABLE cat.sch.bronze_events AS
SELECT * FROM STREAM read_files('/Volumes/cat/sch/vol/landing', format => 'json');

2 · Built-in features — click each

Schema inference & evolution — inferred schema stored at schemaLocation; behavior set by cloudFiles.schemaEvolutionMode (default addNewColumns: fail with UnknownFieldException, then restart resumes with the new column). Pin types with schemaHints; enable inferColumnTypes.

_rescued_data — values that don't match the schema (missing col, type/case mismatch) are preserved here instead of dropped. Rename via cloudFiles.rescuedDataColumn. Check it when rows look "missing".

File detection: directory listing (default) → file notification (cloudFiles.useNotifications=true) → file events on UC external locations (recommended at scale; DBR 14.3 LTS+).

Checkpoint — seen-file metadata in RocksDB → exactly-once, restart-safe. One checkpoint per stream; deleting it reprocesses everything.

3 · Schema evolution modes

Mode	Behavior
`addNewColumns` (default)	New column → stream fails (`UnknownFieldException`), schema updated; restart resumes.
`addNewColumnsWithTypeWidening`	Same, plus widens types (e.g. int→long).
`rescue`	Never evolves, never fails — odd data → `_rescued_data`.
`failOnNewColumns`	Fails and won't restart until you update the schema.
`none`	Ignores new columns; does not rescue.

.option("cloudFiles.schemaEvolutionMode", "addNewColumns")
.option("cloudFiles.schemaHints", "id long, event_ts timestamp")
.option("cloudFiles.inferColumnTypes", "true")
.option("cloudFiles.rescuedDataColumn", "_rescued")

💡 The default fail-then-restart is intentional (clean, auditable schema change). Run under a job with retries so the restart is automatic in production.

Detection modes at a glance

	Directory listing	File notification	File events (UC)
Setup	none	cloud queue resources	enable on UC ext. location
Cost at scale	high (list calls)	low	lowest
Recommended	small dirs	large dirs	default for scale

Why it matters

"List the folder & read everything" reprocesses old data and doesn't scale to millions of files. Auto Loader solves both.
It's the default answer for landing raw files into bronze — guaranteed interview territory.

🖱️ Real-world use case: clickstream JSON lands in S3 every few minutes. Auto Loader streams only new files into a bronze Delta table, evolving the schema as fields appear and parking malformed records in _rescued_data.

Uses, edge cases & limitations

Uses: continuous or scheduled (trigger(availableNow=True)) bronze ingestion from S3/ADLS/GCS/UC Volumes; JSON, CSV, Parquet, Avro, XML…
Edge cases: default addNewColumns fails-then-restarts on a new column (run under a retrying job); directory listing gets slow on huge dirs → file notification / file events; deleting the checkpoint reprocesses everything.
Limitation: ingests files — for Kafka/Event Hubs use streaming connectors (4.4); not for OLTP row writes.

Common gotchas

❌ Two streams sharing one checkpoint — corrupts state. One checkpoint per stream.
❌ Expecting it to re-read old files — by design it won't; use a new checkpoint to reprocess.
❌ Ignoring _rescued_data — that's where malformed rows go.
❌ Directory listing at massive scale — switch to file notification / file events.
❌ Assuming a new column "just works" — default mode fails the batch first; ensure the job restarts.