8.3 Compaction (OPTIMIZE), Z-Ordering, & Liquid Clustering (CLUSTER BY)

8.3 Compaction (OPTIMIZE), Z-Ordering, & Liquid Clustering (CLUSTER BY)

In big data architectures, the physical layout of files on cloud object storage dictates query performance and execution costs. As data pipelines ingest streaming micro-batches, append frequent batch deltas, or execute updates, Delta tables naturally accumulate millions of tiny Parquet files. This phenomenon—the small file problem—degrades query throughput by overwhelming the Spark driver with filesystem metadata listings and triggering inefficient storage I/O.

Azure Databricks provides three powerful physical optimization mechanisms: File Compaction (OPTIMIZE), Multi-Dimensional Clustering (ZORDER BY), and next-generation Liquid Clustering (CLUSTER BY).

---

1. The Small File Problem & Storage Inefficiencies

+-----------------------------------------------------------------------------------+
|                             THE SMALL FILE PROBLEM                                |
|                                                                                   |
|  10,000 Files x 1 MB (Unoptimized)           10 Files x 1 GB (OPTIMIZED)          |
|  - 10,000 HTTP GET metadata requests         - 10 HTTP GET requests               |
|  - Severe Spark driver listing overhead      - Vectorized sequential Parquet read |
|  - Poor compression ratios                   - Maximum compression & throughput   |
|  - High IOPS costs                           - Sub-second query planning          |
+-----------------------------------------------------------------------------------+

Root Causes of Small Files:

1. Streaming Ingestion: Near-real-time streaming pipelines writing micro-batches every few seconds generate thousands of small files per day.
2. Over-Partitioning: Partitioning tables by high-cardinality columns (e.g., timestamp, user ID) creates millions of subdirectories containing tiny files.
3. High Parallelism Writes: Spark jobs configured with hundreds of shuffle partitions (spark.sql.shuffle.partitions = 200) writing modest datasets generate 200 tiny files per commit.

---

2. File Compaction via the OPTIMIZE Command

The OPTIMIZE command executes a bin-packing algorithm to coalesce collections of small Parquet data files into larger, uniform files (targeting ~1GB by default, or auto-tuned between 128MB and 1GB based on table volume).

-- Compact all small files across the entire Delta table
OPTIMIZE sales_silver;

-- Target compaction to specific partitions to save compute resources
OPTIMIZE sales_silver 
WHERE order_date >= '2026-08-01' AND order_date <= '2026-08-26';

Operational Mechanics of OPTIMIZE

1. Identifies Candidates: The Spark driver scans the table metadata to find contiguous groups of Parquet files smaller than the target size (e.g., < 128MB).
2. Rewrites into Optimized Files: Spark worker tasks read the small files and rewrite them into coalesced ~1GB Parquet files.
3. Atomic Transaction: The driver writes an atomic commit log recording remove actions for the old small files and add actions for the new coalesced files.
4. Non-Blocking Execution: Readers querying the table during OPTIMIZE continue reading the old files without interruption due to snapshot isolation.

Auto-Compaction and Optimized Writes

Databricks provides automated settings to minimize small file generation during active writes:

• Optimized Writes: Coalesces data in memory across executors prior to writing, reducing the number of files generated per partition (SET spark.databricks.delta.optimizeWrite.enabled = true).
• Auto-Compaction: Automatically executes a lightweight compaction job immediately after an append write finishes (SET spark.databricks.delta.autoCompact.enabled = true).

---

3. Multi-Dimensional Data Skipping with ZORDER BY

While OPTIMIZE solves file size problems, queries filtering on non-partitioned columns must still scan every file. Z-Ordering is a technique that colocates related information along a multidimensional space-filling curve (the Morton Z-curve).

-- Run OPTIMIZE with Z-Ordering along high-cardinality query filter columns
OPTIMIZE customer_orders 
ZORDER BY (customer_id, order_date);

                      Z-ORDER SPACE-FILLING CURVE (2D)
                      
             order_date
                 ^  
                 |   (0,3)---(1,3)   (2,3)---(3,3)
                 |     |   /   |       |   /   |
                 |   (0,2)   (1,2)---(2,2)   (3,2)
                 |             |   /   |     
                 |   (0,1)---(1,1)   (2,1)---(3,1)
                 |     |   /   |       |   /   |
                 |   (0,0)   (1,0)---(2,0)   (3,0)
                 +---------------------------------> customer_id

How Z-Ordering Improves Data Skipping

• Z-ordering maps multi-dimensional column values into a 1D scalar value while preserving spatial locality.
• Data rows with similar customer_id and order_date values are grouped together inside the same physical Parquet files.
• The resulting minValues and maxValues ranges in the _delta_log become extremely tight and narrow. When a query filters by customer_id = 4500, Spark skips 95%+ of the files because their min/max boundaries do not overlap the filter value.

Z-Order Rules of Thumb & Limitations

• Column Count: Limit Z-Order to 1 to 4 columns. Adding more columns dilutes the clustering effectiveness along each individual dimension.
• Cardinality: Z-order is most effective on high-cardinality columns used frequently in WHERE clauses and JOIN conditions (e.g., user_id, device_id, transaction_timestamp).
• Maintenance Overhead: Z-Ordering is not incremental. When new data is appended to a partition, running OPTIMIZE ... ZORDER BY must read and rewrite all data in that partition from scratch, consuming significant compute.

