DuckDB Spatial Extension Integration

Running geospatial transformations through an in-process engine removes an entire class of operational overhead: there is no server to provision, no extension to grant, and no network round-trip between dbt and the database. But that convenience comes with a sharp edge. The DuckDB spatial extension is session-scoped, so every dbt invocation starts with a clean engine that knows nothing about geometry types, ST_ functions, or projection libraries until you load it. Get the initialization order wrong and models fail to compile; get coordinate handling wrong and you silently corrupt every downstream metric. This guide shows how to integrate the extension into a dbt project so that spatial builds are deterministic across a laptop, a CI runner, and a production orchestrator — the same reliability contract laid out in Core Fundamentals & Architecture for dbt Geospatial.

The integration sits at a specific point in the engine decision. Once you have worked through choosing the right spatial adapter and settled on DuckDB for local development, CI validation, or single-node analytical batch work, this page is the implementation reference: how to load the extension, enforce a single coordinate reference system, structure joins so the vectorized engine actually pays off, and verify the runtime before any spatial model runs.

Prerequisites

Before wiring spatial models into the DAG, confirm the toolchain is pinned and the runtime can reach its projection data:

• dbt-core ≥ 1.7 and the dbt-duckdb adapter ≥ 1.7.x (the adapter version should track the dbt-core minor).
• DuckDB ≥ 0.10 — the GEOMETRY type and ST_Transform projection support stabilised across the 0.9 → 0.10 line, so older pins behave inconsistently.
• The spatial extension available from the DuckDB extension repository, or vendored locally for air-gapped CI.
• Network egress (or a cached extension directory) so the first INSTALL spatial can fetch the signed binary; CI runners without egress must pre-seed ~/.duckdb/extensions.
• A writable PROJ data path so ST_Transform can resolve datum grids; set it explicitly when the bundled GDAL/PROJ cannot find its dictionary.
• Environment values supplied through dbt’s env_var() pattern, never hard-coded paths.

# profiles.yml — DuckDB target for local + CI spatial builds
dbt_geospatial:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: "{{ env_var('DUCKDB_PATH', 'dev.duckdb') }}"
      threads: 8
      extensions:
        - spatial
      settings:
        # point PROJ at a writable dictionary so ST_Transform resolves datums
        PROJ_DATA: "{{ env_var('PROJ_DATA', '/usr/share/proj') }}"

Declaring spatial under extensions lets the adapter install and load it at connection time, but production pipelines should not rely on that alone — the explicit hook pattern below guarantees the same behaviour regardless of profile drift.

Architecture Context

Within the broader spatial DAG, the DuckDB extension is the execution substrate for the staging and intermediate layers: raw spatial payloads land, get validated and re-projected, then feed the heavier joins before marts are materialized. A dedicated initialization model acts as a synthetic root so the extension is guaranteed loaded before any geometry function is parsed. How those edges are ordered — and how to keep the graph acyclic when geometry operations fan out — is covered in spatial model dependency graphs.

Configuration Walkthrough

Production-grade pipelines cannot rely on implicit or ad-hoc extension loading. Unlike a traditional RDBMS where spatial functions are registered once at the database level, DuckDB treats extensions as session-scoped, so initialization must run before any model that references a geometry function. The most reliable mechanism is an on-run-start hook in dbt_project.yml, which executes once per invocation ahead of the DAG:

# dbt_project.yml — install/load runs once per invocation, before any model
on-run-start:
  - "INSTALL spatial;"
  - "LOAD spatial;"

models:
  dbt_geospatial:
    staging:
      +materialized: view
    intermediate:
      +materialized: table

The hook handles the runtime, but execution order still needs an explicit anchor. A small initialization model gives downstream spatial models a concrete node to depend on through {{ ref('_spatial_init') }}, so dbt’s scheduler cannot start a geometry build before the extension is proven ready:

-- models/_spatial_init.sql
-- A single SELECT that validates spatial runtime readiness. Reference it via
-- {{ ref('_spatial_init') }} from downstream spatial models to fix execution order.
{{ config(materialized='view', tags=['infrastructure', 'spatial-init']) }}

SELECT
    'spatial'                                                         AS extension_name,
    version()                                                         AS duckdb_version,
    ST_IsValid(ST_GeomFromText('POINT(0 0)'))                         AS topology_ready,
    ST_AsText(ST_Transform(
        ST_GeomFromText('POINT(0 0)'),
        'EPSG:4326', 'EPSG:3857'
    ))                                                                AS crs_transform_test

This pattern eliminates silent topology failures caused by missing GEOS binaries or a mismatched library version across deployment environments. For deeper environment configuration — adapter version pinning, CI runner provisioning, vendored extension binaries, and PROJ dictionary path resolution — see configuring the DuckDB spatial extension in dbt projects.

Core Implementation

CRS enforcement and geometry validation

