Auto Optimize
& autotuning.

What it is

"Auto optimize" is an umbrella name for two settings working together so you don't drown in tiny files:

• delta.autoOptimize.optimizeWrite — the write-time half (Lesson 05). Before data lands, Spark shuffles it so each write produces fewer, larger files.
• delta.autoOptimize.autoCompact — the post-write half (Lesson 06). After a write commits, a quick compaction pass merges leftover small files within partitions.

This lesson ties the two together and answers the next question: how big should a "right-sized" file be? That's the target file size — either an explicit number you set (delta.targetFileSize), or, when you set nothing, an autotuned size based on how big the table is (256 MB for most tables, scaling to 1 GB for very large ones).

Analogy
Auto optimize is the self-cleaning kitchen. Optimized writes plates food onto a few big plates instead of dozens of tiny ramekins (write-time); auto compaction is the busser who clears and consolidates leftover small plates right after the meal (post-write). Target file size is "use dinner plates, not saucers" — and autotuning is "the bigger the banquet, the bigger the platters."

Real-world use case
A streaming-fed bronze.events table getting hundreds of tiny micro-batch commits per hour. Turn on both settings so each commit writes larger files and gets compacted right after — the small-file problem never builds up. For long-term consolidation and skipping you still schedule OPTIMIZE / use liquid clustering.

Why it matters

• The small-file problem never sleeps. Streaming and frequent MERGE/UPDATE/DELETE commit constantly; without help, every commit drips more tiny files and scans get slower and pricier.
• Auto optimize handles the routine case for you. Optimized writes prevents many small files at the source; auto compaction mops up the rest right after each write — no separate job, no scheduling.
• It REDUCES, but does not REPLACE, OPTIMIZE. It keeps a single write's files healthy; it does not globally consolidate the whole table or give skipping locality.
• File size is a real tuning knob. Too small → metadata bloat and slow scans; too big → expensive rewrites on small updates and weaker skipping. Databricks picks a sensible target automatically, but you can override it per table.

See it work — the autotune curve

When delta.targetFileSize is not set, Databricks picks the target file size from the table's size. Drag the slider to a table size and watch the target — and the rough file count — change.

Simplified illustration. The real target is computed from table size by Databricks; file count here is target-implied (size ÷ target) and ignores partitioning, the growing-target caveat, and any explicit targetFileSize.

Target file size & autotuning

1 · delta.targetFileSize — the explicit target

Sets the size each file should aim for. Accept a readable size like '100mb' or raw bytes like 104857600. Default is None (unset → autotuning). When set, it's honored by OPTIMIZE, liquid clustering, auto compaction, and optimized writes.

SQL-- Pin an explicit target. Accepts '100mb' or a raw byte count (104857600).
ALTER TABLE main.delta_opt_demo.events
  SET TBLPROPERTIES ('delta.targetFileSize' = '128mb');  -- honored by OPTIMIZE, LC, autoCompact, optimizeWrite

-- Equivalent using raw bytes (128 MB = 134217728). Same effect.
ALTER TABLE main.delta_opt_demo.events
  SET TBLPROPERTIES ('delta.targetFileSize' = '134217728');

2 · File-size autotuning by table size (when no explicit target)

When targetFileSize is not set, Databricks autotunes the target from the table's size:

SQL-- Autotuning is the DEFAULT (no targetFileSize set) — nothing to enable.
-- To AVOID the "growing target leaves old small files" caveat on a huge table,
-- pin a fixed target and OPTIMIZE so existing files are rewritten to it:
ALTER TABLE main.delta_opt_demo.events
  SET TBLPROPERTIES ('delta.targetFileSize' = '512mb');  -- fixed target for a very large table
OPTIMIZE main.delta_opt_demo.events;                  -- rewrite existing files to the new target

3 · delta.tuneFileSizesForRewrites

Biases sizing toward rewrite-friendly sizes for tables that see frequent rewrites (heavy MERGE/UPDATE/DELETE), where smaller files make each rewrite cheaper. Niche — most workloads are fine with autotuning or one targetFileSize.

SQL-- For very rewrite-heavy tables, bias toward rewrite-friendly file sizes.
ALTER TABLE main.delta_opt_demo.events
  SET TBLPROPERTIES ('delta.tuneFileSizesForRewrites' = 'true');

4 · UC managed tables autotune by default

Managed = self-sizing
Unity Catalog managed tables get automatic file-size tuning by default (DBR 11.3 LTS+ / SQL warehouse), and can benefit from background auto compaction that does not require predictive optimization. EXTERNAL tables don't get the same automatic behavior.

SQL-- On UC MANAGED tables, file-size tuning is automatic by default. Inspect:
DESCRIBE EXTENDED main.delta_opt_demo.events;   -- properties + Predictive Optimization field
SHOW TBLPROPERTIES main.delta_opt_demo.events;  -- see autoOptimize.* and targetFileSize if set

Auto-optimize decision map

Pick a scenario and see which levers apply — and which fall to OPTIMIZE / liquid clustering / predictive optimization. The point: auto optimize handles the routine; the rest handle whole-table layout.

