Quarantining Invalid Geometries in Staging

This page shows you how to split a dbt staging model into two paths — a clean stream of valid geometry and a quarantine table of everything that failed — so invalid rows are captured with their reason and alerted on instead of silently dropped or crashing a downstream join.

When to use this approach

Quarantining, rather than failing the whole build or blindly repairing, is the right call when:

• You cannot afford to drop rows silently, but must not block the pipeline either. A quarantine split lets valid data flow to marts while invalid rows are set aside for review, so one bad geometry does not halt every downstream model. This is the routing half of the geometry validation and data quality sweep.
• Repair is not always acceptable. For legal, cadastral, or regulatory boundaries, having ST_MakeValid move a vertex is worse than rejecting the row. Quarantine preserves the original for a human decision. If you only need a hard pass/fail gate instead, use testing geometry validity with dbt generic tests.
• You want a feedback loop to the upstream data owner. A persisted quarantine table with ST_IsValidReason is evidence you can hand back to whoever produces the source, rather than a transient test failure that disappears on the next green run.

Prerequisites

• dbt Core 1.7+ with a spatial adapter — dbt-postgres against PostGIS 3.x (for ST_IsValidReason), or dbt-duckdb with the spatial extension.
• ST_IsValid, ST_MakeValid, ST_IsValidReason reachable on the target. Setup is in setting up PostGIS with dbt.
• A canonical SRID decided, so the split can also route wrong-SRID rows to quarantine. Reprojection mechanics are in automating CRS conversions in dbt pipelines.
• CREATE grants on the target schema so both the clean and quarantine models can materialize as tables.

# dbt_project.yml
vars:
  canonical_srid: "{{ env_var('DBT_CANONICAL_SRID', '4326') }}"
  # 'repair' merges ST_MakeValid successes back into clean; 'reject' keeps them quarantined
  invalid_geometry_policy: "{{ env_var('DBT_INVALID_GEOM_POLICY', 'repair') }}"

Step-by-step instructions

1. Build a single classified base model

The cleanest design classifies every row exactly once in an intermediate model, so the valid and quarantine models are just filtered views of the same source of truth. This avoids the classic bug where the two paths use slightly different predicates and a row lands in neither — or both.

-- models/staging/stg_parcels_classified.sql
{{ config(materialized='ephemeral') }}

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

stamped as (
    select
        parcel_id,
        ST_SetSRID(geom, {{ var('canonical_srid') }}) as geom
    from source
),

classified as (
    select
        parcel_id,
        geom,
        geom is null                              as is_null,
        ST_SRID(geom) <> {{ var('canonical_srid') }} as wrong_srid,
        coalesce(ST_IsValid(geom), false)         as valid_on_arrival,
        ST_MakeValid(geom)                        as geom_repaired
    from stamped
)

select
    parcel_id,
    geom,
    geom_repaired,
    is_null,
    wrong_srid,
    valid_on_arrival,
    coalesce(ST_IsValid(geom_repaired), false)    as valid_after_repair
from classified

Verify every source row is classified exactly once:

select count(*) as total,
       count(*) filter (where valid_on_arrival)            as clean,
       count(*) filter (where not valid_on_arrival)        as needs_attention
from {{ ref('stg_parcels_classified') }};
-- clean + needs_attention must equal total

2. Derive the clean staging model

The clean model selects rows that are valid — either on arrival, or after repair when policy allows it. Branch the predicate on invalid_geometry_policy so the same code serves a repair-aggressive telemetry feed and a reject-only cadastral feed.

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

select
    parcel_id,
    {% if var('invalid_geometry_policy') == 'repair' %}
      case when valid_on_arrival then geom else geom_repaired end as geometry
    {% else %}
      geom as geometry
    {% endif %}
from {{ ref('stg_parcels_classified') }}
where not is_null
  and not wrong_srid
  and {% if var('invalid_geometry_policy') == 'repair' %}
        valid_after_repair
      {% else %}
        valid_on_arrival
      {% endif %}

Verify the clean model contains only valid, correctly-projected geometry:

select count(*) as leaks
from {{ ref('stg_parcels') }}
where geometry is null
   or not ST_IsValid(geometry)
   or ST_SRID(geometry) <> {{ var('canonical_srid') }};
-- expect 0

3. Derive the quarantine model with captured reasons

The quarantine model is the complement: everything the clean model excluded, tagged with a machine-readable failure category and the human-readable ST_IsValidReason. Persist it as a table so it survives across runs for triage and trending.

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

