Incremental Spatial Models with Bounding-Box Filters

This page shows you how to gate an incremental dbt spatial model on a ST_MakeEnvelope bounding box combined with the && overlap operator, so each run recomputes only the geometry inside a changed spatial window instead of the whole table.

When to use this approach

Reach for a bounding-box gate — rather than a plain updated_at watermark — when any of these hold:

• Changes cluster in a known region. A daily feed that only touches one metro area, one survey tile, or one delivery zone is the ideal case: an envelope around that region lets the GiST index discard everything else. If instead your changes are scattered globally, a grid-partition key is a better fit — see incremental materialization for large geometry tables.
• A moved geometry affects its neighbours. For an overlay or union, the correct output changes for every feature whose envelope overlaps a changed one, not just the changed rows themselves. A temporal filter alone is silently wrong here; the spatial gate is what restores correctness. The parent incremental spatial materializations guide frames why.
• You want the planner to use the spatial index for the gate. The && operator is index-assisted, so the envelope test is cheap. Keeping it index-eligible is the same discipline covered in using spatial index hints in dbt materializations.

Prerequisites

• dbt Core 1.7+ with dbt-postgres against PostGIS 3.x (the examples use PostGIS syntax; DuckDB spatial supports ST_MakeEnvelope and && with minor differences).
• A source model carrying a valid geometry column in a single canonical SRID, already normalized in staging.
• A change signal — an updated_at timestamp or load id — on the source.
• CREATE INDEX grants on the target schema so the model can build its own GiST index.
• The envelope bounds surfaced through var() so the window is configurable per environment and per backfill:

# dbt_project.yml
vars:
  canonical_srid: 3857
  bbox_min_x: "{{ env_var('DBT_BBOX_MIN_X', '-13700000') }}"
  bbox_min_y: "{{ env_var('DBT_BBOX_MIN_Y', '5900000') }}"
  bbox_max_x: "{{ env_var('DBT_BBOX_MAX_X', '-13600000') }}"
  bbox_max_y: "{{ env_var('DBT_BBOX_MAX_Y', '6000000') }}"

Step-by-step instructions

1. Configure the model as incremental with a unique_key and index post_hook

Start from the materialization contract. The unique_key must match the output grain — one row per feature for an assignment, a composite key for an overlay — and the post_hook re-establishes the GiST index that the && gate relies on after any full refresh.

-- models/spatial_incremental/int_sensor_zone_overlay.sql
{{ config(
    materialized='incremental',
    unique_key='sensor_id',
    incremental_strategy='delete+insert',
    post_hook="CREATE INDEX IF NOT EXISTS {{ this.identifier }}_geom_gist ON {{ this }} USING GIST (geom)"
) }}

Verify the config compiles and the hook is attached:

dbt compile --select int_sensor_zone_overlay
# Confirm the compiled config shows materialized='incremental'
# and the CREATE INDEX ... USING GIST post_hook.

2. Build the changed-window envelope with ST_MakeEnvelope

ST_MakeEnvelope(xmin, ymin, xmax, ymax, srid) constructs a rectangular polygon in one SRID. Wrap the four bounds in var() calls so the same model serves both the routine incremental window and a wider backfill window without an edit. The SRID argument must match the geometry column’s SRID exactly, or the && test compares mismatched frames.

with changed_window as (
  select ST_MakeEnvelope(
    {{ var('bbox_min_x') }},
    {{ var('bbox_min_y') }},
    {{ var('bbox_max_x') }},
    {{ var('bbox_max_y') }},
    {{ var('canonical_srid') }}
  ) as bbox
)

Verify the envelope is a valid, non-empty polygon in the expected SRID:

select ST_SRID(bbox), ST_Area(bbox), ST_IsValid(bbox)
from (
  select ST_MakeEnvelope(-13700000, 5900000, -13600000, 6000000, 3857) as bbox
) t;
-- Expect the SRID you passed, a positive area, and true validity.

3. Gate the source scan with && inside is_incremental()

