Testing Geometry Validity with dbt Generic Tests

This page shows you how to package ST_IsValid as a reusable dbt generic test, wire it into a model’s schema YAML, run it inside dbt build, and turn each failure into an actionable diagnosis with ST_IsValidReason.

When to use this approach

A reusable generic test — rather than a one-off singular test or a manual ST_IsValid query — is the right tool when:

• The same validity rule applies to many geometry columns. A generic test is written once and attached to every geometry column by name, so parcels, service zones, and sensor points all inherit the same guarantee. This is the test that the geometry validation and data quality sweep depends on.
• You want validity failures to block the build, not surface after a query looks wrong. A generic test attached in schema YAML runs under dbt build and fails the run, unlike an ad-hoc check nobody remembers to run.
• You need the same test to run in CI on a different engine. If production is PostGIS but CI validates on the DuckDB spatial extension, one generic test keeps the assertion identical across both. If instead your goal is to route invalid rows somewhere rather than fail, see quarantining invalid geometries in staging.

Prerequisites

• dbt Core 1.7+ with a spatial adapter — dbt-postgres against PostGIS 3.x, or dbt-duckdb with the spatial extension loaded.
• ST_IsValid and ST_IsValidReason reachable on the target. On PostGIS both ship with the extension; on DuckDB, ST_IsValid is available but ST_IsValidReason is not, which the test below accounts for. Extension setup is in setting up PostGIS with dbt.
• A tests/generic/ directory in the project (dbt discovers generic tests placed there or under any configured test-paths).
• A staging model with a geometry column to attach the test to.

Step-by-step instructions

1. Write the reusable generic test

A dbt generic test is a macro wrapped in {% test %} that returns the failing rows: the test passes when the query returns zero rows. Place it at tests/generic/assert_valid_geometry.sql. The test selects every row whose geometry fails ST_IsValid, and — where the engine supports it — carries the reason along so the failure output is self-explanatory.

-- tests/generic/assert_valid_geometry.sql
{% test assert_valid_geometry(model, column_name, ignore_null=true) %}

with validation as (
    select
        {{ column_name }} as geom
    from {{ model }}
    where {{ column_name }} is not null
      and not ST_IsValid({{ column_name }})
    {% if not ignore_null %}
      union all
      select {{ column_name }} as geom
      from {{ model }}
      where {{ column_name }} is null
    {% endif %}
)

select
    geom,
    {% if target.type == 'postgres' %}
      ST_IsValidReason(geom) as invalid_reason
    {% else %}
      'invalid geometry' as invalid_reason
    {% endif %}
from validation

{% endtest %}

The ignore_null argument is deliberate: null-handling belongs to a separate not_null test by default, but some pipelines want validity and non-null enforced by one assertion, so the flag makes that a per-attachment choice. The target.type branch keeps the test portable — PostGIS attaches ST_IsValidReason, other engines fall back to a constant string.

Verify the test macro compiles and is discovered by dbt:

dbt parse            # fails loudly if the {% test %} block has a syntax error
dbt list --resource-type test | grep assert_valid_geometry

2. Wire the test into schema YAML

Attach the test to the geometry column of the model it guards. Because it is a generic test, it goes under columns[].tests exactly like not_null or unique, and it can take the ignore_null argument and a config block for severity.

# models/staging/_staging.yml
version: 2
models:
  - name: stg_parcels
    columns:
      - name: geometry
        description: "Parcel boundary, canonical SRID, validated at staging."
        data_type: geometry
        tests:
          - not_null
          - assert_valid_geometry:
              ignore_null: true
              config:
                severity: error
                # surface the offending rows in the failures table for triage
                store_failures: true

Verify dbt resolves the attachment against the model without error:

dbt ls --select stg_parcels --output json | grep -q stg_parcels && echo "model resolves"
dbt parse   # confirms the test args match the macro signature

3. Run the test inside dbt build

dbt build runs models and their tests together in DAG order, so the validity check executes immediately after stg_parcels materializes and before any downstream model consumes it. This is what makes an invalid geometry fail the run rather than the dashboard.

# build the model and everything it feeds, running tests inline
dbt build --select +stg_parcels

# or run only the geometry tests during fast iteration
dbt test --select stg_parcels,test_name:assert_valid_geometry

Verify the test actually catches a bad geometry by seeding a known-invalid fixture — a bowtie polygon self-intersects — and confirming the build goes red:

-- seeds/test_fixtures/invalid_geom_sample.sql (or a seed CSV loaded in CI)
-- a classic self-intersecting "bowtie" polygon: ST_IsValid returns false
select ST_GeomFromText(
  'POLYGON((0 0, 1 1, 1 0, 0 1, 0 0))', 4326
) as geometry

Wiring fixtures like this into CI is covered in seeding geometry fixtures for dbt tests.

4. Interpret failures with ST_IsValidReason

When the test fails with store_failures: true, the offending rows land in a failures table (<schema>_dbt_test__audit.assert_valid_geometry_stg_parcels_geometry or similar). Query it to read the diagnosis:

-- inspect why each row failed
select invalid_reason, count(*) as n
from {{ target.schema }}_dbt_test__audit.assert_valid_geometry_stg_parcels_geometry
group by invalid_reason
order by n desc;

Common reasons map directly to source problems: Self-intersection and Ring Self-intersection are repairable with ST_MakeValid; Too few points in geometry component usually means a truncated import; Hole lies outside shell signals a malformed multipolygon. For the rows that ST_MakeValid can rescue, route them through the repair path rather than failing the build outright — that decision is the subject of the quarantine pattern.

Verify your read of the failure by repairing one row and re-testing:

select ST_IsValid(ST_MakeValid(geometry)) as fixed
from {{ ref('stg_parcels') }}
where not ST_IsValid(geometry)
limit 1;   -- expect true for self-intersections and unclosed rings

Configuration reference

Gotchas & edge cases

• DuckDB lacks ST_IsValidReason. The target.type branch in the test falls back to a constant string on non-PostGIS engines. If you need the reason in CI, run that leg on PostGIS, or accept a plain pass/fail on DuckDB.
• store_failures needs write grants. The audit schema must be creatable by the run’s role; without it, store_failures: true errors instead of persisting rows. Grant CREATE on the audit schema in CI.
• Valid is not the same as GEOS-clean. A geometry can pass ST_IsValid yet still break ST_Union because of near-duplicate vertices. If topology ops fail on “valid” input, add an ST_SnapToGrid step — validity is necessary, not always sufficient.
• Testing the raw source hides the fix. Attach the test to the validated staging model, not the raw source, so it verifies the sweep actually worked. Testing source() only tells you the input was dirty, which you already assumed.
• A geometry stored as text passes nothing meaningfully. If the column drifted to text via a WKB round-trip, ST_IsValid may error or coerce unexpectedly. Pin the column with a schema contract data_type: geometry so structural drift fails at compile time.

FAQ

Related

• Geometry Validation & Data Quality — the full staging validity sweep this test plugs into.
• Quarantining Invalid Geometries in Staging — route invalid rows aside instead of failing the build.
• Seeding Geometry Fixtures for dbt Tests — build the known-invalid fixtures that prove the test works.

Up: Part of Geometry Validation & Data Quality.