Auto Loader Batch Ingestion into Unity Catalog Tables

1.How Auto Loader finds new files

In directory listing mode, Auto Loader finds new files by listing the input directory. Apart from access to the data in cloud storage, it needs no permissions, so it is the quickest mode to start. It is also the default. It is still optimised: instead of listing every subdirectory the way the plain Spark file source does, it asks storage for a flattened listing.

File notification mode uses your cloud's notification and queue services, and it comes in two forms. In *classic* mode, you set cloudFiles.useNotifications to true. Auto Loader then creates a separate notification service and queue for each stream, such as SNS plus SQS on S3, or Event Grid plus Queue Storage on ADLS. This requires elevated permissions. With *file events*, which Databricks recommends, every stream reading from a Unity Catalog external location shares one queue. Databricks can set up the subscriptions without you passing extra credentials to Auto Loader. File events need Databricks Runtime 14.3 LTS or above, and you turn them on with cloudFiles.useManagedFileEvents. Databricks also recommends giving Auto Loader Unity Catalog volume paths, with a separate external volume for each subpath, instead of raw cloud URLs.

You can switch between modes across restarts without losing exactly-once guarantees. No mode guarantees the order in which files are processed.

Sources12

2.Running Auto Loader as a scheduled batch

Auto Loader is a streaming source, but it does not have to run continuously. With the availableNow trigger, a run processes every file that has arrived since the last run and then stops. That makes it fit a job scheduled hourly or nightly. Exactly-once processing still holds between runs. Auto Loader stores the metadata of the files it has discovered in a RocksDB key-value store in the checkpoint location, and each new run continues from there. You do not manage any of that state yourself.

checkpoint_path = "s3://dev-bucket/_checkpoint/dev_table"

(spark.readStream
  .format("cloudFiles")
  .option("cloudFiles.format", "json")
  .option("cloudFiles.schemaLocation", checkpoint_path)
  .load("s3://autoloader-source/json-data")
  .writeStream
  .option("checkpointLocation", checkpoint_path)
  .trigger( ? =True)
  .toTable("dev_catalog.dev_database.dev_table"))

The choice of detection mode matters more for batch runs. With continuous triggers, directory listing keeps listing the whole directory and can drive up LIST API costs, which is one reason Databricks steers you toward file events. File events have their own catch for infrequent runs. The service caches recently created files, and if Auto Loader runs too rarely the cache expires and Auto Loader falls back to directory listing. Run it at least once every seven days to avoid that.

Directory listing has a batch-friendly feature of its own. On Databricks Runtime 11.3 LTS and above, you can point a stream at a different input directory without creating a new checkpoint. A daily job can therefore read /YYYYMMDD/ folders one day at a time while keeping a single ingestion history. File notification mode does not support changing the path.

Sources3425

3.Landing data in Unity Catalog managed and external tables

Auto Loader can ingest from external locations governed by Unity Catalog. On Databricks Runtime 11.3 LTS and above it runs on both standard and dedicated access modes. Unity Catalog assumes that it governs every storage path a workload touches, and that includes Auto Loader's own state. The checkpoint and schema-evolution files should therefore live in Unity Catalog-managed storage. They must not sit inside the table directory, because Unity Catalog does not allow that nesting.

The documented managed-table example assumes the user has these grants, plus ownership of the target table:

For a managed table, you write with .toTable("catalog.schema.table"), as in the batch example above, and Unity Catalog decides where the data is stored. Use an external table when the data has to stay at a path you choose, for example so that non-Databricks clients can read it. First register the table with CREATE TABLE ... LOCATION, then stream into it by name. The table location must be inside an external location where you have CREATE EXTERNAL TABLE. The checkpoint must also be in a Unity Catalog-managed external location, on a path separate from the table data. If you define the table with read_files in a CREATE STREAMING TABLE statement inside Lakeflow pipelines, the checkpoint and schema locations are managed for you.

checkpoint_path = "s3://dev-bucket/_checkpoint/dev_table"
table_path = "s3://dev-bucket/external/dev_table"

# One-time: register the external table in UC.
spark.sql(f"""
  CREATE TABLE IF NOT EXISTS dev_catalog.dev_database.dev_table
  USING DELTA
  LOCATION '{table_path}'
""")

Sources6

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Auto Loader uses file notification mode by default because it is the recommended mode.Why is that wrong?

   Directory listing is the default. File notification with file events is recommended, but you have to enable it with cloudFiles.useManagedFileEvents (or cloudFiles.useNotifications for classic mode).

   Covered in How Auto Loader finds new files

2. 2.Putting the checkpoint and schema location inside the target table's directory keeps everything tidy in Unity Catalog.Why is that wrong?

   Unity Catalog does not allow checkpoint or schema files under the table directory. Keep them in a separate path in Unity Catalog-managed storage.

   Covered in Landing data in Unity Catalog managed and external tables

Practise it for real

Land JSON files into a Unity Catalog managed table with a scheduled Auto Loader batch run, and watch schema evolution happen between runs

1. 1.Make sure you have READ FILES on the source external location and READ FILES, WRITE FILES and CREATE TABLE on the location that will hold the checkpoint.

   Why: Unity Catalog governs both the source path and Auto Loader's own state.

   You should see: Both external locations are visible to you and you have the grants listed in the table above.

2. 2.Run the Python stream with cloudFiles.schemaLocation and checkpointLocation set to the same s3://dev-bucket/_checkpoint path, .trigger(availableNow=True), and .toTable on a three-level table name.

   Why: availableNow makes the stream process the backlog and exit, which is how it behaves as a batch.

   You should see: The run finishes on its own, and the table contains the existing files, with JSON columns inferred as strings.

3. 3.Drop a new JSON file into the source folder that adds a field the earlier files did not have, then run the same code again.

   Why: In the default addNewColumns mode, this exercises the evolution path.

   You should see: The run stops with an UnknownFieldException, and the schema location now contains the new column.

4. 4.Run the code a third time.

   Why: A restart picks up the updated schema from the schema location.

   You should see: The run succeeds, and the table has the new column, populated for the new file.