Spatial Testing in CI Pipelines

A geometry column can pass every relational test a data team writes — not_null, unique, a foreign-key relationship — and still be quietly broken. A self-intersecting polygon, a point tagged with the wrong SRID, or a spatial join that fanned out by a factor of ten will all sail through the checks most CI pipelines run, then surface as a distorted map, a mismeasured distance, or a runaway query bill once the model reaches production. Spatial correctness needs its own class of tests, and those tests need to run automatically on every pull request — before a reviewer approves, and long before the model touches a production warehouse. This guide, part of Core Fundamentals & Architecture for dbt Geospatial, sets out how to build that gate.

The design principle running through everything below is fast feedback on a cheap engine, then promotion to the expensive one. Standing up a PostGIS service inside a CI runner, loading it, and tearing it down on every push is slow and flaky; paying BigQuery for a full DAG build on each commit is worse. In-process DuckDB spatial runs the same geometry-aware SQL with no service to manage, so a pull request can build and test the entire dependency graph in seconds. Only once that gate is green do the identical models promote to PostGIS or BigQuery for production. The three companion guides in this section make each piece concrete: running spatial tests in GitHub Actions wires the workflow, seeding geometry fixtures for dbt tests proves the tests actually catch failures, and DuckDB as a lightweight CI validator covers where the fast engine diverges from PostGIS.

Prerequisites checklist

Before wiring geometry tests into CI, confirm the pieces that let the gate run identically on a laptop and on a hosted runner:

• dbt Core ≥ 1.7 with two configured targets — a dev/prod target on your production engine, and a ci target on dbt-duckdb ≥ 1.7 with the DuckDB spatial extension. The adapter split is the whole reason CI is cheap; the trade-offs behind it are laid out in choosing the right spatial adapter and, specifically for this use case, in PostGIS vs DuckDB spatial for CI pipelines.
• A CI runner — GitHub Actions, GitLab CI, or equivalent — that can install Python packages. No Docker service container is required for the DuckDB gate, which is precisely what keeps it fast.
• The DuckDB spatial extension available offline or cacheable. CI runners are ephemeral; the spatial extension is downloaded on first INSTALL spatial, so plan to cache it (covered below) rather than re-fetch it on every job.
• A canonical project SRID decided up front and stored as a dbt var, so SRID-consistency tests have a single value to assert against. Enforcement policy lives in detecting SRID mismatches with dbt tests.
• A generic geometry test library — either hand-rolled generic tests or the validity checks described in testing geometry validity with dbt generic tests. CI orchestrates the tests; it does not define what “valid” means.
• Secrets surfaced through env_var(), never hard-coded in profiles.yml, so the same project runs locally, in CI against DuckDB, and in production against PostGIS without edits.

Architecture context: the fast gate before the slow warehouse

The pattern is a two-stage promotion. Every pull request builds the DAG on DuckDB spatial and runs the geometry tests in-process; the job’s exit code is the gate. A red build blocks the merge. A green build signals that the same models — unchanged — are safe to promote to the production warehouse, where a scheduled deploy job runs them against PostGIS or BigQuery.

Two properties make this worth the setup. First, the CI engine and the production engine run the same compiled models and the same tests — the only thing that changes is the --target, so a green DuckDB build is a genuine signal about the SQL, not a separate test harness that can drift. Second, because DuckDB is in-process, the feedback loop is short enough that engineers actually wait for it, which is what keeps spatial regressions out of main. The one caveat is dialect parity: a small set of functions and index behaviours differ between DuckDB and PostGIS, and the CI gate must be scoped to the checks that hold on both engines. That boundary is the subject of the DuckDB CI validator guide.

Configuration walkthrough

The gate needs a dedicated CI profile that points dbt at DuckDB and loads the spatial extension automatically. Keep it in the repository under ci/ so the runner can find it without touching a developer’s ~/.dbt/profiles.yml.

# ci/profiles.yml — used only by the CI target
dbt_geospatial:
  target: ci
  outputs:
    ci:
      type: duckdb
      path: "{{ env_var('DBT_DUCKDB_PATH', 'ci.duckdb') }}"
      # DuckDB fetches and loads the spatial extension on connect
      extensions:
        - spatial
      settings:
        # deterministic ordering makes cardinality tests reproducible
        threads: 4

Point the CI job at that directory and pass the canonical SRID through a var so tests do not hard-code it:

# dbt_project.yml
vars:
  canonical_srid: "{{ env_var('DBT_CANONICAL_SRID', '4326') }}"

# every model in the ci_fixtures path is seeded edge-case data, never promoted
models:
  dbt_geospatial:
    ci_fixtures:
      +tags: ["ci_only"]
      +enabled: "{{ target.name == 'ci' }}"

The +enabled guard is doing real work: fixture models of deliberately broken geometry (an unclosed ring, a wrong-SRID point) must build in the CI target so their tests can fire, but must never materialize in production. Gating on target.name keeps the two worlds separate from one config block. The fixtures themselves are built as seeds and models in seeding geometry fixtures for dbt tests.

Core implementation: geometry-aware generic tests

Three test families cover the spatial failure modes that relational tests miss. Define them as generic tests so any model can attach them in schema YAML.

Validity. A geometry is topologically invalid if it self-intersects, has an unclosed ring, or collapses to zero area. ST_IsValid catches all three; the test returns the offending rows, and a non-empty result fails the build.