Coordinate reference system mismatches remain the primary source of spatial data corruption in analytical pipelines. DuckDB stores geometries in the GEOMETRY type, but the engine does not automatically harmonize disparate projections during joins or aggregations — two layers in different SRIDs will join without error and produce meaningless distances. Production workflows must therefore enforce explicit re-projection at the model boundary, using ST_Transform to land every geometry in one analytical coordinate space before it travels downstream.

-- macros/standardize_geometry.sql
{% macro standardize_geometry(column_name, source_srid, target_srid=4326) %}
  CASE
    WHEN {{ column_name }} IS NULL THEN NULL
    WHEN {{ source_srid }} = {{ target_srid }} THEN {{ column_name }}
    ELSE ST_Transform(
      {{ column_name }},
      'EPSG:{{ source_srid }}',
      'EPSG:{{ target_srid }}'
    )
  END
{% endmacro %}

-- models/intermediate/geo_standardized.sql
{{ config(materialized='table') }}

WITH normalized AS (
    SELECT
        id,
        {{ standardize_geometry('geom', 3857, 4326) }} AS geom_wgs84
    FROM {{ ref('raw_geospatial_feed') }}
)
SELECT
    id,
    geom_wgs84,
    ST_IsValid(geom_wgs84) AS is_valid_topology,
    ST_Area(geom_wgs84)    AS area_sq_meters
FROM normalized
WHERE ST_IsValid(geom_wgs84)

Wrapping projection logic in a reusable dbt macro standardizes normalization across the whole project and guarantees every downstream model operates in a unified coordinate space — the same abstraction principle explored in building custom spatial macros. Centralising the rule here also prevents the silent metric inflation that mixed degree/meter calculations cause when an analyst computes ST_Area on un-projected lon/lat data.

Optimizing spatial joins for vectorized execution

DuckDB’s columnar storage and vectorized engine evaluate spatial predicates far faster than row-by-row execution, but query architecture still dictates whether that advantage materializes. Joins on ST_Intersects, ST_DWithin, or ST_Contains benefit heavily from a coarse bounding-box pre-filter before the exact topology check runs. When joining large polygon datasets against high-cardinality point streams, filter on ST_Envelope overlap first, then apply the precise predicate:

-- models/marts/location_enrichment.sql
{{ config(materialized='table') }}

WITH bbox_candidates AS (
    SELECT
        p.id,
        p.geom AS point_geom,
        z.zone_id,
        z.geom AS zone_geom
    FROM {{ ref('stg_sensor_points') }} p
    JOIN {{ ref('stg_admin_zones') }} z
      ON ST_Intersects(ST_Envelope(p.geom), ST_Envelope(z.geom))
)
SELECT
    id,
    zone_id,
    ST_Distance(point_geom, ST_Centroid(zone_geom)) AS dist_to_centroid_m
FROM bbox_candidates
WHERE ST_Intersects(point_geom, zone_geom)

This two-stage filter minimizes expensive GEOS topology computations while letting the engine prune candidate pairs cheaply. For high-volume feeds where even the bounding-box pass strains memory, partition the input as described in handling large geospatial datasets. The DuckDB function reference and execution model are documented in the official DuckDB Spatial extension overview.

Validation & Testing

Because the extension is loaded per session, the first thing a spatial build should prove is that the runtime is actually present. Run a one-shot readiness query — the same shape as the _spatial_init model — and assert the values rather than eyeballing them:

-- analyses/check_spatial_runtime.sql — run with: dbt show -s check_spatial_runtime
SELECT
    version()                                          AS duckdb_version,
    ST_IsValid(ST_GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))')) AS topology_ok,
    ST_SRID(ST_Transform(
        ST_GeomFromText('POINT(13.4 52.5)'), 'EPSG:4326', 'EPSG:3857'
    ))                                                 AS reprojected_srid  -- expect 3857

Past the runtime check, geometry quality belongs in the project’s test suite so a bad load fails the build instead of polluting a mart. dbt’s generic tests cover null guards and accepted SRIDs declaratively, while a singular test sweeps for invalid topology:

# models/intermediate/_intermediate.yml
version: 2
models:
  - name: geo_standardized
    columns:
      - name: geom_wgs84
        tests:
          - not_null
      - name: is_valid_topology
        tests:
          - accepted_values:
              values: [true]

-- tests/assert_no_invalid_geometries.sql
-- Fails the build if any geometry survives standardization with broken topology.
SELECT id, ST_AsText(geom_wgs84) AS wkt
FROM {{ ref('geo_standardized') }}
WHERE NOT ST_IsValid(geom_wgs84)

Keeping these assertions in CI means a DuckDB run can act as a lightweight gate before promoting changes to a heavier warehouse — the cheap validator pattern that pairs naturally with setting up PostGIS with dbt for production.

Advanced Patterns

Incremental spatial models

