Running spatial tests in GitHub Actions

This page gives you a working GitHub Actions workflow that builds your dbt models on the DuckDB spatial extension and runs geometry validity and SRID tests on every pull request, so spatially broken geometry never reaches review.

When to use this approach

Wire dbt spatial tests into GitHub Actions — rather than relying on local dbt test runs or a manual pre-merge check — when any of these hold:

  • Your repository already uses GitHub for pull requests. A pull_request-triggered workflow makes the geometry gate a required status check, so a red build blocks the merge automatically. This is the concrete implementation of the pattern described in spatial testing in CI pipelines.
  • You want the gate to run on a cheap engine. DuckDB spatial runs in-process with no service container, so the job finishes in seconds; the reasoning is in DuckDB as a lightweight CI validator. If your models depend on PostGIS-only functions, run this DuckDB gate on every push and a slower PostGIS job on merges only.
  • You need the same test definitions in CI and production. The workflow changes only the dbt --target, so the geometry tests you author in geometry validation and data quality run identically on the runner and against the production warehouse.

Prerequisites

  • dbt Core ≥ 1.7 and dbt-duckdb ≥ 1.7 declared in a requirements.txt (or lockfile) at the repository root.
  • A CI profile committed under ci/ — never ~/.dbt — so the runner finds it without a developer’s local config.
  • Geometry generic tests defined in the project (assert_valid_geometry, assert_srid) and attached to models in schema YAML.
  • A canonical SRID stored as a dbt var so the SRID test has one value to assert against. Enforcement policy lives in enforcing a canonical SRID across dbt models.
  • Any secret (a warehouse password for the promote step) stored as a GitHub Actions secret and read through dbt’s env_var().

Step-by-step instructions

1. Write the CI profile with env_var()

The profile points dbt at DuckDB, loads the spatial extension on connect, and reads its database path and canonical SRID from the environment so nothing is hard-coded.

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

Verify the profile resolves before touching CI:

bash
dbt debug --target ci --profiles-dir ./ci
# Expect: "All checks passed!" and Connection test: OK for the duckdb target

2. Add the workflow file

Commit the workflow at .github/workflows/dbt-spatial-ci.yml. It checks out the repo, sets up Python, installs dbt-duckdb, and runs dbt build against the CI target — which interleaves model runs and geometry tests in DAG order.

yaml
# .github/workflows/dbt-spatial-ci.yml
name: dbt spatial CI
on:
  pull_request:
    branches: [main]

jobs:
  geometry-gate:
    runs-on: ubuntu-latest
    env:
      DBT_PROFILES_DIR: ./ci
      DBT_CANONICAL_SRID: "4326"
      DBT_DUCKDB_PATH: ci.duckdb
    steps:
      - uses: actions/checkout@v4

      - uses: actions/setup-python@v5
        with:
          python-version: "3.11"
          cache: pip

      - name: Cache DuckDB spatial extension
        uses: actions/cache@v4
        with:
          path: ~/.duckdb/extensions
          key: duckdb-spatial-${{ runner.os }}

      - name: Install dbt
        run: pip install -r requirements.txt

      - name: Install dbt packages
        run: dbt deps --target ci

      - name: Build DAG and run geometry tests
        run: dbt build --target ci

Verify the workflow is picked up by pushing a branch and opening a pull request; the “dbt spatial CI / geometry-gate” check should appear on the PR within a few seconds of the push.

The two GitHub events drive two different jobs: a pull_request event runs the fast DuckDB gate that blocks the merge button, and the push to main that a merge produces runs the slower PostGIS parity build and the production promotion.

GitHub events mapped to CI jobs: pull_request gate versus merge promotion Two horizontal lanes. The upper lane starts from a pull_request event, runs the DuckDB spatial geometry gate as a required status check, and either blocks the merge on failure or allows it on success. The lower lane starts from a push to main produced by a merge, runs a slower PostGIS parity build, and then promotes the models to the production warehouse. The two lanes share the same models and tests, differing only in the dbt target. on: pull_request PR opened push / reopen DuckDB gate dbt build --target ci required status check pass? Merge allowed green build yes red build blocks the merge button on: push → main (merge) Merge commit on main PostGIS parity service container Promote production warehouse

3. Seed edge-case fixtures so the gate can fail

