I filtered just one-third of a 400 million row table and expected the query to fly. Instead, it took 11 minutes just to scan the data.
I was running a fairly simple filter on a 400 million row orders table which was around 7.34 GB. Two years out of six, roughly a third of the data. In my head, this was going to be quick.
I opened the Spark UI to confirm it, and what I saw made me stop and read it twice. Scan time, 11.2 minutes. Number of files read, 29,200. For one filter.
My first instinct was the one everyone has. Something must be wrong with the cluster. So I checked. Compute was fine. Then I checked the query itself. Nothing fancy, nothing had changed from earlier runs that worked perfectly well.
So if it was not the compute and it was not the query, what was left?
It was how the data was sitting on storage.
That single realization changed the way I look at every slow Spark job now, and it is exactly what I want to walk you through here. Not the textbook definition, but what I actually saw happen, number by number, and what I did about it.
By the end of this, you will know:
- What the small file problem really is, in plain language
- The exact scenarios that cause it, with real numbers from my experiments
- How to diagnose it using the Spark UI, without guessing
- How to fix it using OPTIMIZE, Auto Optimize, and Liquid Clustering
If you’ve ever looked at a slow Spark job and struggled to understand where the time was actually going, this kind of hands-on problem solving is exactly what we focus on in TrendyTech’s Ultimate Data Engineering Masters Program. The goal is not just to learn Data Engineering concepts, but to understand how they behave in real-world scenarios and how to approach problems like a Data Engineer.
What Is the Small File Problem, Really?
Once I stopped blaming the cluster, I went looking for what those 29,200 files were actually doing to my query.
Here is the part that made it click for me. Imagine you have 10,000 parquet files, and each one is roughly 1 MB. Now run something as simple as:
%sql
SELECT avg(sales) FROM orders_managed;
To calculate this average, Spark cannot lean on table level stats alone. It has to open every single file, read its footer, pull out whatever it needs, and close it again. That opening and closing has a cost of its own, completely separate from the actual processing.
Now picture the same amount of data sitting in 100 files of 100 MB each instead. Same total size, same records, but this time Spark opens 100 files instead of 10,000.
That difference, opening and closing thousands of tiny files versus a much smaller number of properly sized ones, was exactly what my 29,200 files were doing to that filter. The overhead of managing files had quietly started costing more than the actual work of reading them.
And here is the part that surprised me the most once I dug further. Both Parquet and Delta Lake suffer from this. Delta is better because it maintains a centralized transaction log instead of asking cloud storage to list every folder, so it avoids what we call the directory scan problem. But once you are down at the level of actually reading the files, small files hurt Delta almost as much as they hurt raw Parquet, and I saw that first hand when I ran the same query against both formats.
Problem 1: Over Partitioning
So I went back and looked at the full picture. Across all six years of order history, this table held close to 80,000 files in total.
This is the top level of the table, one folder per year. Nothing looks alarming yet. The damage is hiding inside each of these.
The real count: 2,000 folders, 80,000 files, 96.20 KB average file size, nowhere close to the 128 MB Spark actually wants to work with.
My query only filtered on two of those six years, so partition pruning did exactly what it was supposed to do.
PartitionFilters shows order_year IN (2018, 2019), proof that pruning correctly skipped the other four years. The problem was never here, it was what got left behind inside these two
It skipped straight to the right year-month-day folders and never touched the rest. It still opened 29,200 files, because that is what was sitting inside just those two years worth of folders.
Pruning was never the problem. What pruning left me with was the problem.
I had partitioned the orders table by year, month, and day together. Two years of daily data works out to roughly 730 day-level folders, with about 40 files landing in each one from repeated writes. Multiply that out and you land at exactly 29,200 files, most of them barely a few KB each, and that is before you even count the other four years sitting untouched in the remaining 50,000 or so files.
This is what over partitioning actually looks like once you do the math on it. It does not feel dangerous while you are typing out the partitionBy clause. It only shows up later, sitting quietly in your storage account, waiting for someone to run a query on it.
Here is the exact query I ran against the Parquet version of this table:
I was filtering on two years out of six, which should mean roughly a third of the data, somewhere around 2.7 GB out of 7.34 GB. Instead, here is what the Spark UI showed me.
The scan alone took 11.2 minutes, and out of the total job time, 10.9 minutes was spent just scanning parquet files, for a query that should have been quick and simple. That is the first thing I check in the Spark UI now, before anything else, scan time eating almost the entire job while the actual filter and write logic barely register.
The second thing I check is the number of files read against what I expect from my partitioning. 29,200 files against roughly 730 expected folders was the giveaway here.
For 2.7 GB, a table sitting in properly sized files, somewhere around 128 MB each, would need roughly 22 to 23 tasks to scan. This job launched more than necessary for the identical amount of data. Every one of those extra tasks carries its own fixed cost, getting scheduled, opening a file handle, reading a footer, closing it again, that has nothing to do with the 2.7 GB of actually useful data sitting underneath all of it.
I ran the same query on the Delta version of the same table. Delta is meant to be smarter here because of its centralized transaction log, and it was, but it still had to read all 29,200 files, and most of the time again went into scanning rather than processing. Even Delta cannot save you from a genuinely bad file layout.
Understanding Spark performance becomes much easier when you can connect what you see in the Spark UI with what is happening underneath. We go much deeper into this approach in TrendyTech’s Databricks Performance Tuning Program, where concepts such as partitioning, file layouts, Spark UI analysis and Delta Lake optimization are explored through hands-on performance scenarios.
Problem 2: Partitioning on a High Cardinality Column
While I was digging into the over partitioning issue, I ran into the same outcome from a completely different angle.
I generated a fresh 400 million row dataset and partitioned it by product_id, a column with close to 5,000 distinct values.
I expected this to fix things. It did not. The small file problem still showed up, simply because the column itself was a poor choice for partitioning in the first place. Auto compaction can clean up what is inside a folder. It cannot undo the decision to create 5,000 folders for a table that never needed that many. This is the trap with high cardinality columns, ones where the number of distinct values is very large, like a customer ID or a product ID. Compare that to something like order_status, which might only have three or four values and would have been a far safer choice.
The real cost of this decision showed up when I generated the same dataset in Parquet instead, with 5 files written under every folder.
The write took roughly 30 minutes on serverless, and it cost close to ₹1,000. That number stopped me for a second. Serverless is supposed to feel free until you actually use it badly.
Here is why. I had only 5 tasks writing 400 million records across 5,000 folders. So each task kept doing the same tiny dance, open a file, write, close, move to the next folder, open again. Every open and close is a billed write on ADLS Gen2, roughly ₹0.04 to ₹0.05 per 10,000 transactions on the hot tier. Multiply that across 350 to 400 million of those tiny operations and you get exactly the bill I saw. Not one rupee of it moved data. All of it paid for opening and closing files, because of a single partitioning decision.
Problem 3: Frequent Small Writes
There is a third way small files show up, and this one is not really anyone’s fault.
Think of a table getting an incremental update every single hour. Each update does not touch the existing data, it just writes a new file. If that hourly file is only 1 or 2 MB, nobody upstream did anything wrong. You cannot call them up and ask them to stop sending hourly updates. But left unattended over weeks and months, those tiny batches keep landing in the same table until you are staring at thousands of small files, and by then, it is your problem to fix, not theirs.
I wanted to see this up close instead of just describing it, so I built a small table with auto optimize turned off on purpose, and inserted 100 rows one at a time to mimic 100 separate tiny writes landing over time.
That is 100 files sitting in one table, each holding exactly one row. Now here is the interesting part. Not every query suffers from this equally.
This is what 100 separate append writes actually look like in storage. One tiny file per insert, nothing merging them.
I ran a query where Delta’s stats cannot help with:
An average has to touch every row, so there was nothing to skip. This one reads all 100 files, one row at a time, and this is exactly the shape frequent small writes leave behind. Not a dramatic scan time on 100 rows, you will not feel this at this scale, but stretch this same pattern out to a table that has been collecting hourly writes for a year and you are looking at approximately 8,640 files doing the exact same thing to every aggregate query that runs against it.
There is no folder count to compare against, and no cardinality to match. What gives it away is simpler, the size of the largest file read sitting right next to the size of the smallest file read, both tiny, combined with a query that has no partition or filter to prune against.
Three very different situations. Same outcome every single time. And once you have confirmed which one you are looking at, using exactly these Spark UI signals instead of guessing, fixing it becomes a lot more straightforward.
How Do We Solve It
OPTIMIZE: The Manual Fix
OPTIMIZE takes a bunch of small files and combines them into fewer, right sized ones using a bin packing approach.
Remember the orders_managed table from Problem 3, the one with 100 tiny files where the average price query had to open every single one? That is exactly where OPTIMIZE earns its place. I ran it against that same table.
Running an average price query touched all 100 of them. Then I ran:
And all 100 files became one.
Running the exact same average query afterward hit just that single file instead of a hundred, and the difference at real scale, a table that has been collecting hourly writes for a year instead of a quick demo with 100 rows, is enormous.
Under the hood, OPTIMIZE sorts files in descending size order and packs them into bins, without ever exceeding a target size, which defaults to 1 GB.
How OPTIMIZE bin-packs files toward a 1 GB target
Given files of 600, 300, 300, 300, 100, and 100 MB, it sorts them largest first, then fills Bin 1 with 600 plus 300 plus 100 to land close to 1 GB, and puts the rest into Bin 2. The ideal file size for Delta tables generally sits between 16 MB and 1 GB. Below 16 MB you start paying overhead, above 1 GB files get unwieldy for parallelism.
Auto Optimize: For Tables That Keep Getting Small Writes
If your table is being fed by an hourly job that always writes 1 or 2 MB at a time, running OPTIMIZE manually every time is not realistic. That is where Auto Optimize helps, controlled through two settings.
I cloned the same setup from Problem 3, but this time left the table properties untouched, so Auto Optimize would stay on by default instead of being switched off on purpose.
Then I ran the exact same 100 tiny inserts as before.
Scroll through the table history and there it is: an OPTIMIZE operation sitting between two WRITE operations, triggered automatically while the inserts were still running.
Delta did not wait for me to notice. Somewhere around the 33rd insert, it decided enough small files had piled up and triggered a compaction on its own. It kept doing that quietly as more inserts landed. By the time all 100 rows were in and I ran the same average price query again:
Instead of hitting 100 files, it hit 7. Two triggering points control this behaviour:
Whether you run OPTIMIZE by hand or let Auto Optimize trigger it for you, the underlying idea is exactly the same, stop the small files from piling up in the first place.
This is also why performance tuning is much more than memorising commands such as OPTIMIZE. Knowing when to compact files, when to rethink partitioning, and how to validate the improvement through Spark metrics is the kind of practical decision-making we focus on in our Databricks Performance Tuning Course.
Rethinking the partitioning column
Sometimes the honest fix is simpler than any command. If your partition column has thousands or millions of distinct values, no amount of OPTIMIZE is going to undo that decision cleanly. Partitioning works best on low cardinality columns, things like country, payment_mode, or order_status, where each folder still ends up with a meaningful volume of data.
Liquid Clustering: When Your Access Pattern Keeps Changing
Partitioning and Z-order both share one real weakness. You have to commit to a column upfront, and if your query patterns change six months later, you are looking at rewriting the entire table.
I set up a fresh table using 10 NYC taxi parquet files, repartitioned into 200 files of roughly 10 to 12 MB each.
Before touching clustering at all, I ran a baseline query to see what 200 unsorted files cost me.
Then I turned clustering on.
Nothing happens the moment you run this. It just tells Delta which column to prefer the next time it lays out data. I reran the exact same query right after to prove it.
To actually make it happen, you run OPTIMIZE.
This is the step that actually rewrites the files, grouping rows so trip_distance ranges stop overlapping between them.
Now the same query again.
What makes Liquid Clustering genuinely different is that it does not force a rigid folder structure. Picture a table partitioned by country and date where some combinations produce a healthy file and others produce a nearly empty one.
Rigid partitions vs. Liquid Clustering’s flexible grouping.
With traditional partitioning, those small combinations stay small forever, because the folder structure is fixed. Liquid Clustering takes the opposite approach. It decides what the ideal file size should be first, and then groups data to match that target, combining several small partitions into one right sized file, and splitting an oversized one into two. It clusters new data incrementally as it lands, and gradually reorganises older data through maintenance operations, without ever needing a full table rewrite when your access pattern shifts.
It is worth remembering that clustering does not rewrite everything the moment you enable it. It works incrementally, so give it a maintenance cycle, an OPTIMIZE run like the one above, before expecting the full benefit.
Conclusion
Three different causes. One common outcome, files too small for Spark to work with efficiently.
1. Over Partitioning
Partitioning down to year, month, and day created close to 80,000 files across six years of data.
Result: 1,825 tasks to scan 2.7 GB that should have needed roughly 25.
2. Partitioning on a High Cardinality Column
Partitioning by product_id created 5,000 folders, and Delta’s auto-compaction could shrink what was inside each folder but never undo that decision.
Result: ₹1,000 and 30 minutes just to write the data, before a single query even ran against it.
3. Frequent Small Writes
Hourly writes of 1 to 2 MB each look harmless on their own, but they quietly accumulate into thousands of files over weeks and months.
Result: any query that cannot prune on a filter, like an average or a group by, ends up touching every one of those files.
None of these needed a bigger cluster. They needed the file layout fixed, using OPTIMIZE, Auto Optimize, or Liquid Clustering depending on whether the problem is a one time cleanup or an ongoing pattern.
“The small file problem rarely announces itself directly. It hides behind a scan stage that quietly takes ten times longer than it should, and it disappears the moment you stop looking only at the query and start looking at how the data is actually sitting in storage.”
Performance tuning is rarely about finding one configuration that makes every Spark job faster. It is about understanding where the time is being spent, identifying the real bottleneck, and knowing which optimization makes sense for that workload.
If you want to build that understanding through hands-on Spark and Databricks scenarios, explore TrendyTech’s Databricks Performance Tuning Program.
And if you’re looking for a broader learning path covering Data Engineering from fundamentals to advanced technologies, explore all Ultimate Data Engineering Masters Program.