Re-projecting and re-validating the full history on every run wastes compute once a feed grows. An incremental materialization re-processes only new rows, while ST_MakeValid repairs minor topology defects in place rather than dropping them:

-- models/intermediate/geo_standardized_inc.sql
{{ config(materialized='incremental', unique_key='id') }}

SELECT
    id,
    ST_MakeValid({{ standardize_geometry('geom', 3857, 4326) }}) AS geom_wgs84,
    loaded_at
FROM {{ ref('raw_geospatial_feed') }}
{% if is_incremental() %}
WHERE loaded_at > (SELECT MAX(loaded_at) FROM {{ this }})
{% endif %}

Cross-engine portability

The macro-wrapped projection logic above is deliberately engine-agnostic in its interface: ST_Transform, ST_IsValid, and ST_MakeValid exist in both DuckDB and PostGIS, so the same intermediate model can target either by swapping the profile. Where dialects diverge — SRID-string vs SRID-integer arguments, or ST_DWithin distance units — branch on target.type inside the macro to keep one definition. The batch re-projection variant of this pattern is detailed in batch transforming coordinate systems with dbt, and CRS policy enforcement across environments in automating CRS conversions in dbt pipelines.

Troubleshooting

Configuration that has to reach every connection

One property of dbt on DuckDB catches almost everyone once: dbt opens a connection per thread, and a DuckDB extension is loaded per connection. A LOAD spatial issued as a one-off statement, or from a hook that runs once per invocation, therefore reaches exactly one of them, and the other threads fail on the first spatial function they meet — usually with an error that names a missing function rather than a missing extension, which sends the investigation in the wrong direction.

Declaring the extension in the profile’s extensions list is what makes every connection load it, and the same reasoning applies to settings such as the memory limit and the S3 credentials: anything a query needs must be established where connections are created, not where models run. The memory limit deserves a second look for another reason — it is per connection, so the effective ceiling is the limit multiplied by the thread count. Four threads at four gigabytes fits comfortably on a sixteen-gigabyte machine; eight threads at the same limit asks for thirty-two and pushes the machine into swapping, at which point a spatial join that would have taken two minutes takes an hour and looks like a DuckDB problem rather than an arithmetic one.

The practical rule is to set threads and memory limit together, from the machine rather than from habit, and to write both in the profile where they are visible next to each other rather than split between a profile and an environment variable.

Reading data where it already lives

The most valuable property of an in-process engine is not raw speed but the absence of a loading step. DuckDB reads Parquet, GeoParquet, CSV and JSON directly, from local disk or from object storage, which means a staging model can query a bucket path as its source and never materialise an intermediate copy. For datasets that are large but read a few times, that removes the storage duplication and the orchestration that a conventional load implies.

Three things make the difference between that working well and working badly. The first is projection: DuckDB reads Parquet column by column, so selecting three columns of nine touches a third of the bytes, and the single largest saving available is usually to stop selecting geometry in models that only need keys. The second is partition pruning, which depends on the file layout rather than on the query — a hive-partitioned path that names the partitions to read skips whole directories before any file is opened, whereas a wildcard path with a where clause opens every file’s footer first. The third is decoding: GeoParquet stores geometry as well-known binary, and a column that has not been passed through a geometry constructor is an opaque blob on which every spatial function silently fails.

Materialisation policy follows from the same reasoning. A remote read is cheap per query and not free, so anything read by more than one model belongs in a local table, which is exactly what materialising staging as table achieves. The full setup, including credentials from the environment and the source template that keeps bucket layout in one place, is in reading GeoParquet from object storage with DuckDB.

Where the local engine stops being enough

DuckDB’s limits are as worth stating as its strengths, because a project that discovers them late has usually built around them. There is no persistent spatial index to create and maintain, so tuning is a matter of data ordering and predicate shape rather than index management. There is no concurrency model for many simultaneous readers, which rules it out as a serving engine behind an API. And its geometry implementation, while broad, is not identical to PostGIS at the edges — validity repair and some predicate boundary cases differ, which is precisely why a project that develops locally and runs in production elsewhere needs parity checks rather than assumptions. Those differences are catalogued in PostGIS vs DuckDB spatial for CI pipelines.

One further consequence of the per-connection model is worth stating for people arriving from a client-server background: there is no shared cache between dbt’s threads. Each connection warms its own buffers, so a model that reads a large Parquet file in three threads reads it three times unless the data has been materialised locally first. That is another argument for materialising staging as tables rather than views when the same source feeds several downstream models.

Frequently asked questions

Related

• Configuring the DuckDB spatial extension in dbt projects — version pinning, vendored binaries, and PROJ paths.
• Choosing the right spatial adapter — when DuckDB beats PostGIS or BigQuery GIS.
• Setting up PostGIS with dbt — the production counterpart for high-concurrency workloads.
• Spatial model dependency graphs — ordering geometry-heavy DAG edges.

Up: Core Fundamentals & Architecture for dbt Geospatial