06.8 OPTIMIZE & Data File Layout

A Delta table can be logically correct and still perform poorly. One common reason is physical file layout.

1. The core mental model

many small / poorly organized files
              ↓
        file-layout work
              ↓
      fewer / better-organized files
              ↓
      less metadata + less I/O
              ↓
          faster queries

OPTIMIZE changes the physical layout of active data; it does not change the business meaning of the table.

2. Why file layout matters

A query must discover files, apply data skipping, open relevant files and process their columns. Poor layout can increase file discovery, metadata work, file-open overhead, task scheduling overhead and unnecessary I/O.

SQL / DataFrame query
        ↓
logical plan
        ↓
file discovery
        ↓
data skipping
        ↓
Parquet reads
        ↓
Spark execution

3. The small-file problem

Suppose a table receives thousands of tiny writes:

batch 1 → 20 files
batch 2 → 18 files
batch 3 → 27 files
...
day 1 → 25,000 files

The data volume may be reasonable while file count becomes expensive.

Typical symptoms:

• too many tiny scan tasks
• high scheduling overhead
• excessive metadata work
• poor scan throughput
• frequent compaction pressure

4. What OPTIMIZE does

Conceptually, OPTIMIZE reads eligible active files and writes a better physical arrangement.

file-A  file-B  file-C  file-D  file-E
  \      |      /       |      /
       OPTIMIZE
           ↓
      file-X   file-Y

The architectural result is fewer, better-sized files with improved physical locality.

See Databricks — OPTIMIZE.

5. OPTIMIZE does not mean "make one file"

A common mistake is assuming fewer files is always better.

Distributed engines need parallelism. Turning a multi-terabyte table into a handful of enormous files can reduce parallelism.

The objective is an efficient balance of:

• file size
• locality
• parallelism
• query concurrency

6. File size is workload-dependent

Workload	Layout consideration
Tiny interactive table	Avoid unnecessary maintenance
Large analytical fact	Large efficient files + sufficient parallelism
High-concurrency BI	Balance scan parallelism
Streaming append	Incremental maintenance
Large batch ETL	Efficient large-file layout

Avoid blindly applying one target everywhere.

7. Basic syntax

OPTIMIZE main.sales.orders;

For supported selective optimization:

OPTIMIZE main.sales.orders
WHERE order_date >= current_date() - INTERVAL 7 DAYS;

Selective optimization can be useful when only a recent slice changes frequently.

See Databricks — OPTIMIZE syntax.

8. OPTIMIZE and data skipping

Data skipping allows the engine to avoid files that cannot contain relevant values.

Example file statistics:

file-A: order_date 2026-01-01 → 2026-01-07
file-B: order_date 2026-01-08 → 2026-01-14
file-C: order_date 2026-01-15 → 2026-01-21

A query for 2026-01-20 can potentially skip file-A and file-B.

Data skipping is not the same as partition pruning.

9. Partition pruning vs data skipping

Partition pruning

Uses table partition boundaries to eliminate partitions.

Data skipping

Uses file-level statistics to eliminate files inside the remaining search space.

partition: 2026-02
  ├── file-A min/max
  ├── file-B min/max
  ├── file-C min/max
  └── file-D min/max

A strong architecture understands both layers.

10. OPTIMIZE and Z-Ordering

Historically, many Delta workloads used:

OPTIMIZE main.sales.orders
ZORDER BY (customer_id);

Z-ordering can colocate related values so file-level statistics can skip more data for selective queries.

It is workload-dependent.

Ask:

1. Is the column frequently filtered?
2. Is the filter selective?
3. Is the table large enough to justify maintenance?
4. Is liquid clustering a better fit?

See Databricks — data skipping and Z-Order.

11. Liquid clustering

Modern Databricks architectures increasingly favor liquid clustering for many new tables.

Example:

CREATE TABLE main.sales.orders (
  order_id BIGINT,
  customer_id BIGINT,
  order_date DATE,
  amount DECIMAL(18,2)
)
CLUSTER BY (customer_id, order_date);

Exact capabilities depend on the Databricks Runtime and table type.

See Databricks — liquid clustering.

12. Z-Order vs liquid clustering

Concern	Z-Order	Liquid clustering
Physical organization	OPTIMIZE operation	Evolving table layout
Existing workload	Often useful when measured	Also a migration candidate
Layout evolution	Explicit maintenance	Designed to evolve
New-table design	Case-dependent	Strong candidate for many modern workloads

Do not mechanically migrate every existing Z-Ordered table. Measure first.

13. Partitioning is not a substitute for OPTIMIZE

A table can be partitioned and still contain thousands of tiny files per partition.

Partitioning answers:

Which partition should I search?

Physical layout answers:

How should data inside the searchable space be organized?

14. OPTIMIZE vs VACUUM

These are frequently confused.

Operation	Primary purpose
OPTIMIZE	Improve active file layout
VACUUM	Remove eligible stale physical files

Typical lifecycle:

MERGE
 ↓
new files
 ↓
old files become stale
 ↓
OPTIMIZE → reorganize active files
 ↓
retention window
 ↓
VACUUM → remove eligible stale files

Neither is a generic "make Delta faster" button.

15. OPTIMIZE after MERGE-heavy workloads

CDC workloads can produce replacement files and fragmentation.

CDC events
   ↓
MERGE
   ↓
replacement files
   ↓
file fragmentation
   ↓
OPTIMIZE
   ↓
better active layout

Maintenance cadence depends on mutation rate, query frequency, table size, storage cost and performance SLA.

Optimize too often and maintenance becomes expensive. Optimize too rarely and query performance can degrade.

