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.