Enforcing a canonical SRID across dbt models

This page shows you how to make one canonical SRID an enforced invariant across every dbt model — declared once as a project variable, applied by a normalize macro, and guarded by a generic test that fails the build on any deviation.

When to use this approach

Enforce a single canonical SRID centrally — rather than reprojecting ad hoc in each model — when any of these hold:

• Sources arrive in more than one SRID. If feeds land in a mix of EPSG:4326, EPSG:3857, and untagged SRID = 0, a central normalize step is the only reliable way to guarantee everything downstream shares a frame. The policy rationale is set out in the CRS governance policy.
• Distance or area bugs keep recurring from silent SRID drift. Centralizing the stamp fixes the whole class of bug in one place. For the complementary detection side — a test that finds existing mismatches — see detecting SRID mismatches with dbt tests.
• You will change the canonical SRID later (a new region, a switch to a projected frame). A single project var makes that a one-line change; hard-coded literals make it a risky sweep. If you are unsure which SRID to standardize on, weigh the geometry vs geography type trade-offs first.

Prerequisites

• dbt Core 1.5+ with a spatial adapter — dbt-postgres against PostGIS 3.x, and/or dbt-duckdb with the spatial extension for CI.
• CREATE on the target schema and permission to run generic tests in CI.
• An inventory of the SRIDs your sources actually emit — run SELECT DISTINCT ST_SRID(geom) … on each feed before you decide what to normalize from.
• The canonical SRID chosen and declared once in dbt_project.yml:

# dbt_project.yml
vars:
  canonical_srid: "{{ env_var('DBT_CANONICAL_SRID', '26910') }}"  # UTM Zone 10N (metric)

• Connection secrets wired through env_var() — never hardcode hosts or credentials in profiles.yml.

Step-by-step instructions

1. Declare the canonical SRID as a project var

The whole point is one source of truth. Declare the SRID in dbt_project.yml and read it through a thin macro so no model ever contains a literal EPSG code. Changing the project’s canonical frame then means editing one line.

-- macros/spatial/canonical_srid.sql
{% macro canonical_srid() %}
  {{ return(var('canonical_srid') | int) }}
{% endmacro %}

Verify the variable resolves to the integer you expect:

dbt compile -s stg_parcels
# In target/compiled/.../stg_parcels.sql confirm the literal 26910 appears
# wherever canonical_srid() was called — not the string "26910".

2. Write a normalize macro that stamps and reprojects

The normalize macro is the one place that decides how a raw geometry becomes a canonical one. It repairs a missing tag with ST_SetSRID, then reprojects with ST_Transform — the correct order, because ST_SetSRID fixes metadata and ST_Transform moves coordinates. Getting that order wrong silently corrupts data, so encoding it once in a macro removes the chance of a per-model mistake.

-- macros/spatial/normalize_srid.sql
{% macro normalize_srid(geom_col, source_srid=none) %}
  {%- set target = canonical_srid() -%}
  ST_Transform(
    {%- if source_srid is not none %}
    ST_SetSRID({{ geom_col }}, {{ source_srid }})   -- assert true source frame (metadata only)
    {%- else %}
    {{ geom_col }}   -- source already carries a correct SRID tag
    {%- endif %},
    {{ target }}     -- reproject coordinates to the canonical SRID
  )
{% endmacro %}

Verify the macro emits the ST_SetSRID → ST_Transform nesting in the right order by compiling a model that calls it and reading the generated SQL.

The macro turns any raw geometry into a canonical, stamped one through two ordered operations — a metadata repair followed by a coordinate reprojection — and the assert_srid test then gates whatever it produces:

3. Apply the macro in staging

Call normalize_srid in every staging model so the geometry is canonical the moment it crosses the staging boundary. Pass source_srid only when the feed is mistagged; omit it when the source already carries a correct SRID.

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

cleaned as (
    select
        parcel_id,
        case when ST_IsValid(geom) then geom else ST_MakeValid(geom) end as geom
    from source
)

select
    parcel_id,
    -- source feed arrives tagged 0 but is genuinely EPSG:4326
    {{ normalize_srid('geom', source_srid=4326) }} as geometry
from cleaned
where geom is not null

Verify the output actually carries the canonical SRID:

select distinct ST_SRID(geometry) from {{ ref('stg_parcels') }};
-- Expect exactly one row: 26910.

4. Add a generic test asserting the SRID

The stamp is only trustworthy if a test proves it. This generic test returns any row whose geometry SRID differs from the canonical value, so a deviation fails dbt build instead of reaching a dashboard.

-- tests/generic/assert_srid.sql
{% test assert_srid(model, column_name, srid=none) %}
    {% set expected = srid or var('canonical_srid') %}
    select {{ column_name }}
    from {{ model }}
    where {{ column_name }} is not null
      and ST_SRID({{ column_name }}) <> {{ expected }}
{% endtest %}

# models/staging/_staging.yml
version: 2
models:
  - name: stg_parcels
    columns:
      - name: geometry
        tests:
          - assert_srid       # defaults to var('canonical_srid')
          - not_null

Verify the test both passes on good data and fails on bad — seed one deliberately wrong-SRID row and confirm the run goes red:

dbt build -s stg_parcels
# PASS when every geometry is 26910; the assert_srid test fails loudly
# the moment a row carries any other SRID.

5. Extend enforcement to every geometry column

Staging alone is not enough — a downstream model can still reproject by accident. Apply assert_srid to every materialized geometry column so the invariant is checked across the whole DAG, and centralize the tag with a meta.crs block for the docs site.

# models/marts/_marts.yml
models:
  - name: mart_service_areas
    columns:
      - name: geometry
        description: "Canonical geometry — always EPSG:26910."
        meta:
          crs: "EPSG:26910"
        tests:
          - assert_srid

Verify coverage by listing every geometry column and confirming each has the test attached:

dbt ls --resource-type test | grep assert_srid
# One assert_srid test per geometry column across staging, intermediate, and marts.

Configuration reference

Gotchas & edge cases

• ST_SetSRID is not ST_Transform. ST_SetSRID relabels metadata without moving coordinates; ST_Transform recomputes them. Using ST_SetSRID where reprojection was needed tells the database that projected metres are degrees — a silent, catastrophic error. The normalize macro nests them in the correct order for exactly this reason.
• SRID = 0 inputs. Untagged geometry cannot be reprojected — ST_Transform has no source frame. Always ST_SetSRID to the true source frame first (pass source_srid), then reproject.
• Metric ops still need a projected frame. If your canonical SRID is EPSG:4326, assert_srid will pass but ST_Area/ST_Distance return degrees. Reproject inside the metric CTE only, and keep the model’s output canonical.
• The test skips nulls by design. assert_srid excludes NULL geometries so it does not double-report what a not_null test already covers — pair the two on every geometry column.
• Managed warehouses. On BigQuery, GEOGRAPHY is fixed to EPSG:4326 with no per-column SRID, so ST_SRID enforcement does not apply; assert the storage type instead and pick the engine per choosing the right spatial adapter.

FAQ

Related

• CRS Governance Policy — the policy and ownership model this enforcement implements.
• Detecting SRID Mismatches with dbt Tests — the detection test that finds mismatches this stamp prevents.
• Automating CRS Conversions in dbt Pipelines — reprojection mechanics behind the normalize macro.

Up: Part of CRS Governance Policy.