Geometry Validation & Data Quality

An invalid geometry does not announce itself. A polygon whose boundary crosses itself, a ring that never closes, a multipolygon with a zero-area sliver — these parse cleanly, load into a geometry column without complaint, and sit in staging looking exactly like every valid neighbour. The failure surfaces three models downstream, when an ST_Intersects join returns half the rows it should, or an ST_Union throws GEOSUnion: TopologyException, or a coverage metric drifts by a few percent and nobody can say why. This page, part of Core Fundamentals & Architecture for dbt Geospatial, is about closing that gap: enforcing validity, SRID consistency, and null guards as first-class dbt tests at the staging boundary, so a bad geometry fails the build instead of corrupting a join.

The discipline is cheap to adopt and expensive to skip. Every spatial predicate — ST_Contains, ST_Intersects, ST_DWithin — assumes its operands satisfy the OGC Simple Features validity rules, and the GEOS library that PostGIS and DuckDB both lean on will either error or return a wrong answer when they do not. Treating validity as an ingest-time contract, rather than a thing you check after a query looks off, turns a whole class of silent corruption into a loud, early, reproducible test failure. The two mechanics that make this tractable — a reusable generic test and a quarantine split in staging — get their own deep treatments in testing geometry validity with dbt generic tests and quarantining invalid geometries in staging.

Prerequisites checklist

Before wiring validity tests into a project, confirm the pieces below are in place:

• dbt-core ≥ 1.7 with a spatial adapter — dbt-postgres against PostGIS ≥ 3.3, or dbt-duckdb with the spatial extension for CI. The dbt_utils package installed for expression_is_true.
• PostGIS or DuckDB spatial functions reachable from your models — ST_IsValid, ST_MakeValid, and ST_IsValidReason must resolve on the target. Setup steps are in setting up PostGIS with dbt.
• A canonical project SRID already decided, so the SRID-consistency assertion has a value to check against. Policy lives in spatial reference system management.
• A staging layer that owns validation — raw sources land untouched; staging is the single boundary where geometry is validated, repaired, or quarantined before anything downstream calls a spatial predicate.
• CI that runs dbt build, not just dbt run, so tests execute on every pull request rather than after deploy. The pattern is covered in spatial testing in CI pipelines.

# packages.yml
packages:
  - package: dbt-labs/dbt_utils
    version: [">=1.1.0", "<2.0.0"]

Architecture context: the validity sweep

Validation is a staging-layer responsibility, and it has exactly one job: guarantee that every geometry crossing into the intermediate layer is non-null, carries the canonical SRID, and is topologically valid. Everything downstream — joins, unions, buffers, marts — is allowed to assume that contract holds. The sweep below is the shape most production staging models take: a single ST_IsValid gate routes each row down one of three paths.

Each arrow is a routing decision, not a mutation applied blindly. Valid geometries pass through untouched — running ST_MakeValid on already-valid input is wasted compute and can subtly re-order vertices. Repairable geometries go through ST_MakeValid and, if the repair succeeds, rejoin the clean output. Anything that is null, carries the wrong SRID, or survives ST_MakeValid still invalid is written to a quarantine relation alongside its ST_IsValidReason text, where it can be triaged instead of silently dropped. A generic test on the clean output is the backstop: if a single invalid geometry ever leaks past the sweep, dbt build fails.

Configuration walkthrough

Two settings make validity enforcement consistent across a project. The first pins the canonical SRID and a validity policy as project variables, so every staging model reads the same values. The second declares that staging validation runs as a build-blocking test tier, not an advisory one.

# dbt_project.yml
vars:
  canonical_srid: "{{ env_var('DBT_CANONICAL_SRID', '4326') }}"
  # 'repair' attempts ST_MakeValid; 'reject' quarantines without repair
  invalid_geometry_policy: "{{ env_var('DBT_INVALID_GEOM_POLICY', 'repair') }}"

models:
  my_project:
    staging:
      +materialized: view
      +tags: ["spatial", "staging"]

A validity check is only as trustworthy as its severity. Configure the geometry tests to error in CI so a regression blocks the merge, while allowing a warn threshold during backfills where a known bad batch is being triaged rather than gated:

