Geometry Transformation Pipelines

Raw spatial data almost never arrives query-ready. GPS telemetry, CAD exports, and legacy GIS dumps carry mismatched coordinate reference systems, inverted polygon rings, unclosed boundaries, and silent topology violations that surface only after a spatial join returns wrong distances. A geometry transformation pipeline is the staged dbt workflow that turns that mess into a single, documented spatial contract: every geometry that reaches a downstream model carries a known SRID, a validated topology, and a predictable execution cost. This page is part of the Advanced Spatial Macros & UDF Patterns collection, and it covers the concrete layering, configuration, and tests you need to move spatial preprocessing out of desktop GIS tools and into a reproducible dbt DAG.

The problem this workflow solves is determinism. A pipeline that buffers, snaps, reprojects, and unions geometries must produce byte-identical output on every rerun, or incremental models and CI comparisons become meaningless. The patterns below treat each transformation as an idempotent step, wrap the engine-specific spatial functions behind reusable macros, and gate the output with explicit validity tests before any consumer sees the data.

Prerequisites checklist

Before building the pipeline, confirm the following are in place. Versions are the minimums these patterns were validated against.

• dbt Core ≥ 1.7 (for --empty CI runs and stable adapter.dispatch behavior).
• Adapter + spatial engine, one of:
  • dbt-postgres ≥ 1.7 against PostGIS ≥ 3.3 (CREATE EXTENSION postgis; already run).
  • dbt-duckdb ≥ 1.7 with the DuckDB spatial extension installed and loaded — see the DuckDB spatial extension integration guide for the INSTALL spatial; LOAD spatial; bootstrap.
  • dbt-bigquery ≥ 1.7 for native GEOGRAPHY (note: spherical only, no planar SRIDs).
• Database permissions: CREATE on the target schema, plus CREATE EXTENSION (or membership of a role that already enabled PostGIS) and permission to build GiST indexes.
• Environment variables referenced through dbt’s env_var() pattern (never hardcoded): DBT_PG_HOST, DBT_PG_USER, DBT_PG_PASSWORD, and a DBT_TARGET_SRID that pins the canonical projection for the serving layer.
• A decision on your canonical CRS. If you have not made it yet, read choosing the right spatial adapter and the spatial reference system management policy first — it dictates every transformation downstream.

Architecture context

A production geometry pipeline runs across five layers — ingestion, normalization, projection, validation, and serving — each a distinct dbt materialization. Staging ingests raw WKT, WKB, or GeoJSON fragments and casts them to the warehouse’s native spatial type. Intermediate models normalize SRID, repair topology, and apply geometric operations (buffer, snap, union) while preserving column-level lineage. The mart layer exposes spatially indexed, single-CRS geometries ready for analytical joins. This sits inside the broader spatial model dependency graph that the core architecture guide describes.

Configuration walkthrough

The pipeline needs three pieces of project plumbing: connection profiles, project-level variables, and a hook that guarantees the spatial extension is loaded before any model runs.

profiles.yml keeps all secrets in env_var() so the same project runs in local dev, CI, and production without edits:

dbt_geospatial:
  target: dev
  outputs:
    dev:
      type: postgres
      host: "{{ env_var('DBT_PG_HOST') }}"
      user: "{{ env_var('DBT_PG_USER') }}"
      password: "{{ env_var('DBT_PG_PASSWORD') }}"
      port: 5432
      dbname: spatial
      schema: analytics
      threads: 4

dbt_project.yml pins the canonical SRID as a project variable and materializes each pipeline layer correctly:

name: dbt_geospatial
version: "1.0.0"
profile: dbt_geospatial

vars:
  target_srid: "{{ env_var('DBT_TARGET_SRID', '4326') }}"

models:
  dbt_geospatial:
    staging:
      +materialized: view
    intermediate:
      +materialized: ephemeral
    marts:
      +materialized: table

An on-run-start hook makes the spatial extension a precondition of the run rather than a manual setup step. The companion PostGIS adapter configuration guide covers the install in depth:

on-run-start:
  - "CREATE EXTENSION IF NOT EXISTS postgis"

Core implementation

Staging: cast raw geometry without transforming it

The staging layer does exactly one thing — parse the source representation into a native geometry and attach its source SRID. It never reprojects or repairs, so lineage stays auditable. Tagging the source SRID here (rather than assuming it) is what makes later transformations deterministic.

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

select
    parcel_id,
    -- raw geometry arrives as WKT in EPSG:3857; tag it, do not reproject yet
    ST_SetSRID(ST_GeomFromText(geom_wkt), 3857) as geom,
    ingested_at
from source

Normalize: enforce a single SRID and repair topology

