Automating CRS conversions in dbt pipelines

This page shows you how to turn coordinate reference system (CRS) conversion into a centralized, version-controlled, test-gated step in dbt — so every geometry lands in one canonical projection without hand-edited SRID patches or one-off scripts.

When spatial datasets from disparate sources converge in a warehouse, mismatched coordinate reference systems are the silent pipeline killers. A single unconverted geometry column cascades into false-negative spatial joins, distance calculations that drift by kilometres, and KPIs that cannot be reproduced run to run — and it fails silently, because every individual function call still succeeds. This guide sits under the Spatial Reference System Management workflow and supplies the automation layer: the registry that decides which SRID is canonical and the macro that enforces it everywhere.

When to use this approach

Reach for a registry-driven, automated CRS conversion when:

  • Multiple sources feed one canonical store and you need governance, not just a function call. If you only need to reproject one large table in bounded chunks, use batch transforming coordinate systems with dbt instead — it owns the chunked, memory-safe mechanics.
  • Projection targets change over time (web tiles want 3857, analytics wants 4326) and you want to swap them globally without editing every model. For the broader reusable-macro patterns behind this, see building custom spatial macros.
  • You need CRS drift to fail CI, not a dashboard. This pairs with the storage and scale tactics in handling large geospatial datasets when volumes grow.

Prerequisites

  • dbt Core ≥ 1.7 (stable adapter.dispatch, --empty CI runs, --state for targeted reruns).
  • One spatial adapter: dbt-postgres ≥ 1.7 against PostGIS ≥ 3.3, dbt-snowflake ≥ 1.7, or dbt-bigquery ≥ 1.7 (spherical GEOGRAPHY fixed to EPSG:4326 — no planar SRIDs).
  • Grants: CREATE on the target schema and permission to rebuild a GiST (PostGIS) or search-optimized index on the converted output.
  • Environment variables through dbt’s env_var() (never hardcoded): connection secrets plus any per-environment SRID overrides.
  • A staging model that already stamps each geometry with its true source SRID via ST_SetSRID — an untagged geometry cannot be reprojected correctly.

Step-by-step instructions

Governed CRS conversion flow in a dbt pipeline A left-to-right pipeline. A centralized crs_registry of dbt_project.yml vars supplies source SRID, target SRID and a metre tolerance to a dispatch-aware transform_crs macro, which fans out to PostGIS, Snowflake and BigQuery implementations. The macro feeds an incremental staging model that reprojects only changed geometries using a watermark and geometry hash. The staging output passes through a dbt test gate that checks the SRID equals the canonical target and that control-point drift stays under tolerance. On pass it reaches the canonical mart; on failure CI blocks the deploy because a geometry carries a non-canonical SRID or drift exceeds tolerance. crs_registry dbt_project.yml vars source_srid target_srid tolerance_m transform_crs adapter.dispatch PostGIS ST_ Transform Snow- flake ST_ Transform BigQuery 4326 only staging model incremental + merge is_incremental() watermark + geom_hash reproject deltas only dbt test gate singular test ST_SRID = target + control-point drift < tolerance_m canonical mart one projection for every consumer reproducible run-to-run CI blocks deploy non-canonical SRID or drift > tolerance → fail pass on failure

Step 1: Decouple SRID logic into a centralized registry

Hardcoding EPSG codes inside model SQL creates unmaintainable debt and hides transformation intent. Instead, map logical dataset identifiers to source SRID, canonical target SRID, and a validation tolerance as structured vars in dbt_project.yml. Externalizing projection logic lets you retarget globally during a platform migration without touching individual models.

yaml
# dbt_project.yml
vars:
  crs_registry:
    parcel:
      source_srid: 2263   # NY State Plane (ft)
      target_srid: 4326   # canonical cross-platform analytics CRS
      tolerance_m: 0.5
    basemap_tiles:
      source_srid: 4326
      target_srid: 3857   # Web Mercator for tile rendering
      tolerance_m: 1.0

Verify dbt parses the registry before wiring it into a model:

