The Small File Problem Is Quietly Killing Your Delta Lake Performance — Here's the Fix

From OPTIMIZE to Liquid Clustering: understanding why your queries slow down and what to do about it

Your pipeline hasn't changed. Your data volume hasn't doubled. But your queries just went from 2 minutes to 10.

The culprit isn't your code. It's 4,000 tiny Parquet files sitting inside a single partition, each one demanding Spark's attention like a separate conversation it has to manage before it can answer your question.

This is the small file problem. It's one of the most common performance issues in Delta Lake, and most teams don't realize it's happening until their jobs start timing out.

Let's break down why it happens, how to diagnose it, and the modern solution that changes how you think about file organization entirely.

What Is the Small File Problem?

Every time you write data to a Delta table, a new Parquet file is created. That's how Delta Lake maintains its ACID transaction guarantees. each write is an atomic addition to the transaction log.

This works beautifully for correctness. But it creates a physical problem.

If your pipeline ingests data every 10 minutes, that's 6 writes per hour. 144 writes per day. Per partition.

After one week, a single partition contains over 1,000 files. After a month, you're looking at 4,000+ files. most of them tiny, often just a few megabytes each.

graph LR
    subgraph Day1["Day 1"]
        D1["144 files"]
    end
    subgraph Week1["Week 1"]
        W1["1,008 files"]
    end
    subgraph Month1["Month 1"]
        M1["4,320 files"]
    end
    Day1 --> Week1 --> Month1
    style Day1 fill:#d4edda,color:#155724
    style Week1 fill:#fff3cd,color:#856404
    style Month1 fill:#f8d7da,color:#721c24

When Spark reads this partition, it doesn't just open one file. It opens thousands. Each file requires:

A metadata read from the transaction log
A file open operation against storage
A schema check
Memory allocation for a separate task

Multiply that by thousands of files and your simple read query becomes a metadata management nightmare.

The symptoms are unmistakable:

Queries that used to take 2 minutes now take 10
The Spark UI shows thousands of tasks for a single read
Job durations increase week over week despite unchanged data volumes
Driver memory pressure increases from tracking too many tasks

If you want to understand how file formats and storage optimization affect these patterns, that chapter walks through the physical layout in detail.

Why Partitioning Alone Doesn't Fix It

Partitioning is often the first optimization teams apply. And it helps. a lot. It tells Spark which folders to skip entirely based on your filter conditions.

But partitioning solves a different problem. It answers: where should Spark look?

It does not answer: how many files will Spark find when it gets there?

Consider a table partitioned by event_date:

SELECT * FROM events
WHERE event_date = '2026-03-15'

Partition pruning correctly skips every date except March 15th. But within that single partition folder, Spark still finds 144 tiny files from that day's writes. Each one becomes a separate task.

You can learn more about how partitioning interacts with file layout in Performance Optimization. The key insight: partitioning and file compaction solve complementary problems. You need both.

How to Diagnose It

Before you fix anything, confirm the problem exists. Delta Lake gives you the tools.

Check file count and average file size:

DESCRIBE DETAIL my_catalog.my_schema.events

This returns numFiles and sizeInBytes. Divide to get average file size. If your average file is under 32 MB and you have thousands of files, you have a small file problem.

Check recent write operations:

DESCRIBE HISTORY my_catalog.my_schema.events
LIMIT 20

Look at the operationMetrics column. If you see many WRITE operations adding small numbers of rows, files are accumulating.

In PySpark, you can also inspect the file listing directly:

from delta.tables import DeltaTable

dt = DeltaTable.forName(spark, "my_catalog.my_schema.events")
detail = dt.detail().collect()[0]

num_files = detail["numFiles"]
size_bytes = detail["sizeInBytes"]
avg_file_mb = (size_bytes / num_files) / (1024 * 1024)

print(f"Files: {num_files}")
print(f"Avg file size: {avg_file_mb:.1f} MB")

If avg_file_mb is under 32 and num_files is in the thousands, it's time to compact.

For more on using Spark diagnostics and the Spark UI to trace performance issues, see Observability and Debugging.

Solution 1: OPTIMIZE + ZORDER (The Reactive Fix)

The traditional solution is the OPTIMIZE command. It reads all the small files in a table (or partition) and rewrites them into larger, healthier files. typically targeting around 1 GB each.

OPTIMIZE my_catalog.my_schema.events

To target a specific partition:

OPTIMIZE my_catalog.my_schema.events
WHERE event_date = '2026-03-15'

flowchart LR
    subgraph Before["Before OPTIMIZE"]
        F1["2 MB"]
        F2["5 MB"]
        F3["1 MB"]
        F4["8 MB"]
        F5["3 MB"]
        F6["4 MB"]
    end
    subgraph After["After OPTIMIZE"]
        O1["~1 GB consolidated file"]
    end
    Before -->|OPTIMIZE| After
    style Before fill:#f8d7da,color:#721c24
    style After fill:#d4edda,color:#155724

When paired with ZORDER, OPTIMIZE also physically co-locates related data within the compacted files:

OPTIMIZE my_catalog.my_schema.events
ZORDER BY (customer_id, event_type)

Z-ordering arranges data so that rows with similar values in the specified columns end up in the same files. This enables data skipping. Delta Lake's min/max statistics let it skip entire files that can't contain matching rows.

The result: range queries on customer_id or event_type read far fewer files.

However, OPTIMIZE is reactive. It fixes the problem after files have already accumulated. You need to schedule it as a maintenance job. typically daily or after large batch writes.

A production maintenance schedule might look like this:

