Enabling Liquid Clustering on Spatial Delta Tables

This guide converts a spatial Delta table from scheduled Z-order compaction to liquid clustering, choosing the clustering columns correctly for geometry, and verifying that pruning quality stops oscillating between maintenance runs.

Context and prerequisites

Classic Z-ordering is applied by an OPTIMIZE run and decays until the next one, so a continuously-appended spatial table’s query latency follows a sawtooth. Liquid clustering makes the ordering a table property maintained incrementally, flattening that curve. This recipe needs Delta 3.1 or later on a runtime that supports clustering; the ordering theory is in Z-ordering for geospatial queries, and the Delta-specific layout context in Delta Lake geometry handling.

What changes, and what does not

Two things do not change. The clustering columns are still numeric — a binary WKB column carries no spatial ordering, so clustering must be on the derived bounding-box columns or a grid cell identifier, exactly as with Z-order. And the statistics window still applies: columns outside dataSkippingNumIndexedCols have no min/max, and clustering them accomplishes nothing.

What changes is who maintains the order and when. Under liquid clustering the write path places new data with awareness of the existing layout, and incremental clustering rewrites only the files that need it, so the ordering never degrades far before it is repaired.

Complete working solution

-- Delta 3.1+. New table: clustering is declared, not applied per OPTIMIZE.
CREATE TABLE lakehouse.spatial.telemetry (
  asset_id     BIGINT,
  event_ts     TIMESTAMP,
  event_day    DATE,
  h3_r5        BIGINT,
  bbox_min_x   DOUBLE, bbox_min_y DOUBLE,
  bbox_max_x   DOUBLE, bbox_max_y DOUBLE,
  geom_wkb     BINARY
) USING DELTA
CLUSTER BY (bbox_min_x, bbox_min_y)
TBLPROPERTIES (
  'delta.dataSkippingNumIndexedCols' = '8',
  'delta.enableDeletionVectors'      = 'true'
);

# Converting an existing Z-ordered table, without a rewrite of history.
spark.sql("""
ALTER TABLE lakehouse.spatial.telemetry_legacy
CLUSTER BY (bbox_min_x, bbox_min_y)
""")

# Incremental clustering: no ZORDER clause, no column list, no full rewrite.
spark.sql("OPTIMIZE lakehouse.spatial.telemetry_legacy")

# Optional: bring historical files under the new layout, scoped and scheduled.
for day in recent_days(30):
    spark.sql(f"""
      OPTIMIZE lakehouse.spatial.telemetry_legacy
      WHERE event_day = DATE '{day}'
    """)

Existing data is not reorganised by the ALTER; the table becomes a mixture of layouts and queries plan across both. That is deliberate and it is what makes the conversion safe — the improvement arrives as data is naturally rewritten, and a backfill of history is optional and schedulable.

Step-by-step walkthrough

1. Cluster on derived columns, never on geometry. CLUSTER BY (geom_wkb) is accepted syntactically and orders rows by byte sequence, which has no spatial meaning whatsoever. Use the bounding-box minima, or a grid cell identifier where one exists.

2. Keep the clustering columns inside the statistics window. Setting dataSkippingNumIndexedCols to a value that comfortably covers them, and placing them early in the schema, is what makes the ordering usable by the planner. Clustering columns without statistics produce a tidy layout nobody can exploit.

3. Do not also partition on the same dimension. Partitioning by h3_r5 and clustering by the bounding box duplicates the spatial dimension across two mechanisms and produces a directory explosion for no gain. Partition on time, cluster on space.

4. Run OPTIMIZE without a ZORDER clause. Under liquid clustering the columns come from the table, and supplying them per run is both unnecessary and, on some runtimes, an error. The invocation becomes stable across every table.

5. Backfill history deliberately. The ALTER is instant and affects only new writes; bringing older partitions under the new layout is a scoped rewrite that should be scheduled against partitions no longer receiving writes.

