Auto Loader Strategy: Balancing Cloud Notification Costs vs. Directory Listing in Massive Migrations

[Khasim_1]

Hi everyone,

I’m currently architecting a high-volume migration for a platform that generates millions of small files daily across thousands of prefixes. As I evaluate the best ingestion strategy using Databricks Auto Loader, I’m running into an architectural trade-off regarding the discovery mode.

While Directory Listing is simpler to set up, we are concerned about the increasing latency as the number of files in the cloud storage grows. On the other hand, File Notification (using AWS SQS/SNS or Azure Event Grid) offers better scalability but introduces additional cloud infrastructure costs and management overhead.

I’d love to hear from those of you managing petabyte-scale Auto Loader streams:

1. At what point (file count or directory depth) did you find Directory Listing became a bottleneck, forcing a switch to File Notification?
2. For those using File Notification, how are you managing the cost and "orphaned" notification events during massive historical backfills?
3. Have you found a "hybrid" approach—using Directory Listing for the initial backfill and then switching to File Notification for the incremental live stream?

[Islam_hoti · Accepted solution]

Hi @Khasim_1 

Worth flagging that the tradeoff you describe is the older one. There is now a third option that removes the management overhead: enable file events on the Unity Catalog external location and set cloudFiles.useManagedFileEvents. Databricks then runs one shared notification service per external location instead of a queue per stream, so you are not provisioning SQS or SNS yourself. Needs DBR 14.3 LTS or above, works on S3, ADLS and GCS, not on Azure Blob Storage. Databricks now recommends this over directory listing for most workloads, so at your scale I would not treat it as a close call.

On your questions.

There is no published file count threshold, and listing cost scales with total objects under the path rather than with new arrivals, so the same directory keeps getting slower even at a flat arrival rate. The signal to watch is listing time per microbatch growing while input rate does not. Lexically ordered filenames help a lot here. Do not build around incremental listing, it is deprecated.

For missed or dropped events, the mechanism is cloudFiles.backfillInterval, which triggers an asynchronous full listing at a set interval as a safety net. Worth setting in production regardless of mode.

The hybrid works. You can switch discovery modes across stream restarts and keep exactly once guarantees. One gotcha for your design: changing the input path without a new checkpoint is supported in directory listing mode from DBR 11.3 LTS, but not in file notification mode, where files already present in the new path may never be ingested. With thousands of prefixes, settle the path strategy before switching.

Separately, millions of small files per day is expensive to ingest whatever mode you pick, since per file overhead dominates. If the producer can batch into larger objects, that will help more than any Auto Loader tuning.

[Satyasai]

In the context of enterprise-grade cloud migrations (think legacy databases or petabyte-scale data lakes ingestion into Databricks Delta Lake), the industry standard approach involves splitting the ingestion stack into 2:

History (Initial Bulk Load) and CDC (Change Data Capture / Continuous Stream) with both eventually writing into the same target table(s). This allows to treat the initial data import and ongoing changes as separate but strongly related operations.

Mechanics

Source Export: Point in time dump of the source database/lake into parquet or csv files in cloud storage

Ingestion Engine: Auto Loader in Directory Listing Mode + Trigger.AvailableNow

Target Write: Copy the raw parquet files into either Bronze layer Delta table in append mode or directly into Silver via bulk Insert Into

Phase 2: CDC Ingestion (Incremental Stream)

The CDC stream captures all inserts, updates and deletes (CREATE, UPDATE, DELETE) that occurred in the source system after the snapshot moment in time ($T_{\text{snapshot}}$)

Mechanics

Source Capture: Debezium / AWS DMS / Qlik etc. reading database WAL logs and writing individual small JSON/AVRO files into landing/cloud storage

Ingestion Engine: Auto Loader in File Notification Mode (cloudFiles.useNotifications = true OR

cloudFiles.useManagedFileEvents = true)

State Resolution (Silver): Delta Lake MERGE INTO or Lakeflow APPLY CHANGES INTO to de-duplicate and keep only the latest record version per PK