-- Run daily as a scheduled job
-- Step 1: Compact small files
OPTIMIZE my_catalog.my_schema.events
ZORDER BY (customer_id);

-- Step 2: Clean up old file versions
VACUUM my_catalog.my_schema.events
RETAIN 168 HOURS;

-- Step 3: Refresh statistics
ANALYZE TABLE my_catalog.my_schema.events
COMPUTE STATISTICS FOR ALL COLUMNS;

Scheduling this as a production workflow ensures compaction happens automatically. And the VACUUM step removes old files that Delta Lake no longer needs, reclaiming storage.

For deeper understanding of how ZORDER interacts with file-level statistics, the Storage and File Optimization chapter covers the physical mechanics.

Solution 2: Liquid Clustering (The Modern Fix)

Liquid Clustering, available in Delta Lake 3.0+, takes a fundamentally different approach. Instead of letting files accumulate and then fixing them, it organizes data incrementally as part of the write process.

Create a liquid-clustered table:

CREATE TABLE my_catalog.my_schema.events (
    event_id BIGINT,
    customer_id STRING,
    event_type STRING,
    event_date DATE,
    amount DECIMAL(10,2)
)
CLUSTER BY (customer_id, event_date)

That's it. No separate OPTIMIZE step. No ZORDER scheduling. Delta Lake handles file organization automatically during writes.

What makes Liquid Clustering powerful:

Incremental organization. Files are compacted and co-located during regular write operations. You don't wait for a batch maintenance job.

Flexible clustering keys. Need to change what you cluster by? No full table rewrite required:

ALTER TABLE my_catalog.my_schema.events
CLUSTER BY (event_type, event_date)

The table adapts incrementally. New writes use the new clustering keys. Old data gets reorganized gradually as OPTIMIZE runs in the background. This flexibility is something Schema Management and Evolution prepares you to think about. your table's physical organization should evolve with your query patterns.

No partition management. Liquid Clustering removes the need to choose partition columns upfront. No more worrying about partition cardinality, skewed partitions, or partition evolution.

You can still run OPTIMIZE on a liquid-clustered table to trigger additional compaction:

OPTIMIZE my_catalog.my_schema.events

But the key difference: the table is always in a reasonable state, even without manual maintenance.

Decision Guide: Which Approach Should You Use?

Aspect	Partitioning	OPTIMIZE + ZORDER	Liquid Clustering
Best for	Stable, low-cardinality columns	Older Delta Lake setups	Delta 3.0+, frequent writes
Handles frequent writes	Poorly (creates many small files)	Reactively (needs scheduled runs)	Automatically (incremental)
Key flexibility	Partition columns are fixed	ZORDER columns can change	Clustering keys easily altered
Maintenance effort	Low (but limited optimization)	Medium (requires scheduling)	Low (self-managing)
Query performance	Good for equality filters	Great for range queries	Great for mixed query patterns
Delta version	Any	Any	3.0+
Databricks recommendation	Legacy approach	Transitional	Start here

The general guidance: if you're starting a new table on Databricks today, use Liquid Clustering. If you're maintaining existing tables, OPTIMIZE + ZORDER remains effective and well-understood.

The Connection to Your Lakehouse

The small file problem doesn't exist in isolation. It's a symptom of how your entire data architecture operates.

If your medallion architecture ingests into Bronze every 10 minutes, those Bronze tables are the first to suffer. Silver and Gold tables that read from Bronze inherit the overhead if they do full rescans.

Incremental processing patterns help here too. Instead of rereading entire partitions, Change Data Feed and incremental reads process only new files. reducing the impact of file proliferation downstream. Lakeflow Declarative Pipelines (formerly Delta Live Tables) automate much of this incremental logic within Unity Catalog.

And if you're using streaming for near-real-time ingestion, you'll create even more files per partition. Streaming workloads are where Liquid Clustering provides the most dramatic improvement. For millisecond query latency on these tables, Lakehouse//RT powered by the Reyden engine now provides a real-time serving layer directly on Delta Lake.

Even data quality checks benefit from compaction. Scanning thousands of tiny files for validation is slower than scanning a few well-organized ones.

Learning Path

Every performance concept connects back to fundamentals. Here's how to build the understanding:

Performance Concept	BricksNotes Chapter	What You'll Learn
How Delta Lake stores data	Delta Lake Architecture	Transaction log, file versioning, ACID guarantees
File formats and sizes	Storage and File Optimization	Parquet internals, row groups, compression
Partition strategies	Performance Optimization	Pruning, cardinality, partition design
Diagnosing slow queries	Observability and Debugging	Spark UI, query plans, bottleneck identification
Scheduling maintenance	Production Orchestration	Job scheduling, dependencies, alerting
Schema flexibility	Schema Management	Evolution, enforcement, column management
Incremental patterns	Change Data Capture	Processing only new data efficiently
Streaming file creation	Real-Time Processing	Micro-batch writes and their file impact
End-to-end architecture	Lakehouse Architecture	Bronze/Silver/Gold layer design
Data validation	Data Quality Engineering	Expectations, constraints, monitoring

The Bottom Line

Performance isn't about bigger clusters or more expensive compute. It's about understanding how your data is physically organized on disk.

The small file problem teaches you something fundamental about distributed systems: the overhead of managing many small things often exceeds the cost of processing the data itself.

Whether you use OPTIMIZE + ZORDER or adopt Liquid Clustering, the engineers who understand why these solutions work are the ones who build pipelines that stay fast at any scale.

That understanding starts with the fundamentals. And the fundamentals are what BricksNotes is built to teach.

Want to go deeper into Delta Lake performance, file optimization, and production-grade pipeline design? Explore the full learning path at bricksnotes.com.