Reading GeoParquet from object storage with DuckDB

This page wires a dbt project on DuckDB directly to GeoParquet in object storage: credentials from the environment, a source that reads remote files without staging them locally, partition pruning that keeps the scan small, and geometry decoding that produces a real geometry column rather than bytes.

When to use this approach

  • Your spatial data already lands in a bucket. Reading it in place removes an entire loading step and the storage duplication that comes with it.
  • The dataset is larger than the machine. DuckDB reads Parquet column by column and row group by row group, so a projection and a filter can touch a fraction of a file that would not fit in memory.
  • You want CI to read production-shaped data. A CI job can read a bucket path with no database to provision — the pattern behind DuckDB as a lightweight CI validator.

Prerequisites

  • dbt-duckdb ≥ 1.7 and DuckDB 0.10+ with the spatial and httpfs extensions.
  • Credentials for the bucket available as environment variables — never in profiles.yml literally.
  • GeoParquet files with geometry stored as WKB, which is what the specification mandates and what every producer emits.
  • A hive-style path layout if you intend to prune by partition; without it, every query reads every file’s footer.

Step-by-step instructions

1. Load the extensions and credentials in the profile

yaml
# profiles.yml
dbt_geospatial:
  target: duckdb_local
  outputs:
    duckdb_local:
      type: duckdb
      path: "{{ env_var('DBT_DUCKDB_PATH', 'local.duckdb') }}"
      extensions:
        - spatial
        - httpfs
      settings:
        s3_region: "{{ env_var('AWS_REGION', 'eu-central-1') }}"
        s3_access_key_id: "{{ env_var('AWS_ACCESS_KEY_ID') }}"
        s3_secret_access_key: "{{ env_var('AWS_SECRET_ACCESS_KEY') }}"
        memory_limit: '8GB'
        threads: 4

Verify the connection reaches the bucket before writing any model:

bash
dbt debug --target duckdb_local
duckdb -c "install httpfs; load httpfs; load spatial;
           select count(*) from read_parquet('s3://geo-lake/zones/*/*.parquet');"

2. Declare the remote files as a source

yaml
# models/sources.yml
sources:
  - name: geo_lake
    meta:
      external_location: "s3://geo-lake/{name}/year={{ var('load_year') }}/*.parquet"
    tables:
      - name: zones
      - name: trip_pings

dbt-duckdb resolves external_location at compile time, so {{ source('geo_lake', 'zones') }} becomes a read_parquet call over the matching path. That keeps the bucket layout in one place rather than scattered through models.

Verify the resolved path is the one you meant:

bash
dbt compile --select stg_zones
grep -A2 "read_parquet" target/compiled/dbt_geospatial/models/staging/stg_zones.sql
What a projected, filtered read actually fetches from a Parquet dataset A Parquet dataset drawn as a grid of row groups by columns. A query selecting three of nine columns with a partition filter and a bounding-box predicate fetches only the shaded cells: the three columns, and only the row groups whose statistics overlap the predicate. The unshaded majority is never transferred. Byte counts underneath contrast the full dataset with what crossed the network. the dataset in the bucket rows down, columns across — shaded cells are what moved what the query cost dataset in the bucket 184 GB after partition pruning 12 GB after column projection 1.4 GB select fewer columns before optimising anything else

3. Decode the geometry once, in staging

GeoParquet stores geometry as WKB in a binary column. Decode it in staging and nowhere else, so every downstream model works with a real geometry type.

sql
-- models/staging/stg_zones.sql
{{ config(materialized = 'table') }}

select
    zone_id,
    zone_name,
    st_geomfromwkb(geometry)                        as geom,
    st_geomfromwkb(geometry).st_astext()            as geom_wkt_sample
from {{ source('geo_lake', 'zones') }}
where year = {{ var('load_year') }}
  and st_geomfromwkb(geometry) is not null

Verify the decode produced geometry and not silence:

sql
select
    count(*)                                   as rows,
    count(geom)                                as decoded,
    count(*) - count(geom)                     as failed_decode,
    min(st_npoints(geom))                      as min_vertices
from {{ ref('stg_zones') }};

4. Prune with the path, not with a predicate

A hive-partitioned layout lets DuckDB skip whole directories before it opens a single file. A where clause on a partition column only prunes if the partition is in the path.

sql
-- Reads three directories, not the whole bucket
select *
from read_parquet('s3://geo-lake/trip_pings/year=2026/month=0[6-8]/*.parquet',
                  hive_partitioning = true)
