Skip to content

Databricks Auto Loader: File Discovery, Schema Evolution and Checkpoints

Step 7 of 30

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 INTO for large file counts.
  • Write the cloudFiles source with cloudFiles.format and the target table, checkpoint and schema location.
  • Compare directory listing mode and file notification mode.
  • Use cloudFiles.inferColumnTypes, cloudFiles.schemaHints and cloudFiles.schemaLocation.
  • Choose a cloudFiles.schemaEvolutionMode (addNewColumns, rescue, failOnNewColumns, none) and explain the rescued data column.
  • Explain checkpoints and the availableNow trigger 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 availableNow trigger (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.

Python
(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:

  1. format("cloudFiles") selects Auto Loader. It is cloudFiles, not autoloader.
  2. cloudFiles.format is the format of the source files (json, csv, parquet, ...). A frequent mistake is to write .format("json") and lose all Auto Loader behaviour.
  3. cloudFiles.schemaLocation is 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.
  4. load(path) points at the input directory.
  5. checkpointLocation stores the stream progress. It must be unique per stream.
  6. 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:

SQL
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.

Python
df = (spark.readStream.format("json")
        .load("s3://acme-landing/events/"))

What is wrong, and how do you fix it?

Reasoning.

  1. 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.
  2. Auto Loader is selected with format("cloudFiles") and the file format moves to cloudFiles.format.
  3. Add cloudFiles.schemaLocation so the inferred schema is persisted.
Python
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):

Python
.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:

Python
.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.inferColumnTypes to true to 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.schemaHints overrides the inferred type of specific columns using SQL DDL syntax, without writing the whole schema.
Python
(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:

  1. With addNewColumns, the stream stops with an exception the first time it sees the new column, updates the schema in schemaLocation, 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.
  2. 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:

Python
.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.

SQL
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.

  1. failOnNewColumns rejects a newly observed field.
  2. addNewColumns would also stop the stream once per change and then change the bronze schema, which may break dashboards that use SELECT *.
  3. rescue avoids failure due to those schema changes and preserves unexpected fields in _rescued_data; other failures still require monitoring.
  4. The team reviews _rescued_data regularly 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_time and current_timestamp() make each row traceable.
  • Limit batch size with cloudFiles.maxFilesPerTrigger or cloudFiles.maxBytesPerTrigger when a first run over a large backlog overwhelms the cluster.
  • Initial files: cloudFiles.includeExistingFiles is 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 of format("cloudFiles"). The file format belongs in cloudFiles.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 inferColumnTypes or 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_data with 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 rescue mode.

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: addNewColumns with job retries is acceptable if finance accepts evolving columns; if bronze consumers must never change, use rescue and promote fields deliberately. Given that finance wants stable reports, choose rescue and review _rescued_data.
  • Schema: cloudFiles.inferColumnTypes = true plus hints for monetary columns (total DECIMAL(12,2)).
  • Monitoring: count non-null _rescued_data rows per day and alert when they rise; keep _metadata.file_path for traceability.

Key terms

  • Auto Loader: the cloudFiles streaming 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, schemaLocation and 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 inferColumnTypes or hints are used.
  • I can name the four evolution modes and describe the stream behaviour of each.
  • I know what _rescued_data stores.
  • 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