# models/staging/_staging.yml
version: 2
models:
  - name: stg_parcels
    columns:
      - name: geometry
        tests:
          - assert_valid_geometry:
              config:
                severity: "{{ 'warn' if var('backfill_mode', false) else 'error' }}"

The assert_valid_geometry generic test referenced here is the reusable unit built step by step in testing geometry validity with dbt generic tests.

Core implementation

The staging validity sweep in SQL

A staging model that implements the three-path sweep keeps each concern in a named CTE, so the routing logic is auditable rather than buried in a nested case. Note that the repair path calls ST_MakeValid only on rows that failed the gate — valid geometry is never mutated.

-- models/staging/stg_parcels.sql
with source as (
    select parcel_id, geom
    from {{ source('gis', 'parcels_raw') }}
),

typed as (
    select
        parcel_id,
        -- stamp SRID before any validity test; a wrong-SRID geom is a data-quality failure, not a repair target
        ST_SetSRID(geom, {{ var('canonical_srid') }}) as geom
    from source
    where geom is not null
),

routed as (
    select
        parcel_id,
        geom,
        ST_IsValid(geom) as is_valid,
        case
            when ST_IsValid(geom) then geom
            else ST_MakeValid(geom)
        end as geom_repaired
    from typed
)

select
    parcel_id,
    geom_repaired as geometry
from routed
where ST_IsValid(geom_repaired)   -- keep only rows that are valid after the sweep

The where ST_IsValid(geom_repaired) filter is deliberate: it discards the rare geometry that ST_MakeValid cannot rescue, so the clean output is provably valid. Those discarded rows are exactly what the quarantine pattern captures rather than drops, so an operator can see what was rejected and why.

Guarding SRID consistency and nulls

Validity is one of three ingest-time contracts. The other two — a single SRID across the column, and no unexpected nulls — are cheap declarative tests using dbt_utils.expression_is_true. Assert them together in the same schema block:

# models/staging/_staging.yml
version: 2
models:
  - name: stg_parcels
    config:
      contract:
        enforced: true
    columns:
      - name: parcel_id
        tests:
          - not_null
          - unique
      - name: geometry
        data_type: geometry
        tests:
          - not_null
          - assert_valid_geometry
          - dbt_utils.expression_is_true:
              name: stg_parcels_geometry_single_srid
              expression: "ST_SRID(geometry) = {{ var('canonical_srid') }}"

The contract.enforced: true block is doing quiet but important work. A schema contract pins the column’s data_type to geometry, so a later refactor that accidentally coerces the column to text or bytea — a common way a WKB round-trip goes wrong — fails at compile time rather than producing a column that passes not_null while being unusable as geometry. SRID governance at the whole-project scale is the subject of detecting SRID mismatches with dbt tests.

Validation & testing

Reading failures with ST_IsValidReason

When assert_valid_geometry fails, the raw fact that a geometry is invalid is not actionable — you need to know why. ST_IsValidReason returns a human-readable diagnosis and, in PostGIS, the coordinate of the fault:

-- ad-hoc triage: what exactly is wrong, and where
select
    parcel_id,
    ST_IsValidReason(geometry) as reason,
    ST_AsText(ST_PointOnSurface(geometry)) as near
from {{ ref('stg_parcels') }}
where not ST_IsValid(geometry)
order by parcel_id
limit 50;

Typical output — Self-intersection[-122.41 37.77], Ring Self-intersection, Too few points in geometry component — maps directly to a source-data problem. Self-intersections and unclosed rings are what ST_MakeValid repairs cleanly; “too few points” usually signals a truncated or corrupt import that repair cannot fix and quarantine should catch.

A validity funnel to quantify data quality

A single pass/fail test tells you whether the build is green, but not whether data quality is improving or degrading over time. A validity funnel — a small model that counts rows surviving each stage of the sweep — turns geometry quality into a metric you can trend and alert on:

-- models/staging/stg_parcels_validity_funnel.sql
{{ config(materialized='table', tags=['spatial', 'dq']) }}

with source as (
    select geom from {{ source('gis', 'parcels_raw') }}
)

