CRS Governance Policy

Mixing SRIDs in one calculation is the most expensive silent failure a spatial pipeline can ship. A distance between an EPSG:4326 point and an EPSG:3857 point is not an error — it is a number, wrong by orders of magnitude, that flows into a dashboard, a routing decision, or a billing calculation without a single stack trace. The remedy is not a clever query; it is policy. A coordinate reference system governance policy declares one canonical storage SRID for the project, names the exact places where reprojection is permitted, and assigns an owner to every change. This page, part of Spatial Data Architecture & Governance, sets out how to write that policy and — the part most teams skip — how to make dbt enforce it on every run.

Governance here means three concrete artifacts, not a wiki page. First, a canonical SRID declared once as a dbt variable and consumed everywhere. Second, a set of boundaries in the DAG where ST_Transform is allowed and everywhere else forbidden, so coordinates never drift frame mid-pipeline. Third, a suite of generic tests that fail the build when any geometry deviates from the declared SRID. Together they turn “we agreed to use UTM Zone 10N” from a hallway conversation into a machine-checked contract. The broader project-wide standard sits in spatial reference system management; this page is the policy-and-enforcement layer that makes the standard real.

Prerequisites checklist

Before writing any policy into dbt_project.yml, confirm the ground truth of your data and environment:

• dbt-core ≥ 1.5 with a spatial-capable adapter — dbt-postgres against PostGIS ≥ 3.1, and/or dbt-duckdb with the spatial extension for CI. Adapter differences in SRID handling are compared in choosing the right spatial adapter.
• An inventory of source SRIDs. Query every geometry feed for its actual SRID (SELECT DISTINCT ST_SRID(geom) FROM …) — sources frequently arrive tagged 0 (unknown) or in a projection nobody documented. You cannot normalize to a canonical frame you have not measured against.
• A decision on the canonical SRID for storage versus metric operations (covered in the next section), agreed with the analytics consumers who will read distances and areas.
• Grants to CREATE in the target schema and to run generic tests in CI, so the policy can enforce itself rather than relying on reviewers.
• Environment variables for the SRID surfaced through dbt’s env_var() pattern, so the same models run in dev, CI, and production without hand edits.

Architecture context: CRS across the pipeline

A CRS policy is not a single macro call — it is a property that must hold across every layer of the DAG. Raw sources carry whatever SRID the upstream system happened to emit, sometimes several within one feed. The staging boundary is the one place reprojection is mandatory: every geometry is normalized to the canonical SRID and stamped. From that boundary onward, the SRID is an invariant, and any model that changes it is doing something exceptional that the policy must name explicitly.

Each arrow that crosses the staging boundary is where the policy earns its keep: coordinates enter in mixed frames and leave in exactly one. The only sanctioned second reprojection is a deliberate, documented step — for example, a serving view that emits EPSG:4326 for a web map — and even that is an owned exception, never an incidental cast buried in a SELECT.

Core concepts of a CRS policy

Choosing the canonical SRID

The first policy decision is which SRID is canonical, and it is really two decisions because storage and metric computation pull in opposite directions. EPSG:4326 (WGS 84, lat/long in degrees) is the universal interchange format: every source speaks it, every map tile consumes it, and it stores geometry without projection distortion anywhere on Earth. But ST_Distance, ST_Area, and ST_Buffer on EPSG:4326 return degrees, which are meaningless as a metric unit and wildly distorted away from the equator. A projected system — a UTM zone such as EPSG:26910 (UTM Zone 10N) or a state-plane SRID — gives true metres and square metres, but only within the zone it was designed for; feed it a coordinate from the wrong hemisphere and the numbers silently degrade.

Two policy shapes resolve this. The common default is to store in EPSG:4326 for interoperability and reproject to a projected SRID only inside the intermediate models that compute distance or area — this keeps interchange trivial and confines projection to where metric accuracy matters. The alternative, preferable when the project’s footprint fits inside one zone and nearly every query is metric, is to store in the projected SRID and reproject to EPSG:4326 only in the serving view that feeds maps. The GEOMETRY-vs-GEOGRAPHY type choice interacts with this directly; the geometry vs geography type trade-offs guide covers when GEOGRAPHY lets you sidestep projection entirely for globe-spanning distance work. Whichever you choose, write it down as one number and make everything reference that number.

