Incremental Spatial Materializations

A spatial join, union, or buffer sweep across a full dataset is one of the most expensive things a warehouse ever does, and rebuilding one from scratch on every scheduled run is how spatial pipelines quietly become the most costly job in the platform. When a parcels-to-flood-zone overlay recomputes forty million ST_Intersects evaluations at 03:00 to reflect the two thousand parcels that changed yesterday, the waste is not marginal — it is three or four orders of magnitude. This page, part of the Advanced Spatial Macros & UDF Patterns collection, covers how to make heavy spatial models incremental so they recompute only the geometry that actually changed, without ever drifting out of agreement with a full refresh.

Incremental materialization on relational data is well-trodden: pick a unique_key, gate the source scan on a watermark, merge the delta. Spatial data breaks two of the assumptions underneath that pattern. First, “what changed” is rarely a single row — a moved polygon can change the join result for every neighbour whose bounding box it overlaps, so the delta has a spatial footprint, not just a temporal one. Second, the correctness of the whole optimization rests on a spatial index that PostGIS drops and rebuilds outside dbt’s materialization, which means the index has to be re-established in a post_hook or every incremental run degrades to a sequential scan. Getting both right is what separates an incremental spatial model that is merely fast from one that is fast and returns the same answer as the ground truth.

Prerequisites checklist

Before converting a spatial model to incremental, confirm the pipeline can support correct delta computation:

• dbt-core ≥ 1.7 with a spatial-capable adapter — dbt-postgres ≥ 1.7 against PostGIS ≥ 3.3 for production, or dbt-duckdb ≥ 1.7 with the spatial extension for CI validation. Adapter differences in incremental strategy support are compared in choosing the right spatial adapter.
• A reliable change signal on the source — an updated_at timestamp, a monotonic load id, or a change-data-capture feed. Without one, there is no watermark to gate on and the model cannot be incremental in any correct sense.
• A single canonical projected or geographic SRID already enforced upstream, so bounding-box gates and predicates never mix coordinate frames. CRS normalization belongs in staging, not in the incremental model.
• CREATE INDEX grants on the target schema — the GiST index that makes the delta join cheap has to be (re)built by the model’s post_hook, so the role dbt runs as must be able to create it.
• A stable unique_key that identifies one output row uniquely. For a point-to-zone assignment that is the point id; for a many-to-many overlay it is a composite of both parent keys.
• Configuration surfaced through env_var() and var() so the same model runs in CI, staging, and production without edited SQL.

# dbt_project.yml — defaults the incremental spatial models consume
vars:
  canonical_srid: "{{ env_var('DBT_CANONICAL_SRID', '3857') }}"
  spatial_lookback_hours: "{{ env_var('DBT_SPATIAL_LOOKBACK_HOURS', '48') }}"

models:
  my_project:
    spatial_incremental:
      +materialized: incremental
      +on_schema_change: append_new_columns
      +post-hook: "{{ build_spatial_index(this, 'geom') }}"

Architecture context

An incremental spatial model is an intermediate-layer concern. It consumes validated, reprojected geometry from staging and emits an enriched fact table for marts; it must never see raw, mixed-CRS input. What makes it incremental is that the source scan is narrowed to a window — a slice of time, a set of partition keys, and a bounding box — so the expensive predicate only ever touches geometry inside that window. The diagram below contrasts the full-refresh run, which recomputes every cell, with the incremental run, which lets a partition and bounding-box gate discard everything untouched before any ST_Intersects is evaluated.

The gate is the whole optimization. On a full refresh both grids are identical; the incremental win comes entirely from proving, cheaply, that thirteen of the sixteen cells cannot have changed and can be left as they already sit in the target. That proof has two parts — a temporal filter that finds the changed source rows, and a spatial filter that expands the affected region to include every existing output row a moved geometry could have touched. Skip the second and the model is fast but wrong; the union of a shifted polygon with its old neighbours never gets recomputed.

Configuration walkthrough

Two pieces of shared configuration keep every incremental spatial model honest. The first is a reusable index-builder macro, invoked from each model’s post_hook, so the GiST index is rebuilt after PostGIS drops it on a full --full-refresh. The second is a lookback-window variable, so the temporal gate always reaches slightly further back than the pure watermark — cheap insurance against late-arriving source rows.

-- macros/build_spatial_index.sql
{% macro build_spatial_index(relation, geom_col='geom') %}
  CREATE INDEX IF NOT EXISTS
    {{ (relation.identifier ~ '_' ~ geom_col ~ '_gist') | trim }}
  ON {{ relation }}
  USING GIST ({{ geom_col }});
{% endmacro %}

