Optimizing Delta Tables: OPTIMIZE and Z-ORDER

As Delta tables grow in production environments, maintaining optimal query performance becomes increasingly challenging. Small files accumulate from frequent writes, and data layout becomes suboptimal for common query patterns. Delta Lake provides two powerful commands—OPTIMIZE and Z-ORDER—that address these issues through file compaction and intelligent data clustering.

Understanding the Small File Problem

Delta Lake's ACID transaction support and append-only architecture create a natural tendency toward file fragmentation. Each write operation, whether from batch jobs or streaming pipelines, generates new Parquet files. Over time, this leads to:

• Metadata overhead: Reading thousands of small files requires significant I/O operations just to process file metadata

• Inefficient parallelization: Query engines struggle to distribute small files efficiently across executors

• Reduced data skipping effectiveness: Statistics at the file level become less useful when data is scattered across many files

A table with 10,000 files of 10 MB each performs significantly worse than the same data consolidated into 100 files of 1 GB each. The OPTIMIZE command addresses this directly.

The OPTIMIZE Command: File Compaction

The OPTIMIZE command performs bin-packing to combine small files into larger, more efficiently sized files. This process rewrites data files while preserving the transaction log integrity.

Basic Syntax

-- Optimize entire table
OPTIMIZE delta_table;

-- Optimize specific partition
OPTIMIZE delta_table WHERE date = '2025-12-01';

Configuration and Performance Metrics

The target file size is controlled by the delta.targetFileSize table property (default: 1 GB). For streaming workloads with frequent small writes, setting this appropriately is critical:

ALTER TABLE delta_table SET TBLPROPERTIES (
  'delta.targetFileSize' = '134217728'  -- 128 MB for streaming
);

Real-world performance improvements from OPTIMIZE operations:

• Query latency reduction: 40-60% faster queries on tables with severe fragmentation

• Metadata processing: Up to 80% reduction in time spent reading file metadata

• Cloud storage costs: Fewer API calls to object storage (S3, ADLS, GCS)

A production e-commerce table with 15,000 small files (avg 8 MB) was optimized to 150 files (avg 800 MB), reducing typical aggregate queries from 45 seconds to 18 seconds—a 60% improvement.

Z-ORDER: Multi-Dimensional Clustering

While file compaction addresses the quantity of files, Z-ORDER addresses their internal organization. Z-ORDER is a technique that co-locates related data within the same file set, maximizing the effectiveness of Delta Lake's data skipping.

How Z-ORDER Works

Z-ORDER uses a space-filling curve (Z-curve or Morton curve) to map multi-dimensional data into a single dimension while preserving locality. When you Z-ORDER by multiple columns, data with similar values across those dimensions is stored physically close together.

OPTIMIZE delta_table
ZORDER BY (customer_id, event_date);

This command reorganizes data so that queries filtering on customer_id, event_date, or both benefit from enhanced data skipping.

Data Skipping in Action

Delta Lake maintains min/max statistics for each data file. When a query includes filters, the engine uses these statistics to skip entire files that cannot contain matching data. Z-ORDER maximizes this effectiveness:

Without Z-ORDER:

• Query: SELECT * FROM events WHERE customer_id = 12345 AND event_date = '2025-12-01'

• Files scanned: 450 out of 500 files

• Data skipping effectiveness: 10%

With Z-ORDER on (customer_id, event_date):

• Query: Same query

• Files scanned: 45 out of 500 files

• Data skipping effectiveness: 91%

The performance impact is substantial. A financial services company reduced query times on a 5 TB transaction table from 3 minutes to 22 seconds by implementing Z-ORDER on account_id and transaction_date.

Choosing Z-ORDER Columns

Selecting the right columns for Z-ORDER requires understanding your query patterns:

1. High cardinality columns: Low cardinality columns (e.g., status with 3 values) provide minimal benefit

2. Frequently filtered columns: Columns used in WHERE clauses benefit most

3. Column order matters: Place the most selective column first when possible

4. Limit to 3-4 columns: Z-ORDER effectiveness diminishes beyond 3-4 dimensions

-- Good: High cardinality, frequently queried together
OPTIMIZE transactions
ZORDER BY (user_id, transaction_date, merchant_id);

-- Poor: Low cardinality, rarely queried together
OPTIMIZE transactions
ZORDER BY (status, is_refunded);

Streaming Ecosystem Integration

In streaming architectures, OPTIMIZE and Z-ORDER play complementary roles to batch optimization strategies. Streaming workloads generate small files continuously, making regular optimization essential.

Structured Streaming and Auto-Optimize

Delta Lake supports auto-optimization features that work seamlessly with Spark Structured Streaming:

# Enable auto-optimize for streaming writes
spark.conf.set("spark.databricks.delta.properties.defaults.autoOptimize.optimizeWrite", "true")
spark.conf.set("spark.databricks.delta.properties.defaults.autoOptimize.autoCompact", "true")