Simplified illustration. "Applies" is a guidance signal, not a guarantee for every table/runtime; for MERGE/UPDATE/DELETE optimized writes & auto compaction are always on (can't disable).

How it works — the umbrella & what it doesn't do

"Auto optimize" = optimizeWrite + autoCompact

It's not a single switch — it's the pair that runs at different moments of the same write: optimizeWrite shuffles before the write (fewer, larger files); autoCompact compacts after the commit (within partitions). They're independent — you can enable either alone. "Auto optimize" is a legacy umbrella term; modern guidance thinks in the individual settings.

SQL-- Enable BOTH halves of "auto optimize" on a table.
ALTER TABLE main.delta_opt_demo.events
  SET TBLPROPERTIES (
    'delta.autoOptimize.optimizeWrite' = 'true',   -- write-time: fewer, larger files (Lesson 05)
    'delta.autoOptimize.autoCompact'   = 'auto'    -- post-write: compact small files; 'auto' autotunes size
  );
SHOW TBLPROPERTIES main.delta_opt_demo.events;       -- confirm both are set

PYSPARK# Session-level equivalents (apply to writes from this Spark session).
spark.conf.set("spark.databricks.delta.optimizeWrite.enabled", "true")   # write-time half
spark.conf.set("spark.databricks.delta.autoCompact.enabled",  "auto")    # post-write half ('auto' autotunes)

Reduce, don't replace OPTIMIZE — and the 1 TB rule

These settings keep each write's files healthy, operating per-write / within partitions. They don't do a global whole-table consolidation, and they don't give data-skipping locality on filter columns. On a large, long-lived table, many independent writes still leave it sub-optimal overall.

SQL-- Auto optimize handles routine sizing; for big tables ALSO consolidate periodically.
OPTIMIZE main.delta_opt_demo.events
WHERE event_date >= DATE'2026-06-01';   -- scope to recent data to keep the job cheap

-- For NEW tables, prefer liquid clustering so data is laid out for fast filtering
-- (same OPTIMIZE triggers clustering incrementally). See Lesson 08.
ALTER TABLE main.delta_opt_demo.events CLUSTER BY (event_type, event_date);

Where auto optimize ends and the rest begins

• Auto optimize (optimizeWrite + autoCompact) → routine, automatic small-file control on every write. Set-and-forget for streaming / frequent DML.
• Scheduled OPTIMIZE → whole-table consolidation; needed past 1 TB.
• Liquid clustering (Lesson 08) → lays data out for fast filtering; consolidates incrementally via OPTIMIZE; the modern default.
• Predictive optimization (Lesson 09) → on UC managed tables, runs OPTIMIZE / VACUUM / ANALYZE for you.

Who does what

Uses, edge cases & limitations

Uses

• Both auto-optimize settings: streaming / frequent MERGE-UPDATE-DELETE tables where small commits pile up. Set-and-forget.
• delta.targetFileSize: when you know the ideal size, or to pin a fixed size on a very large table so a growing autotune target doesn't leave old small files.
• Autotuning (default): virtually all tables — leave it unset and let the platform scale 256 MB → 1 GB.
• tuneFileSizesForRewrites: rewrite-heavy tables where each rewrite cost dominates.

Edge cases an interviewer probes

• Table grows past a threshold → autotune target rises, but existing files are NOT re-optimized; old small files remain until you OPTIMIZE or pin a fixed target.
• You set targetFileSize on a UC managed table + SQL warehouse / DBR 11.3 LTS+ and writes don't change → only OPTIMIZE respects it there; run OPTIMIZE to apply.
• Tiny table → autotune still targets 256 MB; auto compaction won't fire below its small-file count threshold.
• Table > 1 TB on auto optimize alone → no global consolidation; schedule OPTIMIZE / use liquid clustering.
• MERGE/UPDATE/DELETE → optimized writes + auto compaction are always on (can't disable) — sizing already applies.

Limitations

• Auto optimize reduces but does not replace OPTIMIZE — no whole-table consolidation or skipping locality.
• A growing autotune target does not re-optimize existing files (large tables may keep small files unless you set a fixed targetFileSize).
• On UC managed + SQL warehouse / DBR 11.3 LTS+, only OPTIMIZE respects targetFileSize.
• Automatic file-size tuning by default is a UC managed-table behavior (DBR 11.3 LTS+ / SQL warehouse); EXTERNAL tables differ.

Common gotchas

• "Auto optimize" is two settings, not one — optimizeWrite (write-time) + autoCompact (post-write).
• It reduces, doesn't replace, OPTIMIZE. For tables > 1 TB, schedule OPTIMIZE (and prefer liquid clustering for skipping).
• The autotune target grows but doesn't backfill. A big table can keep small files from when it was smaller — pin a fixed targetFileSize and OPTIMIZE to rewrite them.
• targetFileSize isn't honored everywhere on managed tables — on UC managed + SQL warehouse / DBR 11.3 LTS+, only OPTIMIZE respects it.
• Default is autotuning, not a fixed size. delta.targetFileSize defaults to None — don't assume 128 MB everywhere (that's the optimized-writes / autoCompact='true' target, not the table-size autotune).
• MERGE/UPDATE/DELETE already optimize writes + auto compact (always on).
• For new tables, think liquid clustering + predictive optimization — auto optimize is routine plumbing; the modern story is Lessons 08–09.