Compact Small Files with OPTIMIZE

You've just landed a new dataset for your Delta table, only to find your queries crawling and your storage bill climbing. The culprit? Thousands of tiny files — each one a few kilobytes — strewn across your table directory. This is the silent killer of Spark performance, and it's more common than you think.

In this lesson, you'll learn how to compact small files with OPTIMIZE — a cornerstone of Delta Lake maintenance. You'll grasp why small files happen, how OPTIMIZE solves the problem, and how to run it effectively in Databricks. By the end, you'll be ready to keep your tables fast and lean, and you'll be set for the next lesson in our Databricks track.

The problem this lesson solves

Every time you write to a Delta table, Spark creates one or more files per partition. If you write frequently with small batches, you get a flood of tiny files. The pain shows up in three ways:

• Slow queries: Spark must open and read metadata for every file, so a query over a thousand 10 KB files is far slower than one over a handful of 100 MB files.
• Runaway overhead: Each file carries metadata overhead, bloating your transaction log and making planning time explode.
• Wasted resources: Your cluster spends more time scheduling tasks than executing them, burning CPU and memory.

This is not a niche issue — it's the #1 cause of mysterious performance degradation on Delta Lake after a few weeks of streaming or frequent upserts. You need a way to consolidate those files, and that's exactly what OPTIMIZE is built for.

Pro tip: If you've ever seen a query plan with thousands of "FileScan" nodes, you're looking at the small-files problem in action.

Core concept / mental model

Think of your Delta table as a file cabinet. Each drawer is a partition, and each folder is a file. When you write a few rows, you're adding a new folder with just a page inside. Over time, you end up with hundreds of folders holding one or two pages — hard to search, hard to maintain.

OPTIMIZE is the archivist. It merges those tiny folders into a few well-organized binders, without losing any data. It works by rewriting small files into larger ones, then updating the Delta transaction log atomically. Queries see the new, consolidated files immediately, while older files remain valid until the operation completes.

Here's the key definition:

• Small files: Files significantly smaller than the ideal target size (typically 128 MB to 1 GB).
• OPTIMIZE: A Delta Lake command that rewrites small files into fewer, larger files.
• Bin packing: The algorithm OPTIMIZE uses to group small files efficiently.

Metrics to know:

You can run OPTIMIZE on an entire table, a partition, or with a predicate filter. It's a DML (Data Manipulation Language) operation that rewrites data, so it's not free — but it's nearly always worth it.

How it works step by step

OPTIMIZE follows a predictable, safe sequence:

1. Analyze the table: Delta Lake scans the transaction log to list all files and their sizes.
2. Select candidates: It picks files that are below the target size, optionally filtered by a partition predicate.
3. Bin pack: The algorithm groups candidate files into bins that sum to a healthy target size (adjustable via Spark config).
4. Rewrite: It reads the data from the small files and writes new consolidated files in-place (respecting the same partition layout).
5. Commit: A new transaction is created. The new files are added, and the old small files are logically deleted — but not physically, until you run VACUUM.
6. Log update: The transaction log now points to the new files. Any query started after the commit sees the compacted files.

The key is that OPTIMIZE is idempotent and safe: if it fails mid-way, the old files remain valid, and you can retry. You don't need to stop writes — Delta supports concurrent OPTIMIZE and writes, though you may hit conflicts.

Pro tip: OPTIMIZE is a write-heavy operation, so run it during off-peak hours or on a dedicated cluster to avoid hitting your main pipeline's performance.

Hands-on walkthrough

Let's get your hands dirty. In a Databricks notebook, you'll create a delta table with many small files, inspect it, then compact it.

Step 1: Set up a Delta table with small files

Run this to simulate the problem:

from pyspark.sql import functions as F
from pyspark.sql.types import StructType, StructField, IntegerType, StringType

# Create a schema
schema = StructType([
    StructField("id", IntegerType()),
    StructField("category", StringType())
])

# Generate 200 small partitions (each just a few rows)
df = spark.range(0, 1000, 1, 200)
df = df.withColumn("category", F.when(F.col("id") % 2 == 0, "even").otherwise("odd"))

# Write as Delta (this creates many small files)
df.write.format("delta").mode("overwrite").save("/tmp/compact_demo")

# Register as a table for SQL
spark.sql("DROP TABLE IF EXISTS compact_demo")
spark.sql("CREATE TABLE compact_demo USING DELTA LOCATION '/tmp/compact_demo'")

Now check the file count:

# Show the table's file info
spark.sql("DESCRIBE DETAIL compact_demo").select("numFiles", "sizeInBytes").show(truncate=False)

Expected output (your numbers may vary):