A gate that never goes red proves nothing. Seed a handful of deliberately broken geometries and let their tests fire in the same build. Keep them behind a ci_only tag so they never reach production, then include them in the build selection.

yaml
# add to the "Build DAG and run geometry tests" step
      - name: Build DAG and run geometry tests
        run: dbt build --target ci --select "state:modified+ tag:ci_only"

The fixtures themselves — WKT CSV seeds of an unclosed ring, a self-intersection, a wrong-SRID point, and a null geometry — are built in seeding geometry fixtures for dbt tests.

Verify locally that a known-bad fixture trips its test:

bash
dbt build --target ci --profiles-dir ./ci --select tag:ci_only
# Expect a non-zero exit: assert_valid_geometry fails on the bowtie fixture

4. Surface failures on the pull request

By default a failed test buries its detail in the log. Annotate the PR by printing the failing rows so a reviewer sees which geometry broke without opening the run.

yaml
      - name: Show failing test rows
        if: failure()
        run: |
          dbt build --target ci --store-failures \
            --select "result:fail+" --state target || true
          echo "::group::Failed geometry tests"
          cat target/run_results.json | python -m json.tool | head -c 4000
          echo "::endgroup::"

Verify by opening a PR that introduces an invalid geometry; the check fails and the “Show failing test rows” step lists the offending model and column.

Configuration reference

Setting Where Purpose
on: pull_request workflow trigger Runs the gate on every PR against main; make it a required status check in branch protection
DBT_PROFILES_DIR: ./ci job env Points dbt at the committed CI profile instead of ~/.dbt
extensions: [spatial] ci/profiles.yml Loads the DuckDB spatial functions (ST_IsValid, ST_SRID) on connect
env_var('DBT_DUCKDB_PATH', …) profile path Reads the database path from the environment with a safe default
actions/cache on ~/.duckdb/extensions workflow step Skips re-downloading the spatial extension on warm runs
dbt build --select "state:modified+" build step Rebuilds only changed nodes plus children once the DAG is large
--store-failures build step Persists failing rows to a table for inspection in the annotation step

Gotchas & edge cases

  • The extension download flakes on cold runs. The first INSTALL spatial fetches a binary over the network; without the cache step, a throttled endpoint fails the job. Cache ~/.duckdb/extensions keyed on the runner OS so warm runs skip the download entirely.
  • dbt run then dbt test lets bad data through. Split commands let a child model build on a parent that failed its test. Use dbt build so a node’s tests gate its children in DAG order.
  • A missing profile dir silently uses the wrong target. If DBT_PROFILES_DIR is unset, dbt reaches for ~/.dbt/profiles.yml, which does not exist on a fresh runner. Set it in job env and verify with dbt debug.
  • Fixtures materialize in production. If the ci_only models are not gated on target.name, a production deploy will try to build broken geometry. Guard them with +enabled: "{{ target.name == 'ci' }}".
  • SRID-0 seeds fail the SRID test spuriously. WKT loaded without an SRID reports ST_SRID = 0. Stamp the intended SRID with ST_SetSRID in the staging model, or the fixture, before the test runs.

FAQ

Do I need a Postgres service container in the workflow?

Not for the DuckDB gate. DuckDB spatial runs in-process inside the runner, so there is no service to declare, start, or wait on — that is what keeps the job fast. Add a services: postgres block only in a separate, slower job that runs on merges to main to catch the handful of PostGIS-only behaviours DuckDB cannot reproduce.

How do I make the geometry gate a required check?

In the repository’s branch-protection rules for main, add the workflow’s job name (“dbt spatial CI / geometry-gate”) to the list of required status checks. GitHub then blocks the merge button until the DuckDB build and its geometry tests pass. Keep the job name stable so the required-check reference does not break.

Why cache the DuckDB extension instead of installing it in requirements?

The spatial extension is a DuckDB-native binary, not a Python package, so pip install cannot fetch it — DuckDB downloads it on the first INSTALL spatial. Caching the ~/.duckdb/extensions directory across runs avoids re-fetching it on every job and removes a network-flake failure mode.

Can I run the tests against changed models only?

Yes. Store the production manifest.json as an artifact and pass --select "state:modified+" with --state, so dbt rebuilds only the models that differ from production plus their downstream children. Always union the tag:ci_only fixtures into the selection so the negative tests still fire.

Up: Part of Spatial Testing in CI Pipelines.