Mixed and missing SRIDs are the primary cause of spatial drift, so normalization centralizes CRS handling and topology repair behind a macro. Wrapping ST_Transform and ST_MakeValid in one reusable interface — the pattern documented in Building Custom Spatial Macros — means the projection rule lives in exactly one place across every model:

-- macros/normalize_geom.sql
{% macro normalize_geom(geom_col, target_srid) %}
  ST_MakeValid(
    case
      when ST_SRID({{ geom_col }}) = {{ target_srid }} then {{ geom_col }}
      else ST_Transform({{ geom_col }}, {{ target_srid }})
    end
  )
{% endmacro %}

-- models/intermediate/int_parcels_normalized.sql
select
    parcel_id,
    {{ normalize_geom('geom', var('target_srid')) }} as geom,
    ingested_at
from {{ ref('stg_parcels') }}

For datasets exceeding tens of millions of features, do the reprojection in bounded chunks rather than one full-table pass — the Batch transforming coordinate systems with dbt guide details the chunked, idempotent approach that respects warehouse memory limits.

Serving: materialize and index

The serving model materializes as a table and declares a GiST index via a post-hook so every spatial predicate downstream can be pushed to the index rather than evaluated in memory. Bounding-box pre-filtering at this layer is what keeps later proximity work cheap; the Optimizing Proximity Joins guide builds directly on an indexed mart like this one.

-- models/marts/mart_parcels.sql
{{
  config(
    materialized = 'table',
    post_hook = "CREATE INDEX IF NOT EXISTS idx_{{ this.name }}_geom ON {{ this }} USING GIST (geom)"
  )
}}

select
    parcel_id,
    geom,
    ST_Area(geom) as area_m2
from {{ ref('int_parcels_normalized') }}
where ST_IsValid(geom)

Validation and testing

Validation is a first-class layer, not an afterthought. Confirm the environment, then assert geometric validity, SRID consistency, and coordinate bounds as dbt tests so the gate runs in CI on every build.

Verify the engine is present and the data is sane:

-- Confirm PostGIS is installed and at the expected version
SELECT PostGIS_Version();

-- Sweep for invalid geometries before promotion
SELECT parcel_id, ST_IsValidReason(geom)
FROM analytics.int_parcels_normalized
WHERE NOT ST_IsValid(geom);

-- Assert every row carries the canonical SRID
SELECT DISTINCT ST_SRID(geom) FROM analytics.mart_parcels;

Encode the same checks as generic tests so they fail the run, not a dashboard. Geometries that fail should be routed to a quarantine table with diagnostic metadata rather than silently dropped, so GIS engineers can trace the upstream ingestion anomaly:

# models/marts/_marts.yml
version: 2

models:
  - name: mart_parcels
    columns:
      - name: geom
        tests:
          - dbt_utils.expression_is_true:
              expression: "ST_IsValid(geom)"
          - dbt_utils.expression_is_true:
              expression: "ST_SRID(geom) = {{ var('target_srid') }}"
      - name: parcel_id
        tests:
          - not_null
          - unique

Advanced patterns

Incremental spatial models. Once the mart materializes as a table, switch the projection layer to incremental so only newly ingested geometries get reprojected. Reprojection is deterministic, so the unique_key plus an is_incremental() filter on ingested_at gives correct, cheap reruns:

{{ config(materialized='incremental', unique_key='parcel_id') }}

select
    parcel_id,
    {{ normalize_geom('geom', var('target_srid')) }} as geom,
    ingested_at
from {{ ref('stg_parcels') }}
{% if is_incremental() %}
where ingested_at > (select max(ingested_at) from {{ this }})
{% endif %}

Macro parameterization. Extend normalize_geom to accept an optional snapping tolerance so the same macro covers raw GPS (which needs ST_SnapToGrid to collapse jitter) and clean cadastral data (which does not). Parameterizing tolerance keeps the abstraction honest while still living in one file.

Multi-engine compatibility. PostGIS planar geometry, DuckDB spatial, and BigQuery spherical GEOGRAPHY differ in default units and supported SRIDs. Route engine-specific syntax through adapter.dispatch so the pipeline compiles on each warehouse — DuckDB is ideal as a lightweight local validator before promotion to PostGIS, and governance for the long-lived schema belongs with versioning spatial schemas in dbt.

Troubleshooting

Frequently asked questions

Related

• Building Custom Spatial Macros — the reusable macro patterns that wrap every transformation in this pipeline.
• Batch transforming coordinate systems with dbt — chunked, idempotent reprojection for very large feature sets.
• Optimizing Proximity Joins — what to do with the indexed, single-CRS mart this pipeline produces.
• Index Hints for Spatial Queries — forcing the planner onto the GiST index declared at the serving layer.
• Automating CRS conversions in dbt pipelines — governance and policy around the canonical SRID this pipeline enforces.

Up: Advanced Spatial Macros & UDF Patterns