Materializing Bbox Columns for Pushdown

This guide adds the four numeric bounding-box columns to an existing spatial table, places them where statistics will be collected, backfills history without a maintenance window, and verifies that file pruning actually improves.

Context and prerequisites

Every pruning mechanism in a lakehouse compares numbers. A geometry column is opaque to all of them, so a table without derived bounding-box columns cannot prune on location no matter how it is partitioned or sorted. This recipe works with Iceberg 1.4 or Delta 3.x on Spark 3.5; the mechanism is explained in predicate pushdown optimization, and the join-side benefit in reducing candidate pairs with bbox covering columns.

Where the columns must sit

The failure this prevents is entirely silent. A table with the columns present but positioned past the statistics limit has all the right data, all the right properties, and no file pruning whatsoever: the planner reads every file because it has no bounds to compare against. Nothing in the query, the plan or the result indicates it.

Complete working solution

-- Iceberg. Add the columns, then set metrics explicitly — the default truncates.
ALTER TABLE lakehouse.spatial.telemetry ADD COLUMN bbox_min_x DOUBLE AFTER h3_r5;
ALTER TABLE lakehouse.spatial.telemetry ADD COLUMN bbox_min_y DOUBLE AFTER bbox_min_x;
ALTER TABLE lakehouse.spatial.telemetry ADD COLUMN bbox_max_x DOUBLE AFTER bbox_min_y;
ALTER TABLE lakehouse.spatial.telemetry ADD COLUMN bbox_max_y DOUBLE AFTER bbox_max_x;

ALTER TABLE lakehouse.spatial.telemetry SET TBLPROPERTIES (
  'write.metadata.metrics.column.bbox_min_x' = 'full',
  'write.metadata.metrics.column.bbox_min_y' = 'full',
  'write.metadata.metrics.column.bbox_max_x' = 'full',
  'write.metadata.metrics.column.bbox_max_y' = 'full',
  'write.metadata.metrics.column.geom_wkb'   = 'none'
);

# Backfill one partition at a time. Idempotent; safe to re-run a failed partition.
from pyspark.sql import functions as F

def backfill_day(spark, table: str, day: str) -> None:
    src = spark.table(table).where(F.col("event_day") == day)
    filled = src.selectExpr(
        "*",
        "ST_XMin(ST_GeomFromWKB(geom_wkb)) AS new_min_x",
        "ST_YMin(ST_GeomFromWKB(geom_wkb)) AS new_min_y",
        "ST_XMax(ST_GeomFromWKB(geom_wkb)) AS new_max_x",
        "ST_YMax(ST_GeomFromWKB(geom_wkb)) AS new_max_y",
    ).drop("bbox_min_x", "bbox_min_y", "bbox_max_x", "bbox_max_y") \
     .withColumnRenamed("new_min_x", "bbox_min_x") \
     .withColumnRenamed("new_min_y", "bbox_min_y") \
     .withColumnRenamed("new_max_x", "bbox_max_x") \
     .withColumnRenamed("new_max_y", "bbox_max_y")

    (filled.sortWithinPartitions("bbox_min_x", "bbox_min_y")
           .writeTo(table).overwritePartitions())

# And in the write path, from now on — derived in the same expression as everything else.
enriched = raw.selectExpr(
    "*",
    "ST_XMin(ST_GeomFromWKB(geom_wkb)) AS bbox_min_x",
    "ST_YMin(ST_GeomFromWKB(geom_wkb)) AS bbox_min_y",
    "ST_XMax(ST_GeomFromWKB(geom_wkb)) AS bbox_max_x",
    "ST_YMax(ST_GeomFromWKB(geom_wkb)) AS bbox_max_y")

Step-by-step walkthrough

1. Add the columns before the geometry, not after it. AFTER clauses give explicit control over position; without them, added columns land at the end, which is exactly where they must not be on a wide table.

2. Set the metrics mode explicitly. The default is commonly a truncated mode that is useless for doubles, so a column with default metrics has bounds that no predicate can use. Setting the geometry column to none is equally deliberate: min/max of WKB bytes is meaningless and inflates the manifests.

3. Backfill per partition with an overwrite. Overwriting a partition is atomic and idempotent, so a failed partition can simply be re-run. Attempting a table-wide update instead makes a partial failure very awkward to reason about.