+--------+-----------+
|numFiles|sizeInBytes|
+--------+-----------+
|200     | 42000     |
+--------+-----------+

Average file size = 210 bytes — horrible.

Step 2: Run OPTIMIZE

Now the magic command:

OPTIMIZE compact_demo;

Or in Python:

spark.sql("OPTIMIZE compact_demo").show()

You'll see output like:

+---------+---------+---------+
|path     |metrics  |numFiles |...
+---------+---------+---------+
|/tmp/... |{numFilesAdded: 2, numFilesRemoved: 200} | ...
+---------+---------+---------+

Afterward, re-check:

spark.sql("DESCRIBE DETAIL compact_demo").select("numFiles", "sizeInBytes").show(truncate=False)

Expected:

+--------+-----------+
|numFiles|sizeInBytes|
+--------+-----------+
|2       | 42000     |
+--------+-----------+

Same data, but now only 2 files. Your queries will fly.

Pro tip: OPTIMIZE works on the whole table or a partition predicate. To optimize just a specific date partition, use OPTIMIZE events WHERE date = '2024-01-01'.

Step 3: Clean up old files (optional)

OPTIMIZE logically deletes old files, but they linger on disk until you run VACUUM. Use it when you're ready to free physical storage:

VACUUM compact_demo;

Warning: VACUUM permanently deletes files older than the retention threshold (default 7 days). Don't run it on tables with active time-travel queries unless you know the history window.

Compare options / when to choose what

OPTIMIZE isn't your only tool. Here's how it stacks up against alternatives:

General rules:

• If you have a batch pipeline writing large, infrequent loads — you may not need OPTIMIZE at all.
• If you have frequent small writes (streaming, upserts), run OPTIMIZE daily or use Auto Optimize.
• If you need data in a Z-order for high-dimensional filtering, OPTIMIZE first, then ZORDER BY.

Variations: You can run OPTIMIZE ... ZORDER BY (col1, col2) to combine compaction with clustering. On Delta Lake on Databricks, you also have OPTIMIZE with WHERE for incremental maintenance.

Troubleshooting & edge cases

Even a simple command can trip you up. Here are the common pitfalls and their fixes:

1. OPTIMIZE is too slow

• Cause: Huge table, or too many partitions.
• Fix: Run it on a partition predicate, or increase cluster resources. Use spark.databricks.delta.optimize.maxFileSize to set a larger target.

2. Concurrent writes during OPTIMIZE cause conflicts

• Cause: Another job writes to the same table while OPTIMIZE is running.
• Fix: Use OPTIMIZE with a retry. Delta's optimistic concurrency may throw a ConcurrentAppendException. Retry the command — it's safe. Alternatively, schedule OPTIMIZE when writes are paused.

3. OPTIMIZE doesn't reduce file count as expected

• Cause: Files are already above the target size, or you're filtering a partition with large files already.
• Fix: Check the average file size. If it's already >128 MB, you're fine. If not, check your predicate and table stats.

4. Storage not shrinking after OPTIMIZE

• Cause: You didn't run VACUUM.
• Fix: Run VACUUM to physically delete old files. Remember: OPTIMIZE is logical, VACUUM is physical.

5. Permissions or ACL errors

• Cause: The user lacks write permission on the underlying storage.
• Fix: Grant write access to the bucket/container, or use a service principal with proper IAM roles.

6. OPTIMIZE on a very large table runs out of memory

• Cause: The whole table attempt exceeds executor memory.
• Fix: Run per partition, or increase spark.sql.adaptive.enabled and tune partition sizes. Use spark.databricks.delta.optimize.shrinkPartition to avoid overloading.

Pro tip: Always check the metrics column of the OPTIMIZE output. It tells you exactly how many files were added/removed. If it shows zero, your table was already healthy.

What you learned & what's next

You now understand why compact small files with OPTIMIZE is a critical maintenance task for Delta Lake. You've seen:

• The problem: small files degrade query performance and inflate metadata.
• The mental model: OPTIMIZE is the archivist that rewrites tiny files into big ones.
• The step-by-step mechanics: analyze, bin pack, rewrite, commit.
• Hands-on practice: you created a table with 200 files, ran OPTIMIZE, and reduced it to 2.
• How to compare OPTIMIZE with alternatives like VACUUM and Auto Optimize.
• How to troubleshoot common issues like slow runs and concurrent write conflicts.

You've met the learning objectives: you can explain the core idea and apply OPTIMIZE in a practical exercise. This is a skill you'll use daily as your tables grow.

Now that you've mastered compaction, the next lesson in our Databricks track will focus on Z-ORDERING for data clustering — a way to further speed up queries on high-cardinality columns. You'll build on the same table you optimized here. Get ready to make your queries even faster.