select
    count(*)                                                          as raw_rows,
    count(geom)                                                       as non_null_rows,
    count(*) filter (where ST_IsValid(geom))                         as valid_on_arrival,
    count(*) filter (where not ST_IsValid(geom)
                       and ST_IsValid(ST_MakeValid(geom)))           as repaired_rows,
    count(*) filter (where not ST_IsValid(ST_MakeValid(geom)))       as irreparable_rows
from source

Snapshotting this table on each run gives an early-warning signal: a spike in irreparable_rows means an upstream export changed shape, long before the failure reaches a mart. The same counting discipline feeds source-level monitoring in source freshness for geometry feeds.

Advanced patterns

Policy-driven repair vs. reject. Not every pipeline should auto-repair. A cadastral or legal-boundary dataset where ST_MakeValid silently moving a vertex is unacceptable should run in reject mode, quarantining every invalid row for human review; a high-volume telemetry feed where approximate geometry is fine can repair aggressively. Branch the staging sweep on the invalid_geometry_policy var so the same model serves both stances without a fork.

Validity-aware incremental models. In an incremental staging model, re-validating the entire table every run is wasteful. Gate the ST_IsValid sweep behind is_incremental() so only newly landed rows are checked, and keep a dateModified watermark so a source that re-emits a previously-valid geometry as invalid is re-caught. This composes with the large-table strategies in incremental materialization for large geometry tables.

Cross-engine validity parity. DuckDB’s spatial extension and PostGIS both expose ST_IsValid and ST_MakeValid, but GEOS versions differ, so a geometry ruled invalid on one can pass on the other. Pin the validity test to run in CI on the same engine that serves production, or accept that DuckDB is a fast first filter and PostGIS is the authority. Running the suite in a lightweight validator is covered in DuckDB as a lightweight CI validator.

Contract-backed geometry types. Pair every geometry column’s validity test with a schema contract that pins both data_type and, where the engine supports it, the geometry subtype and SRID modifier (geometry(Polygon, 4326) in PostGIS). The contract catches structural drift; the test catches topological drift. Together they close both halves of “this column still holds what I think it holds.”

Severity that follows the deployment stage. The same validity test should be advisory during an initial backfill and blocking in steady state. Drive severity from a project var — warn while a known-dirty history is being cleaned, error once the feed is stable — so the test tightens automatically as the pipeline matures instead of requiring a code change at each transition. Pair this with the error_if/warn_if thresholds so a single stubborn legacy row does not hold a merge hostage while still surfacing in the run summary.

Validation & data-quality metrics

Two questions deserve separate answers: “did the build stay green?” and “is the data getting cleaner or dirtier?” The generic test answers the first. The validity funnel from the previous section answers the second, but only if its output is retained. Snapshot the funnel model on each run and a few derived ratios become trendable signals:

• Arrival validity rate — valid_on_arrival / non_null_rows. A slow decline means the upstream producer’s export is degrading; a cliff means a format or projection change.
• Repair yield — repaired_rows / (repaired_rows + irreparable_rows). If ST_MakeValid is rescuing a shrinking fraction, the incoming corruption is getting more structural, not just topological.
• Quarantine share — quarantined rows as a fraction of source rows, the single number to alert on. A budget breach in CI is the earliest signal that something upstream broke.

These ratios turn geometry quality from a binary build status into a monitored metric, which is what makes a regression visible before it reaches a mart. The producer-facing side of this — knowing a feed went stale or changed shape at the source — is handled by source freshness for geometry feeds.

Troubleshooting

Structural changes to a validated column — tightening a contract, switching subtype, changing SRID — are breaking changes that deserve migration discipline; versioning spatial schemas in dbt covers backward-compatible rollout and rollback.

FAQ

Related

• Testing Geometry Validity with dbt Generic Tests — build the reusable assert_valid_geometry test end to end.
• Quarantining Invalid Geometries in Staging — split a staging model into valid and quarantined paths with captured reasons.
• Setting Up PostGIS with dbt — make ST_IsValid and ST_MakeValid reachable from your models.
• Spatial Testing in CI Pipelines — run the validity suite on every pull request.
• Detecting SRID Mismatches with dbt Tests — the SRID half of the ingest-time geometry contract.

↑ Back to Core Fundamentals & Architecture