bash
dbt parse
dbt run-operation print_var --args '{var: crs_registry}'  # or: dbt compile and inspect the rendered SQL

Step 2: Wrap conversion in a dispatch-aware macro

The macro must handle adapter dispatch, null/invalid geometries, and a no-op fast path when the geometry is already in the target SRID. Warehouses diverge sharply: PostGIS needs an explicit source SRID stamped before transform, Snowflake reads it from metadata, and BigQuery fixes GEOGRAPHY to EPSG:4326 and has no runtime reprojection at all.

sql
-- macros/transform_crs.sql
{% macro transform_crs(geometry_col, source_srid, target_srid) %}
  {{ return(adapter.dispatch('transform_crs', 'dbt_geospatial')(geometry_col, source_srid, target_srid)) }}
{% endmacro %}

{% macro default__transform_crs(geometry_col, source_srid, target_srid) %}
  {{ exceptions.raise_compiler_error("No transform_crs implementation for adapter " ~ target.type) }}
{% endmacro %}

{% macro snowflake__transform_crs(geometry_col, source_srid, target_srid) %}
  CASE
    WHEN {{ geometry_col }} IS NULL THEN NULL
    WHEN ST_SRID({{ geometry_col }}) = {{ target_srid }} THEN {{ geometry_col }}
    ELSE ST_TRANSFORM({{ geometry_col }}, {{ target_srid }})
  END
{% endmacro %}

{% macro bigquery__transform_crs(geometry_col, source_srid, target_srid) %}
  {% if target_srid | int != 4326 %}
    {{ exceptions.raise_compiler_error("BigQuery GEOGRAPHY only supports EPSG:4326; reproject upstream before loading") }}
  {% endif %}
  CASE WHEN {{ geometry_col }} IS NULL THEN NULL ELSE {{ geometry_col }} END
{% endmacro %}

{% macro postgres__transform_crs(geometry_col, source_srid, target_srid) %}
  CASE
    WHEN {{ geometry_col }} IS NULL THEN NULL
    WHEN NOT ST_IsValid({{ geometry_col }}) THEN NULL   -- route invalid input out, don't crash the run
    ELSE ST_Transform(
      ST_SetSRID({{ geometry_col }}::geometry, {{ source_srid }}),
      {{ target_srid }}
    )
  END
{% endmacro %}

Compile against your adapter and confirm the rendered SQL names the right variant:

bash
dbt compile --select stg_parcel_boundaries
# target/compiled/... should show ST_Transform (PostGIS) or ST_TRANSFORM (Snowflake)

Step 3: Invoke the macro from a staging model

In the model, conversion becomes declarative and environment-agnostic — the registry supplies the SRIDs, so the SQL never names a raw EPSG code.

sql
-- models/staging/stg_parcel_boundaries.sql
{{ config(materialized='incremental', unique_key='parcel_id', incremental_strategy='merge') }}

SELECT
  parcel_id,
  owner_name,
  {{ transform_crs(
       'boundary_geom',
       var('crs_registry')['parcel']['source_srid'],
       var('crs_registry')['parcel']['target_srid']
     ) }} AS boundary_geom,
  updated_at
FROM {{ ref('raw_parcel_boundaries') }}
{% if is_incremental() %}
  WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}

Build a single incremental slice first and confirm row counts look sane:

bash
dbt run --select stg_parcel_boundaries

Step 4: Only convert deltas with incremental state

Full-table CRS conversion is expensive and forces a complete spatial index rebuild on every run. Couple the macro with is_incremental() and a reliable watermark so only new or modified geometries are reprojected. Keep a source_srid audit column and a cheap content hash so unchanged geometries re-emitted by an upstream API are not needlessly recomputed.

sql
SELECT
  parcel_id,
  md5(ST_AsBinary(boundary_geom)) AS geom_hash,   -- detect mutations without parsing coordinates
  {{ var('crs_registry')['parcel']['source_srid'] }} AS source_srid,
  {{ transform_crs(
       'boundary_geom',
       var('crs_registry')['parcel']['source_srid'],
       var('crs_registry')['parcel']['target_srid']
     ) }} AS boundary_geom