select
    parcel_id,
    geom as original_geometry,
    case
        when is_null      then 'null_geometry'
        when wrong_srid   then 'wrong_srid'
        when not valid_after_repair then 'irreparable'
        else 'repaired_but_rejected_by_policy'
    end as failure_category,
    {% if target.type == 'postgres' %}
      ST_IsValidReason(geom) as invalid_reason,
    {% else %}
      'see failure_category' as invalid_reason,
    {% endif %}
    current_timestamp as quarantined_at
from {{ ref('stg_parcels_classified') }}
where is_null
   or wrong_srid
   or {% if var('invalid_geometry_policy') == 'repair' %}
        not valid_after_repair
      {% else %}
        not valid_on_arrival
      {% endif %}

Verify the two paths partition the source with no overlap and no gap:

select
  (select count(*) from {{ ref('stg_parcels') }})            as clean_rows,
  (select count(*) from {{ ref('stg_parcels_quarantine') }}) as quarantined_rows,
  (select count(*) from {{ source('gis', 'parcels_raw') }})  as source_rows;
-- clean_rows + quarantined_rows should equal source_rows

4. Alert on quarantine growth

A quarantine that nobody watches is just a slower silent drop. Attach a dbt test that fails — or warns — when the quarantine exceeds an acceptable share of input, so a schema change upstream trips an alert in CI rather than surfacing weeks later.

# models/staging/_staging.yml
version: 2
models:
  - name: stg_parcels_quarantine
    description: "Invalid, null, or wrong-SRID parcel geometries held for review."
    tests:
      - dbt_utils.expression_is_true:
          name: parcels_quarantine_within_budget
          expression: >
            (select count(*) from {{ ref('stg_parcels_quarantine') }})
            <= (select count(*) * 0.02 from {{ source('gis', 'parcels_raw') }})
          config:
            severity: warn   # tighten to error once the feed is stable
    columns:
      - name: failure_category
        tests:
          - accepted_values:
              values: ['null_geometry', 'wrong_srid', 'irreparable', 'repaired_but_rejected_by_policy']

Verify the alert fires as intended by running it against current data:

dbt test --select stg_parcels_quarantine
# a warn/error here means quarantine crossed the 2% budget — investigate the source

Wiring this warning into a failing CI check is covered in spatial testing in CI pipelines.

5. Optionally auto-repair and re-admit

When policy is repair, the repaired rows are already merged into the clean model by step 2 — but it is worth confirming what ST_MakeValid actually did, since it can change geometry type (a self-intersecting polygon may become a GeometryCollection). Normalize the repair output so downstream type expectations hold.

-- inside stg_parcels.sql, replace geom_repaired usage with a type-safe repair
ST_CollectionExtract(ST_MakeValid(geom), 3) as geometry  -- 3 = keep polygonal parts only

Verify the repair preserved geometry type and did not silently empty the row:

select
  count(*) filter (where ST_GeometryType(geometry) <> 'ST_Polygon') as type_changed,
  count(*) filter (where ST_IsEmpty(geometry))                      as emptied
from {{ ref('stg_parcels') }};
-- both should be 0 for a polygon feed

Configuration reference

Gotchas & edge cases

• ST_MakeValid changes geometry type. Repairing a self-intersecting polygon can yield a GeometryCollection of polygon plus stray lines. Always follow repair with ST_CollectionExtract(..., 3) for a polygon feed so a downstream geometry(Polygon) contract still holds.
• SRID stamped after validation routes everything to quarantine. Stamp or transform the SRID before the validity classification, or every row trips the wrong_srid branch. Use ST_Transform to reproject and ST_SetSRID only to label a known-but-unstamped frame.
• Quarantine table grows unbounded. Persisted quarantine tables accumulate. Add a quarantined_at timestamp (as above) and periodically prune or snapshot resolved rows, or the table becomes its own data-quality problem.
• DuckDB has no ST_IsValidReason. The target.type branch substitutes the failure_category tag on non-PostGIS engines, so triage still works, just with less coordinate-level detail. Run the reason-carrying leg on PostGIS if you need the fault location.
• Repaired rows re-fail downstream. A row ST_MakeValid marks valid can still break ST_Union on near-duplicate vertices. If topology ops fail on repaired input, add ST_SnapToGrid before re-admitting, or send borderline repairs back to quarantine for review.

FAQ

Related

• Geometry Validation & Data Quality — the full staging validity sweep this quarantine split completes.
• Testing Geometry Validity with dbt Generic Tests — the hard pass/fail gate alternative to quarantining.
• Automating CRS Conversions in dbt Pipelines — fix wrong-SRID rows before they reach the quarantine branch.

Up: Part of Geometry Validation & Data Quality.