Definition
- OPTIMIZE – compacts many small files into fewer large ones (bin-packing) as a Delta transaction, fixing the small-file problem.
- ZORDER BY – physically co-locates rows that share values in chosen columns so data skipping prunes more files on those filter columns.
- Data skipping – uses per-file min and max statistics in the transaction log to skip files whose range cannot match a query's filter.
- Auto-compaction – triggers a small compaction automatically after writes when many small files exist.
- Optimized writes – rebalances partitions before writing to produce fewer, larger files.
- VACUUM – deletes data files no longer referenced by the log after a retention window (default seven days).
The National Bank's transaction lake is like a filing room. Over time clerks dump thousands of tiny slips (small files), making every search slow. OPTIMIZE is a clerk who staples slips into tidy folders. ZORDER BY account_id arranges folders so all of Rajesh's slips sit together, so a search for Rajesh opens only his drawer. The index card on each folder lists the smallest and largest date inside (min/max stats), so the clerk skips folders that cannot contain what you want. VACUUM shreds the old loose slips nobody references anymore.
Understanding the Concepts
What is the small-file problem that OPTIMIZE solves?
Streaming and frequent appends create many tiny files, and each file carries open-and-read overhead. A query touching thousands of small files spends more time on file bookkeeping than on actual data processing.
-- Run OPTIMIZE to compact small files into larger ones
OPTIMIZE delta.`/data/lake/transactions`;
-- Default target file size is around 1 GB (128 MB with auto-optimize)
-- The compaction is committed as a Delta transaction, so readers
-- never see a broken state during the rewrite
from delta.tables import DeltaTable
# Programmatic OPTIMIZE in PySpark
delta_table = DeltaTable.forPath(spark, "/data/lake/transactions")
delta_table.optimize().executeCompaction()
How does ZORDER BY work?
ZORDER BY is a multi-dimensional clustering that reorders data so rows with similar values in the Z-order columns land in the same files. This tightens the min/max ranges per file, making data skipping far more effective.
-- ZORDER on high-cardinality filter columns
OPTIMIZE delta.`/data/lake/transactions`
ZORDER BY (account_id, txn_date);
-- After ZORDER, files covering account SAV2001 have tight ranges:
-- File 1: account_id min=SAV2001, max=SAV2001, date min=Jan, max=Mar
-- File 2: account_id min=SAV2001, max=SAV2001, date min=Apr, max=Jun
-- A query for SAV2001 in February reads only File 1
# Programmatic ZORDER
delta_table.optimize().executeZOrderBy("account_id", "txn_date")
How does data skipping use min/max statistics?
When Delta writes a data file, it records per-file statistics in the transaction log: the minimum and maximum value of each column. When a query filters on a column, Delta checks each file's min/max and skips files whose range cannot match.
# Query with a filter on account_id
result = spark.read.format("delta") \
.load("/data/lake/transactions") \
.filter("account_id = 'SAV2001'")
# Delta checks each file's min/max for account_id:
# File A: min=AAA001, max=CUR999 -> SAV2001 outside range -> SKIP
# File B: min=SAV1000, max=SAV3000 -> SAV2001 in range -> READ
# File C: min=TRD001, max=TRD999 -> SAV2001 outside range -> SKIP
# Only File B is read!
result.explain() # Shows FileScan with partition filters and data filters
== Physical Plan ==
*(1) Filter (account_id = SAV2001)
+- *(1) ColumnarToRow
+- FileScan parquet [account_id,amount,txn_date]
DataFilters: [account_id = SAV2001]
PushedFilters: [EqualTo(account_id,SAV2001)]
ReadSchema: struct<account_id:string,amount:decimal(12,2),txn_date:date>
Files read: 1 of 50 <-- data skipping in action
What are auto-compaction and optimized writes?
These features reduce small files at write time rather than requiring a separate OPTIMIZE step.
# Enable auto-compaction: triggers compaction after writes
spark.conf.set("spark.databricks.delta.autoCompact.enabled", "true")
# Enable optimized writes: rebalances partitions before writing
spark.conf.set("spark.databricks.delta.optimizeWrite.enabled", "true")
# With both enabled, streaming writes produce fewer small files
df.writeStream \
.format("delta") \
.option("checkpointLocation", "/checkpoints/txns") \
.start("/data/lake/transactions")
How does VACUUM work?
VACUUM deletes data files that the transaction log no longer references and that are older than the retention window. The default retention is seven days, which protects in-flight readers and time travel.
-- Remove files older than 7 days that are no longer referenced
VACUUM delta.`/data/lake/transactions` RETAIN 168 HOURS;
-- WARNING: shrinking retention below 7 days risks breaking
-- in-flight readers and time travel queries
-- You must explicitly disable the safety check:
SET spark.databricks.delta.retentionDurationCheck.enabled = false;
VACUUM delta.`/data/lake/transactions` RETAIN 24 HOURS;
# Programmatic VACUUM
delta_table.vacuum(retentionHours=168)
When should you ZORDER vs partition?
ZORDER and partitioning serve different purposes. Use partitioning for low-cardinality columns and ZORDER for high-cardinality filter columns.
| Strategy | Best For | Example Column | Why |
|---|---|---|---|
| Partition BY | Low cardinality | date, region | Separate directories, efficient pruning |
| ZORDER BY | High cardinality | account_id, txn_id | Co-locates within files, tight min/max |
| Both | Combined filters | Partition by date, ZORDER by account_id | Best of both |
-- Partition by date (low cardinality) + ZORDER by account_id (high cardinality)
CREATE TABLE transactions
USING DELTA
PARTITIONED BY (txn_date)
AS SELECT * FROM raw_transactions;
OPTIMIZE transactions ZORDER BY (account_id);
What is a typical maintenance schedule?
At the National Bank of India, the maintenance schedule for the transaction Delta table ran nightly.
# Nightly maintenance job
def nightly_delta_maintenance(table_path):
delta_table = DeltaTable.forPath(spark, table_path)
# Step 1: Compact small files and cluster by filter columns
delta_table.optimize().executeZOrderBy("account_id")
# Step 2: Remove old unreferenced files
delta_table.vacuum(retentionHours=168)
# Step 3: Log metrics
history = delta_table.history(1).collect()[0]
print(f"Operation: {history['operation']}")
print(f"Files added: {history['operationMetrics'].get('numAddedFiles', 0)}")
print(f"Files removed: {history['operationMetrics'].get('numRemovedFiles', 0)}")
nightly_delta_maintenance("/data/lake/transactions")
What should a senior engineer know?
- OPTIMIZE and ZORDER rewrite files, which means older time-travel queries may reference files that no longer exist after VACUUM. Plan retention windows accordingly.
- ZORDER is most effective on high-cardinality columns that appear in WHERE clauses. Low-cardinality columns are better served by partitioning.
- Data skipping relies on column statistics, and by default Delta collects stats for the first 32 columns. Reorder your schema to put the most-filtered columns first.
- Auto-compaction adds write latency, so for latency-sensitive streaming use a separate scheduled OPTIMIZE instead.
- VACUUM is irreversible: once dead files are deleted, you cannot time-travel to versions that referenced them.
Visual Overview
Before OPTIMIZE (many small files) ┌────┐┌────┐┌────┐┌────┐┌────┐┌────┐┌────┐┌────┐ │1 MB││2 MB││500K││1 MB││3 MB││800K││1 MB││2 MB│ └────┘└────┘└────┘└────┘└────┘└────┘└────┘└────┘ After OPTIMIZE (compacted) ┌────────────────────────────────────────────────┐ │ ~1 GB (target size) │ └────────────────────────────────────────────────┘ After ZORDER BY account_id (clustered) ┌───────────────────┐ ┌───────────────────┐ │ File 1 │ │ File 2 │ │ acct: AAA..DDD │ │ acct: EEE..HHH │ │ min=AAA max=DDD │ │ min=EEE max=HHH │ └───────────────────┘ └───────────────────┘ Query: WHERE account_id = 'CCC' File 1: CCC in [AAA,DDD] -> READ File 2: CCC not in [EEE,HHH] -> SKIP
VACUUM Timeline ┌──────────────────────────────────────────┐ │ Day 1 Day 2 Day 3 ... Day 7 Day 8 │ │ files files files files VACUUM │ │ v1 v2 v3 v7 runs │ │ │ │ Unreferenced files older than 7 days │ │ are physically deleted by VACUUM │ └──────────────────────────────────────────┘
Key Takeaway
Delta Lake provides a set of maintenance and read-acceleration operations that every busy table needs. OPTIMIZE compacts many small files into fewer large ones, fixing the overhead that streaming and frequent appends create. ZORDER BY physically clusters rows sharing values in the chosen columns, tightening the per-file min/max ranges so data skipping can prune more files. VACUUM reclaims data files that the transaction log no longer references once they are older than the retention window.
The engine behind all of this is data skipping. Whenever Delta writes a file, it records per-file minimum and maximum statistics for each column in the transaction log. When a query filters on a column, Delta checks each file's min and max, and if the filter value falls outside a file's range, it skips that file entirely without reading a byte. ZORDER makes those ranges tight and non-overlapping on the columns people filter by, so the fraction of files skipped goes up dramatically.
At the National Bank of India, the card transaction stream produced thousands of tiny files daily, so a nightly OPTIMIZE with ZORDER on account_id compacted and clustered them. After that, a query for a single account's history read only the files whose min/max actually covered that account, and the rest were skipped. VACUUM ran with the default seven-day retention to reclaim dead files without breaking in-flight readers or recent time travel.
The senior perspective is to ZORDER on high-cardinality filter columns and partition on low-cardinality ones like date, to schedule OPTIMIZE as regular maintenance rather than waiting for reads to degrade, and to treat VACUUM retention as a deliberate choice that balances storage cost against time-travel and in-flight-reader safety.
Remember one sentence: OPTIMIZE compacts, ZORDER clusters, min/max statistics drive skipping, and VACUUM reclaims – and data skipping is the part that actually makes queries fast.
---