Delta Lake Liquid Clustering Benchmark

Liquid Clustering in Delta Lake is designed to replace traditional Hive-style partitioning and Z-ordering, offering dynamic data layout optimization. This blog post explores its impact on upsert (MERGE) performance using a real-world telematics dataset.

Benchmark Environment

• Processor: 12th Gen Intel Core i3-1215U
• RAM: 31.13 GB
• OS: Linux (6.17.0-35-generic)
• Spark Version: 3.5.8
• Delta Lake Version: 3.3.2
• Python: 3.11.15

Baseline Benchmark Results

Before applying Liquid Clustering, we established a baseline using standard Delta Lake over 10 folds of upsert operations on the telamatics (basically time series of vehicle movements) table. The core MERGE operation was executed using the Delta Lake Python API:

delta_table.alias("target").merge(
    df_upsert.alias("source"), "target.ID = source.ID"
).whenMatchedUpdateAll().whenNotMatchedInsertAll().execute()

Dataset Profile:

• Total Rows: 515,411,694
• Size on Disk: 9.83 GB
• Number of Files: 61
• Average File Size: 165.07 MB

Liquid Clustering Results

After running ALTER TABLE delta.<table_path> CLUSTER BY (ID) and issuing an OPTIMIZE command, we performed the exact same 10-fold upsert test. The optimization step reorganizes the data into a clustered layout that significantly accelerates point lookups and merges.

# Enabling Liquid Clustering on 'ID'
spark.sql(f"ALTER TABLE delta.`{table_path}` CLUSTER BY (ID)")

# Applying clustering to all existing data
spark.sql(f"OPTIMIZE delta.`{table_path}` FULL")

Analysis

The Liquid Clustering median merge time dropped to 55.33 seconds compared to the baseline’s 65.58 seconds—an improvement of roughly 15.6%.

Note: The first upsert iteration in the clustered benchmark took ~140 seconds due to the initial overhead of Spark context initialization and query planning, but the subsequent runs immediately stabilized at lower execution times than the baseline.

Under the Hood: Liquid Clustering Mechanics

What is Liquid Clustering?

Traditionally in Delta Lake, achieving fast lookups required Hive-style Partitioning (which creates a physical directory for every partition key) and Z-Ordering (which sorts data within those directories). This approach breaks down when clustering by high-cardinality columns (like an ID), which creates millions of tiny files and overwhelms the storage system.

Liquid Clustering replaces both mechanisms. It dynamically clusters related data together into the same Parquet files on write, without creating rigid folder boundaries. Because the clustering is “liquid”, it adapts over time to prevent data skew and allows clustering on high-cardinality keys that were previously impossible to partition by.

Spark History Server Insights

The Spark History Server profiling revealed why the OPTIMIZE command requires a significant initial time investment:

• The OPTIMIZE delta.<table_path> command dominated the runtime. The History Server logged multiple CPU-intensive stages taking 400+ seconds.
• During these stages, Spark is executing complex ForkJoinTask operations to read the unoptimized Parquet files, map them into the new clustered layout based on the ID, and rewrite them.
• Once optimized, however, the subsequent MERGE operations become highly efficient, dropping to ~53 seconds per fold.

Delta Table Protocol Upgrades

When ALTER TABLE ... CLUSTER BY was executed, Delta Lake quietly upgraded the table’s capabilities:

• minWriterVersion Bump: The table’s minWriterVersion was upgraded to 7. Liquid Clustering is an advanced feature that requires version 7 to support the internal metadata structures used for tracking dynamic clustering keys.
• Efficient Merges: The transaction log (_delta_log) shows a sequence of MERGE operations with matchedPredicates -> [{"actionType":"update"}]. Because the data is liquid clustered by ID, Delta’s query engine doesn’t have to scan the entire table to evaluate the merge predicate—it uses the clustering metadata to instantly identify the exact Parquet files containing the target ID and skips the rest.

The Two-Pass Secret: Broadcast + Liquid Clustering

While an incremental OPTIMIZE reduced merge times to 55 seconds, combining a strict OPTIMIZE FULL with a Spark broadcast() hint led to an astonishing drop down to 8.8 seconds.

How does this work? It turns out a Delta Lake MERGE executes in two distinct passes:

1. Pass 1 (File Pruning / scanTimeMs): Spark does an Inner Join between the incoming payload and the target table to identify which Parquet files contain the matching ID.
2. Pass 2 (File Rewriting / rewriteTimeMs): Spark performs a Full Outer Join to actually merge the new rows into the affected Parquet files and rewrite them.

Without a broadcast hint, Spark defaults to a Sort Merge Join for Pass 1. Even with Liquid Clustering perfectly organizing the data, sorting and merging a 1.3 GB file to find a single matching ID takes immense CPU time. In our non-broadcast run, Delta logged a crippling scanTimeMs of 55.4 seconds just to find the match!

