Databricks Auto Loader: Complete Guide to Automated Data Ingestion

Auto Loader is a Databricks-native ingestion feature that automatically detects files arriving in cloud storage (S3, ADLS, GCS) and ingests them incrementally into Delta Lake tables. Under the hood it is built on top of Structured Streaming and runs as a data source format called cloudFiles. Because it tracks processed files via checkpoints, the same file is never ingested twice, and it scales efficiently to storage with millions of files.

Auto Loader makes up 10-15% of the Data Engineer Associate exam, making it one of the most frequently tested topics. This article walks through the basic syntax, file detection modes, schema inference and evolution, the comparison with COPY INTO, and checkpoint design, all with code examples.

Auto Loader Basic Syntax (cloudFiles)

Auto Loader is started with spark.readStream.format("cloudFiles"). The key point is that you use the Structured Streaming readStream API rather than the standard spark.read. The two required options are cloudFiles.format (the file format) and cloudFiles.schemaLocation (where the inferred schema is stored).

# Auto Loader basic syntax (PySpark)
df = (spark.readStream
    .format("cloudFiles")
    .option("cloudFiles.format", "json")
    .option("cloudFiles.schemaLocation", "/checkpoint/schema/events")
    .load("s3://my-bucket/landing/events/")
)

# Write to a Delta Table
query = (df.writeStream
    .format("delta")
    .option("checkpointLocation", "/checkpoint/bronze/events")
    .option("mergeSchema", "true")
    .trigger(availableNow=True)
    .toTable("bronze.raw_events")
)

The DataFrame returned by readStream supports the same transformations as a regular DataFrame (filter, withColumn, select, and so on). When writing to a Delta Table with writeStream, the checkpointLocation option is required. The checkpoint records which files have already been processed, so when the job restarts it resumes exactly where it left off.

Common cloudFiles Options

File Detection Modes: Directory Listing vs File Notification

Auto Loader has two modes for detecting new files. The default is directory listing: on every stream trigger it lists the cloud storage directory and compares the result against the files recorded in the checkpoint to identify new ones. In file notification mode, it subscribes to the cloud provider's event notification service and receives file arrival events through a queue.

# Directory listing (default)
df = (spark.readStream
    .format("cloudFiles")
    .option("cloudFiles.format", "json")
    .option("cloudFiles.schemaLocation", "/checkpoint/schema")
    .load("s3://bucket/landing/")
)

# Switch to file notification mode
df = (spark.readStream
    .format("cloudFiles")
    .option("cloudFiles.format", "json")
    .option("cloudFiles.schemaLocation", "/checkpoint/schema")
    .option("cloudFiles.useNotifications", "true")
    .load("s3://bucket/landing/")
)

Switching from directory listing to file notification mode takes a single extra option line. The existing checkpoint carries over, so no files are reprocessed during the switch. However, in file notification mode Auto Loader automatically provisions cloud resources (an SQS queue and so on), so the cluster or service principal must have the appropriate IAM permissions.

Schema Inference and Schema Evolution

Auto Loader performs automatic schema inference for JSON and CSV files. On the first run it samples files to determine the schema and persists it under _schemas/ inside the directory specified by cloudFiles.schemaLocation. Subsequent runs use the stored schema, so there is no per-run inference cost.

schemaHints: Overriding Inferred Types

Schema inference may infer JSON numeric fields as Long or Double when you actually want them as Timestamp or Decimal for business reasons. cloudFiles.schemaHints lets you override the inferred type for specific columns.

# Override inferred types with schemaHints
df = (spark.readStream
    .format("cloudFiles")
    .option("cloudFiles.format", "json")
    .option("cloudFiles.schemaLocation", "/checkpoint/schema")
    .option("cloudFiles.schemaHints",
            "event_time TIMESTAMP, amount DECIMAL(10,2), user_id STRING")
    .load("s3://bucket/landing/events/")
)

Schema Evolution: Handling New Columns

When the data source gains new columns, Auto Loader's schema evolution feature handles it. You control the behavior with cloudFiles.schemaEvolutionMode.

When using addNewColumns mode, also specify .option("mergeSchema", "true") on the writeStream so that new columns are added to the Delta Table schema automatically. Without it, the write fails with a schema mismatch error.

# Combining addNewColumns with mergeSchema
df = (spark.readStream
    .format("cloudFiles")
    .option("cloudFiles.format", "json")
    .option("cloudFiles.schemaLocation", "/checkpoint/schema")
    .option("cloudFiles.schemaEvolutionMode", "addNewColumns")
    .load("s3://bucket/landing/")
)

query = (df.writeStream
    .option("checkpointLocation", "/checkpoint/bronze")
    .option("mergeSchema", "true")
    .trigger(availableNow=True)
    .toTable("bronze.raw_events")
)

Rescued Data Column: Capturing Schema Mismatches

Auto Loader produces a _rescued_data column by default. When data arrives that does not match the schema (type mismatches, unknown columns, malformed records), it is not discarded; instead, it is stored as a JSON string in the _rescued_data column. Fields that parsed correctly land in the regular columns and the rest is captured in _rescued_data, so you avoid data loss and can investigate quality issues after the fact.

# Inspect _rescued_data
df.select("user_id", "event_type", "amount", "_rescued_data").show(truncate=False)

Example output:

# Monitor the number of schema-mismatched records
bad_count = df.filter("_rescued_data IS NOT NULL").count()
print(f"Schema-mismatched record count: {bad_count}")

