Choosing the Right Spatial Adapter

Adapter selection is the first architectural decision in a dbt geospatial project, and it quietly governs everything downstream: which ST_ functions you can call, whether the query planner reaches for a spatial index, how coordinate reference system (CRS) metadata survives an incremental run, and how much infrastructure you have to operate to serve a single map tile. Pick the wrong engine and you discover the limit only after the dependency graph is built — a missing topology function, a GEOGRAPHY type the warehouse refuses to materialize, or a spatial join that silently degrades to a sequential scan over millions of polygons.

This guide is the implementation companion to Core Fundamentals & Architecture for dbt Geospatial. Where that overview frames the layered spatial pipeline, this page resolves one concrete question — which adapter executes your spatial transformations — by mapping engine capabilities to workload profiles, then giving you the profiles.yml config, the validation queries, and the failure-mode table you need to commit to a choice with confidence.

Prerequisites

Before benchmarking or committing to an adapter, confirm the following are in place:

• dbt Core 1.5+ (or dbt Cloud on a recent version) — adapter dispatch and modern config() materialization options are assumed throughout.
• A spatial engine you can connect to, sized to the candidate workload:
  • PostGIS 3.1+ on PostgreSQL 13+ with the postgis extension installable, reached through the dbt-postgres adapter. See setting up PostGIS with dbt for the full provisioning path.
  • DuckDB 0.10+ with the spatial extension, driven by dbt-duckdb — covered end to end in the DuckDB spatial extension integration.
  • Optional: a warehouse with native geography support (Snowflake GEOGRAPHY, BigQuery GEOGRAPHY) if your serving layer already lives there.
• Database grants: CREATE on the target schema, plus USAGE on the schema that holds the spatial extension when it is isolated from public.
• Environment variables wired through dbt’s env_var() so the same project resolves to different engines per target without hardcoded hosts or credentials.
• A canonical project SRID decided up front (commonly EPSG:4326 for storage, a metric CRS such as EPSG:3857 for distance math) so every adapter comparison measures the same coordinate baseline.

Architecture Context: Where the Adapter Sits

The adapter is the boundary between your dbt models and the engine that actually evaluates spatial predicates. The same staging-to-mart spatial model dependency graph can compile against more than one engine, but each engine resolves ST_ functions, index access paths, and type coercion differently — so the adapter is where workload requirements become hard constraints. The decision tree below collapses the trade-offs into a single path; the comparison table that follows fills in the rationale.

Evaluation criteria that actually move the decision

When you score a candidate adapter, four dimensions separate a comfortable production fit from a slow regret. Not every engine implements the OGC Simple Features specification identically — some compute vectorized in memory, others push predicates to disk-based indexes, and a subset require explicit geometry casting to avoid silent precision loss during type coercion.

• Function parity — Does the engine support ST_Union, ST_Difference, ST_Snap, ST_MakeValid, and topology validation natively, or do you have to backfill them with UDFs?
• Indexing strategy — Does the planner automatically use a spatial index (GiST, R-Tree, or H3 cell IDs) inside JOIN and WHERE predicates, or must the access path be hinted?
• CRS determinism — Are ST_Transform reprojections lossless, and does the engine preserve the SRID tag through incremental materializations?
• Materialization overhead — How does the adapter behave under merge, incremental, and view strategies when the payload is a large GEOMETRY or GEOGRAPHY column?

Configuration Walkthrough

The cleanest way to keep an adapter choice reversible is to define one dbt project that targets multiple engines, selecting per environment. PostGIS for production serving, DuckDB for CI and local development. The profiles.yml below does exactly that, and every secret flows through env_var().

# profiles.yml
dbt_geospatial:
  target: dev
  outputs:
    prod:
      type: postgres
      host: "{{ env_var('PG_HOST') }}"
      port: 5432
      user: "{{ env_var('PG_USER') }}"
      password: "{{ env_var('PG_PASSWORD') }}"
      dbname: analytics
      schema: spatial_marts
      threads: 8
      search_path: spatial_marts, public, postgis
    dev:
      type: duckdb
      path: "{{ env_var('DUCKDB_PATH', 'dev_spatial.duckdb') }}"
      extensions:
        - spatial
      threads: 4

Bootstrap the spatial capability before any model compiles, so a missing extension fails the run instead of a single model. In dbt_project.yml, an on-run-start hook guards PostGIS targets, and a project-wide variable pins the canonical SRID so adapter behavior is compared on equal footing.

# dbt_project.yml
vars:
  canonical_srid: 4326   # storage CRS shared by every adapter
  metric_srid: 3857      # planar CRS for metre-based math

on-run-start:
  - "{{ ensure_spatial_extension() }}"

-- macros/ensure_spatial_extension.sql
{% macro ensure_spatial_extension() %}
  {% if execute and target.type == 'postgres' %}
    CREATE EXTENSION IF NOT EXISTS postgis SCHEMA public
  {% elif execute and target.type == 'duckdb' %}
    {% do run_query("INSTALL spatial; LOAD spatial;") %}
    {{ return('') }}
  {% endif %}
{% endmacro %}

The detailed adapter install path — wheel compilation, version pinning, and dependency resolution — lives in how to install the dbt-postgis adapter step by step for PostGIS and in configuring the DuckDB spatial extension in dbt projects for the in-process route.

Core Implementation

PostGIS: the production-serving default

PostgreSQL with PostGIS remains the most mature spatial engine for production analytics. It offers comprehensive topology functions, GiST and SP-GiST indexing, and deterministic reprojections backed by the PROJ library. In dbt, incremental materializations can exploit bounding-box filters to avoid full-table scans, and a post_hook keeps the spatial index in lockstep with the model. The adapter resolves ST_Transform, ST_Intersects, and ST_Union with stable plans that scale close to linearly with data volume.

