Spatial Model Dependency Graphs

In analytics engineering, the directed acyclic graph (DAG) governs execution order, data lineage, and failure isolation. When geometry enters a dbt pipeline that topology stops behaving like a tidy star schema: spatial predicates evaluate row-by-row, spatial indexes are dropped on every rebuild, and a single coordinate reference system (CRS) mismatch can silently corrupt every downstream metric. A spatial model dependency graph is the answer — an explicitly ordered set of dbt models in which CRS alignment, expensive predicate evaluation, and index lifecycle are first-class DAG edges rather than incidental side effects. This page shows how to shape that graph so heavy compute, tiling, and geometry validation run predictably without stalling the BI marts and feature pipelines that depend on them.

This is an implementation reference within Core Fundamentals & Architecture for dbt Geospatial. It assumes you have already provisioned an engine — whether through setting up PostGIS with dbt for high-concurrency production work or the in-process DuckDB spatial extension integration for local and CI runs — and now need to decide what order the geometry models execute in.

Prerequisites

Before sequencing geometry-heavy models, confirm the toolchain and the database are ready to enforce ordering:

• dbt-core ≥ 1.7 so post_hook, pre_hook, and unique_key incremental semantics behave consistently across the DAG.
• A spatial-capable adapter: dbt-postgres against PostGIS ≥ 3.1, or dbt-duckdb ≥ 1.7 with the spatial extension loaded — see choosing the right spatial adapter if that decision is still open.
• Database grants to CREATE INDEX and run DDL in the target schema, since index-aware materialization issues CREATE INDEX ... USING GIST from a post_hook.
• A single, agreed projected SRID for all distance and area work (this guide uses EPSG:3857) and a known source SRID (EPSG:4326) for raw geometry.
• Connection and path values supplied through dbt’s env_var() pattern, never hard-coded.

Architecture Context

Traditional dbt models follow linear or star-schema dependency patterns tuned for scalar and relational work. Spatial models introduce asymmetric fan-in and fan-out: functions like ST_Intersects, ST_DWithin, ST_Union, and aggregates such as ST_Collect and ST_Envelope force the planner to evaluate geometric relationships row-by-row or via spatial index scans. Without deliberate sequencing, those operations trigger exponential compute growth and memory pressure.

A resilient spatial DAG isolates heavy geometric processing into discrete, explicitly ordered layers. The fan-in below — two raw sources, normalized in parallel, joined under an indexed spatial predicate, then served from a mart — is the canonical shape of every production spatial DAG.

The dependency chain enforces a strict execution order: raw ingestion → CRS normalization → spatial indexing → downstream joins → mart aggregation. Bypassing the normalization layer or deferring index creation forces every downstream ref() to perform full-table spatial scans, effectively collapsing pipeline throughput.

Configuration Walkthrough

DAG ordering is a project-level concern, not just a per-model one. A few dbt_project.yml defaults make the spatial layers self-document their materialization and keep index creation inside the graph rather than in an out-of-band migration.

# dbt_project.yml — materialization defaults per layer
models:
  dbt_geospatial:
    staging:
      +materialized: view          # cheap, re-derived on every run
    intermediate:
      +materialized: table         # projected + indexed, the join inputs
      +tags: ["spatial_core"]
    marts:
      +materialized: table

# Make every run register the engine before any spatial model compiles.
on-run-start:
  - "{{ ensure_spatial_runtime() }}"

The ensure_spatial_runtime() macro branches on target.type so the same project bootstraps either engine without editing models:

-- macros/ensure_spatial_runtime.sql
{% macro ensure_spatial_runtime() %}
  {% if target.type == 'postgres' %}
    CREATE EXTENSION IF NOT EXISTS postgis SCHEMA public;
  {% elif target.type == 'duckdb' %}
    INSTALL spatial; LOAD spatial;
  {% endif %}
{% endmacro %}

Keeping the runtime bootstrap in on-run-start rather than inside a model means it is not itself a DAG node — it runs once before compilation, so no spatial model has to declare a dependency on it just to find ST_Transform.

Core Implementation

The staging view: validate and stamp SRID

The staging layer is the cheapest place to reject bad geometry and to assert a known source SRID. It stays a view so it is re-derived for free on every run and never holds a stale copy of the feed.

-- models/staging/stg_parcel_boundaries.sql
{{ config(materialized='view') }}

SELECT
    parcel_id,
    ST_SetSRID(ST_GeomFromText(wkt_geom), 4326) AS raw_geom,
    address,
    zoning_class
FROM {{ source('gis_raw', 'parcel_imports') }}

The intermediate table: project once, index once

CRS normalization is the first hard dependency in the graph. The intermediate model re-projects into the working SRID exactly once, materializes as a table so the geometry is physically persisted, and recreates its spatial index in a post_hook so downstream joins can use an index scan rather than a sequential scan.

-- models/intermediate/int_crs_normalized_parcels.sql
{{ config(
    materialized='table',
    post_hook=["CREATE INDEX IF NOT EXISTS idx_parcels_geom ON {{ this }} USING GIST (geom)"]
) }}

WITH projected AS (
    SELECT
        parcel_id,
        ST_Transform(raw_geom, 3857) AS geom,
        zoning_class
    FROM {{ ref('stg_parcel_boundaries') }}
)
SELECT
    parcel_id,
    geom,
    ST_Area(geom) AS area_sqm,
    zoning_class
FROM projected

CRS normalization as a hard dependency

Spatial integrity degrades fast when models reference geometries in mismatched coordinate systems. A frequent anti-pattern embeds ad-hoc ST_Transform calls directly inside join conditions: it fragments the graph, duplicates expensive projection math across nodes, and introduces silent precision drift when transforms are applied inconsistently.

