#Querying GeoParquet in S3 with DuckDB

To query GeoParquet in S3 with DuckDB efficiently, load the httpfs extension, set s3_region and credentials from the environment, and point read_parquet at a Hive-partitioned glob — then push a partition predicate, a bbox filter, and a narrow projection into the same query so DuckDB reads a small fraction of the dataset instead of the whole bucket. Turning on enable_object_cache makes repeated queries fast by reusing fetched footers. This page is the remote-data companion to the DuckDB spatial extension for GeoParquet guide; the mechanics of ST_ functions and bbox pushdown are covered there, and here the focus is the S3 read path: configuration, glob patterns, bytes scanned, and time-to-first-byte.

#Quick-Reference Table

#How the S3 Read Path Works

DuckDB does not download a GeoParquet file before querying it. Over httpfs, it issues HTTP range requests: first a small read of the Parquet footer to learn the schema and per-row-group statistics, then targeted reads for exactly the column chunks a query needs. The cost you feel on the first query — the time-to-first-byte — is dominated by the round trips to list the prefix and fetch footers, plus TLS negotiation. Everything after that is proportional to the bytes the query actually requires, which is why layout and pushdown matter more than raw bandwidth.

Three filters compound to keep bytes scanned small, and all three belong in the same query. A Hive partition predicate (WHERE region = 'eu') drops entire S3 prefixes before a single file is opened, so hive_partitioning => true on the glob is the coarsest and cheapest win. A bbox predicate on the bbox struct columns lets DuckDB compare each row group’s footer min/max against your window and skip groups that cannot overlap — effective only when the data was Hilbert-sorted before upload so nearby features share groups. Finally, a minimal projection uses column pruning to transfer only the columns you name. Managing bytes scanned this way is the core cost lever across the whole Query Engines & Cloud Analytics topic.

Glob patterns control how many objects DuckDB must consider. A broad s3://bucket/parcels/**/*.parquet forces a full recursive listing; a targeted s3://bucket/parcels/region=eu/year=2026/*.parquet narrows the listing and hands the partition values straight to the pruner. Prefer the most specific glob your query allows, and pair it with the partition predicate so DuckDB both lists less and reads less.

#Implementation

The function below configures S3 from the environment, globs a partitioned dataset, applies all three filters, and profiles the query so bytes read is observable. It is deliberately small — the remote-read essentials with nothing extra.

# Requires: duckdb>=0.10  (Python 3.10+)
from __future__ import annotations

import os

import duckdb


def query_s3_geoparquet(
    s3_glob: str,
    region_value: str,
    window: tuple[float, float, float, float],
    *,
    aws_region: str = "eu-west-1",
    columns: tuple[str, ...] = ("id", "name", "geometry"),
) -> tuple[list[tuple], int]:
    """Query partitioned GeoParquet in S3 and report bytes read.

    window is (min_x, min_y, max_x, max_y) in the dataset's CRS.
    Returns (rows, bytes_read).
    """
    if "AWS_ACCESS_KEY_ID" not in os.environ:
        raise RuntimeError("AWS_ACCESS_KEY_ID must be set in the environment")

    min_x, min_y, max_x, max_y = window
    con = duckdb.connect()
    try:
        con.execute("INSTALL httpfs; LOAD httpfs;")
        con.execute("INSTALL spatial; LOAD spatial;")
        con.execute(f"SET s3_region='{aws_region}';")
        con.execute("SET s3_access_key_id=getenv('AWS_ACCESS_KEY_ID');")
        con.execute("SET s3_secret_access_key=getenv('AWS_SECRET_ACCESS_KEY');")
        con.execute("SET enable_object_cache=true;")  # reuse footers across queries

        projection = ", ".join(columns)
        rows = con.execute(
            f"""
            SELECT {projection}
            FROM read_parquet(?, hive_partitioning => true)
            WHERE region = ?
              AND bbox.xmin <= ? AND bbox.xmax >= ?
              AND bbox.ymin <= ? AND bbox.ymax >= ?
              AND ST_Intersects(
                    ST_GeomFromWKB(geometry),
                    ST_MakeEnvelope(?, ?, ?, ?)
                  )
            """,
            [s3_glob, region_value,
             max_x, min_x, max_y, min_y,
             min_x, min_y, max_x, max_y],
        ).fetchall()

        stat = con.execute(
            "SELECT bytes_read FROM duckdb_profiling_output()"
        ).fetchone()
        bytes_read = int(stat[0]) if stat and stat[0] else 0
        return rows, bytes_read
    except duckdb.IOException as exc:
        raise RuntimeError(f"S3 read failed for {s3_glob}: {exc}") from exc
    finally:
        con.close()