Now restrict the driving set. On the first run is_incremental() is false and the model builds the full table; on later runs the && overlap keeps only source geometry whose bounding box intersects the window. Because && is index-assisted, the planner probes the GiST index rather than scanning every row.

sensors as (
  select s.sensor_id, s.geom, s.updated_at
  from {{ ref('stg_sensors') }} s
  {% if is_incremental() %}
  cross join changed_window w
  where s.geom && w.bbox
  {% endif %}
)
select
  se.sensor_id,
  z.zone_id,
  se.geom,
  se.updated_at
from sensors se
join {{ ref('stg_zones') }} z
  on se.geom && z.geom
  and ST_Intersects(se.geom, z.geom)

Verify the gate prunes rows and uses the index rather than a sequential scan:

EXPLAIN ANALYZE
SELECT s.sensor_id
FROM stg_sensors s
WHERE s.geom && ST_MakeEnvelope(-13700000, 5900000, -13600000, 6000000, 3857);
-- Expect "Index Scan using ..._geom_gist", not "Seq Scan",
-- and far fewer rows than the full table.

The envelope selects a spatial window; the && index probe keeps only the features whose bounding box overlaps it, and the exact ST_Intersects then runs on that small candidate set before the delta is merged:

4. Pair the spatial gate with a temporal watermark

The envelope narrows where the model looks; a watermark narrows when. Combining them means a run recomputes only geometry that is both inside the window and newer than the last load — the smallest correct delta. Read the high-water mark from {{ this }} so the model stays self-describing.

  {% if is_incremental() %}
  cross join changed_window w
  where s.geom && w.bbox
    and s.updated_at >= (
      select coalesce(max(updated_at), '1900-01-01'::timestamptz)
      from {{ this }}
    )
  {% endif %}

Verify correctness by comparing against a full refresh — the two must agree for the window:

dbt run --select int_sensor_zone_overlay --full-refresh
dbt run --select int_sensor_zone_overlay
# Row counts and a geometry checksum for the window must match.

5. Widen the window for a bounded backfill

When logic changes, override the bounds at the command line to rebuild one region at a time instead of the whole table. The var()-driven envelope makes this a flag, not a code edit.

dbt run --select int_sensor_zone_overlay \
  --vars '{bbox_min_x: -13800000, bbox_min_y: 5800000, bbox_max_x: -13500000, bbox_max_y: 6100000}'

Verify the backfill touched only the intended region by asserting every output geometry falls inside the widened envelope before moving to the next slice.

Configuration reference

Gotchas & edge cases

• SRID mismatch between envelope and column. ST_MakeEnvelope stamps whatever SRID you pass; if it differs from the geometry column, && either errors or silently matches nothing. Assert ST_SRID(geom) equals the envelope SRID upstream, and normalize CRS in staging.
• Degrees vs metres in the bounds. In a projected SRID the bounds are metres; on raw EPSG:4326 they are degrees. A window that looks right in one frame spans a continent in the other. Pick the canonical projected SRID before writing bounds.
• Neighbours outside the window. If a moved feature sits at the window edge, a zone just outside it can still overlap. Pad the envelope with ST_Expand by at least the largest feature’s extent so edge overlaps are not missed — the same neighbour-inclusion logic the optimizing proximity joins patterns rely on.
• && is bounding-box only. The operator tests envelope overlap, not true intersection — it is a pre-filter, so always follow it with an exact predicate like ST_Intersects for correctness. Dropping the exact test returns false positives at the corners.
• Index missing after full refresh. A --full-refresh recreates the relation and drops the index; without the post_hook rebuild the next && gate has no index and scans the table. Keep the CREATE INDEX IF NOT EXISTS hook on every run.

FAQ

Related

• Incremental Spatial Materializations — the full incremental spatial pattern this envelope gate plugs into.
• Speeding up nearest-neighbor joins in PostGIS — the <-> KNN alternative when you need the nearest feature, not a window.
• Using spatial index hints in dbt materializations — keep the && gate on the GiST access path.

Up: Part of Incremental Spatial Materializations.