DuckDB as a lightweight CI validator

This page shows you how to use in-process DuckDB spatial as a fast validation engine for dbt geometry models in CI — installing the spatial extension, running the geometry tests, and knowing precisely where DuckDB’s results can diverge from PostGIS before you promote.

When to use this approach

Validate spatial models on DuckDB in CI — rather than standing up PostGIS or paying for a BigQuery build on every push — when any of these hold:

• Your CI feedback loop is too slow. A PostGIS service container adds startup, load, and teardown to every job. DuckDB spatial runs in-process, so the gate finishes in seconds, which is what makes the two-stage pattern in spatial testing in CI pipelines practical.
• Your models use the common ST_* core. Validity, SRID, containment, distance, and buffer operations behave identically enough on DuckDB and PostGIS to gate on. The engine trade-offs behind this choice are compared in PostGIS vs DuckDB spatial for CI pipelines.
• You develop locally without a database. DuckDB is the same engine described in DuckDB spatial extension integration — a single file, no server — so an engineer validates the DAG on a laptop with the identical target CI uses.

If your models lean on PostGIS-only features (topology, raster, <-> KNN laterals), DuckDB validates the portable subset and a nightly PostGIS job covers the rest.

Prerequisites

• dbt-duckdb ≥ 1.7 installed in the CI environment.
• The DuckDB spatial extension, fetched on first connect or cached on the runner.
• Geometry generic tests (assert_valid_geometry, assert_srid) and, ideally, edge-case geometry fixtures so the validator has something to catch.
• A canonical SRID agreed for the project, stored as a dbt var.

Step-by-step instructions

1. Install and load the spatial extension

DuckDB ships spatial as a loadable extension, not a built-in. The dbt-duckdb adapter loads it for you when you list it under extensions, which is equivalent to running INSTALL spatial; LOAD spatial; on connect.

# ci/profiles.yml
dbt_geospatial:
  target: ci
  outputs:
    ci:
      type: duckdb
      path: "{{ env_var('DBT_DUCKDB_PATH', 'ci.duckdb') }}"
      extensions:
        - spatial

Verify the extension is present and the spatial functions resolve:

dbt run-operation debug_spatial --target ci --profiles-dir ./ci
# or, directly in the DuckDB CLI:
#   INSTALL spatial; LOAD spatial;
#   SELECT ST_IsValid(ST_GeomFromText('POLYGON((0 0,0 1,1 1,1 0,0 0))'));  -- true

2. Build the DAG on DuckDB and run the geometry tests

Run dbt build against the CI target so every model materializes into the DuckDB file and its attached geometry tests run in DAG order. Nothing external is touched.

dbt build --target ci --profiles-dir ./ci
# builds every model into ci.duckdb and runs assert_valid_geometry / assert_srid inline

Verify the validator catches a real defect by pointing it at the edge-case fixtures:

dbt build --target ci --profiles-dir ./ci --select tag:ci_only
# Expect a non-zero exit: the unclosed-ring and self-intersection fixtures fail ST_IsValid

3. Scope the gate to the parity subset

The gate should assert only what holds identically on DuckDB and the production engine. In practice that is validity, SRID, containment, distance, and buffered-area comparisons — the checks whose verdict does not depend on engine internals. Keep PostGIS-only assertions (topology, KNN operator plans) out of the DuckDB selection and run them separately.

# DuckDB gate: portable checks only, on every push
dbt build --target ci --profiles-dir ./ci --select tag:portable

# PostGIS parity job: the engine-specific remainder, on merge or nightly
dbt build --target prod_ci --select tag:postgis_only

Verify the split by confirming the portable selection contains no model that calls a PostGIS-only function:

dbt ls --target ci --select tag:portable --output path
# review the listed models; none should reference topology or the <-> operator

4. Promote only after the DuckDB gate is green

Wire the DuckDB gate as the required pull-request check and let promotion to PostGIS run only on merge. The models are unchanged between the two — only the --target differs — so a green DuckDB build is a genuine signal about the SQL.

# runs on merge to main, after the DuckDB gate has already passed
dbt build --target prod

Verify parity on the first few promotions by diffing a validity summary across engines:

-- run on each engine; the counts should match for the portable subset
select count(*) filter (where not ST_IsValid(geometry)) as invalid,
       count(distinct ST_SRID(geometry))                as srid_variants
from mart_service_areas;

Configuration reference

Gotchas & edge cases

• GEOGRAPHY semantics are absent. DuckDB spatial is planar GEOMETRY only; a model that relies on ::geography great-circle distance cannot be validated for distance accuracy on DuckDB. Validate the query shape on DuckDB and assert distance correctness in the PostGIS parity job.
• KNN laterals do not port. There is no <-> operator idiom in DuckDB, so a PostGIS nearest-neighbour model has no direct equivalent. Use the portable ST_DWithin radius form for anything the DuckDB gate must run; the divergence is detailed in optimizing proximity joins.
• Validity verdicts on degenerate shapes. Both engines use GEOS, but version skew means a handful of borderline self-touching rings can be judged differently. Pin the fixture set to defects both flag, and treat any divergence as a signal to run that model through the PostGIS job.
• Index behaviour is not reproduced. DuckDB’s in-memory R-tree does not model a PostGIS GiST post_hook, so CI cannot catch a “planner ignored the index” regression. Keep EXPLAIN-based index assertions in the PostGIS parity job.
• Extension download on cold runners. The first INSTALL spatial reaches the network; cache the extension directory so CI does not flake on a throttled endpoint.

FAQ

Related

• Spatial Testing in CI Pipelines — the two-stage gate this validator anchors.
• DuckDB Spatial Extension Integration — configuring the same engine for local development.
• Choosing the Right Spatial Adapter — the trade-offs behind validating on one engine and promoting to another.

Up: Part of Spatial Testing in CI Pipelines.