-- tests/generic/assert_valid_geometry.sql
{% test assert_valid_geometry(model, column_name) %}
    select {{ column_name }}
    from {{ model }}
    where {{ column_name }} is not null
      and not ST_IsValid({{ column_name }})
{% endtest %}

SRID consistency. Mixing SRIDs in one calculation is the single most common cause of silently wrong spatial results. Assert that every geometry in a model carries the canonical SRID rather than trusting upstream reprojection:

-- tests/generic/assert_srid.sql
{% test assert_srid(model, column_name, srid) %}
    select {{ column_name }}
    from {{ model }}
    where {{ column_name }} is not null
      and ST_SRID({{ column_name }}) <> {{ srid }}
{% endtest %}

Cardinality. A spatial join can silently drop rows (a missing match) or multiply them (fan-out). A row-count expectation catches both. dbt_utils.expression_is_true expresses it declaratively:

# models/marts/_marts.yml
version: 2
models:
  - name: mart_service_areas
    columns:
      - name: geometry
        tests:
          - not_null
          - assert_valid_geometry
          - assert_srid:
              srid: "{{ var('canonical_srid') }}"
    tests:
      - dbt_utils.expression_is_true:
          # a point-in-polygon enrichment must not multiply the fact grain
          expression: "count(*) = (select count(*) from {{ ref('stg_service_points') }})"

Wiring the tests into schema YAML rather than standalone .sql files matters for CI: dbt build runs a model and its attached tests as one unit, so a failing geometry test stops the DAG at that node instead of letting downstream models build on corrupt input.

Wiring dbt build into CI

The command that ties it together is dbt build, not dbt run followed by dbt test. dbt build interleaves runs and tests in DAG order, so a node’s tests execute immediately after it materializes and before any child depends on it. A single non-zero exit code then means “something in the graph is spatially wrong,” which is exactly the gate signal CI needs.

# the entire CI gate, reduced to two commands
dbt deps --target ci
dbt build --target ci --profiles-dir ./ci

Scope the build to what a pull request actually changed with dbt’s state comparison when the DAG grows large, so CI only rebuilds affected nodes plus the fixtures:

# rebuild only models changed vs. the main-branch manifest, plus their children
dbt build --target ci --profiles-dir ./ci \
  --select "state:modified+ tag:ci_only" \
  --state ./prod-manifest

The full GitHub Actions workflow — checkout, Python setup, dependency and extension caching, and the failure-annotation step — is built end to end in running spatial tests in GitHub Actions.

Validation & testing: prove the gate can fail

A test suite that never goes red proves nothing. The discipline that separates a real spatial gate from a decorative one is seeding known-bad geometry and asserting the tests catch it. Build a small set of fixtures — an unclosed ring, a self-intersecting bowtie, a point stamped EPSG:3857 where the project expects EPSG:4326, a null geometry — and confirm each trips exactly the test it should.

# fixtures live behind the ci_only tag; build them, expect a controlled failure
dbt seed --target ci --profiles-dir ./ci --select tag:ci_only
dbt test --target ci --profiles-dir ./ci --select tag:ci_only
# EXPECT non-zero exit: the assert_valid_geometry and assert_srid tests must fail

Because a passing run on broken data would be the real bug, teams often invert the assertion in a dedicated “meta” CI step: run the tests against the fixtures and fail the pipeline if they pass. The mechanics of that inversion, and the WKT CSV seeds behind it, are in seeding geometry fixtures for dbt tests. Pair the fixtures with the broader quality regime in geometry validation and data quality so production models inherit the same checks the fixtures exercise.

Advanced patterns

Caching the DuckDB extension and Python deps. The spatial extension is a downloaded binary; fetching it on every job adds latency and a network-flake failure mode. Cache the extension directory and the pip wheel cache keyed on the lockfile so warm runs skip both downloads. On GitHub Actions this is an actions/cache step keyed on hashFiles('**/requirements.txt') plus a static key for the extension path — detailed in the GitHub Actions guide.

Parallelizing across engines. For projects that must guarantee production parity, run a matrix: the DuckDB gate on every push for speed, and a slower PostGIS job — using a service container — only on merges to main or on a nightly schedule. The DuckDB job blocks merges; the PostGIS job catches the dialect divergences DuckDB cannot, without paying the service cost on every commit.

Testing macros, not just models. Spatial macros carry logic worth unit-testing in isolation — a bounding-box builder, an SRID normalizer. Compiling a macro against the CI target and asserting on the generated SQL catches dialect regressions before any data moves; the pattern is covered in unit testing spatial macros with dbt.

Freshness gates for source feeds. When spatial models depend on external geometry feeds, add a dbt source freshness step so a stale feed fails CI before a downstream model rebuilds on old coordinates. Configuration is in configuring dbt source freshness for spatial feeds.

Troubleshooting

Schema-breaking changes — flipping a column from GEOGRAPHY to GEOMETRY, or changing the canonical SRID — deserve their own review discipline beyond the CI gate; tracking spatial schema changes across environments covers backward-compatible migration.

Related

• Running Spatial Tests in GitHub Actions — the complete workflow YAML, CI profile, and caching steps.
• Seeding Geometry Fixtures for dbt Tests — WKT CSV seeds of edge-case geometry that prove the tests catch failures.
• DuckDB as a Lightweight CI Validator — why in-process DuckDB is the fast gate, and where it diverges from PostGIS.
• Geometry Validation & Data Quality — the generic tests the CI gate orchestrates.
• Core Fundamentals & Architecture for dbt Geospatial — the reference architecture this testing layer plugs into.

↑ Back to Core Fundamentals & Architecture