FROM {{ ref('raw_parcel_boundaries') }}

This deterministic merge logic enforces idempotent runs and is a core part of Spatial Data Architecture & Governance: the same input always produces the same canonical output, regardless of how many times a source re-emits it.

Step 5: Gate the pipeline with a CRS consistency test

Successful compilation is not validation — SRID mismatches and coordinate drift must be caught before downstream consumption. A custom singular test that returns rows on failure lets dbt block the deploy.

sql
-- tests/test_crs_consistency.sql
SELECT
  parcel_id,
  ST_SRID(boundary_geom) AS actual_srid
FROM {{ ref('stg_parcel_boundaries') }}
WHERE boundary_geom IS NOT NULL
  AND ST_SRID(boundary_geom) != {{ var('crs_registry')['parcel']['target_srid'] }}

Wire it into CI so projection errors never reach a dashboard or a feature store:

bash
dbt build --select stg_parcel_boundaries
# the run fails if any row carries a non-canonical SRID

For accuracy beyond SRID equality, compare transformed centroids against known control points within the registry’s tolerance_m, and verify datum-shift expectations against the EPSG Geodetic Parameter Registry and the PostGIS ST_Transform documentation.

Configuration reference

Parameter Accepted values Default Spatial notes
crs_registry.<ds>.source_srid any EPSG the engine knows — Must match what staging stamped with ST_SetSRID; a wrong value yields plausible-but-wrong coordinates, not an error
crs_registry.<ds>.target_srid any EPSG the engine supports — BigQuery accepts 4326 only; the macro raises a compiler error otherwise
crs_registry.<ds>.tolerance_m metres — Drift budget for control-point validation tests
materialized incremental, table incremental table is fine for one-off conversions under a few million rows
incremental_strategy merge, delete+insert merge merge is idempotent on unique_key; required for late-arriving data
unique_key grain column(s) — Omitting it on an incremental model duplicates rows every run

Gotchas and edge cases

  • Unknown (SRID 0) input. ST_Transform cannot reproject an untagged geometry. Always ST_SetSRID at staging; the PostGIS macro stamps source_srid defensively for exactly this reason.
  • BigQuery has no planar reprojection. GEOGRAPHY is always EPSG:4326. Any non-4326 target must be projected upstream (in PostGIS or the DuckDB spatial extension) before loading.
  • Geometry vs geography coercion. PostGIS geometry is planar and unit-dependent; casting to geography mid-model silently changes distance semantics. Keep the type explicit across the model.
  • Stale planner statistics after a bulk convert. A fresh write can make the warehouse ignore the spatial index. Run ANALYZE (PostGIS) or the equivalent in a post-hook so the next incremental run stays index-eligible.
  • Chained transforms. Avoid stacking multiple ST_Transform calls in one query; convert once in a single staging step so the optimizer can push down spatial predicates.

Frequently asked questions

Why does ST_Transform return NULL after this step?

Almost always a NULL or invalid input geometry, or one tagged with SRID 0. The PostGIS macro deliberately returns NULL for invalid input rather than crashing the run — guard against it with a WHERE ST_IsValid(...) filter, route failures to a quarantine table, and confirm staging applied ST_SetSRID with the real source projection.

Should the registry live in dbt_project.yml or a seed?

Use vars in dbt_project.yml for a handful of datasets — it is version-controlled and available at compile time. Promote to a CSV seed once the registry grows large enough that you want it joinable inside SQL tests or queryable as a table.

How do I change the canonical target SRID without rebuilding everything?

Update target_srid in the registry, then run dbt build --select stg_parcel_boundaries+ --full-refresh for only the affected datasets. The consistency test will fail any model still emitting the old SRID, so CI catches a partial migration.

Can I validate accuracy, not just that the SRID matches?

Yes. SRID equality is necessary but not sufficient. Compare transformed centroids (or known survey control points) against expected coordinates and assert the offset stays under the registry’s tolerance_m. Datum-shift errors pass an SRID check but fail a control-point check.

Up: Spatial Reference System Management