The IF NOT EXISTS guard matters: on an incremental run the index already exists and the statement is a no-op, but on the initial build or a --full-refresh the relation is recreated and the index must come back with it. Pair this with a stored SRID assertion at run start so a mismatched incremental append fails loudly instead of writing geometry in the wrong frame — the same discipline the index hints for spatial queries guides apply to steer the planner onto the GiST access path.

# dbt_project.yml
on-run-start:
  - "{{ assert_postgis_available() }}"

A GiST index is what lets both the bounding-box gate (&&) and any downstream proximity predicate prune the candidate set before exact geometry math. Without it, every incremental run still touches the entire target table during the merge, and the delta computation you worked to shrink is undone by an unindexed anti-join.

Core implementation

Choosing the unique_key

The unique_key is the contract that makes a re-run idempotent: dbt uses it to decide whether an incoming row updates an existing row or inserts a new one. For spatial outputs it must identify one result row exactly.

• Point-to-zone assignment (each input point gets one nearest or containing zone): the unique_key is the point id. Re-running for a moved point overwrites its single row.
• Many-to-many overlay (each parcel-flood-zone intersection is a row): the key is a composite ['parcel_id', 'flood_zone_id']. dbt supports a list unique_key, which compiles to a multi-column merge condition.
• Grid aggregation (one row per H3 or QuadKey cell): the key is the cell id.

Picking a key that is not actually unique is the most common incremental spatial bug — a merge against a non-unique key silently updates an arbitrary matching row and leaves the rest stale.

-- models/spatial_incremental/int_parcel_flood_overlay.sql
{{ config(
    materialized='incremental',
    unique_key=['parcel_id', 'flood_zone_id'],
    incremental_strategy='delete+insert',
    on_schema_change='append_new_columns',
    post_hook="{{ build_spatial_index(this, 'overlap_geom') }}"
) }}

Gating the source scan with is_incremental()

The is_incremental() block is where the temporal window is applied. On the first run it is false and the model builds in full; on subsequent runs it narrows the driving set to rows that changed since the last load, with a lookback cushion for late arrivals.

with changed_parcels as (
  select parcel_id, geom, updated_at
  from {{ ref('stg_parcels') }}
  {% if is_incremental() %}
    where updated_at >= (
      select coalesce(max(updated_at), '1900-01-01'::timestamptz)
             - interval '{{ var("spatial_lookback_hours") }} hours'
      from {{ this }}
    )
  {% endif %}
)

Reading the watermark from {{ this }} — the model’s own prior output — rather than hardcoding a date keeps the model self-describing: the high-water mark is wherever the last successful run left it. The lookback interval absorbs source rows whose updated_at predates their actual arrival, a routine hazard with batch geometry feeds. Source-side timeliness itself is a separate governance concern, handled by source freshness for geometry feeds.

Adding the bounding-box gate

The temporal filter alone is unsafe for anything but a strict one-to-one assignment. When a parcel’s geometry moves, the correct output changes for every flood zone the parcel used to overlap and every one it now overlaps — rows keyed by parcels that did not themselves change. The fix is to expand the recompute region to the bounding box of all changed geometry, then re-run the overlay for every parcel whose envelope intersects that region.

changed_envelope as (
  -- pad the extent so neighbours a moved parcel could touch are included
  select ST_Expand(ST_Extent(geom), {{ var('envelope_pad_m', 50) }}) as bbox
  from changed_parcels
),
affected_parcels as (
  select p.parcel_id, p.geom, p.updated_at
  from {{ ref('stg_parcels') }} p
  {% if is_incremental() %}
  cross join changed_envelope e
  where p.geom && e.bbox           -- index-assisted bbox overlap
  {% endif %}
)
select
  ap.parcel_id,
  fz.flood_zone_id,
  ST_Intersection(ap.geom, fz.geom) as overlap_geom,
  ST_Area(ST_Intersection(ap.geom, fz.geom)) as overlap_area,
  ap.updated_at
from affected_parcels ap
join {{ ref('stg_flood_zones') }} fz
  on ap.geom && fz.geom
  and ST_Intersects(ap.geom, fz.geom)

The && operator is the load-bearing clause: it is index-assisted, so the planner walks the GiST index to find candidates before evaluating the exact ST_Intersects. The step-by-step construction of exactly this envelope filter — including ST_MakeEnvelope for a fixed window rather than a data-derived one — is worked through in incremental spatial models with bounding-box filters.

Choosing an incremental strategy

The strategy controls how the delta lands in the target. Two matter for spatial work, and the choice hinges on whether a changed key can produce a different number of output rows.