Where ST_Transform is allowed

ST_Transform actually reprojects coordinates: it recomputes every vertex from the source CRS into the target CRS using the datum and projection math. That is exactly what you want at the staging boundary and exactly what you must forbid everywhere else, because an unplanned reprojection mid-DAG desynchronizes two models that were supposed to share a frame. The policy names the allowed sites explicitly:

• The staging normalization step — mandatory. Every geometry is transformed to the canonical SRID here, once.
• A metric-computation boundary — optional. If storage is EPSG:4326, the intermediate model that computes areas transforms into a projected SRID for the duration of that computation.
• A serving reprojection — optional. A view that emits tiles reprojects to EPSG:4326.

Every other ST_Transform in the codebase is a policy violation to be caught in review or by a lint rule. The mechanics of doing these reprojections reliably across many models are the subject of automating CRS conversions in dbt pipelines, and the bulk case is covered in batch transforming coordinate systems with dbt.

ST_SetSRID versus ST_Transform

The single most dangerous confusion in CRS governance is treating ST_SetSRID and ST_Transform as interchangeable. They are opposites.

• ST_SetSRID(geom, srid) only relabels the metadata tag. It changes what the SRID says without touching a single coordinate. Use it when a geometry carries the correct coordinates but a wrong or missing tag — the classic SRID = 0 import that is genuinely lon/lat but was never labelled.
• ST_Transform(geom, srid) actually recomputes every coordinate into the new CRS. Use it when the coordinates must move from one frame to another.

Getting these backwards corrupts data quietly. ST_SetSRID on data that needs reprojection tells the database that EPSG:3857 metre coordinates are EPSG:4326 degrees — a factor-of-100,000 lie. ST_Transform on data with a wrong SRID tag reprojects from the wrong source frame and lands nowhere real. The correct repair for a mistagged import is always ST_SetSRID first (assert the true source frame), then ST_Transform (move to canonical):

-- Repair a mistagged EPSG:4326 import, then normalize to canonical
ST_Transform(
  ST_SetSRID(geom, 4326),   -- assert the true source frame (metadata only)
  {{ var('canonical_srid') }}  -- reproject coordinates to canonical
)

Configuration walkthrough

The policy lives in dbt_project.yml as variables, so the canonical SRID exists in exactly one place and every model references it. Hard-coding 4326 or 26910 into model SQL is the anti-pattern the whole policy exists to prevent.

# dbt_project.yml
vars:
  # The one canonical storage SRID for the whole project.
  canonical_srid: "{{ env_var('DBT_CANONICAL_SRID', '26910') }}"   # UTM Zone 10N, metric
  # The frame the serving layer emits for web maps.
  serve_srid: 4326
  # Frames the staging layer is allowed to receive and repair.
  accepted_source_srids: [4326, 3857, 26910, 0]

models:
  my_project:
    staging:
      +tags: ["crs_boundary"]     # the one layer where ST_Transform is expected

A thin macro reads the variable so models never see a literal. Centralizing it means a project-wide SRID change is a one-line edit, not a find-and-replace across dozens of models:

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

Core implementation

Normalizing to canonical at the staging boundary

The staging model is where every geometry is repaired, reprojected, and stamped. This is the load-bearing step of the whole policy — get it right and the invariant holds for free downstream. Note the deliberate ST_SetSRID → ST_Transform order for mistagged rows, and the validity repair from the OGC Simple Features rules folded into the same pass:

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

repaired as (
    select
        parcel_id,
        -- Repair a missing/unknown tag before any coordinate math.
        case
            when ST_SRID(geom) = 0 then ST_SetSRID(geom, 4326)  -- known true source
            else geom
        end as geom_tagged
    from source
),

