What you will be able to do
- Explain what OPTIMIZE bin-packing does to partitioned and clustered Delta and Iceberg tables
- Tell auto compaction, optimized writes and scheduled OPTIMIZE apart, and know when each one is enough
- Predict the autotuned target file size for a table, and know when to set targetFileSize yourself
1.OPTIMIZE: compacting files into balanced sizes
A good layout still degrades as small writes pile up and leave many small files behind. OPTIMIZE fixes this by rewriting data files, and it works on both Delta Lake and Apache Iceberg tables. What it does depends on the layout. On a liquid-clustered table, it groups data by the clustering keys. On a partitioned table, compaction happens inside each partition, because files can't be merged across partition boundaries. You can limit it to part of the table with a WHERE predicate.
OPTIMIZE table_name WHERE date >= '2022-11-18'Bin-packing is idempotent. It balances files by their size in storage rather than by row count, although the two usually go together. It is also safe to run while the table is in use. Readers get snapshot isolation, the results are the same before and after because OPTIMIZE changes no data, and streams that read from the table are not affected. When the command finishes, it returns file statistics for the files it removed and the files it added. How often to run it is a cost trade-off: running it more often makes queries faster but uses more compute. Databricks suggests starting with a daily run, ideally at night when spot prices are low, and adjusting from there. OPTIMIZE is CPU-heavy, so compute-optimized instance types are recommended.
Checkpoint 1 of 6· Fill the gap
Which DeltaTable method triggers bin-packing compaction in Python?
from delta.tables import *
deltaTable = DeltaTable.forName(spark, "table_name")
deltaTable.optimize(). ? ()optimize().executeCompaction() is the Delta-specific Python equivalent of a plain OPTIMIZE table_name.
Source: docs.databricks.comCheckpoint 2 of 6· Check yourself
What does bin-packing OPTIMIZE try to balance across the output files?
Bin-packing balances storage size, not row count, although the two are often correlated.
“Bin-packing aims to produce evenly-balanced data files with respect to their size in storage”Source: docs.databricks.com
Sources1
2.Auto compaction and optimized writes
Two features stop small files from piling up between OPTIMIZE runs. Auto compaction combines small files within partitions. It runs synchronously on the cluster that did the write, after the write succeeds, and only touches files that haven't been compacted yet. Optimized writes shuffle data before writing so that fewer, larger files land in the first place. They help partitioned tables most, but the shuffle can add write latency. If you use optimized writes, don't add coalesce(n) or repartition(n) right before the write. Both features are always on for MERGE, UPDATE and DELETE, and you can't turn them off. Neither one replaces OPTIMIZE. For tables over 1 TB, Databricks still recommends running OPTIMIZE on a schedule.
| Setting | Delta | Iceberg |
|---|---|---|
| Auto compaction (table property) | autoOptimize.autoCompact | autoOptimize.autoCompact |
| Auto compaction (Spark session) | spark.databricks.delta.autoCompact.enabled | spark.databricks.iceberg.autoCompact.enabled |
| Optimized writes (Spark session) | spark.databricks.delta.optimizeWrite.enabled | spark.databricks.iceberg.optimizeWrite.enabled |
| Target file size (table property) | delta.targetFileSize | iceberg.targetFileSize |
The auto compaction settings accept auto (recommended, which tunes the target size), true or legacy (a fixed 128 MB target), or false. Auto compaction shows up in table history as an OPTIMIZE operation. You can tell it apart from a manual OPTIMIZE by one field.
Checkpoint 3 of 6· Check yourself
In DESCRIBE HISTORY you see several OPTIMIZE operations. How do you tell which ones came from auto compaction?
Auto compaction is logged as OPTIMIZE with auto set to true. A manual OPTIMIZE has auto set to false.
“Auto compaction appears as an OPTIMIZE operation with the operationParameters.auto field set to true.”Source: docs.databricks.com
Checkpoint 4 of 6· Exam question
An existing Delta table `orders` is partitioned by `order_month` and has accumulated hundreds of small partitions after a shift in order volume, degrading query performance. The team wants to move the table to liquid clustering on `order_date` and `region` without exporting and reloading the underlying data. Which statement should they run?
Correct answer: A — ``` ALTER TABLE orders REPLACE PARTITIONED BY WITH CLUSTER BY (order_date, region); ``` This removes the existing Hive-style partition scheme and registers the new clustering keys on the same table in place.
- A. The `REPLACE PARTITIONED BY ... WITH CLUSTER BY` clause is the documented in-place conversion path: it drops the partition scheme and applies the new clustering keys to the existing table without exporting and reloading data.
- B. Liquid clustering is not compatible with an existing partition scheme, so applying `CLUSTER BY` directly to a still-partitioned table is not a valid conversion path, and manually dropping partitions afterward does not register clustering keys.
- C. A full CTAS rewrite can produce a clustered table, but it is unnecessary here and the claim that clustered tables must originate from a full rewrite is false, since the in-place `REPLACE PARTITIONED BY WITH CLUSTER BY` path exists precisely to avoid that cost.
- D. `ZORDER BY` is a distinct, non-adaptive indexing technique used with partitioned tables; running it does not register liquid clustering keys or convert the table's clustering metadata.
Sources2
3.How big should the files be?
Unless you set a size yourself, Databricks picks the target file size from the table's current size. Small tables get small files, and large tables get large files so the file count doesn't get out of hand. Below 2.56 TB the target is 256 MB. From 2.56 TB to 10 TB it grows linearly up to 1 GB, and above 10 TB it stays at 1 GB. Back to the prediction: when the target goes up, OPTIMIZE does not rewrite existing files into larger ones, so a growing table always keeps some undersized files. If those files matter, set a fixed targetFileSize. Once you do, Databricks stops autotuning that table.
| Table size | Target file size | Approximate number of files |
|---|---|---|
| 1 TB | 256 MB | 4096 |
| 2.56 TB | 256 MB | 10240 |
| 5 TB | 512 MB | 17339 |
| 10 TB | 1 GB | 24437 |
| 100 TB | 1 GB | 114437 |
On Unity Catalog managed tables, file sizes are tuned automatically and manual tuning advice doesn't apply. Even if you set targetFileSize, on SQL warehouses or Databricks Runtime 11.3 LTS and above only OPTIMIZE commands respect it. For very narrow tables that hit Parquet's row-count limit per file, maxRecordsPerFile is available, but Databricks advises against it unless you need it to avoid that error.
Checkpoint 5 of 6· Check yourself
With no targetFileSize set, roughly what target file size does Databricks use for a 5 TB table?
Between 2.56 TB and 10 TB the target grows linearly from 256 MB to 1 GB, which puts a 5 TB table at about 512 MB.
“For tables with a size between 2.56 TB and 10 TB, the target size grows linearly from 256 MB to 1 GB.”Source: docs.databricks.com
Sources2
4.Letting predictive optimization run compaction
On Unity Catalog managed tables, both Delta and Iceberg, predictive optimization takes care of upkeep. It decides which tables would benefit from OPTIMIZE, VACUUM and ANALYZE and queues those operations. For clustered tables, its OPTIMIZE runs incremental clustering. It works separately from auto compaction: auto compaction runs on the cluster that does the write, while predictive optimization runs asynchronously on serverless compute. There is one catch for older layouts. Predictive optimization never runs ZORDER and skips Z-ordered files, which is another reason to move those tables to liquid clustering. Background auto compaction is also available for managed tables and doesn't need predictive optimization. To switch a legacy table over to it, remove the old auto compaction Spark config and unset the old table property.
ALTER TABLE <table_name> UNSET TBLPROPERTIES (delta.autoOptimize.autoCompact)Checkpoint 6 of 6· Check yourself
A managed table was Z-ordered years ago and now has predictive optimization enabled. What happens to the Z-ordering?
The OPTIMIZE that predictive optimization runs never includes ZORDER, so Z-ordered files are left as they are.
“OPTIMIZE does not run ZORDER when executed by predictive optimization.”Source: docs.databricks.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.With auto compaction and optimized writes turned on, you never need to schedule OPTIMIZE.Why is that wrong?
Both features reduce small files but don't replace OPTIMIZE. For tables over 1 TB, Databricks recommends scheduled OPTIMIZE runs.
Covered in Auto compaction and optimized writes
2.When a growing table's autotuned target size goes up, OPTIMIZE rewrites the older, smaller files to the new size.Why is that wrong?
Existing files stay at their old size. To consolidate them, set a fixed targetFileSize.
Covered in How big should the files be?
Practise it for real
Compact a Delta table by hand and confirm in its history what ran
1.Run OPTIMIZE table_name on a test Delta table that has received many small appends.
Why: Bin-packing merges small files into evenly sized files without changing any data.
You should see: The command returns file statistics (min, max, total) for the files it removed and the files it added.
2.Run OPTIMIZE table_name a second time without writing any new data.
Why: Bin-packing is idempotent, so this shows that repeat runs on unchanged data are no-ops.
You should see: No files are removed or added.
3.Run DESCRIBE HISTORY on the table and inspect operationParameters for the OPTIMIZE entries.
Why: This separates manual runs from auto compaction.
You should see: Your manual runs show auto set to false. Any auto compaction entries show auto set to true.
Stuck? Get a nudge
If the second run rewrites files, check whether another job wrote to the table in between.
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“The OPTIMIZE command rewrites data files to improve data layout for both Delta Lake and Apache Iceberg tables.”
↩︎ OPTIMIZE: compacting files into balanced sizes“For tables with partitions defined, file compaction and data layout are performed within partitions.”
↩︎ OPTIMIZE: compacting files into balanced sizes“Databricks recommends that you start by running OPTIMIZE on a daily basis”
↩︎ OPTIMIZE: compacting files into balanced sizes“if it is run twice on the same dataset, the second run has no effect.”
↩︎ Prediction“Bin-packing aims to produce evenly-balanced data files with respect to their size in storage”
↩︎ Checkpoint - 2.
“It runs synchronously on the cluster that performs the write, after the write succeeds”
↩︎ Auto compaction and optimized writes“Optimized writes are most effective for partitioned tables, as they reduce the number of small files written to each partition.”
↩︎ Auto compaction and optimized writes“For tables larger than 1 TB, Databricks recommends running OPTIMIZE on a schedule to further consolidate files.”
↩︎ Auto compaction and optimized writes“For tables smaller than 2.56 TB, the autotuned target file size is 256 MB.”
↩︎ How big should the files be?“Databricks doesn't autotune tables that you have tuned with a specific target size.”
↩︎ How big should the files be?“When using Unity Catalog managed tables and SQL warehouses or Databricks Runtime 11.3 LTS and above, only OPTIMIZE commands respect the targetFileSize setting.”
↩︎ How big should the files be?“Auto compaction runs on the cluster performing the write, while predictive optimization runs maintenance operations asynchronously using serverless compute.”
↩︎ Letting predictive optimization run compaction“Background auto compaction doesn't require predictive optimization.”
↩︎ Letting predictive optimization run compaction“Auto compaction and optimized writes each reduce small file problems, but are not a full replacement for OPTIMIZE.”
↩︎ Exam trap 1“existing files are not re-optimized into larger files by the OPTIMIZE command”
↩︎ Exam trap 2“Auto compaction appears as an OPTIMIZE operation with the operationParameters.auto field set to true.”
↩︎ Checkpoint“For tables with a size between 2.56 TB and 10 TB, the target size grows linearly from 256 MB to 1 GB.”
↩︎ Checkpoint - 3.
“Predictive optimization automatically runs OPTIMIZE, VACUUM, and ANALYZE on Unity Catalog managed tables (Delta Lake and Iceberg)”
↩︎ Letting predictive optimization run compaction“OPTIMIZE does not run ZORDER when executed by predictive optimization.”
↩︎ Checkpoint