By wrapping the source dataframe in a broadcast() hint, Spark happily applies it to Pass 1, replacing the Sort Merge Join with a lightning-fast Broadcast Hash Join. It instantly streams the 1.3 GB file through memory against the 1-row payload (preventing a Sort Merge Join across the massive 9.8 GB table). In our broadcast run, Delta logged a scanTimeMs of only 7.3 seconds—completely eliminating the 55-second bottleneck.

from pyspark.sql.functions import broadcast

delta_table.alias("target").merge(
    broadcast(df_upsert).alias("source"), "target.ID = source.ID"
).whenMatchedUpdateAll().whenNotMatchedInsertAll().execute()

During Pass 2, Spark correctly falls back to a Sort Merge Join (since full outer joins cannot be broadcasted). However, because Pass 1 was so efficient and Liquid Clustering perfectly pruned the files, Pass 2 only has to rewrite a microscopic fraction of the table.

Neither optimization achieves sub-10 seconds on its own. The Broadcast Hint eliminates the 55-second Sort Merge Join bottleneck for file discovery, and Liquid Clustering drastically minimizes the volume of data rewritten in Pass 2!

Why not rely on Adaptive Query Execution (AQE)?

Spark’s Adaptive Query Execution (AQE) is incredibly smart for standard SELECT queries. If you have spark.sql.autoBroadcastJoinThreshold set (default is 10MB), AQE will automatically detect a tiny 1-row or 10-row payload and convert a slow SortMergeJoin into a lightning-fast BroadcastHashJoin.

However, this does not work automatically for Delta Lake MERGE operations.

Because a MERGE isn’t a standard join, Delta intercepts the Spark Catalyst planner and creates a specialized, complex two-pass execution plan. This custom plan often confuses Spark’s standard AQE logic, causing it to fail to accurately estimate the payload size mid-execution. As a result, Spark “plays it safe” and defaults to a catastrophic SortMergeJoin across the entire target table—even if your payload is just a few bytes.

To achieve sub-10 second upserts in Delta, you must manually wrap your source dataframe with the broadcast() hint.

The Shattering Effect: Write Amplification and 1GB Target File Sizes

While the broadcast hint and Liquid Clustering drop the median time to 8.8 seconds, there is a glaring anomaly in the broadcast benchmark: the first fold took 85 seconds.

Often, initial execution spikes are blamed entirely on JVM Warmup and Catalyst Whole-Stage Code Generation. While compilation does take significant time on the first pass, diving into the Delta History logs (DESCRIBE HISTORY) reveals the true “elephant in the room”: massive write amplification.

When OPTIMIZE FULL runs in Open Source (OSS) Delta Lake, it targets a default file size of 1 GB (similar to behavior noted in our OPTIMIZE, ZORDER, and VACUUM analysis). The OPTIMIZE command generated several Parquet files ranging from 1.1 GB to 1.3 GB in size.

Because Parquet files are immutable, inserting a single row means Delta must rewrite the entire file. Here is what happens under the hood during the first two MERGE operations:

1. The First MERGE (85 seconds): The new row landed within one of those massive 1.3 GB Z-cube boundaries. Delta had to read the entire 1.3 GB file into memory, insert the row, and write the data back out. However, MERGE does not enforce the 1 GB target size like OPTIMIZE does. Operating through standard Spark shuffle partitions (default 200), it took that 1.3 GB of data and shattered it into 50 smaller files (~26 MB each). The history log confirms this: numTargetBytesRemoved: 1.3 GB, numTargetBytesAdded: 1.3 GB, numTargetFilesAdded: 50.
2. The Second MERGE (11 seconds): The next row inserted landed in the exact same Z-cube boundary. But this time, the massive 1 GB file had already been shattered into 50 smaller files. Delta only had to touch one of those newly created 26 MB files! The log reflects this massive drop: numTargetBytesRemoved: 28 MB, numTargetBytesAdded: 28 MB, numTargetFilesAdded: 4. The duration dropped from 85 seconds to a mere 11 seconds.

The Takeaway: Liquid Clustering and OPTIMIZE crave massive 1 GB files to maximize read speeds for analytical workloads. However, these massive files introduce extreme write amplification for point MERGEs. If you require consistent, predictable latency even on the very first upsert, you must trade some read performance for write performance by lowering the target file size (e.g., configuring spark.databricks.delta.optimize.maxFileSize = 134217728 in your Spark Session to force ~128 MB files).

The Alternative: Broadcast-Only (Unclustered)

If broadcasting a 1-row upsert removes the scan bottleneck, do we even need Liquid Clustering? We ran the exact same broadcast benchmark on an Unclustered version of the table to find out.

Surprisingly, for a pure single-row upsert, the unclustered table achieved a ~7.9s median—slightly faster than the Liquid Clustered table (~8.8s). Why? Because a Broadcast Hash Join evaluates the entire table in-memory regardless of clustering. The Liquid Clustered table inherently carries a larger metadata payload (tracking clustering keys across hundreds of files) and potentially touches more files during the rewrite pass if the table is heavily fragmented. For raw single-row write throughput, a broadcast join on an unpartitioned table is a highly potent and simple strategy.