normalized as (
    select
        parcel_id,
        ST_Transform(
            case when ST_IsValid(geom_tagged) then geom_tagged
                 else ST_MakeValid(geom_tagged) end,
            {{ canonical_srid() }}
        ) as geometry   -- reprojected + stamped to the canonical SRID
    from repaired
)

select parcel_id, geometry
from normalized
where geometry is not null

The step-by-step mechanics of stamping and normalizing every model consistently — including a reusable macro that wraps this pattern — are the subject of enforcing a canonical SRID across dbt models.

Confining metric operations to a reprojection boundary

When storage is EPSG:4326, the policy allows exactly one downstream reprojection: into a projected SRID for the duration of a metric computation. Keep the reprojection local to the CTE that needs it, and never let the projected geometry leak into the model’s output — the output stays canonical.

-- models/intermediate/int_parcel_areas.sql
with metric as (
    select
        parcel_id,
        geometry,
        -- Reproject only for the area math; do not persist the projected geom.
        ST_Area(ST_Transform(geometry, 26910)) as area_sqm
    from {{ ref('stg_parcels') }}
)
select parcel_id, geometry, area_sqm   -- geometry column is still canonical
from metric

Validation and testing

Policy that is not enforced is decoration. The enforcement mechanism is a generic dbt test asserting that every geometry column in a model carries the canonical SRID, wired into schema YAML so a violation fails dbt build rather than surfacing as a wrong number on a map.

-- 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

Apply assert_srid to every materialized geometry column in the project, not just staging — that is what makes the invariant machine-checked across the whole DAG. Designing this test to catch the subtle mixed-SRID-join case, and to report which SRID it found rather than just failing, is covered in depth in detecting SRID mismatches with dbt tests. A quick ad-hoc audit before you trust a feed:

-- ad-hoc: what SRIDs actually exist in a source?
select ST_SRID(geom) as srid, count(*)
from {{ source('gis', 'parcels_raw') }}
group by 1 order by 2 desc;

Advanced patterns

Documenting the policy in the schema. The canonical SRID belongs in dbt documentation, not just in a variable. Add a meta block to geometry columns so the SRID is discoverable in the generated docs site and lineage graph, keeping the human-readable contract next to the machine-checked one:

# models/marts/_marts.yml
models:
  - name: mart_service_areas
    columns:
      - name: geometry
        description: "Canonical geometry — always EPSG:26910 (UTM 10N). Reproject at the serving view only."
        meta:
          crs: "EPSG:26910"
          crs_owner: "spatial-platform-team"
        tests:
          - assert_srid

Ownership and change control. Name an owner for the canonical SRID in the meta.crs_owner field and require their review on any PR that touches a crs_boundary-tagged model or edits the canonical_srid var. A CRS change is a breaking change to every downstream consumer; treat it with the same rigor as a schema migration, using the discipline in versioning spatial schemas in dbt.

Cross-engine consistency. On managed warehouses such as BigQuery, GEOGRAPHY is fixed to EPSG:4326 and there is no per-column SRID to set — the policy shifts from “assert the SRID” to “assert the storage type and the reprojection points.” Isolate the engine difference behind a macro so the policy reads the same in project config regardless of adapter, per choosing the right spatial adapter.

Troubleshooting

Schema-affecting changes — switching the canonical SRID, or moving from GEOMETRY to GEOGRAPHY storage — are breaking changes that ripple through every consumer; versioning spatial schemas in dbt covers the backward-compatible migration and rollback path.

FAQ

Related

• Enforcing a Canonical SRID Across dbt Models — the macro-and-test recipe that stamps every model to one SRID.
• Detecting SRID Mismatches with dbt Tests — a generic test that flags any geometry deviating from the canonical frame.
• Spatial Reference System Management — the project-wide standard this policy operationalizes.
• Automating CRS Conversions in dbt Pipelines — the reprojection mechanics behind the staging boundary.
• Spatial Data Architecture & Governance — CRS policy alongside schema versioning, security scoping, and large-dataset handling.

↑ Back to Spatial Data Architecture & Governance