Folder structure for Autoloader and Declarative Pipelines

[batch_bender]

We have six years of Parquet files organised under yyyy/mm/dd/, with around 100 table files in each daily folder. We want to process these 100 tables using Auto Loader in a Declarative Pipeline with the AUTO CDC API.

If each table reads the same root path with a filename wildcard, will Auto Loader independently discover all files for every table before applying the filter? Could this cause significant discovery overhead?

What folder structure and Auto Loader configuration are recommended as best practice for this scenario?

[balajij8]

The concerned about the discovery overhead is valid. If all 100 tables are configured to read from the same root path using a wildcard or pathGlobFilter, Auto Loader will independently discover every single file in that directory structure before applying the pattern filter. Auto Loader maintains an internal Rocks DB checkpoint to track state per stream, you would be forcing it to perform 100 redundant, full-directory tree listings across six years of partitioned data. It generally will severely bottleneck the pipeline initialization and create massive API listing costs on the cloud storage account.

You can organize the folders - by table name first and then partition by date if feasible. Storage path should look like below

s3://bucket/database_name/table_name/yyyy/mm/dd/

It allows each pipelines to point directly to its own dedicated root. The stream scans only the directory tree it needs to process isolating the state and completely dropping the need for wildcards.

If restructuring six years of historical data is not feasible, your alternative to avoid that directory listing overhead is to enable File Notification mode. By switching to it, Auto Loader sets up cloud-native event subscriptions (like AWS SQS, Azure Event Grid) to track file arrivals as they happen instead of expensive directory listings. It requires elevated cloud infrastructure permissions to deploy and the pipeline will still have to perform one initial massive directory listing to backfill the historical files that existed before the queues were created.

[GabFernandes]

Hi @batch_bender ,

Yes, pointing Auto Loader to the root path with a filename wildcard for every table will cause significant file discovery overhead, as each stream independently crawls the entire directory tree (yyyy/mm/dd/).

Best Practice Recommendations:

Enable File Notification: Instead of directory listing, configure Auto Loader with .option("cloudFiles.useNotifications", "true"). This uses cloud event queues (SQS/Event Grid) to send new file paths directly to the streams, avoiding directory scanning altogether.

Re-structure Landing Zone (Ideal): If possible, write files by table first (table_name/yyyy/mm/dd/). This reduces discovery cost to $O(1)$ per table stream.

Use Path Globbing Filter: If you must keep directory listing on the current layout, use .option("cloudFiles.globResourcePath", "*/*/*/*_tableName.parquet") to restrict the search scope efficiently.

Here is the official documentation on Auto Loader directory listing vs file notifications:

https://docs.databricks.com/en/ingestion/auto-loader/file-detection-modes.html

If my answer was helpful, please consider marking it as accepted solution!