Codify normalization as a hard dependency instead. Every spatial model that joins, measures distance, or aggregates area should consume one authoritative geometry column produced by the intermediate layer. Centralizing projection there guarantees that all downstream models inherit a consistent spatial reference — the same schema-level discipline described in setting up PostGIS with dbt. Adhering to the geometric validity rules of the Open Geospatial Consortium Simple Features specification keeps normalized geometries topologically sound across the entire chain.

Predicate isolation in the join model

Spatial joins are inherently asymmetric: joining a high-cardinality point dataset against a complex polygon layer can explode the intermediate result set if the DAG does not enforce pre-filtering. Separate the cheap bounding-box overlap (&&) from the precise predicate so the planner prunes early, then bind the two indexed inputs in an incremental fact model.

-- models/marts/fct_parcels_with_zoning.sql
{{ config(materialized='incremental', unique_key='parcel_id') }}

SELECT
    p.parcel_id,
    p.area_sqm,
    z.zoning_district,
    ST_Area(ST_Intersection(p.geom, z.geom)) AS overlap_sqm
FROM {{ ref('int_crs_normalized_parcels') }} AS p
JOIN {{ ref('int_zoning_normalized') }} AS z
  ON p.geom && z.geom                 -- bounding-box pre-filter (index-assisted)
 AND ST_Intersects(p.geom, z.geom)    -- precise predicate on the survivors
{% if is_incremental() %}
WHERE p.parcel_id NOT IN (SELECT parcel_id FROM {{ this }})
{% endif %}

Because both inputs are tables with GiST indexes, the && stage runs as an index scan and the exact ST_Intersects check only touches the surviving candidate pairs. Isolating predicate evaluation into sequential DAG nodes keeps SLAs predictable even as cardinality grows.

Validation & Testing

A spatial DAG is only as trustworthy as its assertions about geometry. Verify the runtime, sweep for invalid geometry, and confirm SRID consistency before the join models ever run.

-- analysis/check_spatial_runtime.sql — run ad hoc
SELECT PostGIS_Version();                              -- engine + lib versions
SELECT count(*) AS invalid_rows
FROM   {{ ref('int_crs_normalized_parcels') }}
WHERE  NOT ST_IsValid(geom);                           -- expect 0
SELECT DISTINCT ST_SRID(geom) AS srid
FROM   {{ ref('int_crs_normalized_parcels') }};        -- expect exactly 3857

Encode the same guarantees as dbt tests so they gate the build rather than living in a notebook. A small custom generic test asserts a single SRID, and built-in tests cover validity and non-null geometry:

# models/intermediate/_intermediate.yml
version: 2
models:
  - name: int_crs_normalized_parcels
    columns:
      - name: geom
        tests:
          - not_null
          - assert_srid:
              srid: 3857
          - assert_valid_geometry      # wraps NOT ST_IsValid(geom)

-- macros/test_assert_srid.sql
{% test assert_srid(model, column_name, srid) %}
SELECT {{ column_name }}
FROM {{ model }}
WHERE ST_SRID({{ column_name }}) <> {{ srid }}
{% endtest %}

Running these as part of dbt build means a CRS drift or an unindexed rebuild fails the run instead of surfacing as a wrong number on a dashboard.

Advanced Patterns

Incremental spatial models

For large feeds, re-projecting and re-validating the full history on every run wastes compute. An incremental materialization re-processes only new rows, but spatial incrementals demand a robust unique_key and careful is_incremental() logic to avoid geometry duplication or index fragmentation. Recreate the spatial index in a post_hook that runs after each incremental merge, and prefer a watermark column over a geometry-based filter. The full strategy for very large geometry feeds is covered in handling large geospatial datasets.

Index-aware materialization across engines

Spatial indexes are not preserved across dbt materializations — when a model is rebuilt the table is dropped and recreated, invalidating its index until regenerated. PostGIS uses a post_hook to recreate the GiST index; consult the official PostGIS spatial indexing documentation for tuning. On columnar engines the paradigm shifts: the DuckDB spatial extension integration relies on in-memory R-tree structures and vectorized execution, so materialization boundaries — not explicit index DDL — preserve performance. Where you need to force the planner toward a specific access path, the patterns in index hints for spatial queries apply.

Breaking implicit cycles

Spatial workflows frequently introduce implicit circular references — a model that joins parcels to zoning, updates parcel attributes from zoning rules, then re-joins to validate alignment forms a loop. dbt’s compiler rejects explicit cycles, but implicit ones emerge through shared staging layers or recursive validations. Resolve them by breaking the loop into unidirectional stages with explicit ref() boundaries and staged materialization. When a self-reference or mutual dependency stalls a build, the full refactor methodology lives in resolving circular dependencies in spatial models.

Cross-engine portability

The macro-wrapped projection and validation logic is deliberately engine-agnostic: ST_Transform, ST_IsValid, and ST_Intersects exist in both PostGIS and DuckDB, so the same intermediate model targets either by swapping the profile. Where dialects diverge — SRID-string vs SRID-integer arguments, or ST_DWithin distance units — branch on target.type inside the macro to keep a single definition for every DAG node.

Troubleshooting

Related

• Resolving circular dependencies in spatial models — refactoring self-referential and mutual spatial dependencies.
• Setting up PostGIS with dbt — the production engine that backs index-aware materialization.
• DuckDB spatial extension integration — how vectorized execution changes DAG ordering.
• Choosing the right spatial adapter — picking the engine each DAG layer runs on.
• Handling large geospatial datasets — partitioning and incremental strategy at scale.

Up: Core Fundamentals & Architecture for dbt Geospatial