#Validation

Confirm that pushdown is actually working before you trust the cost profile. Wrap the query in EXPLAIN ANALYZE and check that the reported bytes read is a small fraction of the dataset size, and that the partition predicate shows as a pruned scan rather than a full listing.

# Requires: duckdb>=0.10 — verify pushdown reduced the scan
con.execute("PRAGMA enable_profiling='json';")
plan = con.execute("""
    EXPLAIN ANALYZE
    SELECT id FROM read_parquet(
        's3://bucket/parcels/region=eu/*.parquet',
        hive_partitioning => true
    )
    WHERE region = 'eu'
      AND bbox.xmin <= 2.5 AND bbox.xmax >= 2.2
      AND bbox.ymin <= 48.9 AND bbox.ymax >= 48.8
""").fetchall()
print(plan[0][1])

Expected ranges on a well-laid-out, Hilbert-sorted dataset: for a small city-scale window against a continent-scale table, bytes read should land in the low tens of megabytes, not gigabytes, and the scan-to-return ratio should sit well under 5:1. If bytes read is close to the full partition size, the file was probably written without a covering bbox column or without Hilbert sorting — re-export it before blaming the query. The time-to-first-byte on a warm connection (object cache populated) should drop to a fraction of the cold first run.

#Edge Cases and Caveats

Cross-region reads. If s3_region does not match the bucket’s actual region, the first request incurs a redirect and every subsequent request pays cross-region latency and egress. Always set s3_region to the bucket’s home region, and if you query from compute in another region, expect both slower time-to-first-byte and higher egress cost — colocate the compute where you can. Egress economics compound quickly on large scans.

Many tiny partition files. Over-partitioning (for example, one directory per day per sensor) produces thousands of small Parquet files, and the per-file footer fetch dominates the query even though each file is small. DuckDB must still list and open every matching object. Coalesce to coarser partitions and larger files, leaning on row group sizing and bbox skipping for selectivity within each file rather than on directory explosion.

Stale object cache after a rewrite. enable_object_cache keys on the object path, so if a pipeline overwrites a file in place under the same key, a long-lived connection may serve stale footers. Use versioned or date-stamped object keys for rewritten datasets, or open a fresh connection after a bulk rewrite, so the cache never masks updated data.

#Frequently Asked Questions

Why is my first DuckDB query against S3 slow but the second one fast?

The first query pays the round trips to list the prefix and fetch each file’s Parquet footer, plus TLS setup — that is your time-to-first-byte. With enable_object_cache set to true, DuckDB keeps those footers and any fetched column chunks in memory, so a second query on the same files skips the footer round trips and starts reading data almost immediately. Keeping the connection alive across queries is what preserves the cache.

How do I stop DuckDB scanning the whole bucket for a small area query?

Combine three filters. Use a Hive partition predicate so entire prefixes are pruned before any file opens, filter on the bbox struct columns so non-overlapping row groups are skipped from footer statistics, and project a minimal column list so only the needed column chunks transfer. With all three, a small-area query reads a tiny fraction of the dataset rather than the full bucket.

Should I set S3 credentials in DuckDB or use the environment?

Prefer the environment or a DuckDB secret over hard-coded keys in SQL. Set s3_region explicitly to avoid a redirect round trip, and read AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY with getenv so credentials never appear in query text or logs. For production, a scoped IAM role delivering temporary credentials is safer than long-lived keys.

#Related

• DuckDB Spatial Extension for GeoParquet — parent guide: extensions, ST_ functions, joins, and export
• Query Engines & Cloud Analytics for GeoParquet — the full engine landscape and bytes-scanned cost model
• AWS Athena Spatial Queries on GeoParquet — the serverless alternative for the same S3 data
• Row Group Sizing Strategies for Parquet — sizing that governs skip granularity on remote reads
• Spatial Partitioning with Quadtree Indexes — upstream layout that makes bbox skipping effective

← Back to DuckDB Spatial Extension for GeoParquet