Skip to content
Tutorials Center
My learning

Topics

Lesson 31 of 36 ยท Streaming and Production

Auto Loader: Incremental File Ingestion

Ingest new files incrementally with Databricks Auto Loader: cloudFiles format, schema inference and evolution, rescued data, file notification, and a runnable stand-in.

Data often arrives as files: partners drop CSVs, devices write JSON, applications export logs into cloud storage. Reading the whole folder every night gets slower as it grows. Auto Loader reads only the files that are new since last time, remembers which ones it has seen, and copes when the file layout changes.

Databricks cannot run on this site. Auto Loader code is read-only; the runnable stand-ins reproduce file tracking and schema evolution in plain Python.

The basic pattern

Auto Loader is a Structured Streaming source called cloudFiles (see Structured Streaming Basics):

Python
raw = (spark.readStream
       .format("cloudFiles")
       .option("cloudFiles.format", "json")
       .option("cloudFiles.schemaLocation", "/Volumes/prod/sales/chk/orders_schema")
       .load("/Volumes/prod/sales/raw/orders/"))

(raw.writeStream
    .option("checkpointLocation", "/Volumes/prod/sales/chk/orders")
    .trigger(availableNow=True)
    .toTable("prod.bronze.orders"))
  • cloudFiles.format is the file type (json, csv, parquet, avro, text, binaryFile, ...).
  • schemaLocation is where Auto Loader keeps the inferred schema and its history.
  • The checkpoint tracks which files have been processed, so each file is ingested exactly once, even across restarts.
  • availableNow makes the stream behave like a scheduled batch: process whatever new files exist, then stop.

How new files are found

Mode How it works Best for
Directory listing (default) Lists the folder, using incremental listing to avoid re-scanning Small to medium folders; simplest setup
File notification Uses cloud queue and event services (SQS, Event Grid, Pub/Sub) set up for you Millions of files, very frequent arrivals

Enable notifications with .option("cloudFiles.useNotifications", "true") (needs cloud permissions). File-arrival job triggers (see Jobs and Schedules) can start the run when something lands.

Schema inference and evolution

Without a schema, Auto Loader samples files and infers one, storing it in schemaLocation. JSON and CSV columns default to strings unless you set cloudFiles.inferColumnTypes to true, because guessing types from a sample can be wrong. You can give hints for the columns you know:

Python
.option("cloudFiles.inferColumnTypes", "true")
.option("cloudFiles.schemaHints", "order_id BIGINT, amount DECIMAL(12,2)")

When a new file has an extra column, the behaviour depends on cloudFiles.schemaEvolutionMode:

Mode Effect
addNewColumns (default) Stream stops once, schema is updated with the new column, and a restart picks it up
rescue Schema stays fixed; unexpected data goes to _rescued_data
failOnNewColumns Fail until you change the schema yourself
none Ignore new columns (use with a schema)

The rescued data column _rescued_data holds, as JSON, any value that did not fit the schema: extra columns, wrong types, mismatched case. Nothing is silently lost, and you can inspect it later.

Stand-in: tracking files and rescuing data

This is not Auto Loader. It uses a "seen files" set (the checkpoint) and a fixed schema with a rescue column, so you can see all three behaviours: incremental, exactly-once, and rescued data.

Python Stand-in: incremental ingest with a rescue column

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Adding file metadata

Keep provenance: the _metadata column provides file_path, file_name, file_size and file_modification_time for each row.

Python
bronze = raw.select("*", "_metadata.file_path", "_metadata.file_modification_time")

Add an ingested_at timestamp as well, so you can tell load time from event time.

Handling bad and duplicate data

Bronze tables should store data as received and be forgiving. Quality rules belong in the silver step. Two common safeguards:

  • Duplicates (the same file delivered twice under different names) are removed in silver with a key such as order_id using MERGE or dropDuplicates.
  • Corrupt files can be quarantined with cloudFiles.ignoreCorruptFiles or by reading permissive modes and filtering rows where _rescued_data is not null.
Python Stand-in: quarantine rows with rescued data

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Good practice

  • Land raw files in a volume and ingest from the volume path (see Volumes and External Locations).
  • One checkpoint and one schema location per target table.
  • Prefer availableNow triggers on a schedule over an always-on stream unless you need seconds-level freshness.
  • Store raw data as strings or with a rescue column at bronze; type it in silver.
  • Use cloudFiles.maxFilesPerTrigger or maxBytesPerTrigger to limit batch size on huge backfills.
  • Auto Loader can also be used in SQL (read_files) and in declarative pipelines.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does Auto Loader guarantee for files it has ingested?

    The checkpoint remembers which files were processed.
  2. 2. What is the _rescued_data column for?

    Unexpected columns and wrong types are saved there as JSON.
  3. 3. Which source format name do you pass to readStream?

    Auto Loader is the cloudFiles streaming source.
  4. 4. When are file notifications better than directory listing?

    Notifications avoid listing huge directories.

Summary

  • Auto Loader (cloudFiles) ingests new files incrementally and exactly once.
  • Schema inference, hints and evolution modes handle changing files; _rescued_data keeps odd values.
  • Use availableNow to run it as a cheap scheduled job.
  • Keep bronze raw, quarantine bad rows, deduplicate in silver.

Next: Declarative Pipelines