16. Maintenance is an economics problem

Every OPTIMIZE operation consumes compute and reads/writes data.

Think:

maintenance cost
       vs
query-performance benefit
       vs
storage / metadata benefit

A heavily queried Gold table may justify frequent maintenance. An archival Bronze table may not.

17. Predictive optimization

For supported Unity Catalog managed tables, Databricks predictive optimization can automatically perform maintenance such as OPTIMIZE and VACUUM.

The operating model becomes:

define workload + governance policy
             ↓
platform maintenance
             ↓
observe performance + cost
             ↓
tune exceptions

See Databricks — predictive optimization.

18. Query-pattern-first design

Do not start with:

"Which columns should I Z-Order?"

Start with:

"Which queries are expensive and why?"

For a selective query, investigate:

• bytes scanned
• files scanned
• files skipped
• filter selectivity
• clustering
• partitioning
• query frequency

Then choose the physical-layout strategy.

19. SAP/BW example

Suppose a replicated SAP procurement fact contains:

• company code
• purchasing organization
• supplier
• material
• document date
• purchase order
• amount

A common mistake is partitioning by every business dimension.

Instead:

1. identify dominant query filters
2. avoid excessive partition cardinality
3. use modern clustering where appropriate
4. optimize active data
5. monitor scan reduction

For BI workloads filtering heavily by purchasing organization and document date, those access patterns are more useful than simply copying the BW dimensional model into physical storage.

20. Hands-on Lab — create the small-file problem

Create a Delta table and intentionally write many small batches.

from pyspark.sql import functions as F

base = spark.range(0, 100000)

for i in range(20):
    (
        base.withColumn("batch_id", F.lit(i))
            .write
            .format("delta")
            .mode("append")
            .saveAsTable("main.demo.optimize_lab")
    )

Inspect the table and observe the file count.

Then run:

OPTIMIZE main.demo.optimize_lab;

Compare file count, scan behavior, execution time and physical layout.

The goal is to understand why physical layout affects distributed execution.

21. Hands-on Lab — selective optimization

Run:

OPTIMIZE main.demo.optimize_lab
WHERE batch_id >= 15;

Discuss why selective maintenance can reduce work and when a broader optimization is justified.

22. Hands-on Lab — Z-Order decision

Create a table with a selective query column. Measure a baseline query, compare an appropriate Z-Ordered layout and capture:

• files scanned
• files skipped
• bytes read
• elapsed time
• optimization cost

Do not declare success from elapsed time alone.

23. Hands-on Lab — architect choice

Scenario	Candidate
Existing large Delta table with selective filters	OPTIMIZE + workload-specific layout
Modern UC managed table with evolving access patterns	Liquid clustering
Legacy workload already using effective Z-Order	Keep and measure
Stale physical files	VACUUM
Tiny active files	OPTIMIZE / automatic maintenance

Explain why, not only what command you would run.

24. Troubleshooting

Queries are slow after OPTIMIZE

Check:

• query predicate selectivity
• files scanned/skipped
• statistics
• clustering
• partition pruning
• join/shuffle cost
• compute size
• Photon availability

OPTIMIZE cannot fix an expensive join.

OPTIMIZE itself is expensive

Check optimization scope, table mutation rate, maintenance frequency, data volume rewritten and whether predictive optimization is appropriate.

File count keeps growing

Check streaming/batch write frequency, micro-batch size, upstream partitioning, MERGE frequency, automatic maintenance and optimization cadence.

25. Architect decision framework

1. What query is slow?
        ↓
2. Is the bottleneck file layout?
        ↓
3. Can partition pruning help?
        ↓
4. Can data skipping help?
        ↓
5. Should the table use liquid clustering?
        ↓
6. Is Z-Order still justified?
        ↓
7. What maintenance cost is acceptable?
        ↓
8. Measure before / after

This prevents "OPTIMIZE everything" architecture.

26. Interview questions

Q: What does OPTIMIZE do?

It reorganizes active Delta data files to improve physical layout and query efficiency.

Q: Does OPTIMIZE delete stale files?

It is not the primary stale-file lifecycle mechanism. VACUUM handles eligible physical cleanup.

Q: Is fewer files always better?

No. Excessively large files can reduce parallelism. The objective is an efficient balance of file size, locality and parallelism.

Q: Z-Order or liquid clustering?

Evaluate the workload and platform capabilities. Liquid clustering is a strong modern choice for many new workloads, while Z-Order can remain appropriate for existing workloads when measurements show value.

Q: Should every table be partitioned?

No. Partitioning should be driven by access patterns and cardinality, not by the number of business dimensions.

27. Architect mental model

Logical table
     ↓
Active physical files
     ↓
Partition pruning
     ↓
Data skipping
     ↓
Clustering / layout
     ↓
Efficient scan

Separate maintenance responsibilities:

OPTIMIZE → organize active files
VACUUM   → remove eligible stale files

Physical layout is an execution-engine decision, not a copy of the logical data model.

Production checklist

• Measure query patterns before choosing layout.
• Monitor active file count and file sizes.
• Avoid excessive partition cardinality.
• Distinguish partition pruning from data skipping.
• Evaluate liquid clustering for new workloads.
• Keep Z-Order only when measurements justify it.
• Separate OPTIMIZE from VACUUM.
• Consider predictive optimization for supported UC managed tables.
• Measure maintenance cost.
• Compare before/after query metrics.
• Document table-specific exceptions.

Official references

• Databricks — OPTIMIZE
• Databricks — Data skipping and Z-Order
• Databricks — Liquid clustering
• Databricks — Predictive optimization