streaming_df.writeStream \
  .format("delta") \
  .outputMode("append") \
  .option("checkpointLocation", "/checkpoints/events") \
  .trigger(processingTime="5 minutes") \
  .start("/delta/events")

Auto-optimize strategies:

• Optimize Write: Reduces small file creation during writes by shuffling data

• Auto Compaction: Runs OPTIMIZE automatically after writes when fragmentation thresholds are exceeded

Scheduled Optimization for Streaming Tables

For high-throughput streaming tables, scheduled optimization jobs provide better control:

-- Run hourly for recent partitions
OPTIMIZE streaming_events
WHERE event_hour >= current_timestamp() - INTERVAL 24 HOURS;

-- Run daily with Z-ORDER for historical partitions
OPTIMIZE streaming_events
WHERE event_date = current_date() - INTERVAL 1 DAY
ZORDER BY (user_id, event_type);

Governance and Visibility

Managing optimization across multiple streaming pipelines requires visibility into table health and performance. Governance platforms provide capabilities for data streaming platforms, offering:

• Table health monitoring: Track file counts, average file sizes, and fragmentation metrics across Delta tables

• Optimization audit trails: Monitor when OPTIMIZE operations run and their impact on query performance

• Policy enforcement: Set organization-wide standards for optimization schedules and Z-ORDER configurations

• Cross-platform visibility: Unified view of Delta tables across different compute engines (Spark, Flink, Trino)

This governance layer becomes essential in enterprises with dozens of data engineering teams managing hundreds of Delta tables.

Best Practices and Operational Considerations

Optimization Scheduling

Running OPTIMIZE during peak query hours can impact performance and costs. Recommended approaches:

-- Partition-aware optimization (processes only recent data)
OPTIMIZE events
WHERE date >= current_date() - INTERVAL 7 DAYS;

-- Time-windowed execution (runs during off-peak hours)
-- Schedule via cron/Airflow during 2-6 AM UTC

Monitoring Optimization Impact

Track these metrics to measure effectiveness:

-- Check table file statistics
DESCRIBE DETAIL delta_table;

-- View optimization history
DESCRIBE HISTORY delta_table;

-- Analyze data skipping effectiveness (Databricks)
SELECT * FROM (
  SELECT input_file_name() as file, count(*) as records
  FROM delta_table
  WHERE date = '2025-12-01'
  GROUP BY 1
);

Cost Considerations

OPTIMIZE operations rewrite data, incurring compute and storage costs:

• Compute: Rewriting 1 TB typically costs $2-5 depending on cluster configuration

• Storage: Temporary 2x storage until old files are vacuumed (VACUUM command)

• ROI: Query performance improvements and reduced scanning typically offset costs within days

A data warehouse team reduced their monthly query costs by $12,000 while spending $1,500/month on scheduled optimization—an 8x return.

Advanced Techniques

Liquid Clustering (Delta 3.0+)

For workloads with evolving query patterns, Delta Lake 3.0 introduces Liquid Clustering as an alternative to Z-ORDER:

CREATE TABLE events (
  user_id BIGINT,
  event_date DATE,
  event_type STRING
)
USING DELTA
CLUSTER BY (user_id, event_date);

Liquid Clustering automatically maintains clustering without manual Z-ORDER commands, adapting to query patterns over time.

Bloom Filter Indexes

For high-cardinality columns with equality filters, combine Z-ORDER with Bloom filters:

CREATE BLOOMFILTER INDEX ON delta_table FOR COLUMNS (user_id);

OPTIMIZE delta_table
ZORDER BY (event_date, region);

This provides O(1) lookup performance for user_id while maintaining Z-ORDER benefits for other columns.

Summary

Optimizing Delta Tables through OPTIMIZE and Z-ORDER is essential for maintaining production performance at scale. OPTIMIZE addresses file fragmentation through intelligent compaction, while Z-ORDER enhances data skipping through multi-dimensional clustering. Together, they can reduce query latency by 50-90% on fragmented tables.

Key takeaways:

• Run OPTIMIZE regularly on high-write tables to combat fragmentation

• Apply Z-ORDER to high-cardinality columns used in common query filters

• Limit Z-ORDER to 3-4 columns for optimal effectiveness

• Enable auto-optimization for streaming workloads or schedule manual optimization during off-peak hours

• Monitor table health metrics and optimization history to measure ROI

• Consider governance platforms for enterprise-scale visibility

As Delta tables grow from gigabytes to petabytes, these optimization techniques transition from optional enhancements to operational necessities. The investment in regular optimization pays dividends through faster queries, reduced costs, and improved data platform reliability.

Sources and References

• Delta Lake Official Documentation - OPTIMIZE

• Databricks Z-ORDER Technical Documentation

• Delta Lake GitHub Repository

• Research Paper: "The Delta Lake Project: Building Reliable Data Lakes at Scale"

• Apache Spark Structured Streaming Guide

• Parquet File Format Specification

• Z-Order Curve (Morton Code) - Wikipedia