Stop small files at the source: two table properties
Streaming pipelines that write to Delta every few minutes can pile up thousands of tiny files. Every query then pays a “small-file tax” in file listing, task overhead, and slow scans. Instead of relying only on a scheduled OPTIMIZE, stop small files at the source by setting two properties when you create the table — or later with ALTER TABLE.
Start modern: on a new Unity Catalog managed table with a recent runtime, you don't set these at all — Databricks tunes file sizes automatically, and predictive optimization can run OPTIMIZE and VACUUM for you. Create the table, declare liquid clustering keys (Step 2), and move on.
When the properties below apply: external tables and legacy workloads where you manage the settings yourself — streaming or frequent micro-batch writes to Delta, where these properties do the work. Set both when you create the table, or later with ALTER TABLE.
CREATE TABLE IF NOT EXISTS events (
event_id STRING,
user_id STRING,
event_type STRING,
event_date DATE,
event_ts TIMESTAMP
)
USING DELTA
TBLPROPERTIES (
'delta.autoOptimize.optimizeWrite' = 'true',
'delta.autoOptimize.autoCompact' = 'true'
);Or enable the session/cluster-level equivalents:
spark.conf.set("spark.databricks.delta.optimizeWrite.enabled", "true")
spark.conf.set("spark.databricks.delta.autoCompact.enabled", "true")optimizeWrite reduces the number of files written per partition at write time, so fewer, larger files land. autoCompact runs synchronously on the cluster performing the write, right after the write succeeds, compacting only files that haven't been compacted before. Both controls exist at table and session level — note that setting either to false at the session level overrides the table setting for every table the workload touches.
The pipeline that wrote a file per minute
Illustrative scenario: the walkthrough below is a composite example built from common streaming-pipeline failure modes — not a report of one observed incident. The SQL, checks, and validation steps apply to any Delta table paying the small-file tax described above.
The failure
Consider a clickstream ingestion pipeline that writes micro-batches to Delta every minute. After weeks of this, thousands of tiny files have piled up, and a dashboard aggregation filtering on event_type spends more time listing files than reading data. New small files arrive faster than any manual cleanup can keep up.
Pre-compact new micro-batches
ALTER TABLE events SET TBLPROPERTIES (
'delta.autoOptimize.optimizeWrite' = 'true',
'delta.autoOptimize.autoCompact' = 'true'
);New micro-batches now land pre-compacted instead of restarting the file-count problem with every run. These table properties govern this table's writes; the equivalent session settings cover every table the session writes to — and a session-level false overrides the table setting.
Reorganize the remaining live files nightly
-- Declare the clustering once; a plain OPTIMIZE then applies it incrementally
ALTER TABLE events CLUSTER BY (event_type, event_date);
-- Nightly: reorganize new and changed files along those keys
OPTIMIZE events;This rewrites the remaining files with data co-located for the dashboard's filter patterns, allowing data skipping to prune entire files. Liquid clustering is the recommended layout for new tables on Databricks Runtime 15.4 LTS and above — unlike ZORDER, its keys can evolve without rewriting existing data.
Already on ZORDER? Keep OPTIMIZE events ZORDER BY (event_type, event_date) — it remains valid, and you don't need to migrate to get the benefit. That branch is for tables already using it; choose liquid clustering for new tables. Note: on Unity Catalog managed tables, predictive optimization runs OPTIMIZE automatically but never runs ZORDER — it ignores Z-ordered files.
Clean up superseded files weekly
VACUUM events RETAIN 168 HOURS;This deletes files that are both no longer referenced by the table and older than the retention threshold — old versions superseded by OPTIMIZE or overwritten writes. 168 hours is the 7-day default retention. Note that files OPTIMIZE just superseded are not deleted right away; they must age past retention first. So VACUUM is a storage-cost cleanup on its own weekly cadence, not the second half of a speed fix — and VACUUM alone never makes queries faster, because it touches nothing about live files. Going shorter than 7 days trips a safety check: Databricks refuses unless you disable the retention-duration check, which you should only do when you are certain no operation on the table runs longer than the retention interval.
I ran VACUUM and my queries are still slow. Why?
VACUUM cleans storage, not live-file layout
That is expected: VACUUM is a storage cleanup, not a layout fix. It deletes only files that are both unreferenced and older than the retention threshold, and it never changes the size or count of live files. If queries are slow because of thousands of small live files, the fix is OPTIMIZE (or liquid clustering) plus the auto-optimize properties from the tip to prevent recurrence — VACUUM just keeps storage costs down afterward.
A typical maintenance pairing (modern path):
ALTER TABLE events CLUSTER BY (event_type, event_date);
OPTIMIZE events;
VACUUM events RETAIN 168 HOURS;Then prevent recurrence with the two auto-optimize properties from the tip (external/legacy tables), or rely on managed-table defaults and predictive optimization (new tables). Already on ZORDER? OPTIMIZE events ZORDER BY (event_type, event_date) remains valid — keep it; choose liquid clustering for new tables. On Unity Catalog managed tables, predictive optimization can run OPTIMIZE and VACUUM automatically.
Applies to: Delta Lake on Databricks. Liquid clustering: Databricks Runtime 15.4 LTS and above. Auto-optimize table properties: external tables and legacy workloads where you manage settings yourself. Predictive optimization: Unity Catalog managed tables, Premium plan or above, Databricks Runtime 12.2 LTS and above.
Last verified against Databricks docs: September 24, 2026.
Corrections: Sep 24, 2026 — examples reworked to lead with liquid clustering (CLUSTER BY); ZORDER moved to a legacy branch. Sep 23, 2026 — corrected session/table setting precedence; corrected VACUUM vs OPTIMIZE guidance; labeled the walkthrough an illustrative scenario.