In the Bronze layer of the Medallion Architecture, the standard practice is to store _rescued_data alongside the data, then route records where _rescued_data IS NOT NULL into a quarantine table when transforming to Silver. You can rename the column via the rescuedDataColumn option, or disable it by setting it to false.

Auto Loader vs COPY INTO: Comparison Table

Auto Loader and COPY INTO both ingest files from cloud storage into Delta Tables, but they have different design philosophies and target scenarios. The DEA exam frequently asks which one to choose for a given scenario.

# COPY INTO basic syntax (SQL)
COPY INTO bronze.raw_events
FROM 's3://bucket/landing/events/'
FILEFORMAT = JSON
FORMAT_OPTIONS ('mergeSchema' = 'true')
COPY_OPTIONS ('mergeSchema' = 'true');

-- Compare: with Auto Loader (PySpark)
-- spark.readStream.format("cloudFiles")... -> writeStream.toTable("bronze.raw_events")

The decision criteria are simple. Ask: Do files arrive continuously? Will the file count exceed a few thousand? Could the schema change? If any answer is yes, Auto Loader is the right choice; if all are no, COPY INTO is appropriate. When in doubt, pick Auto Loader — it will continue to fit even if the workload grows later.

Supported File Formats

Parquet, Avro, and ORC carry their schema inside the file, so Auto Loader uses the file's schema directly rather than running schema inference. However, when files with differing schemas are mixed, schema evolution still kicks in via cloudFiles.schemaEvolutionMode.

Checkpoints and the Exactly-once Guarantee

Auto Loader uses Structured Streaming's checkpoint mechanism to persist the list of processed files and stream offset information to cloud storage. When a job fails it resumes from the checkpoint, so every file is processed exactly once (the exactly-once guarantee). This guarantee is realized in combination with the Delta Lake transaction log.

• One checkpoint directory per stream. Sharing a directory across streams causes offset conflicts.
• Deleting the checkpoint reprocesses every file. The processing history is lost and every file in the source directory is read again.
• Store checkpoints in cloud storage (dbfs:/, s3://, abfss://, and so on). On local disk they are lost when a node fails.
• Keep schemaLocation and checkpointLocation in separate directories. schemaLocation stores the inferred schema; checkpointLocation stores the stream's processing state. They serve different purposes.

# Example checkpoint layout
df = (spark.readStream
    .format("cloudFiles")
    .option("cloudFiles.format", "json")
    .option("cloudFiles.schemaLocation",
            "dbfs:/checkpoint/autoloader/events/schema")
    .load("s3://bucket/landing/events/")
)

query = (df.writeStream
    .option("checkpointLocation",
            "dbfs:/checkpoint/autoloader/events/stream")
    .trigger(availableNow=True)
    .toTable("bronze.raw_events")
)

Trigger Modes and Use in the Medallion Architecture

Because Auto Loader is built on Structured Streaming, you can choose the trigger mode. In practice the most common pattern is to run trigger(availableNow=True) on a schedule via Databricks Workflows. It processes all already-arrived files across multiple micro-batches and then stops the job, eliminating the need for an always-on cluster and making it very cost-efficient.

# availableNow: process all arrived files, then stop (recommended)
query = (df.writeStream
    .option("checkpointLocation", "/checkpoint/bronze")
    .trigger(availableNow=True)
    .toTable("bronze.events")
)

# processingTime: continuous processing on a fixed interval (when real-time is required)
query = (df.writeStream
    .option("checkpointLocation", "/checkpoint/bronze")
    .trigger(processingTime="30 seconds")
    .toTable("bronze.events")
)

# once=True: deprecated (loads all data into a single batch with OOM risk)
# -> Databricks recommends migrating to availableNow=True

In the Medallion Architecture (Bronze / Silver / Gold three-layer model), Auto Loader is used for ingestion into the Bronze layer. It handles Landing Zone (raw files) → Bronze Table (raw data stored in Delta format, including _rescued_data), while transformations from Silver onward are done with Delta Live Tables or MERGE via foreachBatch.

• Landing Zone → Bronze: Incremental ingestion via Auto Loader (cloudFiles), storing _rescued_data alongside the data
• Bronze → Silver: Cleanse and deduplicate via Delta Streaming, DLT, or foreachBatch + MERGE
• Silver → Gold: Compute business metrics via batch aggregation, materialized views, or SQL

What the Exam Tests (DEA / DEP)

Auto Loader makes up 10-15% of the DEA exam and is one of the most frequently tested topics. The DEP exam also tests it as a Bronze-layer ingestion pattern. Make sure the following points are crystal clear.

• Choosing between Auto Loader and COPY INTO: Many files arriving continuously → Auto Loader. Small, one-off loads → COPY INTO. If the scenario says "files arrive hourly and the count reaches millions," Auto Loader is the answer.
• Directory listing vs file notification: The default is directory listing. At scale, use file notification mode (useNotifications=true). Switching is a single option change — no checkpoint rebuild required.
• Default schema evolution behavior: For JSON/CSV the default is addNewColumns (stop the stream on a new column, then add it automatically on restart). Without mergeSchema=true on the writeStream, the write fails.
• _rescued_data column: Captures type mismatches and unknown columns as a JSON string. Enabled by default. Useful for Bronze-layer quality checks.
• trigger(availableNow=True): Process all arrived files across multiple batches, then stop. The standard pattern for scheduled Workflows execution. once=True is deprecated (everything into one batch creates OOM risk).
• Role of cloudFiles.schemaLocation: Where the inferred schema is persisted. Keep it in a separate directory from checkpointLocation.
• Impact of deleting the checkpoint: The processing history is lost and every file in the source is reprocessed.

Check Your Understanding

Frequently Asked Questions