Databricks Auto Loader: File Discovery, Schema Evolution and Checkpoints
Introduction
Databricks Auto Loader provides checkpointed incremental file ingestion with configurable schema handling. This lesson is the second part of the Data Ingestion and Loading section of the official exam outline (about 21% of the scored questions, roughly nine or ten of 45). The previous lesson introduced COPY INTO and file-level retry safety for modest scheduled SQL loads. Auto Loader is the Databricks answer when files arrive continuously, when there are very many of them, or when the schema changes over time. Auto Loader is an explicit exam objective; distinguish its cloudFiles source options from ordinary reader options.
After this lesson you will be able to write an Auto Loader stream in Python and SQL, choose between directory listing and file notification mode, explain how schemas are inferred, stored and evolved, use the rescued data column, and run Auto Loader as an incremental batch with availableNow.
Learning objectives
- Explain what Auto Loader does and why it scales better than
COPY INTOfor large file counts. - Write the
cloudFilessource withcloudFiles.formatand the target table, checkpoint and schema location. - Compare directory listing mode and file notification mode.
- Use
cloudFiles.inferColumnTypes,cloudFiles.schemaHintsandcloudFiles.schemaLocation. - Choose a
cloudFiles.schemaEvolutionMode(addNewColumns,rescue,failOnNewColumns,none) and explain the rescued data column. - Explain checkpoints and the
availableNowtrigger for scheduled, incremental batch processing.
1. What Auto Loader is
Auto Loader is a Structured Streaming source called cloudFiles. It incrementally and efficiently discovers new files as they land in cloud storage (ADLS, S3, GCS) or in a Unity Catalog volume, and loads them into a Delta table. It can process billions of files, and it supports JSON, CSV, XML, Parquet, Avro, ORC, text and binary files.
Its main properties, which the exam keeps returning to, are:
- Incremental: with immutable source files, preserved checkpoint state and a compatible sink such as Delta, restarts can avoid reprocessing committed files. This is not business-key deduplication.
- Scalable: managed file events reduce repeated listing work; directory listing remains an alternative.
- Schema aware: it can infer the schema, store it, and evolve it as new columns appear, while keeping unexpected data in a rescued data column.
- Flexible latency: the same code can run continuously (seconds of latency) or on a schedule with the
availableNowtrigger (incremental batch).
| Question | COPY INTO | Auto Loader |
|---|---|---|
| Interface | SQL command | Structured Streaming source (cloudFiles), also usable in streaming tables and pipelines |
| File tracking | Table metadata | Checkpoint (RocksDB state) |
| Typical scale | Thousands of files | Millions to billions of files |
| Schema evolution | mergeSchema |
Schema location, evolution modes, rescued data |
| Latency | Batch only | Continuous or triggered |
2. Basic syntax
Python
Assume catalog prod, schemas raw and bronze, a writable state volume prod.bronze.ingestion_state, and an authorized source external location already exist. Grant the job identity parent usage, state-volume read/write and target-table creation/write privileges as appropriate. Schema and checkpoint paths must be durable and outside source cleanup policies.
(spark.readStream
.format("cloudFiles")
.option("cloudFiles.format", "json")
.option("cloudFiles.schemaLocation", "/Volumes/prod/bronze/ingestion_state/schemas/events")
.load("s3://acme-landing/events/")
.writeStream
.option("checkpointLocation", "/Volumes/prod/bronze/ingestion_state/checkpoints/events")
.trigger(availableNow=True)
.toTable("prod.bronze.events"))Reading the code from top to bottom:
format("cloudFiles")selects Auto Loader. It iscloudFiles, notautoloader.cloudFiles.formatis the format of the source files (json,csv,parquet, ...). A frequent mistake is to write.format("json")and lose all Auto Loader behaviour.cloudFiles.schemaLocationis the folder where Auto Loader stores the inferred schema and its history. Outside a pipeline that manages it, it is needed when the schema is inferred. It may be the same folder as the checkpoint.load(path)points at the input directory.checkpointLocationstores the stream progress. It must be unique per stream.toTable(...)writes to a Unity Catalog Delta table.
SQL
In SQL, you read files incrementally with a streaming table and read_files, which uses Auto Loader under the hood:
CREATE OR REFRESH STREAMING TABLE prod.bronze.events
AS SELECT *, _metadata.file_path AS source_file
FROM STREAM read_files(
's3://acme-landing/events/',
format => 'json'
);The keyword STREAM is what makes the read incremental; without it read_files is a plain batch read that rescans everything. In Lakeflow Spark Declarative Pipelines (formerly Delta Live Tables or DLT) the same pattern defines a bronze streaming table, and the pipeline manages the schema location and checkpoints for you.
Worked example: reading with the wrong format option
Scenario. A developer expects Auto Loader schema inference from the following read, but it fails without a supplied schema. No Auto Loader schema history is created.
df = (spark.readStream.format("json")
.load("s3://acme-landing/events/"))What is wrong, and how do you fix it?
Reasoning.
format("json")selects the standard file-stream source, not Auto Loader. It can process new files, but normally requires a supplied schema and does not provide Auto Loader schema management.- Auto Loader is selected with
format("cloudFiles")and the file format moves tocloudFiles.format. - Add
cloudFiles.schemaLocationso the inferred schema is persisted.
df = (spark.readStream.format("cloudFiles")
.option("cloudFiles.format", "json")
.option("cloudFiles.schemaLocation", "/Volumes/prod/bronze/ingestion_state/schemas/events")
.load("s3://acme-landing/events/"))Why other fixes are weaker. Switching to spark.read makes the load batch and non-incremental. Adding inferSchema does not give Auto Loader's file discovery or evolution. Running COPY INTO would work for modest volumes, but the question asks about the streaming source.
3. Auto Loader file discovery modes
Directory listing
Directory listing discovers files through storage listings and is simple to configure when you only have read access. It is a useful fallback, but current guidance prefers managed file events for most supported workloads. Incremental listing based on lexical order is deprecated; do not enable it merely because a folder looks date-partitioned.
Notifications: managed file events and classic queues
For a path covered by a Unity Catalog external location with file events enabled, use managed file events on a supported runtime (14.3 LTS or above):
.option("cloudFiles.useManagedFileEvents", "true")Databricks shares event infrastructure across streams for that location. The administrator configures the storage integration; the notebook does not need embedded queue-creation credentials. A listing can still occur during initialization or after an expired event cache, so this does not mean zero listing calls.
The classic per-stream notification configuration remains:
.option("cloudFiles.useNotifications", "true")Classic notifications need the appropriate cloud permissions or preconfigured resources. Do not combine the two options. A volume path alone does not require listing mode: check its backing external location and supported event configuration. Neither notifications nor listing guarantee processing order.
| Choice | Configuration | Main tradeoff |
|---|---|---|
| Directory listing | Default when notifications are not enabled | Simple setup; listing work grows with the source |
| Managed file events | cloudFiles.useManagedFileEvents = true |
Recommended where supported; external-location setup required |
| Classic notifications | cloudFiles.useNotifications = true |
Per-stream cloud queue configuration |
Example. A telecom feed creates millions of randomly named files. First check whether its external location supports managed file events, rather than relying on lexical listing. For a small proof of concept with read-only storage access, directory listing can be adequate. Compare discovery cost and backlog under the expected schedule.
4. Schema inference and hints
When you do not provide a schema, Auto Loader samples the data (the first 50 GB or 1,000 files, whichever limit is reached first) to infer it, then stores it in the _schemas directory inside cloudFiles.schemaLocation. Later runs reuse the stored schema and evolve it when needed.
Important behaviours:
- For JSON, CSV and XML, Auto Loader infers all columns as strings by default, because text formats do not carry types and sampling cannot guarantee a type. Set
cloudFiles.inferColumnTypestotrueto infer real types (bigint,double,timestamp, nested structs and arrays). - Parquet and Avro encode types, and inference merges sampled file schemas. Auto Loader does not support ORC schema inference/evolution; do not assume format support implies inference support.
cloudFiles.schemaHintsoverrides the inferred type of specific columns using SQL DDL syntax, without writing the whole schema.
(spark.readStream.format("cloudFiles")
.option("cloudFiles.format", "csv")
.option("header", "true")
.option("cloudFiles.inferColumnTypes", "true")
.option("cloudFiles.schemaHints", "order_id BIGINT, amount DECIMAL(12,2)")
.option("cloudFiles.schemaLocation", "/Volumes/prod/bronze/ingestion_state/schemas/orders")
.load("/Volumes/prod/raw/landing/orders/"))Here hints guide the expected types of order_id and amount while other fields are inferred; hints are not a general-purpose cast of incompatible source values. Hints are useful when the sample would infer double for a column that should be a decimal, or bigint for a code that must keep its leading zeros as a string (zip_code STRING).
You can also give a complete schema with .schema(...). Then no inference happens, and schemaLocation is not needed, but evolution is limited because you control the schema.
5. Schema enforcement and evolution
Data sources change: a producer adds a column, or renames one. Auto Loader detects such changes and reacts according to cloudFiles.schemaEvolutionMode.
| Mode | New column appears | Stream behaviour | Notes |
|---|---|---|---|
addNewColumns (default when no schema is given) |
Schema is updated with the new column | Stream fails once with an unknown field error, then succeeds on restart with the new schema | Jobs should retry automatically |
rescue |
Schema is not evolved | Added fields are rescued without a schema-change failure | Good when you want stability |
failOnNewColumns |
Not accepted | Stream fails until you update the schema or the data | Strictest option |
none (default when you provide a schema) |
Ignored unless a rescued data column is set | Stream does not fail | New columns are dropped if not rescued |
Two details deserve attention:
- With
addNewColumns, the stream stops with an exception the first time it sees the new column, updates the schema inschemaLocation, and works after a restart. Configure retry/restart behavior explicitly in a Lakeflow Job; simply placing code in a job does not guarantee a successful restart or target schema evolution. See Lakeflow Jobs orchestration. This surprises beginners who expect the new column to appear without any interruption. - When you supply a schema, evolution does not happen by default; the mode is
none.
Writing to a Delta table also needs mergeSchema when you want the target table to accept the evolved columns:
.writeStream.option("mergeSchema", "true")Type changes are also handled: with Databricks Runtime 16.4 and above, the addNewColumnsWithTypeWidening mode can widen compatible types (for example int to bigint) and rescue incompatible ones. This mode is in Public Preview; verify runtime and target-table compatibility before enabling it.
The rescued data column
The rescued data column preserves schema-mismatched fields for review. It is named _rescued_data and is added by default when Auto Loader infers the schema. It stores, as JSON, the following:
- columns that are absent from the schema (new fields);
- values whose type does not match the schema (for example text in a numeric column);
- columns whose name differs only by case;
- the source file path (
_file_path).
Rename it with the reader option rescuedDataColumn (without the cloudFiles. prefix). Rescue is not a guarantee against malformed files, source deletion or unrelated failures. Typical use: keep it in bronze, then monitor it. A growing number of non-null _rescued_data rows is an alert that the producer changed the format.
SELECT _rescued_data, count(*) AS n
FROM prod.bronze.events
WHERE _rescued_data IS NOT NULL
GROUP BY _rescued_data;Worked example: choosing an evolution mode
Scenario. A mobile app team adds new event properties almost every sprint. The team wants added optional fields to leave the published schema unchanged and avoid schema-change failures overnight. The team still wants to review new fields before exposing them. Which mode and design do you choose?
Reasoning.
failOnNewColumnsrejects a newly observed field.addNewColumnswould also stop the stream once per change and then change the bronze schema, which may break dashboards that useSELECT *.rescueavoids failure due to those schema changes and preserves unexpected fields in_rescued_data; other failures still require monitoring.- The team reviews
_rescued_dataregularly and promotes useful fields with an explicit change to the pipeline.
Why other choices are weaker. none also keeps the job running but drops unknown fields if the rescued column is disabled, losing data. Removing the schema location removes the stored schema and defeats the purpose.
6. Checkpoints and triggers
Checkpoints
The checkpoint (checkpointLocation) stores processing state used for recovery. File-level guarantees depend on retaining it, the source contract and the sink behavior. Rules to remember:
- One checkpoint per stream; never share it between two streams or two target tables.
- Do not delete it casually. A new checkpoint with existing-file inclusion can reprocess retained source files and duplicate previously committed rows.
- Changing the source path of a stream while keeping the checkpoint is not supported; start a new checkpoint.
- Treat schema and checkpoint locations as durable pipeline state; changing one is not a supported shortcut for resetting the whole ingestion workflow.
Triggers
| Trigger | Behaviour | When to use |
|---|---|---|
default (no trigger) / processingTime="1 minute" |
Continuously processes micro-batches | Low latency, always-on cluster |
availableNow=True |
Processes all files available now, in several micro-batches if needed, then stops | Scheduled incremental batch on jobs compute |
once=True (legacy) |
One micro-batch, then stops | Replaced by availableNow |
availableNow is the pattern that connects streaming and batch: you keep the checkpoint and exactly-once behaviour, but you pay for compute only while the job runs. A nightly job with availableNow=True is the typical answer to "incremental, cheap, no always-on cluster".
Use .awaitTermination() on the returned query when later cells must wait. On serverless notebooks/jobs, use a supported bounded trigger such as AvailableNow; do not assume processingTime or Spark continuous processing is supported there.
7. Choosing between Auto Loader and other options
| Situation | Choice |
|---|---|
| Thousands of files per day, simple SQL, no checkpoint management | COPY INTO |
| Millions of files, continuous arrival, evolving schema | Auto Loader |
| Hourly batch with cheap compute but incremental loading | Auto Loader with availableNow |
| Declarative pipeline with quality rules | Streaming table with STREAM read_files in Lakeflow Spark Declarative Pipelines |
| One-off file | Upload UI or CREATE TABLE AS SELECT |
A common distractor on the exam presents COPY INTO and Auto Loader together; the discriminating words are "scalable to millions of files", "schema evolution" and "rescued data", which point to Auto Loader.
8. Operations: monitoring and tuning
- Add audit columns:
_metadata.file_path,_metadata.file_modification_timeandcurrent_timestamp()make each row traceable. - Limit batch size with
cloudFiles.maxFilesPerTriggerorcloudFiles.maxBytesPerTriggerwhen a first run over a large backlog overwhelms the cluster. - Initial files:
cloudFiles.includeExistingFilesis evaluated only on first start with a new checkpoint. Changing it on a restart does not backfill previously excluded files. - Allow overwrites of files only deliberately with
cloudFiles.allowOverwrites; by default a modified file is not reprocessed. - Watch the stream: the streaming query progress and the Lakeflow Jobs run page show rows processed per batch and backlog.
Example. After a three-year backlog of 2 million files, the first run of a new Auto Loader stream takes 10 hours and fails with an out-of-memory error. Choose a measured batch limit for the runtime and file sizes; 10,000 is not necessarily smaller than the default or safe for memory. Runtime 18.0+ dynamically configures these limits. AvailableNow can drain a backlog over multiple micro-batches.
Common pitfalls and exam tips
- Pitfall:
format("json")instead offormat("cloudFiles"). The file format belongs incloudFiles.format. - Pitfall: no
schemaLocation. Inferred schemas must be stored, otherwise the stream cannot track or evolve the schema. - Pitfall: expecting typed columns from JSON or CSV. Without
inferColumnTypesor hints, columns are strings. - Pitfall: deleting the checkpoint to "fix" an error. The stream reprocesses everything and creates duplicates.
- Pitfall: surprise stop with
addNewColumns. One failure is the intended behaviour; retries restart it with the new schema. - Pitfall: confusing
_rescued_datawith a corrupt record column. It also holds extra and mismatched-type fields, not only malformed rows. - Exam tip. Slow listing at scale suggests event-based discovery. Distinguish recommended managed file events from classic per-stream notifications.
- Exam tip. "Incremental, scheduled, no always-on cluster" means
trigger(availableNow=True). - Exam tip. "Keep running when the schema changes and do not lose new fields" means
rescuemode.
Mini case study
Case. Nordic Retail's point-of-sale devices upload JSON receipts to abfss://pos@nordicdata.dfs.core.windows.net/receipts/. About 8 million files accumulate per month, in a flat folder, and the vendor adds new optional fields without warning. Finance needs the bronze table refreshed every hour, and the platform team will not pay for a cluster that runs all day. The team has permission to create storage events.
Tasks. Select the detection mode, trigger and evolution mode, and describe what to watch.
Solution.
- Detection mode: prefer managed file events when enabled on the source external location and supported by the runtime; otherwise configure classic notifications with the required permissions.
- Trigger:
availableNow=True, started hourly by a Lakeflow Job on jobs compute, which gives incremental processing without an always-on cluster. - Evolution mode:
addNewColumnswith job retries is acceptable if finance accepts evolving columns; if bronze consumers must never change, userescueand promote fields deliberately. Given that finance wants stable reports, chooserescueand review_rescued_data. - Schema:
cloudFiles.inferColumnTypes = trueplus hints for monetary columns (total DECIMAL(12,2)). - Monitoring: count non-null
_rescued_datarows per day and alert when they rise; keep_metadata.file_pathfor traceability.
Key terms
- Auto Loader: the
cloudFilesstreaming source for incremental file ingestion. - Checkpoint: stored progress that makes a stream restartable and exactly-once.
- Schema location: folder where Auto Loader keeps the inferred schema history.
- Schema hints: DDL overrides for selected column types.
- Rescued data column:
_rescued_data, the JSON column keeping data that does not fit the schema. - File notification mode: discovery through cloud event notifications rather than directory listing.
availableNow: trigger that processes available data and stops.
Summary checklist
- I can write an Auto Loader stream with
cloudFiles,cloudFiles.format,schemaLocationand a checkpoint. - I can explain directory listing versus file notification mode and when to use each.
- I know JSON and CSV columns are strings unless
inferColumnTypesor hints are used. - I can name the four evolution modes and describe the stream behaviour of each.
- I know what
_rescued_datastores. - I know why the checkpoint must not be deleted or shared.
- I can run Auto Loader as scheduled incremental batch with
availableNow.
For production diagnostics, continue with stream monitoring and job troubleshooting.
Official sources
- Exam guide: https://www.databricks.com/sites/default/files/2026-10/Databricks-Certified-Data-Engineer-Associate-Exam-Guide-August-2026.pdf (opens in a new tab)
- Auto Loader: https://docs.databricks.com/aws/en/ingestion/cloud-object-storage/auto-loader/ (opens in a new tab)
- Schema inference and evolution: https://docs.databricks.com/aws/en/ingestion/cloud-object-storage/auto-loader/schema (opens in a new tab)
- File notifications: https://docs.databricks.com/aws/en/ingestion/cloud-object-storage/auto-loader/file-notification-mode (opens in a new tab)
- Directory listing: https://docs.databricks.com/aws/en/ingestion/cloud-object-storage/auto-loader/directory-listing-mode (opens in a new tab)
- Option reference: https://docs.databricks.com/aws/en/spark/api-options (opens in a new tab)
- Production guidance: https://docs.databricks.com/aws/en/ingestion/cloud-object-storage/auto-loader/production (opens in a new tab)
- Trigger support: https://docs.databricks.com/aws/en/structured-streaming/triggers (opens in a new tab)
- Serverless limitations: https://docs.databricks.com/aws/en/compute/serverless/limitations (opens in a new tab)
- Auto Loader operational guidance: https://docs.databricks.com/aws/en/ingestion/cloud-object-storage/auto-loader/production (opens in a new tab)
