Optimized
writes.

What it is

Optimized writes fix file sizes at the moment data is written — before files ever land in storage. Normally Spark writes roughly one file per write task, so a job with many parallel tasks emits a swarm of tiny files. Optimized writes inserts one extra shuffle stage just before the write: it redistributes rows so each table partition is written by fewer tasks, each producing a larger, right-sized file. The target file size when enabled is 128 MB.

Instead of letting the shape of your compute (how many tasks happened to run) decide the shape of your storage (how many files), optimized writes reshuffles the data so storage is sized on purpose. It is most effective for partitioned tables, where the default behavior scatters each partition's rows across every task.

Analogy
It's a packing station before shipping. Without it, ten workers each toss whatever they hold into its own box — you ship ten half-empty boxes. Optimized writes is the worker at the end of the line who consolidates everything for the same address first, then packs a few full boxes. Same goods, far fewer boxes.

Real-world use case
A nightly MERGE that upserts CDC rows into a large partitioned sales table. Without optimized writes each micro-task appends a tiny file per partition it touches; with optimized writes (which is on by default for MERGE) the rows are reshuffled per partition first, so each partition gets a handful of ~128 MB files.

Why it matters

• It prevents small files instead of cleaning them up later. Manual OPTIMIZE (Lesson 04) and auto compaction (Lesson 06) repair a table that already has too many small files — a second read-and-rewrite pass. Optimized writes avoids creating most of those files in the first place.
• It removes the coalesce/repartition guesswork. The old way to control write file count was df.coalesce(n) / df.repartition(n) before .write — but you had to guess n, and the right n changes with data volume. Optimized writes sizes files automatically toward 128 MB.
• It's on automatically where it matters most. For MERGE, UPDATE with subqueries, and DELETE with subqueries, optimized writes is always on and cannot be turned off — exactly the row-level ops that otherwise spray tiny files across many partitions.

See it work — write-pipeline step-through

Toggle optimized writes ON / OFF, then click Next to walk the write pipeline stage by stage. Watch the resulting files on disk — and note the extra shuffle stage that ON adds.

Simplified illustration. Real file counts depend on data volume, the 128 MB target, partition count, and AQE; the shuffle cost is paid once at write time.

See it work — which operations get it automatically?

Click each operation to reveal whether optimized writes is on by default — and why. The row-level operations are always on and can't be disabled; plain appends need you to opt in.

Simplified illustration. Automatic coverage for CTAS/INSERT depends on compute (SQL warehouse) and on DBR 13.3 LTS+ + Unity Catalog registration + partitioned tables. Set the table property to guarantee the behavior.

How it works — sub-topic by sub-topic

1 · Shuffle before write → fewer, larger files per partition

Without optimized writes, Spark writes about one file per write task per output partition — 200 tasks holding a few region=EU rows each ⇒ ~200 tiny EU files. Optimized writes adds an extra shuffle stage immediately before the write that concentrates each partition's data into a few tasks, each writing one right-sized file (target 128 MB). File count becomes a function of data size + the target, not of how parallel the job happened to be. The cost is the shuffle's network movement = some added write latency.

PySpark# Session config: DBR sizes files toward ~128 MB on writes in this session.
spark.conf.set("spark.databricks.delta.optimizeWrite.enabled", "true")

(spark.table("main.delta_opt_demo.sales_src")
      .write.mode("overwrite")
      # DON'T add .coalesce(n)/.repartition(n) here — let optimized writes size files.
      .saveAsTable("main.delta_opt_demo.sales"))

2 · Most effective for partitioned tables

On a partitioned table the default write fans each partition's rows across many tasks → the worst small-file explosion (many tiny files per partition directory). A job touching 50 partitions with 200 tasks can write up to 50 × 200 files. The pre-write shuffle groups rows by output partition first, so each partition is written by just a few tasks — which is why the docs call partitioned tables the case it helps most. On unpartitioned tables the benefit is smaller (AQE already coalesces somewhat), but it still right-sizes output.

