Detecting SRID mismatches with dbt tests

This page shows you how to build a generic dbt test that flags any geometry whose ST_SRID differs from the canonical SRID — reporting the offending value, catching mixed-SRID joins, and running across PostGIS, DuckDB, and BigQuery.

When to use this approach

Add SRID-detection tests — rather than trusting that upstream normalization held — when any of these hold:

• You already enforce a canonical SRID and want a safety net. Stamping geometries at staging prevents most mismatches; a detection test proves the stamp held everywhere downstream. The stamping recipe is enforcing a canonical SRID across dbt models, and the policy behind both is the CRS governance policy.
• You join two geometry sources and a silent SRID mismatch would return zero rows or nonsense distances. A pre-join assertion catches it at build time.
• Distances or areas look wrong and you need to localize which model introduced a stray SRID. A test that reports the actual value found turns a guessing game into a single failing row. For the underlying reprojection mechanics, see automating CRS conversions in dbt pipelines.

Prerequisites

• dbt Core 1.5+ with a spatial adapter — dbt-postgres against PostGIS 3.x for production, and/or dbt-duckdb with the spatial extension for CI.
• A declared canonical SRID (see the CRS governance policy):

# dbt_project.yml
vars:
  canonical_srid: "{{ env_var('DBT_CANONICAL_SRID', '26910') }}"

• dbt-utils installed if you want the ready-made expression_is_true helper as an alternative to a hand-written generic test.
• Permission to run tests in CI so a mismatch fails the pipeline rather than a downstream report.

Step-by-step instructions

1. Write a generic test that reports the offending SRID

A test that only fails is less useful than one that tells you what it found. This generic test returns the distinct wrong SRIDs and how many rows carry each, so the failure message points straight at the model and value to fix.

-- tests/generic/srid_matches.sql
{% test srid_matches(model, column_name, srid=none) %}
    {% set expected = srid or var('canonical_srid') %}
    select
        ST_SRID({{ column_name }}) as found_srid,
        count(*) as n_rows
    from {{ model }}
    where {{ column_name }} is not null
      and ST_SRID({{ column_name }}) <> {{ expected }}
    group by 1
{% endtest %}

# models/marts/_marts.yml
version: 2
models:
  - name: mart_service_areas
    columns:
      - name: geometry
        tests:
          - srid_matches      # defaults to var('canonical_srid')

Verify the test passes on clean data and names the culprit on dirty data:

dbt test -s mart_service_areas
# A failure prints the failing rows: found_srid | n_rows, e.g. 4326 | 12.

2. Detect a mixed-SRID column within one model

A single column can hold multiple SRIDs when rows from differently-tagged sources are unioned without normalization. A count of distinct SRIDs greater than one is the signature. This test flags the column rather than individual rows.

-- tests/generic/single_srid.sql
{% test single_srid(model, column_name) %}
    select count(*) as n_distinct_srids
    from (
        select distinct ST_SRID({{ column_name }})
        from {{ model }}
        where {{ column_name }} is not null
    ) s
    having count(*) > 1
{% endtest %}

Verify by pointing it at a model that unions two sources; it fails when they were not both normalized first:

-- ad-hoc equivalent to eyeball the spread
select ST_SRID(geometry) as srid, count(*)
from {{ ref('mart_service_areas') }}
group by 1;
-- Expect exactly one row. Two or more rows means a mixed-SRID column.

3. Catch the mixed-SRID join before it ships

The most damaging mismatch is between the two sides of a join: each column is internally consistent, but they disagree with each other, so the spatial predicate compares incompatible frames and returns zero rows or wrong distances. Assert the two inputs agree before the join model builds by testing a small comparison model.

Each test catches a different failure shape — a stray value inside one column, several values mixed in one column, or two columns that each look fine but disagree across a join:

Assert the two inputs agree before the join model builds by testing a small comparison model.

-- models/tests/int_join_srid_guard.sql
-- A tiny model whose only job is to surface a cross-input SRID disagreement.
select
    (select distinct ST_SRID(geom) from {{ ref('stg_customer_locations') }}) as left_srid,
    (select distinct ST_SRID(geom) from {{ ref('stg_service_zones') }})     as right_srid

# models/tests/_tests.yml
version: 2
models:
  - name: int_join_srid_guard
    tests:
      - dbt_utils.expression_is_true:
          expression: "left_srid = right_srid and left_srid = {{ var('canonical_srid') }}"

Verify the guard fails when one side drifts — temporarily point stg_service_zones at an unnormalized source and confirm dbt build goes red before the join model runs.

4. Wire detection into the build gate

A detection test is only a safety net if it runs on every change. Add the tests to the CI build so a stray SRID fails the pull request, not the dashboard.

dbt build --select +mart_service_areas --target ci
# Runs upstream models and all attached srid_matches / single_srid tests
# before the mart materializes; any mismatch fails the CI job.

Verify the gate is wired by checking the test appears in the run manifest:

dbt ls --resource-type test | grep -E 'srid_matches|single_srid'
# Confirms the detection tests are part of the selected build.

Configuration reference

Gotchas & edge cases

• SRID = 0 reads as a mismatch, correctly. An untagged geometry has SRID 0, which differs from any canonical value, so srid_matches flags it — which is the desired behavior. Repair the tag upstream with ST_SetSRID before normalizing, per the enforcing guide.
• A same-column test misses cross-input joins. srid_matches on each side can both pass while the two sides disagree with each other. Use the step-3 cross-input guard for join models specifically.
• Nulls must be excluded. ST_SRID(NULL) behavior varies; always filter column_name is not null in the test so a null geometry does not masquerade as SRID 0.
• DuckDB stores SRID differently. The DuckDB spatial extension does not track a per-geometry SRID the way PostGIS does — ST_SRID may return 0 for geometry loaded without an explicit CRS. In CI on DuckDB, assert the SRID you set explicitly rather than assuming it round-trips from the source.
• BigQuery has no per-column SRID. GEOGRAPHY is fixed to EPSG:4326, so ST_SRID is not the right check; assert the storage type and reprojection points instead, and choose the engine per choosing the right spatial adapter.

FAQ

Related

• Enforcing a Canonical SRID Across dbt Models — the stamping recipe these tests verify held.
• CRS Governance Policy — the policy that defines the canonical SRID these tests compare against.
• Choosing the Right Spatial Adapter — how SRID handling differs across PostGIS, DuckDB, and BigQuery.

Up: Part of CRS Governance Policy.