4. Sort during the backfill. The rewrite is happening anyway, so applying the sort order costs the shuffle once instead of requiring a second pass later.

5. Derive in the write path in the same expression chain as the geometry. Any arrangement in which the geometry is written by one step and the bounding box by another has a window where they can disagree.

Common errors and fixes

Verification

def assert_pruning(spark, table: str, predicate: str, max_fraction: float = 0.05):
    total = spark.sql(f"SELECT count(*) n FROM {table}.files").collect()[0]["n"]
    plan = spark.sql(f"SELECT count(*) FROM {table} WHERE {predicate}") \
                .queryExecution.executedPlan.toString()
    scanned = int(plan.split("numFiles=")[1].split(",")[0].strip(") "))
    assert scanned / total < max_fraction, (
        f"pruning regressed: {scanned}/{total} files scanned")

Wire the assertion into the pipeline that writes the table so a future schema change that pushes the columns past the statistics limit fails a build rather than a customer’s query. This is the specific regression the whole exercise exists to prevent, and it is invisible in every other kind of test.

Record the before-and-after file counts. They are the evidence that the backfill achieved something, and having them written down makes the same argument easy for the next table.

Making the Columns Easy to Use

Materialising the columns is half the job; the other half is ensuring queries actually reference them, because a column nobody filters on prunes nothing.

The table comment is the highest-value single line available, because it reaches a caller at the exact moment they are writing the query. Something as short as “filter bbox_min_x/max_x and bbox_min_y/max_y to prune; ST_Intersects alone reads the whole table” appears in every schema panel and prevents the most common mistake without any tooling at all.

The plan assertion is what catches the queries that ignore all of the above. Applied to scheduled jobs — which are where the sustained cost lives — it converts an invisible waste into a visible failure, and it is a dozen lines.

Cost and Timing of the Backfill

The backfill is a full rewrite of the partitions it touches, so its cost is a table copy and its duration is predictable from the table size and cluster.

Two things reduce it. Restricting the backfill to partitions that are actually queried is legitimate on a table with a long tail of cold history: filling the last two years and leaving the rest unfilled costs a fraction and captures nearly all the benefit, provided the query path tolerates null bounds by falling back to a scan for those partitions. Combining it with a compaction that was due anyway makes the rewrite serve two purposes for one cost.

The timing consideration is contention. Overwriting partitions conflicts with a streaming writer targeting the same ones, so backfill closed partitions and leave the current one until the write path has been updated to derive the columns natively. That ordering means the backfill never contends with ingest and the current partition acquires its bounds by ordinary writing rather than by rewrite.

Run the pruning assertion against a backfilled partition before continuing with the rest. A backfill that runs to completion and produces no pruning improvement — because of the statistics-window mistake — is a great deal of compute spent for nothing, and one partition is enough to find out.

Handling Null and Empty Geometry

A spatial table almost always contains rows whose geometry is missing, and the bounding-box columns need an explicit policy for them rather than whatever the derivation happens to produce.

Null geometry gives null bounds. This is the correct behaviour and it has a useful property: a null in any bound column means every range comparison evaluates to unknown, so the row is excluded from every spatial filter — which is what a row with no location should do. Nothing further is needed.

Empty geometry is the trap. An empty polygon has no coordinates, and different implementations return either nulls, zeros, or infinities for its bounds. Zeros are the dangerous case: they place the row at the origin off the coast of West Africa, where it will be returned by any query covering that area and will contribute a spurious point to every extent calculation. Normalise empty geometry to null at ingest, as the validation gate in geometry validation and repair does, and the case disappears.

A single point gives a degenerate box. Minimum equals maximum on both axes, which is correct and works fine in every range comparison. No special handling is needed, but a validation asserting min <= max should use a non-strict comparison or it will reject every point in the table.

Assert the policy rather than assuming it. A count of rows where the geometry is non-null and any bound is null, and a count where the bounds are zero but the geometry is not at the origin, are two cheap checks that catch both failure modes and take seconds on any table. Both belong in the same scheduled audit as the pruning assertion, since all three answer the same underlying question about whether the table still supports the layout it was designed for. Running them together, on a schedule, keeps the answer current rather than remembered. A remembered answer is one nobody can act on.