Auto Loader: incremental file ingestion

Auto Loader is the Databricks feature for incrementally ingesting files that land in cloud storage: it remembers which files it has already processed and loads only the new ones. The decision rule for exams: if new files keep arriving in a storage location and each must be loaded exactly once, with the schema allowed to change, use Auto Loader (the cloudFiles source). If the load is a simple scheduled SQL statement over a folder, COPY INTO also works; if it is a database or SaaS source rather than files, the answer is a Lakeflow Connect connector, not Auto Loader.

Choosing between the file-ingestion options

Auto Loader is documented as discovering billions of files efficiently, with near real-time ingestion of millions of files per hour. For production, Databricks recommends running it inside Lakeflow Spark Declarative Pipelines (the product formerly known as Delta Live Tables), which add autoscaling, data-quality expectations, automatic schema evolution and event-log monitoring; see Lakeflow Declarative Pipelines.

Anatomy of an Auto Loader stream

(spark.readStream
    .format("cloudFiles")
    .option("cloudFiles.format", "json")
    .option("cloudFiles.schemaLocation", "/Volumes/main/raw/meta/orders_schema")
    .load("/Volumes/main/raw/landing/orders/")
  .writeStream
    .option("checkpointLocation", "/Volumes/main/raw/meta/orders_chk")
    .option("mergeSchema", "true")
    .trigger(availableNow=True)
    .toTable("main.bronze.orders"))

The same ingestion in SQL, as a streaming table in a pipeline:

CREATE OR REFRESH STREAMING TABLE orders_bronze
AS SELECT * FROM STREAM read_files(
  "/Volumes/main/raw/landing/orders/",
  format => "json");

Four parts to recognise:

• cloudFiles and cloudFiles.format select Auto Loader and the file format of the source.
• The checkpoint location stores stream progress. Auto Loader keeps the metadata of discovered files in RocksDB there, which gives exactly-once processing and lets a failed stream resume where it stopped. Do not put a cloud lifecycle policy on this location: if it deletes checkpoint files, the state is corrupted and the stream must start over.
• cloudFiles.schemaLocation enables schema inference and evolution; the inferred schema is stored under a _schemas folder there, and it may be the same path as the checkpoint.
• trigger(availableNow=True) processes all files that arrived before the query started, then stops. This lets Auto Loader run as a scheduled batch job, which saves compute when low latency is not needed. A continuous stream (no trigger limit) suits low-latency needs. By default each micro-batch is capped at 1,000 files (cloudFiles.maxFilesPerTrigger). The choice between the two is the same latency question as in batch versus streaming.

By default cloudFiles.includeExistingFiles is true, so files already in the folder when the stream first starts are loaded too.

Schema handling: what happens when a column appears

Without a supplied schema, JSON, CSV and XML columns are inferred as strings unless cloudFiles.inferColumnTypes is set to true. Mismatches and unexpected data go to the rescued data column, _rescued_data, a JSON blob that also records the source file path. The evolution mode decides what happens to new columns:

Worked scenario. On Monday the files hold order_id and amount. On Tuesday a file arrives with a new coupon field. With the default, the Tuesday run fails once, the schema location is updated, and the retry loads coupon as a new column; this is why Databricks advises running the stream as a Lakeflow Job so it restarts automatically. With rescue, Tuesday’s run succeeds and coupon is visible only inside _rescued_data. cloudFiles.schemaHints overrides inferred types, for example "amount DECIMAL(10,2)", but only when you do not provide a full schema. The table side of this is the Delta behaviour in Delta Lake explained.

How Auto Loader finds new files

You can switch modes across restarts and keep exactly-once processing. Neither mode guarantees the order in which files are processed, so late or out-of-order arrivals must be tolerated.

Which exams test this

How exam questions are built around Auto Loader

• “Only new files, never reprocess” with a growing folder points to Auto Loader or COPY INTO; the tie-break is scale and schema change (Auto Loader) versus simple scheduled SQL (COPY INTO).
• “The source adds columns without notice” points to schema evolution. The trap is the default addNewColumns, which fails the stream once; the answer is a job that restarts it, or rescue if the stream must never fail.
• “Bad or unexpected values must not be lost” points to _rescued_data.
• “Reduce cost, data needed every hour” points to availableNow triggers on a schedule rather than an always-on stream.
• Source type. A database, SaaS application or message bus is not a files-in-storage problem; look for Lakeflow Connect or a Kafka/Event Hubs source instead. The landing layout often feeds a medallion architecture bronze table.

Practice questions

Go deeper

• What is Auto Loader? and schema inference and evolution (Databricks documentation)
• File detection modes and Auto Loader in production
• Load data with COPY INTO
• Related: Unity Catalog explained for the governed target tables, and the DP-750 study plan.