Querying Iceberg Tables with the DuckDB Spatial Extension

This guide shows how to read an Apache Iceberg table from DuckDB using the iceberg extension, decode its WKB geometry column with the spatial extension, and run a spatial query end to end — including catalog and snapshot metadata configuration for both metadata-file and REST-catalog access.

Context and prerequisites

Apache Iceberg (1.4+; these examples target a table written by Spark 3.5 with the 1.9.0 runtime) stores geometry as WKB in a binary column, since core Iceberg has no dedicated geometry type. DuckDB reads Iceberg through the iceberg extension, which parses the table’s metadata to find the current snapshot’s data files, and decodes geometry through the spatial extension. Use DuckDB 1.0 or later. This page belongs to DuckDB geospatial analytics on lakehouse tables; if your data is loose GeoParquet rather than an Iceberg table, the companion recipe how to run ST_Intersects in DuckDB on GeoParquet applies instead.

Complete working solution

import duckdb

con = duckdb.connect()

# 1. Load the three extensions: iceberg (table format), spatial (geometry), httpfs (S3)
con.execute("INSTALL iceberg; LOAD iceberg;")
con.execute("INSTALL spatial; LOAD spatial;")
con.execute("INSTALL httpfs; LOAD httpfs;")

# 2. S3 credentials for the warehouse bucket
con.execute("""
CREATE OR REPLACE SECRET s3_warehouse (
    TYPE S3, PROVIDER credential_chain, REGION 'us-east-1'
);
""")

# 3a. Direct metadata-file access: point iceberg_scan at the current metadata JSON.
#     Iceberg stores geometry as WKB in a BLOB column named "geom_wkb" here.
con.execute("""
CREATE VIEW parcels AS
SELECT
    parcel_id,
    zoning,
    ST_GeomFromWKB(geom_wkb) AS geom
FROM iceberg_scan(
    's3://warehouse/gis/parcels/metadata/00042-abc.metadata.json',
    allow_moved_paths = true
);
""")

# 4. Spatial query: total parcel area per zoning class within a bounding box.
#    ST_MakeEnvelope(xmin, ymin, xmax, ymax) builds the query window (EPSG:4326 coords).
result = con.execute("""
SELECT
    zoning,
    round(sum(ST_Area(geom)), 2) AS total_area,
    count(*)                     AS n
FROM parcels
WHERE ST_Intersects(
        geom,
        ST_MakeEnvelope(-122.42, 37.77, -122.40, 37.79)
      )
GROUP BY zoning
ORDER BY total_area DESC;
""").fetchall()

for zoning, area, n in result:
    print(f"{zoning:12s} area={area:12.2f}  parcels={n}")

con.close()

If you run a REST catalog (Nessie, Polaris, AWS Glue via the Iceberg REST spec), attach it instead of pointing at a metadata file, then query by table name:

con.execute("""
ATTACH 's3://warehouse/gis' AS gis_cat (
    TYPE ICEBERG,
    ENDPOINT 'https://catalog.example.com/api/catalog'
);
""")
con.execute("""
SELECT count(*) FROM gis_cat.parcels
WHERE ST_Contains(
    ST_MakeEnvelope(-122.5, 37.7, -122.3, 37.8),
    ST_GeomFromWKB(geom_wkb)
);
""")

Step-by-step walkthrough

1. Load all three extensions. iceberg reads the table format, spatial supplies ST_GeomFromWKB/ST_Area/ST_Intersects, and httpfs fetches bytes from S3. All three must be loaded in the same connection; missing iceberg yields a Catalog Error: Table Function "iceberg_scan" does not exist.

2. Resolve the snapshot. iceberg_scan with a metadata JSON path reads that file’s current-snapshot-id, walks the manifest list, and produces the set of live data files. To query an older snapshot for time-travel, pass snapshot_from_id or snapshot_from_timestamp as options — useful for reproducing a past spatial aggregate.

3. allow_moved_paths = true. Iceberg metadata records absolute data-file paths. If the table was copied or the warehouse prefix changed, this option lets DuckDB rebase those paths against the metadata location instead of failing on a stale absolute path.

4. Decode WKB. The geometry lives as raw WKB in a BLOB column. ST_GeomFromWKB(geom_wkb) turns it into a DuckDB GEOMETRY. Do this once in the view so downstream queries operate on real geometries.

