Running Auto Loader Pipelines in Production

1.Triggered, continuous, or AvailableNow

For most production ingestion, Databricks recommends running Auto Loader inside Lakeflow pipelines. A pipeline runs in one of two modes. In triggered mode it processes the data that is available and then stops. In continuous mode it keeps tables fresh as new data arrives. Choose the mode deliberately, because it decides whether you pay for a cluster that never shuts down.

Triggered mode is the right default for most pipelines. Keep continuous mode for genuine sub-minute latency needs, because it needs an always-on cluster. When latency doesn't matter much and compute cost is the priority, there is a third option outside pipelines. You can use Lakeflow Jobs to schedule Auto Loader as a batch job with Trigger.AvailableNow. Within Lakeflow Jobs, a file arrival trigger can start those batch jobs to cut the delay between a file landing and its processing.

Sources12

2.File discovery modes and source cleanup

Auto Loader's main costs are compute and file discovery. In directory listing mode, discovery costs you LIST operations on storage. In notification mode it costs subscription-service and queue API requests. Continuous triggers such as Trigger.ProcessingTime are especially expensive with directory listing, because Auto Loader keeps listing the whole directory. If you need continuous triggers, choose the mode by latency:

As files pile up, storage costs rise and discovery slows down. cloudFiles.cleanSource (Databricks Runtime 16.4 LTS and above) archives or deletes files after they are processed. Use MOVE when you have to keep files for compliance. With MOVE, the source and destination must be in the same external location, volume or DBFS mount. Use DELETE to cut storage cost, and enable bucket versioning so deletes act as soft deletes. Inside a pipeline, the same options pass through read_files:

CREATE OR REFRESH STREAMING TABLE raw_events
AS SELECT * FROM STREAM read_files(
  's3://my-bucket/landing/',
  format => 'json',
  cleanSource => 'MOVE',
  `cleanSource.moveDestination` => 's3://my-bucket/archive/landing/',
  `cleanSource.retentionDuration` => '14 days'
);

df = (spark.readStream.format("cloudFiles")
  .option("cloudFiles.format", "json")
  .option("cloudFiles.cleanSource", "MOVE")
  .option("cloudFiles.cleanSource.moveDestination", archive_path)
  .option("cloudFiles.cleanSource.retentionDuration", "14 days")
  .option("cloudFiles.schemaLocation", checkpoint)
  .load("s3://my-bucket/landing/")
)

# Step 2: Write to a Delta table.
(df.writeStream
  .option("checkpointLocation", checkpoint)
  .trigger( ? =True)
  .toTable("my_catalog.my_schema.raw_events")
)

Sources1

3.Monitoring the stream and handling out-of-order files

A production stream needs to be observable. Auto Loader exposes the files it has discovered through the cloud_files_state SQL function (Databricks Runtime 11.3 LTS and above). You pass it the stream's checkpoint location:

SELECT * FROM cloud_files_state('path/to/checkpoint');

For ongoing monitoring, Databricks recommends the Streaming Query Listener. Auto Loader reports backlog metrics to it on every batch. In file notification mode on Databricks Runtime 10.4 LTS and above, the metrics also include approximateQueueSize on AWS and Azure.

{
  "sources": [
    {
      "description": "CloudFilesSource[/path/to/source]",
      "metrics": {
        "numFilesOutstanding": "238",
        "numBytesOutstanding": "163939124006"
      }
    }
  ]
}

File order is a separate design problem. Neither discovery mode guarantees the order in which files are found or processed. In a Lakeflow pipeline that combines Auto Loader with AUTO CDC, set pipelines.cdc.tombstoneGCThresholdInSeconds on the target streaming table. Make it longer than the maximum expected delay so deleted records are kept long enough to handle late files. The default is two days. Without pipelines, use soft deletes, and compare timestamps before an upsert so stale records don't overwrite newer ones.

AUTO CDC is the pipeline flow built for this problem: it handles out-of-order CDC events and supports both SCD Type 1 and SCD Type 2. You tell it how to order changes with a sequencing column (sequence_by in Python, SEQUENCE BY in SQL) and choose the SCD type with stored_as_scd_type (STORED AS SCD TYPE in SQL), so you don't write manual code for out-of-order events or watermarks.

Sources134

4.The production-readiness checklist

Databricks defines production readiness as a checklist across data quality, reliability, observability, deployment, cost and governance. For a streaming ingestion pipeline, these are the items that matter most:

Two items catch people out. The first is error handling. Lakeflow pipelines rely on surfaced failures to manage retry and recovery, so a protective try/except around transformations actually hides real errors from the event log. The second is expectation actions: use fail for conditions that should halt the run, and drop, ideally with a quarantine table, for records you can safely discard.

An expectation is a SQL boolean constraint on a dataset, and it names what happens when a record fails it: warn, drop the record, or fail the update. In SQL you write it as a constraint such as CONSTRAINT valid_id EXPECT (id IS NOT NULL) ON VIOLATION DROP ROW. In Python, expect_all_or_drop takes a dictionary of named constraints. The tutorial's silver layer uses drop to remove rows with a null ID, an invalid CDC operation, or data Auto Loader could not read correctly (a non-null _rescued_data).

Sources24

Exam traps

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

1. 1.A production streaming pipeline should run in continuous mode.Why is that wrong?

   Triggered mode is the default for most pipelines. Continuous mode is for genuine sub-minute latency needs, because it requires an always-on cluster.

   Covered in Triggered, continuous, or AvailableNow

2. 2.Directory listing is the recommended discovery mode because it is the default.Why is that wrong?

   Directory listing is the default, but Databricks recommends file notification mode using file events for most workloads. Directory listing is especially expensive under continuous triggers.

   Covered in File discovery modes and source cleanup

3. 3.File notification mode processes files in the order they arrive, so it solves out-of-order data.Why is that wrong?

   Neither discovery mode guarantees ordering. Handle late files with tombstone retention (AUTO CDC in pipelines), or with soft deletes and timestamp comparisons.

   Covered in Monitoring the stream and handling out-of-order files

Practise it for real

Run an Auto Loader stream as a cost-efficient batch that archives processed files, then inspect its state.

1. 1.In a notebook, define a cloudFiles read with cloudFiles.format json, cloudFiles.cleanSource MOVE, a cloudFiles.cleanSource.moveDestination in the same external location or volume as the source, cloudFiles.cleanSource.retentionDuration, and cloudFiles.schemaLocation set to your checkpoint path.

   Why: MOVE keeps processed files for auditing and makes later directory listings smaller.

   You should see: The streaming DataFrame is defined without errors. A destination in a different bucket would raise an error.

2. 2.Write the stream with checkpointLocation set to the same checkpoint and .trigger(availableNow=True) to a Unity Catalog table.

   Why: AvailableNow processes the backlog and then stops, so you don't pay for always-on compute.

   You should see: The query processes the available files and terminates, and the target table holds the ingested rows.

3. 3.Run SELECT * FROM cloud_files_state('<your checkpoint path>');

   Why: This shows file-level metadata for the files the stream has discovered.

   You should see: One row per discovered file.

4. 4.Drop a new file into the landing path and run the write again.

   Why: This shows that the checkpoint lets Auto Loader resume and ingest only the new file.

   You should see: Only the new file's rows are added; earlier files aren't re-ingested.