Common errors and fixes

Verification

The third check is the one that justifies the change to whoever approved it. Liquid clustering rarely produces a better peak than a fresh Z-order run — it produces a predictable result, which means capacity planning works, dashboards do not mysteriously slow down on Thursdays, and a benchmark taken on any given day is representative rather than a function of when the last maintenance ran.

-- Track it daily; the shape of the series is the finding.
SELECT date_trunc('day', timestamp) AS d,
       sum((maxx - minx) * (maxy - miny)) /
       ((max(maxx) - min(minx)) * (max(maxy) - min(miny))) AS overlap_factor
FROM delta_file_stats('lakehouse.spatial.telemetry')
GROUP BY 1 ORDER BY 1;

When to stay with Z-ordering

Liquid clustering is not universally better, and two cases favour the older mechanism.

A table that is written once and read many times — a quarterly reference release, a static historical archive — gains nothing from incremental maintenance because there is no drift to correct. One Z-order run at load time produces an optimal layout that never degrades, and the simpler mechanism has broader runtime support.

A platform whose readers cannot all be upgraded is the second case. Clustering raises the table’s protocol requirements, and a reader that does not support them will either refuse the table or, on older connectors, behave incorrectly. Enumerate every engine that touches the table — including the ad-hoc sessions and the reporting tool nobody remembers configuring — before enabling it, because the failure mode of an unsupported reader is not always a clean error.

Choosing the Clustering Columns

Two columns is the right answer for almost every spatial table, and the choice between the available pairs matters more than it appears.

The right-hand option is the one most often chosen by mistake. On a table already partitioned by day, adding time to the clustering key spends a clustering dimension on a discrimination the partition already provides, and the spatial clustering is correspondingly weaker. Time belongs in the clustering key only when it is not in the partition key.

Between the first two, the deciding question is what callers actually write. A platform where every spatial query goes through a helper that supplies bounding-box predicates should cluster on the bounding box; one where callers filter on cell identifiers should cluster on the cell. Clustering on a column nobody filters is the most common way to do this work and see no benefit at all — the layout is correct and the planner has nothing to match it against.

Where both patterns exist, cluster on the bounding box: numeric range predicates are the more general form, and a cell filter can be translated into one, while the reverse translation is not always available.

Operating It Afterwards

Once enabled, the maintenance schedule changes shape rather than disappearing, and three habits keep it healthy.

Run OPTIMIZE more often and expect it to do less. Incremental clustering rewrites only the files that drifted, so a run against an active partition is small and fast. Hourly is reasonable on a streaming table where the old model would have run nightly, and the aggregate cost is usually lower because the work is spread rather than batched.

Scope it to the active partitions. Historical partitions that no longer receive writes are already clustered and running against them rewrites nothing at best and wastes I/O at worst. A predicate on the day column keeps every run bounded and keeps it clear of the ingest path.

Keep watching the overlap factor. Liquid clustering makes the ordering self-maintaining, not self-guaranteeing: an unusually large burst of writes, a failed maintenance run, or a partition receiving out-of-order backfill can all leave a region of the table poorly clustered. The metric described in spatial data observability is what tells you, and it costs nothing to collect.

The conversion is also a good moment to review the table’s other layout properties, because most of them were set once and never revisited. Statistics column limits, target file size, deletion vectors, retention window and the partition key itself all interact with clustering, and changing one in isolation is how a table ends up with a set of individually-reasonable settings that do not work together. Reviewing them together, once, at the moment the clustering model changes, is far cheaper than discovering the interaction from a query that has quietly got slower.

A short review checklist covers it: are the clustering columns inside the statistics window, is the partition key still coarse enough, is the target file size still appropriate for the current write volume, and does the retention window still exceed the longest in-flight job? Four questions, ten minutes, and they close the loop on a change that otherwise tends to be evaluated only by whether the ALTER succeeded.