5. Run the spatial predicate. ST_MakeEnvelope builds an axis-aligned rectangle from xmin, ymin, xmax, ymax, and ST_Intersects filters parcels overlapping that window. ST_Area then aggregates by zoning class. Because the geometry is decoded from the scanned column, the predicate runs per row after Iceberg has already pruned data files by partition.

Common errors and fixes

Verification

Confirm you are reading the expected snapshot and that geometry decoded correctly. Iceberg metadata functions expose the snapshot history, and a validity check catches WKB corruption.

# Which snapshot did we resolve, and how many data files?
meta = con.execute("""
SELECT count(*) AS files
FROM iceberg_metadata('s3://warehouse/gis/parcels/metadata/00042-abc.metadata.json');
""").fetchone()
print("data files in snapshot:", meta[0])

# Every decoded geometry should be valid
invalid = con.execute("""
SELECT count(*) FROM parcels WHERE NOT ST_IsValid(geom);
""").fetchone()[0]
assert invalid == 0, f"{invalid} invalid geometries decoded from WKB"
print("all geometries valid")

The read path above shows why Iceberg complements DuckDB’s spatial engine: partition pruning happens at the manifest stage before any geometry is decoded, so the WKB-to-GEOMETRY conversion and the ST_Intersects refinement only run on files the snapshot metadata already selected. To reduce the scan even further with bbox statistics, see predicate pushdown optimization; for how the geometry bytes are laid out in the first place, see GeoParquet encoding standards, and for how Iceberg and Delta compare for spatial storage, see Iceberg vs Delta Lake for spatial data. The authoritative references are the DuckDB Iceberg extension documentation and the Apache Iceberg table specification.

Why Reading the Table Beats Reading the Files

Pointing DuckDB at a table’s Parquet files with a glob is one line and works immediately, which is exactly why it is worth being explicit about what it gives up.

The bottom-right case is the one that surprises people who know the other three. Iceberg tracks columns by identifier rather than by name, so a renamed column is the same column to the table and a different one to a reader working from file schemas alone. The glob returns nulls for it, silently, and a downstream aggregate quietly becomes zero.

Where the extension is available, use it. Where it is not, plan the scan with a client that understands the table and hand DuckDB the resulting file list — that keeps three of the four guarantees, and the fourth can be recovered by applying the delete files explicitly.

Pushing Predicates Through the Boundary

Getting this split wrong in the common direction — sending only the geometry predicate — means the planner has nothing to prune with and DuckDB receives the whole table. Getting it wrong in the other direction, sending only the bounding-box filter, returns rows whose boxes overlap but whose geometries do not. The two halves are complementary rather than alternative, which is the same filter-then-refine structure that appears everywhere else in this section.

Operational Notes

Three practical points close this out, all of which come up on the first production deployment.

Snapshot pinning. Where a job runs for a while and issues several queries, pin the snapshot once and reuse it rather than resolving the current one each time. Otherwise a compaction between two queries in the same job can produce results that disagree with each other, which is a genuinely confusing bug to diagnose because both queries are individually correct.

Credential lifetime. Object-storage credentials configured on a connection do not refresh, and a long-running analysis session will fail partway through when they expire. Refresh explicitly on a timer, or scope sessions short enough that expiry is not reachable.

Result size. A spatial join against a lakehouse table can return more rows than expected — overlapping polygons multiply matches, and a bounding-box pre-filter without an exact test returns false positives. Write results to Parquet rather than materialising them in the client, and count before collecting.

None of these is specific to geometry, and all of them show up faster on spatial workloads because the data volumes and the join fan-out are larger than the scalar equivalents. Settling them once in a shared helper means every subsequent analysis inherits the correct behaviour. A helper is a few dozen lines and removes an entire category of first-week surprises. Write it once, import it everywhere, and the correctness properties come for free. A shared helper is also the natural place to record which snapshot a result came from, which turns a reproducibility question into a lookup.

Six months later, when somebody asks why two reports disagree, the recorded snapshot identifier answers it immediately. Without it, the reconciliation is guesswork against a table that has since changed. Record it with the result, not alongside it. A result without its snapshot is an observation without a timestamp. Treat the two as a single artefact and reproducibility follows for free.