Unit Testing Spatial Macros with dbt

This page shows you how to prove a spatial dbt macro is correct on two levels — the SQL string it emits per adapter, and the geometry it produces from known WKT inputs — using dbt’s native unit tests and a small compile assertion.

When to use this approach

Add unit tests around a spatial macro — rather than relying on end-to-end model runs to surface bugs — when any of these hold:

• The macro branches on target.type. Any macro that emits different SQL per warehouse, like the ones assembled in building custom spatial macros, can silently emit the wrong dialect on an adapter no one exercised locally. A per-adapter compile assertion catches that in seconds.
• The macro’s output is a geometry you can predict. Centroids, envelopes, ST_Force2D, grid snaps, and validity repairs have deterministic results from a fixed WKT input, so a given/expect unit test pins the behaviour exactly. A reusable predicate such as the one in writing reusable ST_DWithin macros in dbt is a prime candidate.
• You ship the same macro across engines. If a macro must behave identically on PostGIS and DuckDB, unit tests running under both targets are the guardrail that keeps cross-engine UDF portability honest instead of aspirational.

Prerequisites

• dbt-core 1.8+, which ships native unit_tests — the given/expect fixture format this guide uses.
• A spatial adapter that runs the fixtures: dbt-postgres against PostGIS 3.x, and/or dbt-duckdb with the spatial extension for fast local runs.
• The macro under test already committed under macros/ and invoked by at least one model, so a unit test has a compilation target.
• WKT literals for your fixture geometries — points, rings, and at least one deliberately awkward case (a POINT Z, a self-touching ring) per macro.
• Connection secrets surfaced through dbt’s env_var() pattern so the same suite runs locally and in CI without edits.

Step-by-step instructions

1. Wrap the macro in a thin model under test

dbt unit tests target a model, not a macro directly, so give the macro a minimal model that does nothing but invoke it. Keep the model a pure passthrough — one input ref, the macro applied to a geometry column, no incidental logic — so a failing test can only mean the macro is wrong.

-- models/intermediate/int_region_centroids.sql
SELECT
  region_id,
  {{ safe_centroid('geom') }} AS centroid
FROM {{ ref('stg_regions') }}

-- macros/spatial/safe_centroid.sql
{% macro safe_centroid(geom_col) %}
  ST_Centroid(ST_MakeValid({{ geom_col }}))
{% endmacro %}

Verify the model compiles and the macro expands to the SQL you expect:

dbt compile --select int_region_centroids
# Inspect target/compiled/.../int_region_centroids.sql — the SELECT should read
# ST_Centroid(ST_MakeValid(geom)) AS centroid, with no stray Jinja left over.

2. Write WKT given/expect fixtures as a unit test

Define the unit test in a YAML file beside the model. Use format: sql for both given and expect so you can build geometries with ST_GeomFromText from readable WKT, and assert against a hand-computable result — the centroid of the unit square is exactly POINT(1 1).

# models/intermediate/_int_region_centroids.yml
unit_tests:
  - name: safe_centroid_of_square_is_center
    model: int_region_centroids
    given:
      - input: ref('stg_regions')
        format: sql
        rows: |
          SELECT 1 AS region_id,
                 ST_GeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))', 4326) AS geom
    expect:
      format: sql
      rows: |
        SELECT 1 AS region_id,
               ST_GeomFromText('POINT(1 1)', 4326) AS centroid

Verify the test runs and passes against the fixture, with no real table touched:

dbt test --select int_region_centroids
# PASS ... safe_centroid_of_square_is_center
# The given rows are injected as the model's input; stg_regions is never read.

3. Normalize geometry so the comparison is stable

Direct geometry comparison only works when the output is bit-for-bit deterministic, as a centroid is. The moment a macro buffers, reprojects, or reorders vertices, the binary WKB differs across PROJ/GEOS versions even when the shape is identical — the classic false failure. Assert on a normalized text form instead. Emit ST_AsText over a precision-reduced geometry so tiny floating-point drift cannot break the test.

# models/intermediate/_int_zone_buffers.yml
unit_tests:
  - name: buffer_100m_keeps_center_and_area_band
    model: int_zone_buffers
    given:
      - input: ref('stg_sites')
        format: sql
        rows: |
          SELECT 1 AS site_id,
                 ST_GeomFromText('POINT(0 0)', 3857) AS geom
    expect:
      format: sql
      rows: |
        SELECT 1 AS site_id,
               'POINT(0 0)' AS center_wkt,
               true          AS area_in_band