SQL-- Most effective on partitioned tables: enable the property so writes self-size.
-- (For NEW tables, prefer liquid clustering over partitioning — see Lesson 08.)
ALTER TABLE main.delta_opt_demo.sales
  SET TBLPROPERTIES ('delta.autoOptimize.optimizeWrite' = 'true');

3 · Turning it on: table property + session config

Two independent switches. The table property delta.autoOptimize.optimizeWrite travels with the table (every write to it goes through optimized writes); the session config spark.databricks.delta.optimizeWrite.enabled applies to all writes in the current session. Use the property for a table you always want sized; use the session config for one-off jobs. Knowing which scope is in force prevents surprises.

SQL-- Durable, per-table switch (recommended for a table you always want right-sized):
ALTER TABLE main.delta_opt_demo.sales
  SET TBLPROPERTIES ('delta.autoOptimize.optimizeWrite' = 'true');

-- Set it at CREATE time too:
CREATE TABLE main.delta_opt_demo.sales (
  sale_id BIGINT, region STRING, amount DOUBLE, sale_date DATE
)
PARTITIONED BY (region)
TBLPROPERTIES ('delta.autoOptimize.optimizeWrite' = 'true');

PySpark# Session-scoped switch (one-off jobs, or to toggle broadly without ALTER TABLE):
spark.conf.set("spark.databricks.delta.optimizeWrite.enabled", "true")

4 · Always on for MERGE, UPDATE w/ subquery, DELETE w/ subquery

Analogy: the packing station you can't switch off CDC merges into big partitioned tables

For three row-level operations — MERGE, UPDATE with subqueries, and DELETE with subqueries — optimized writes is enabled by default and cannot be disabled. These ops rewrite scattered subsets of rows across many partitions, the exact pattern that produces the most small files, so Databricks applies the pre-write shuffle automatically. You accept the (small) shuffle cost in return for not flooding the table with tiny files — almost always the right call.

SQL-- MERGE gets optimized writes automatically (always on) — no property needed.
MERGE INTO main.delta_opt_demo.sales AS t
USING main.delta_opt_demo.sales_updates AS s
  ON t.sale_id = s.sale_id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

-- UPDATE/DELETE *with a subquery* also get optimized writes by default:
UPDATE main.delta_opt_demo.sales
  SET amount = amount * 1.05
  WHERE region IN (SELECT region FROM main.delta_opt_demo.promo_regions);  -- subquery → optimized write

5 · Also for CTAS + INSERT on SQL warehouses; UC tables in DBR 13.3 LTS+

Beyond the always-on row-level ops, optimized writes is also applied for CTAS and INSERT statements run on SQL warehouses. Additionally, since DBR 13.3 LTS+, all Unity Catalog–registered tables get optimized writes for CTAS and INSERT on partitioned tables. Coverage depends on where you run and on runtime + registration — when in doubt, set the table property so the behavior is guaranteed.

SQL-- CTAS on a SQL warehouse gets optimized writes automatically.
-- (Set the property anyway to make it durable across compute types.)
CREATE TABLE main.delta_opt_demo.sales_eu
  PARTITIONED BY (region)
  TBLPROPERTIES ('delta.autoOptimize.optimizeWrite' = 'true')
AS SELECT * FROM main.delta_opt_demo.sales WHERE region = 'EU';

-- INSERT into a partitioned UC table (DBR 13.3 LTS+) is sized by optimized writes too:
INSERT INTO main.delta_opt_demo.sales
SELECT * FROM main.delta_opt_demo.sales_late_arrivals;

6 · The trade-off: extra shuffle latency vs far fewer small files

The single cost is the extra shuffle stage (a network redistribution before the write). The single benefit is far fewer, ~128 MB files. Small files are expensive forever (slow listing, slow scans, metadata bloat, more compaction work); the shuffle is paid once at write time. Trading a one-time write cost to avoid a permanent read-and-maintenance cost is usually a clear win — reconsider only for an extremely latency-sensitive streaming micro-batch.

7 · Hard rule: don't coalesce/repartition before the write