The Trade-Off: Write Throughput vs. Analytical Reads

So why use Liquid Clustering? The answer lies in multi-row operations and analytical read queries.

Multi-Row Distributed Upserts

We sampled 10 rows distributed randomly across the dataset and ran a single MERGE operation for all 10 rows using the broadcast hint.

Both strategies degraded significantly (from ~8s to ~24-27s). While the broadcast join found the matches instantly, the 10 rows landed in multiple different Parquet files (up to 10 files). Spark had to rewrite each of those affected files. File rewrite physics dominate the MERGE operation.

Analytical Read Performance

This is where Liquid Clustering asserts its dominance. We ran an analytical query aggregating data by truck_id, filtered by a specific ID range (the clustering column):

SELECT truck_id, COUNT(*) as cnt, AVG(speed) as avg_speed
FROM delta.`{table_path}`
WHERE ID BETWEEN 606882862 AND 621282078
GROUP BY truck_id
ORDER BY cnt DESC
LIMIT 10

Liquid Clustering delivered a 3x performance improvement on read queries. By dynamically clustering the data, Delta’s query engine skips reading massive swaths of irrelevant Parquet files entirely, resulting in incredibly fast analytical lookups.

The Deletion Vector Illusion: When Delta Chooses Not to Use Them

To combat the write amplification caused by massive 1 GB files, Delta Lake introduced Deletion Vectors (DVs) (supported for MERGE in OSS Delta 3.1.0+). Deletion Vectors promise to eliminate file rewrites by writing a tiny sidecar file that logically marks rows as “deleted” or “updated” without touching the underlying 1 GB Parquet file.

We enabled Deletion Vectors (delta.enableDeletionVectors = true) and ran our 10-row MERGE test again. The result? Write Amplification was NOT eliminated. Delta still removed 8 massive Parquet files and created 8 new ones.

Why did it bypass Deletion Vectors?

1. The whenNotMatchedInsertAll() Trap: A Deletion Vector is purely a “tombstone” for deleted rows. It cannot store new data. If your MERGE payload contains rows that don’t exist in the target table (triggering an INSERT), Delta is forced to create a new physical Parquet file. While it theoretically could use a DV for the updated rows and a new file for the inserts, the Catalyst optimizer often takes the safest route during complex MERGE plans and simply rewrites the base files entirely.
2. The “Full File” Tombstone Exception: To prove DVs worked, we ran a pure DELETE operation targeting two of the IDs we just upserted. The query finished in a blazing 7.9 seconds, but the history log showed DVs Added: 0 and Files Removed: 2. Why? Because our previous MERGE had shattered the 1 GB file into tiny 1-row Parquet files. When we deleted those specific IDs, Delta realized they were the only rows in their respective files. Instead of writing a Deletion Vector, it took the most optimal path: it permanently tombstoned the entire Parquet file.

So, when are Deletion Vectors actually used?

Deletion Vectors shine in a very specific, but common, scenario: Targeted updates or deletes buried deep inside massive files.

If we execute an UPDATE or DELETE on an ID that sits in the middle of a 1 GB Z-cube—alongside millions of other rows that are not changing—Delta cannot simply delete the file without losing the innocent rows. Without DVs, it must read the 1 GB file into memory, modify the single row, and write 1 GB back out. With DVs enabled, Delta leaves the 1 GB file completely untouched, writes a microscopic JSON sidecar identifying the deleted row, and moves on in seconds.

To prove this, we executed a pure DELETE on a single ID (235494431) that was buried inside a ~130 MB Parquet file from our OPTIMIZE FULL pass:

=== HISTORY LOG: VERIFYING DELETION VECTORS ===
Version: 12 | Operation: DELETE
  Files Removed: 0
  Files Added: 0
  DVs Added: 1
  Rows Deleted: 1

The metrics are perfect: Files Removed: 0 and Files Added: 0. Delta completely bypassed rewriting the massive Parquet file. Write amplification was perfectly neutralized by the single DVs Added: 1 sidecar file!

Conclusion

Liquid Clustering is not a magic wand that eliminates all upsert latency. A MERGE operation is fundamentally bound by file pruning (Pass 1) and file rewriting (Pass 2).

• For Single-Row Streaming Upserts: A simple broadcast() hint on an unclustered table can eliminate the file pruning bottleneck and deliver sub-10 second merges with minimal configuration.
• For Analytics and Heavy Lookups: Liquid Clustering is indispensable. It trades a slight metadata overhead during writes for massive (3x+) performance gains on analytical queries.
• The Golden Rule: Always monitor your target file sizes. Massive files are great for reads but introduce crippling write amplification. Use spark.databricks.delta.optimize.maxFileSize to strike the right balance between read speed and write latency.