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

It is worth noting how rarely the inverse operations appear together in one pipeline and how often they should. A project that simplifies for its map and never densifies for its measurements is quietly accepting that its long boundaries are wrong in whichever direction the projection bends them.

Adding vertices as deliberately as removing them

Most transformation work reduces geometry: simplify, round, subdivide. The inverse operation matters as much and is far less familiar. A line between two distant points is straight only in the coordinate system it was drawn in, and reprojecting it moves the endpoints while redrawing the line between them as a straight segment in the new system — which is not where the real boundary goes. For a shape whose edges are a few hundred metres long the discrepancy is sub-millimetre and irrelevant; for one with a fifty-kilometre edge it is metres, and for a continental boundary it can be kilometres.

Densification fixes that by inserting intermediate vertices before the transformation, so the projection has points to place rather than a segment to redraw. Two details decide whether it works. The first is order: densify in the source system and then transform, because densifying afterwards adds points to a line that is already wrong. The second is selectivity: applying it to every geometry multiplies storage across a whole table to fix a handful of shapes, so gate it on a measured edge length and record which rows were affected. The procedure, including how to pick an interval from the deviation you are willing to accept, is in densifying and segmentizing geometries in dbt.

A short worked example makes the point concrete. A national boundary digitised at one-kilometre spacing, reprojected from a geographic system to a national grid and then simplified with a twenty-metre tolerance, comes out visually identical to the original at every zoom a person will use. The same boundary simplified first — with a twenty-unit tolerance interpreted as twenty degrees because the source is in EPSG:4326 — comes out as a triangle, and the model that produced it will not error, because a triangle is a perfectly valid polygon.

Ordering the pipeline so each step can trust the last

Transformation steps are not commutative, and the failures from reordering them are quiet. Validate and repair first, because simplification and subdivision both behave unpredictably on invalid input and ST_Subdivide refuses it outright. Reproject second, so that every subsequent tolerance is expressed in the units the data will actually be measured in. Simplify last, and only for serving, because a tolerance applied before reprojection is a tolerance in the wrong units — and one applied to analytical geometry changes the numbers a mart publishes.

The same ordering argument explains where each step belongs in the DAG. Validation and reprojection are staging concerns, performed once for everything downstream. Densification is an intermediate concern, usually ephemeral, because it exists to serve a specific operation and rarely deserves storage of its own. Simplification is a serving concern and belongs nowhere else. Writing that down in the project’s own terms — which model does what, and what every downstream model may therefore assume — is what stops the sequence from being rediscovered by each new contributor.

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