Hi DBStudent,

You’re absolutely right—coalescing during write won’t help with the initial bottleneck, since the issue is really with the read side, where Spark has to list and open 110k small files. If you can’t pre-compact them before reading, then one thing that could help is using parallelism at the file level by increasing spark.sql.files.maxPartitionBytes to a lower value (e.g., 8MB) and also tweaking spark.sql.files.openCostInBytes to force Spark to parallelize more aggressively. Another option is to enable Spark’s metadata pruning by setting spark.databricks.optimize.maxFileListingParallelism to a higher value. Also, if you haven’t already, try increasing the number of executors (worker nodes) instead of just beefing up cores per node—this spreads out the file scanning better. If you have a list of known table paths, consider parallelizing the load using Workflows or even multiple jobs, so each job picks up a batch of tables. I can help draft a setup using Workflows + table batches if that sounds useful!

Regards,

Brahma