Delta small files from streaming tables: optimized writes, auto compaction and safe OPTIMIZE

#Symptom

A table loaded by a streaming job gets slower by the week. Queries spend a long time before they scan any data, driver memory use grows while reading the Delta log and storage API costs rise. The row count hasn't grown much, but the file count has.

#Why streaming creates small files

Each micro-batch writes at least one file per task, and per partition.

files per day = batches per day x output tasks per batch x partitions touched

For a 30-second trigger, with 8 tasks and 10 partitions touched per batch:

2,880 batches x 8 tasks x 10 partitions = up to 230,400 files a day

#Step 1: measure it

DESCRIBE DETAIL main.sales.events;

Check the file size distribution, so one number doesn't mislead you:

from pyspark.sql import functions as F

files = (spark.read.table("main.sales.events")
         .select(F.col("_metadata.file_path").alias("path"),
                 F.col("_metadata.file_size").alias("size"))
         .distinct())

(files.agg(F.count("*").alias("files"),
           F.expr("percentile(size, 0.5)/1024/1024").alias("p50_mb"),
           F.expr("percentile(size, 0.9)/1024/1024").alias("p90_mb"),
           (F.sum("size")/1024**3).alias("total_gb"))
 .show())

This scans the table, so limit it with a filter on a large one. Rough guide:

Compare with DESCRIBE HISTORY. Lots of STREAMING UPDATE commits with numOutputRows small and numAddedFiles high confirm the cause. See the inspection cheat sheet.

#Step 2: fix the source of the files

Compaction treats the symptom. Start by writing fewer files.

#Lengthen the trigger

Ask what latency the business really needs:

(df.writeStream
   .format("delta")
   .option("checkpointLocation", checkpoint)
   .trigger(processingTime="5 minutes")
   .toTable("main.sales.events"))

If several-minute latency is fine, consider running on a schedule. availableNow processes what's there and stops, so the cluster doesn't run around the clock:

.trigger(availableNow=True)

See streaming trigger costs.

#Remove bad partitioning

Partitioning on a high-cardinality column multiplies your file count. Use date at most, or use liquid clustering:

-- New table
CREATE TABLE main.sales.events_v2 (event_id BIGINT, tenant_id STRING, ts TIMESTAMP, payload STRING)
CLUSTER BY (tenant_id, ts);

Moving a partitioned table to clustering usually needs a rewrite. Read liquid clustering before you plan it.

#Turn on optimized writes

This shuffles data before writing so each partition gets fewer, larger files. It adds a small cost per write:

ALTER TABLE main.sales.events SET TBLPROPERTIES (
  'delta.autoOptimize.optimizeWrite' = 'true'
);

#Step 3: add compaction

#Auto compaction

After a write, Databricks compacts small files synchronously, on the same cluster:

ALTER TABLE main.sales.events SET TBLPROPERTIES (
  'delta.autoOptimize.autoCompact' = 'true'
);

It's a good default for frequent small appends. It can lengthen batches, and it only combines small files up to a limited size, so a full OPTIMIZE may still help. Watch the batch duration after you turn it on.

#Scheduled OPTIMIZE

Run it from a separate job on recent data:

OPTIMIZE main.sales.events
WHERE event_date >= current_date() - 2;

The WHERE clause can use only partition columns. With liquid clustering, run plain OPTIMIZE, which is incremental.

Tip

On Unity Catalog managed tables, predictive optimization can run OPTIMIZE and VACUUM for you. Check whether it's on before you build your own schedule, so you don't do the work twice.

DESCRIBE TABLE EXTENDED main.sales.events;
SELECT table_name, operation_type, operation_status, start_time
FROM system.storage.predictive_optimization_operations_history
WHERE table_name = 'events'
ORDER BY start_time DESC LIMIT 10;

Check that the system table is available in your account.

#Step 4: avoid compaction conflicts

Append-only streams usually coexist with OPTIMIZE. Conflicts arise when a stream also runs MERGE, UPDATE or DELETE on the same files:

ConcurrentDeleteReadException / ConcurrentDeleteDeleteException

Ways to reduce them:

• Compact older partitions that the stream is no longer changing.
• Run OPTIMIZE during quieter periods.
• Turn on deletion vectors and row-level concurrency where your runtime supports them.
• Let the streaming job retry, using job retries or an availableNow schedule.

See concurrent write conflicts.

Warning

Compaction commits show as data changes on the table. If another stream reads this table as a source, a rewrite with no logical change is normally skipped, but MERGE, UPDATE and DELETE commits aren't. Those readers fail unless you set skipChangeCommits. Check this before changing how downstream streams work.

#Step 5: compact the backlog

For a table with months of tiny files, work in slices, so you aren't running one huge job:

from datetime import date, timedelta

start, end = date(2026, 6, 1), date(2026, 11, 1)
d = start
while d < end:
    next_d = d + timedelta(days=7)
    spark.sql(f"""
        OPTIMIZE main.sales.events
        WHERE event_date >= '{d}' AND event_date < '{next_d}'
    """)
    d = next_d

Use compute with enough memory and scale, and do this off-peak. Compute-optimized nodes usually suit this rewrite work.

#Step 6: reclaim storage

OPTIMIZE doesn't delete the old files. They stay until VACUUM removes them after the retention period:

VACUUM main.sales.events DRY RUN;
VACUUM main.sales.events;

Caution

VACUUM limits time travel to the retention window, and long-running readers can fail if files they're using get removed. Never lower the retention below 7 days unless you're sure that nothing needs those versions. See FileNotFound after VACUUM.

Storage may rise at first: both old and new files exist until VACUUM runs.

#Step 7: verify

Check a query's profile, or compare EXPLAIN and run time, before and after.

#Decision guide

#Prevent it next time

• Track numFiles and average size for key tables, and alert when they jump.
• Set table properties in table creation scripts, not after the problem appears.
• Include the file count in any review of a new streaming job.
• Related: Delta table maintenance and Auto Loader tuning.