• merge upserts by unique_key: existing keys are updated in place, new keys inserted. It is the right default for one-row-per-key outputs such as a point-to-zone assignment. It does not delete rows whose key vanished from the delta, which is exactly the trap for many-to-many overlays.
• delete+insert removes every target row matching the delta’s keys, then inserts the fresh delta. This is the correct choice for overlays and unions, where a moved parcel might now intersect three flood zones instead of five — merge would update the surviving rows but leave the two stale intersections behind, while delete+insert clears the parcel’s old rows wholesale before writing the new set.

{{ config(
    materialized='incremental',
    unique_key='point_id',
    incremental_strategy='merge',
    merge_update_columns=['nearest_zone_id', 'distance_m', 'geom', 'updated_at'],
    post_hook="{{ build_spatial_index(this, 'geom') }}"
) }}

Naming merge_update_columns explicitly stops the merge from touching columns it should not, and keeps a large geometry column from being rewritten when only a scalar attribute changed. The broader menu of strategies for very large geometry tables — including microbatch and grid-partitioned approaches — is covered in incremental materialization for large geometry tables.

Validation and testing

An incremental spatial model earns trust only when its output provably matches a full refresh. The single most valuable test compares a --full-refresh build against an incremental build of the same data and asserts they agree row-for-row.

# build the ground truth, snapshot it, then prove the incremental path agrees
dbt run --select int_parcel_flood_overlay --full-refresh
# ... capture row count / checksum ...
dbt run --select int_parcel_flood_overlay      # incremental path
# the two outputs must be identical

Encode the invariant as a dbt test so a broken gate fails the build rather than the map. A row-count floor catches the classic failure where the bounding-box gate is too tight and silently drops affected rows:

# models/spatial_incremental/_spatial_incremental.yml
version: 2
models:
  - name: int_parcel_flood_overlay
    columns:
      - name: overlap_geom
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: "ST_IsValid(overlap_geom)"
    tests:
      - dbt_utils.expression_is_true:
          expression: "count(*) >= (select count(distinct parcel_id) from {{ ref('stg_parcels') }})"
          # every parcel that intersects at least one zone must appear

Run the fast version of this in CI against DuckDB before promoting to PostGIS: an in-process engine builds the full DAG in seconds, so the full-refresh-versus-incremental equivalence check is cheap enough to run on every pull request. A silent SRID drift on the incremental append — geometry written in a different frame than the initial build — is caught by asserting a single ST_SRID across the whole table, the same guardrail the core architecture reference recommends at every layer boundary.

Advanced patterns

Grid-partitioned recomputation. Rather than deriving the affected region from a data-dependent envelope, tag every geometry with a coarse grid cell (H3, S2, or QuadKey) in staging and make the cell the partition key. The incremental gate then reduces to “recompute every cell that received a changed row” — a set membership test on a small integer column, far cheaper than an envelope overlap, and one that parallelizes cleanly. This is the natural bridge to partitioning geospatial tables with H3 and dbt and pairs well with the pruning strategies in optimizing proximity joins.

Handling deletes and tombstones. A watermark on updated_at never sees a hard-deleted source row, so its stale output lingers forever. Either soft-delete at the source with a deleted_at column the gate can read, or periodically reconcile with a scheduled full refresh. For overlays under delete+insert, a deleted parcel that still appears in the changed envelope will have its rows cleared and not re-inserted — which is the correct behaviour only if the delete is represented in the delta at all.

Backfilling a schema or logic change. When the overlay logic changes — a new buffer distance, a corrected predicate — the incremental history is now inconsistent with the new code. A blanket --full-refresh is the safe reset, but on a billion-row table it is exactly the cost incremental was meant to avoid. The middle path is a bounded backfill: temporarily widen the bounding-box gate to a specific region or time range via a var(), run the model over just that slice, and repeat region by region until the whole table is rebuilt under the new logic without a single monolithic rebuild.

Rebuilding indexes and statistics. After a large incremental append the GiST index accumulates dead entries and the planner’s statistics go stale, so a query that used the index yesterday may sequential-scan today. Add a VACUUM ANALYZE (or an engine equivalent) to the post_hook chain after the index creation on heavy-write models, so the planner keeps accurate cardinality estimates for the next run’s bounding-box gate.

Troubleshooting

FAQ

Related

• Incremental Spatial Models with Bounding-Box Filters — the step-by-step ST_MakeEnvelope and && gate this page frames.
• Optimizing Proximity Joins — index-driven pruning the incremental delta join depends on.
• Index Hints for Spatial Queries — keep the planner on the GiST access path the bounding-box gate needs.
• Handling Large Geospatial Datasets — partition strategy and incremental state tracking at volume.
• Advanced Spatial Macros & UDF Patterns — the macro and dispatch patterns these models are built from.

↑ Back to Advanced Spatial Macros & UDF Patterns