3.2 Incremental File Ingestion with Auto Loader

3.2 Incremental File Ingestion with Auto Loader

As data volumes scale into millions of files arriving continuously in cloud object storage, standard batch loading patterns like file listing become a major performance bottleneck. Databricks introduced Auto Loader (configured using the cloudFiles format in Apache Spark Structured Streaming and Databricks SQL) to solve the challenges of continuous, high-scale file ingestion. Auto Loader provides an optimized, scalable, and fault-tolerant mechanism to incrementally process billions of files arriving in cloud storage landing zones.

---

Introduction to Auto Loader (cloudFiles)

In traditional file ingestion engines, processing new files requires listing all objects in a directory and comparing them against previously processed file lists. Cloud storage services (such as AWS S3, Azure ADLS Gen2, and Google Cloud Storage) charge for API directory listing requests (GET / LIST), and performance degrades exponentially as the directory file count grows into hundreds of thousands or millions.

Auto Loader bypasses directory listing bottlenecks by automatically discovering new files as they arrive in cloud object storage. Key architectural benefits include:

• Low latency: Processes incoming files within seconds of arrival.
• Cost reduction: Eliminates expensive, repeated directory listing API requests.
• Scalability: Seamlessly handles directories containing millions to billions of files.
• Fault tolerance: Guarantees exactly-once processing semantics across pipeline restarts.

---

Auto Loader Architecture & Processing Modes

Auto Loader operates in two distinct file discovery modes depending on folder scale and cloud privileges:

1. Directory Listing Mode

Directory listing mode is the default discovery mode. Auto Loader identifies new files by efficiently crawling the file directory and caching file timestamps using an embedded RocksDB key-value state store saved within the checkpoint directory. Unlike standard Spark file listing, Auto Loader uses parallel lexical listing and timestamp-based filtering to discover new files quickly. Directory listing mode requires no special cloud permissions beyond read access to the storage path and supports scaling up to millions of files.

2. File Notification Mode

For directories containing tens or hundreds of millions of files, directory listing becomes inefficient. File notification mode automatically sets up and manages cloud notification services—such as AWS SNS/SQS, Azure Event Grid & Queue Storage, or GCP Pub/Sub.

Cloud Object Storage              Cloud Event Queue              Auto Loader Engine
├── File Uploaded (S3/ADLS) ---> [Notification Queue] ---> [Reads Event & Ingests File]

When a new file lands in cloud storage, the cloud provider generates an OBJECT_CREATED event sent directly to an asynchronous queue. Auto Loader listens to the queue, fetches the target file path, and ingests the file immediately without scanning the storage directory.

---

Ingestion Checkpointing & Exactly-Once Semantics

Auto Loader guarantees exactly-once processing by pairing file discovery with Apache Spark Structured Streaming checkpointing.

The checkpointLocation is a required configuration option pointing to a folder in cloud storage or a Unity Catalog Volume. The checkpoint directory stores state logs, offset metadata, and RocksDB state files.

If an ingestion cluster crashes or a scheduled job is restarted, Auto Loader inspects the checkpointLocation metadata to resume ingestion precisely from the last committed offset, ensuring no files are reprocessed or skipped.

---

Automatic Schema Inference and Schema Evolution

Source files originating from external applications often change over time—columns are added, renamed, or assigned altered data types. Auto Loader handles schema drift automatically through schema inference and schema evolution.

Schema Inference

When Auto Loader initializes, it samples a subset of landing files to infer column names and data types automatically. Auto Loader persists this inferred schema into a designated path specified by cloudFiles.schemaLocation.

Schema Evolution Modes

Analysts control how Auto Loader responds when encountering files with new columns using the cloudFiles.schemaEvolutionMode setting:

The _rescued_data Column Pattern

By default, Auto Loader automatically appends a hidden column named _rescued_data to the target Delta table. If a source record contains an unparsed field, a missing required column, or a data type mismatch (e.g., a string value inside an integer field), Auto Loader does not drop the record or fail the stream. Instead, it serializes the problematic fields into JSON format and stores them inside _rescued_data.

-- Querying rescued data to inspect ingestion anomalies
SELECT 
  transaction_id, 
  user_id, 
  _rescued_data 
FROM catalog.sales.raw_transactions 
WHERE _rescued_data IS NOT NULL;

---

Auto Loader Syntax in SQL and PySpark

Auto Loader can be configured using Databricks SQL streaming queries (read_files) or PySpark Structured Streaming (cloudFiles).

Streaming SQL (read_files) Syntax

CREATE STREAMING TABLE catalog_name.iot_schema.raw_sensor_data AS
SELECT * 
FROM STREAM read_files(
  's3://company-landing-bucket/iot_telemetry/',
  format => 'json',
  cloudFiles.schemaLocation => '/Volumes/catalog_name/iot_schema/checkpoints/schema/',
  cloudFiles.schemaEvolutionMode => 'addNewColumns'
);

PySpark Structured Streaming Syntax

# Auto Loader ingestion in PySpark
df = (spark.readStream
    .format("cloudFiles")
    .option("cloudFiles.format", "json")
    .option("cloudFiles.schemaLocation", "/mnt/telemetry/_schema_path")
    .option("cloudFiles.inferColumnTypes", "true")
    .option("cloudFiles.schemaEvolutionMode", "addNewColumns")
    .load("s3://company-landing-bucket/iot_telemetry/"))

(df.writeStream
    .format("delta")
    .option("checkpointLocation", "/mnt/telemetry/_checkpoint_path")
    .outputMode("append")
    .toTable("catalog_name.iot_schema.raw_sensor_data"))

---

Auto Loader vs. COPY INTO Selection Criteria

Data analysts must choose between COPY INTO and Auto Loader based on data scale and operational requirements:

• Use COPY INTO when dataset sizes are small to moderate (thousands of files), data arrives in scheduled batch batches, and execution is managed purely through SQL scripts without streaming.
• Use Auto Loader when datasets contain millions of files, files arrive continuously, schema drift requires automatic evolution or rescued data handling, or high-scale file notification queues are necessary.