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

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