-- models/staging/stg_parcel_boundaries.sql
{{ config(
    materialized='incremental',
    unique_key='parcel_id',
    post_hook=[
        "CREATE INDEX IF NOT EXISTS idx_stg_parcel_boundaries_geom ON {{ this }} USING GIST (geometry);"
    ]
)}}

SELECT
    parcel_id,
    ST_Transform(ST_SetSRID(geometry, {{ var('canonical_srid') }}), {{ var('metric_srid') }}) AS geometry,
    address,
    updated_at
FROM {{ source('gis', 'raw_parcels') }}
{% if is_incremental() %}
    WHERE updated_at > (SELECT COALESCE(MAX(updated_at), '1970-01-01') FROM {{ this }})
{% endif %}

PostGIS earns its place where strict data integrity, concurrent writes, and complex topological work — ST_IsValidReason, ST_MakeValid, ST_Node — matter more than raw ingestion speed. The cost is operational: a server to run, tune, and back up, which is heavier than a file-based engine.

DuckDB spatial: in-process and file-native

The DuckDB spatial extension delivers exceptional throughput for Parquet-heavy workflows, local development, and serverless execution. It runs in-process, eliminating client-server round trips, reads directly from object storage, and applies an R-Tree index to spatial predicates with native WKB/WKT serialization. For ephemeral compute and CI, it removes the need for a live database entirely.

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

SELECT
    r.region_id,
    r.region_name,
    COUNT(p.parcel_id)                                   AS parcel_count,
    ST_Area(ST_Union_Agg(p.geometry)) / 1000000.0        AS total_area_km2,
    ST_Centroid(ST_Union_Agg(p.geometry))                AS region_centroid
FROM {{ ref('stg_parcels') }} p
JOIN {{ ref('stg_regions') }} r
  ON ST_Intersects(p.geometry, r.geometry)
GROUP BY 1, 2

DuckDB suits pipelines that prize throughput, cost, and developer velocity. Its trade-offs are real: limited concurrent writes, no built-in topology-repair functions, and explicit memory management for very large geometries. It shines in ELT patterns where spatial data is pre-filtered, aggregated, and handed off to a serving layer.

Workload-to-adapter mapping

When neither extreme fits, run a tiered architecture: stage and aggregate with DuckDB, then export validated geometries into PostGIS for serving and topology enforcement. Wrapping the engine-specific ST_ calls behind dispatched macros — see building custom spatial macros — keeps the same models running across both halves of that split.

Validation & Testing

A chosen adapter is only trustworthy once you have proven it preserves spatial integrity through a real run. Confirm the engine is present and at the expected version first, then sweep the materialized output for validity and CRS drift.

-- analyses/check_engine.sql
-- PostGIS:
SELECT PostGIS_Version();           -- expect e.g. 3.4 USE_GEOS=1 USE_PROJ=1
-- DuckDB:
SELECT * FROM duckdb_extensions() WHERE extension_name = 'spatial' AND loaded;

Embed the spatial invariants as dbt tests so adapter-specific quirks surface in CI rather than production:

1. SRID consistency — assert every mart geometry carries the canonical SRID.
2. Geometry validity — sweep ST_IsValid to catch self-intersections, ring-orientation errors, and degenerate polygons.
3. Precision-loss monitoring — compare bounding-box extents before and after reprojection to flag truncation.
4. Index coverage — EXPLAIN the key spatial predicate and confirm an index scan, not a sequential scan.

-- tests/assert_srid_consistency.sql
SELECT parcel_id, geometry
FROM {{ ref('stg_parcels') }}
WHERE ST_SRID(geometry) != {{ var('metric_srid') }}

# models/staging/_staging.yml
models:
  - name: stg_parcels
    columns:
      - name: geometry
        tests:
          - dbt_utils.expression_is_true:
              expression: "ST_IsValid(geometry)"

Advanced Patterns

• Adapter-portable models. Keep models free of vendor syntax by routing every spatial call through dispatched macros (adapter.dispatch). The aggregate above is the clearest example: PostGIS spells it ST_Union, DuckDB spells it ST_Union_Agg. A macro hides that fork so one model compiles on both engines and your CI run on DuckDB genuinely validates the PostGIS path.
• Incremental spatial materializations. On PostGIS, pair an incremental model with a bounding-box predicate and a GiST post_hook so only changed tiles are reprocessed and the index never goes stale. On DuckDB, prefer full table rebuilds from Parquet — incremental state is cheaper to recompute than to maintain in a file-based store.
• CRS governance across engines. Because reprojection determinism differs subtly between PROJ-backed PostGIS and DuckDB, fix the canonical and metric SRIDs as project variables and transform at the staging boundary. The policy side of this — who owns the authoritative SRID and how drift is caught — belongs to spatial reference system management.
• Hybrid promotion. Validate geometries on DuckDB in CI, promote to PostGIS only when the DuckDB run is green, and let the serving layer enforce topology. This keeps the fast feedback loop cheap while reserving the heavier engine for production guarantees.

Troubleshooting

Related

• Setting Up PostGIS with dbt — provisioning, connection architecture, and CRS enforcement for the production-serving engine.
• DuckDB Spatial Extension Integration — in-process and Parquet-native patterns for the lightweight engine.
• Spatial Model Dependency Graphs — how the adapter choice ripples through staging-to-mart DAG topology.
• Building Custom Spatial Macros — dispatched abstractions that keep one model running across engines.
• Spatial Reference System Management — governance for the canonical SRID every adapter must honor.

Up: Part of Core Fundamentals & Architecture for dbt Geospatial.