where month between 6 and 8
sql
-- models/staging/stg_trip_pings.sql — the dbt form
{{ config(materialized = 'table') }}

select
    ping_id, trip_id, observed_at,
    st_geomfromwkb(geometry) as geom,
    year, month
from {{ source('geo_lake', 'trip_pings') }}
where year = {{ var('load_year') }}
  and month >= {{ var('load_month_from', 1) }}

Verify pruning is working by comparing bytes read with and without the filter:

sql
explain analyze select count(*) from {{ ref('stg_trip_pings') }};
-- Look for the number of files scanned; it should match the partitions selected
Hive path pruning compared with filtering after the read Two paths to the same result. In the first, a wildcard path matches every month directory and the filter is applied after reading, so twelve directories are opened. In the second, the path itself names the three month directories, so nine are never opened at all. The difference is marked as the reason the partition layout matters more than the where clause. wildcard path, filter afterwards — 12 directories opened path names the months — 3 directories opened The layout decides what can be pruned. The where clause only filters what was already read.

5. Materialize what you will read more than once

Remote reads are cheap per query and not free. Anything read by more than one model should land in the local DuckDB file.

yaml
models:
  dbt_geospatial:
    staging:
      +materialized: table       # local; remote reads happen once per build
    intermediate:
      +materialized: table

Verify the local database is the size you expect, and that staging is not re-reading the bucket per downstream model:

bash
ls -lh local.duckdb
dbt run --select stg_zones+ --debug 2>&1 | grep -c "read_parquet"
# One occurrence per staging model, not one per downstream model
Where the remote read happens once and where the local work happens repeatedly Object storage on the left holds partitioned GeoParquet. A single arrow crosses the network into a materialized staging table inside the local DuckDB file, where geometry is decoded. From there, three downstream models read locally with no further network traffic. A dashed line marks the network boundary, crossed exactly once per build. object storage year=/month= GeoParquet, WKB network boundary stg_zones (table) ST_GeomFromWKB local DuckDB file int_pings_zoned mart_zone_activity serve_zone_geometry One crossing per build; everything downstream is local.

Configuration reference

Setting Where Example Note
extensions profile [spatial, httpfs] Both are required; httpfs alone cannot decode geometry
s3_* settings profile from env_var() Never literal; the profile is committed
external_location source meta s3://bucket/{name}/… Keeps bucket layout in one place
hive_partitioning read_parquet true Exposes path components as columns
memory_limit profile settings 8GB A spatial join over remote data spills without it
ST_GeomFromWKB staging SQL — Decode once; downstream models use the geometry column

Gotchas & edge cases

  • The geometry column in GeoParquet is WKB, not text. Reading it without decoding gives a BLOB that silently fails every spatial function.
  • CRS lives in the file’s metadata, not in the column. DuckDB does not attach it to the decoded geometry, so assert the SRID yourself in staging rather than assuming it — the enforcement is in enforcing a canonical SRID across dbt models.
  • A wildcard over many small files is slow. Each file costs a request and a footer read; hundreds of megabyte-sized files beat hundreds of thousands of tiny ones by a wide margin.
  • Credentials in the profile are committed to the repository. Use env_var() with no default for secrets, so a missing variable fails loudly instead of falling back to something wrong.
  • Row-group statistics only prune on columns Parquet knows about. Geometry is opaque to them, so a bounding-box filter needs an explicit bbox column written at export time to help.

FAQ

Should I read remote files directly or copy them locally first?

Read directly for exploration and for CI, where the data is read once. Copy locally — which materializing a staging table effectively does — as soon as several models read the same source, because each remote read pays latency and request cost again.

Does GeoParquet's bounding box metadata help DuckDB skip files?

Only if the producer wrote per-column statistics or an explicit bbox column. The GeoParquet specification records the dataset’s bbox in file metadata, which helps a catalogue more than a scan. Writing minimum and maximum x and y as ordinary numeric columns is the pragmatic way to get row-group pruning on spatial extent.

Can this run in CI without cloud credentials?

Yes, by pointing the same source at a local directory of small Parquet fixtures — external_location is just a path template. That keeps the model SQL identical between CI and production, which is the property that makes CI meaningful.

How does this compare with loading into PostGIS?

It trades index-backed query performance for zero load time and zero duplication. For an analytical pass over a large extract, reading in place usually wins; for repeated point lookups or a serving workload, PostGIS with an index wins clearly. The trade-off is set out in PostGIS vs DuckDB spatial for CI pipelines.

Up: Part of DuckDB Spatial Extension Integration.