---

4. Next-Generation Liquid Clustering (CLUSTER BY)

Liquid Clustering is the modern Databricks replacement for both traditional Hive-style directory partitioning and Z-Ordering. It provides flexible, fully incremental, and dynamically redefinable multi-dimensional data clustering.

-- Create a new Delta table with Liquid Clustering
CREATE TABLE events_liquid (
    event_id STRING,
    event_type STRING,
    user_id BIGINT,
    event_timestamp TIMESTAMP,
    payload STRING
)
USING DELTA
CLUSTER BY (event_type, event_timestamp);

-- Trigger incremental clustering on newly appended data
OPTIMIZE events_liquid;

Architectural Advantages of Liquid Clustering

+-------------------------------------------------------------------------+
|                   LIQUID CLUSTERING ARCHITECTURAL BENEFITS              |
+-------------------------------------------------------------------------+
|  1. INCREMENTAL CLUSTERING                                              |
|     - Clusters only new, unclustered files without rewriting the table  |
|     - Reduces OPTIMIZE execution time and cloud compute cost by 70%+   |
|                                                                         |
|  2. ELIMINATES HIVE PARTITIONING PITFALLS                              |
|     - Avoids over-partitioning, small-file sprawl, and data skew        |
|     - Works seamlessly on both low-cardinality and high-cardinality keys|
|                                                                         |
|  3. DYNAMIC CLUSTERING KEY EVOLUTION                                    |
|     - Change clustering keys on the fly without rewriting history       |
|     - ALTER TABLE events_liquid CLUSTER BY (user_id, event_timestamp);  |
|                                                                         |
|  4. CONCURRENCY RESILIENCE                                              |
|     - Concurrent writes target clustered ranges with minimal conflicts  |
+-------------------------------------------------------------------------+

---

5. Deletion Vectors: Merge-on-Read for Row-Level Changes

Clustering decides where rows live. Deletion vectors decide what happens when rows are removed or changed, and the DP-750 blueprint lists them alongside liquid clustering and Z-ordering as part of the clustering strategy bullet.

Without deletion vectors, modifying a single row forces Delta Lake to rewrite the entire Parquet file containing that record - the copy-on-write penalty. Deletion vectors instead mark the affected rows as changed in metadata, and readers apply those entries at query time to resolve the current table state. This accelerates DELETE, UPDATE, and MERGE on both Delta Lake and Apache Iceberg tables.

Enabling Deletion Vectors

-- Delta Lake
CREATE TABLE prod_retail.silver.orders (order_id BIGINT, status STRING)
  TBLPROPERTIES ('delta.enableDeletionVectors' = true);

ALTER TABLE prod_retail.silver.orders
  SET TBLPROPERTIES ('delta.enableDeletionVectors' = true);

-- Iceberg tables use their own property
ALTER TABLE prod_retail.silver.orders_iceberg
  SET TBLPROPERTIES ('iceberg.enableDeletionVectors' = true);

Workspace settings can auto-enable deletion vectors on new tables created with a SQL warehouse or Databricks Runtime 14.3 LTS and above, and defaults vary by region. All Apache Iceberg v3 tables include deletion vectors by default, whereas Delta Lake tables must have them enabled explicitly.

You cannot ALTER a materialized view or a streaming table to add or remove deletion vectors - that has to be decided in the CREATE TABLE statement. And once deletion vectors are enabled on a materialized view or streaming table, the table protocol cannot be downgraded even if you later turn them off.

Runtime and Protocol Consequences

The protocol upgrade is the operational trap: enable deletion vectors on a table that an external Iceberg v2 reader or an old OSS Delta client consumes, and that consumer breaks. In Databricks Runtime 14.1 and above you can drop the deletion vectors table feature to restore compatibility.

Turning Soft Deletes into Physical Deletes

Deletion vectors are soft deletes - the rows are still inside the Parquet files. To physically rewrite those files, do one of:

• Run OPTIMIZE on the table
• Run REORG TABLE ... APPLY (PURGE), which rewrites every data file containing deletion vector changes
• Trigger a write with auto-compaction, which rewrites files carrying a deletion vector

File compaction gives no strict guarantee that every deletion vector change is applied, because some target files may not be compaction candidates. For a GDPR or CCPA hard deletion, the two-step sequence is mandatory:

-- 1. Physically rewrite files that carry soft-deleted rows
REORG TABLE prod_retail.silver.customers APPLY (PURGE);

-- 2. Remove the pre-purge file versions (see Section 8.4 for the retention guardrail)
VACUUM prod_retail.silver.customers;

On very large tables, set spark.databricks.delta.reorg.purgeMode to rows so the purge only touches files that actually contain soft-deleted rows. The default value all also scans every Parquet footer for dropped column data, which is slow on wide tables.

Predictive I/O connection. On Photon-enabled compute, Databricks uses deletion vectors to power predictive I/O for updates, which is why MERGE-heavy silver tables see the largest gains.

---

6. Architectural Comparison Matrix