The model under test exposes normalized, comparable columns rather than the raw buffered polygon:

-- models/intermediate/int_zone_buffers.sql
WITH buffered AS (
  SELECT site_id, {{ buffer_meters('geom', 100) }} AS geom
  FROM {{ ref('stg_sites') }}
)
SELECT
  site_id,
  ST_AsText(ST_ReducePrecision(ST_Centroid(geom), 0.5)) AS center_wkt,
  ST_Area(geom) BETWEEN 30000 AND 32000                 AS area_in_band
FROM buffered

Verify the normalization holds by rerunning after a GEOS/PROJ upgrade — a test that compares ST_AsText(ST_ReducePrecision(...)) or a bounded ST_Area should survive library bumps that a raw-geometry comparison would not.

4. Assert the emitted SQL per adapter with a compile assertion

Unit tests confirm results on the adapter you ran them on; they do not confirm that a different adapter’s branch emits the right syntax. Cover that with a singular test that renders the macro into a Jinja variable at compile time and returns a failing row when the dialect is wrong for the target. It inspects only the compiled string, so it runs without any data.

-- tests/assert_st_dwithin_dialect.sql
{% set rendered = st_dwithin('a.geom', 'b.geom', 500) %}
{% set ok = ('::geography' in rendered) if target.type == 'postgres'
            else ('ST_DWITHIN' in rendered) %}

-- Emits a row (test failure) only when the rendered SQL is wrong for the target
SELECT '{{ rendered }}' AS emitted_sql
WHERE {{ 'true' if not ok else 'false' }}

Verify the assertion fails loudly when the dialect regresses:

dbt test --select assert_st_dwithin_dialect --target postgres
# PASS when the PostGIS branch renders the ::geography cast.
# Temporarily break the macro and rerun — the test must FAIL, proving it bites.

5. Run the suite across adapters in CI

The payoff is running both the unit tests and the compile assertions under each adapter target on every pull request, so a macro can never merge having been proven on only one engine. A DuckDB target keeps the loop fast; PostGIS confirms production dialect. Wiring this into GitHub Actions follows the same shape as the broader spatial testing in CI pipelines setup.

# Fast inner loop on DuckDB, then confirm on PostGIS
dbt test --select tag:spatial_macros --target ci_duckdb
dbt test --select tag:spatial_macros --target prod_postgres

Verify both targets report the same pass set. A divergence — green on DuckDB, red on PostGIS — is exactly the cross-dialect bug the suite exists to catch; route the difference through a dispatched macro rather than patching the model.

Testing the two levels

Configuration reference

Gotchas & edge cases

• Raw geometry columns compare by binary form. Two topologically identical geometries with different vertex order or precision serialize to different WKB and fail an equality check. Compare ST_AsText(ST_ReducePrecision(geom, tol)), or assert topological equality with ST_Equals in a singular test, rather than diffing the geometry column directly.
• SRID travels with the geometry. ST_GeomFromText('POINT(1 1)') (no SRID) and ST_GeomFromText('POINT(1 1)', 4326) are not equal under a binary compare. Always pass the SRID in fixtures, matching what the model emits.
• ST_Equals ignores Z. A POINT Z and its 2D shadow are ST_Equals-true because the predicate is 2D. If a macro must preserve or drop Z, assert on ST_AsText (which shows the Z ordinate) instead of ST_Equals.
• DuckDB and PostGIS diverge on function coverage. ST_ReducePrecision and ST_MakeValid exist on both modern builds but with version-sensitive behaviour; pin the extension/PostGIS version in CI so a fixture’s expected output does not drift under you.
• Unit tests need a compilable model. A macro with no model that calls it has nothing to unit-test. Keep at least the thin passthrough model in the project, tagged so CI selects it.
• Empty and null geometry. Add a fixture row with NULL geom and one with ST_GeomFromText('POINT EMPTY'); centroid and buffer macros should return NULL/empty predictably, and pinning that stops a downstream NOT NULL surprise.

FAQ

Related

• Building Custom Spatial Macros — the dispatch and parameterization patterns these tests protect.
• Writing Reusable ST_DWithin Macros in dbt — a predicate macro whose per-adapter dialect the compile assertion checks.
• Cross-Engine UDF Portability — the parity guarantee cross-adapter unit tests enforce.

Up: Part of Building Custom Spatial Macros.