Optimized writes already shuffles to size files. Adding a manual df.coalesce(n) or df.repartition(n) right before .write fights that mechanism: coalesce can re-create skew and small files; repartition forces a second, redundant shuffle with a number you guessed. Databricks explicitly says: do NOT use coalesce(n) / repartition(n) just before a write when optimized writes is enabled. Let it size the files; only set delta.targetFileSize if you need a different target.

PySpark# ANTI-PATTERN — fights optimized writes (guessed n, redundant/again-skewed shuffle):
(df.repartition(16)            # don't do this when optimized writes is on
   .write.mode("overwrite")
   .saveAsTable("main.delta_opt_demo.sales"))

# CORRECT — let optimized writes size the files; no manual partition count:
(df.write.mode("overwrite")    # optimized writes adds its own shuffle, targets ~128 MB
   .saveAsTable("main.delta_opt_demo.sales"))

8 · Where it sits: write-time vs post-write vs manual

• Optimized writes (this lesson) = write-time. Shuffles before the write so files land right-sized. Prevents small files.
• Auto compaction (Lesson 06) = post-write. Runs after a write commits and merges small files within partitions in a follow-up step. Cleans up small files.
• Manual OPTIMIZE (Lesson 04) = on-demand. A separate command you (or a schedule) run later to bin-pack the table or a partition subset.
• They compose. Both optimized writes and auto compaction are always on for MERGE/UPDATE/DELETE. They reduce but do not replace OPTIMIZE — tables over 1 TB still need scheduled OPTIMIZE.

Comparison — optimized writes vs auto compaction vs manual OPTIMIZE

Uses, edge cases & limitations

Uses

• Partitioned tables with per-partition write fan-out — the case it helps most.
• MERGE / UPDATE-with-subquery / DELETE-with-subquery — already on (and can't be turned off), so just rely on it.
• CTAS / INSERT bulk loads, especially on SQL warehouses and UC-registered partitioned tables (DBR 13.3 LTS+) — set the table property to guarantee it.
• Replacing coalesce/repartition tuning — turn it on and remove the manual partition-count hacks.

Edge cases an interviewer probes

• You repartition/coalesce before the write anyway → you fight the mechanism and re-create small files or a redundant shuffle. Remove it.
• Unpartitioned table → smaller benefit (AQE already coalesces somewhat); shuffle cost is the same — payoff is largest on partitioned tables.
• Latency-critical streaming micro-batch → the pre-write shuffle adds latency; weigh it, though file-count savings usually still win.
• Expecting it to compact EXISTING files → it doesn't; it sizes new writes only. Use OPTIMIZE / auto compaction for files already on disk.
• Plain CTAS/INSERT on a cluster (not a SQL warehouse) on older DBR → may not get it automatically; set the property explicitly.

Limitations

• It is a write-time mechanism: it sizes new files, it does not rewrite files already in the table.
• For MERGE/UPDATE/DELETE it is always on and cannot be disabled (same for auto compaction).
• Automatic coverage for CTAS/INSERT depends on compute (SQL warehouse) and on DBR 13.3 LTS+ + UC registration + partitioned for the broad case.
• It adds an extra shuffle → some added write latency.
• It reduces but does not replace OPTIMIZE; tables > 1 TB still need scheduled OPTIMIZE.

Common gotchas

• Don't coalesce/repartition right before the write. Databricks says not to when optimized writes is on — it overrides the sizing and reintroduces small files.
• It won't fix files you already have. Write-time only; run OPTIMIZE or rely on auto compaction to repair an already-small-file table.
• MERGE/UPDATE/DELETE: it's always on — you can't turn it off. Don't burn time trying to disable it; design around the small shuffle cost.
• Know your coverage. Plain CTAS/INSERT get it automatically on SQL warehouses, and broadly on UC tables in DBR 13.3 LTS+ for partitioned tables — elsewhere set the table property to be sure.
• Write-time vs post-write are different tools. Optimized writes prevents small files; auto compaction (Lesson 06) cleans them up after the commit — they compose, they don't replace each other.
• It complements, not replaces, OPTIMIZE. For large tables (> 1 TB) still schedule OPTIMIZE; prefer liquid clustering for skipping.