Incremental Materialization for Large Geometry Tables

This page shows you how to materialize a large geometry table incrementally in dbt so each run recomputes only new or changed rows — using an is_incremental() watermark, a partition-pruning predicate, a bounding-box pre-filter, and a lookback window that catches late-arriving geometry without a full rebuild.

When to use this approach

Incremental materialization pays off once a full table rebuild costs more than the freshness you gain from it. Reach for it when:

• A rebuild scans tens of millions of geometries or more. Geometry columns are heavy and topology is expensive, so re-materializing the whole table nightly wastes warehouse time. This is the temporal half of the scaling controls in handling large geospatial datasets; combine it with partitioning geospatial tables with H3 and dbt so each run also touches only the relevant cells.
• New rows arrive on a monotonic key. An event_timestamp, loaded_at, or ascending id lets is_incremental() filter the source to just the delta. Without such a watermark, incremental logic has nothing to gate on.
• The expensive work is a spatial join or transform. If a proximity or containment predicate dominates cost, restricting it to changed rows is the single biggest win — the exact-predicate tuning lives in incremental spatial models with bounding-box filters and the broader pattern in optimizing proximity joins.

If your table is small, changes are unpredictable across the whole history, or correctness demands a deterministic full recompute every run, a plain table materialization is simpler and safer.

Prerequisites

• dbt Core >= 1.7 for current incremental_strategy, on_schema_change, and incremental_predicates support.
• A spatial engine with a stable merge/delete+insert path: PostGIS >= 3.3, the DuckDB spatial extension for CI, or BigQuery GIS / Snowflake GEOGRAPHY. Pick per choosing the right spatial adapter.
• A canonical SRID enforced in staging so the incremental predicate never compares mismatched projections — see spatial reference system management.
• A reliable watermark column (event_timestamp or loaded_at) that is indexed and monotonic enough to gate the delta.
• Project vars for the canonical SRID, the lookback window, and the incremental bounding box:

# dbt_project.yml
vars:
  canonical_srid: "{{ env_var('DBT_SPATIAL_CANONICAL_SRID', '4326') }}"
  incremental_lookback_hours: 48   # re-scan window for late-arriving rows

Step-by-step instructions

An incremental run has a distinct shape from a full refresh: it reads only rows past the watermark (minus a lookback), applies the same spatial filters as a full build, and merges the delta on a stable key. The diagram traces both paths.

1. Configure the incremental model with a unique_key

Declare incremental with a merge strategy and a genuinely unique key. The key is what lets a re-processed late row overwrite its earlier version instead of duplicating it.

-- models/marts/fct_events_enriched.sql
{{ config(
    materialized='incremental',
    unique_key='event_id',
    incremental_strategy='merge',
    on_schema_change='append_new_columns',
    post_hook="CREATE INDEX IF NOT EXISTS {{ this.name }}_geom_gist ON {{ this }} USING GIST (geom)"
) }}

SELECT
    e.event_id,
    e.event_timestamp,
    e.geom,
    z.zone_id
FROM {{ ref('stg_events') }} e
LEFT JOIN {{ ref('dim_zones') }} z
  ON ST_Intersects(e.geom, z.geom)

Verify the key is actually unique before you trust the merge:

SELECT event_id, count(*) AS n
FROM {{ ref('stg_events') }}
GROUP BY event_id HAVING count(*) > 1;
-- must return zero rows; a non-unique key silently drops or double-writes on merge

2. Gate the source with an is_incremental() watermark and lookback

Filter the driving table to rows newer than the current maximum, minus a lookback window. The lookback is the cheap insurance that recovers rows whose timestamp landed behind the watermark.

{% if is_incremental() %}
  WHERE e.event_timestamp > (
      SELECT MAX(event_timestamp) - INTERVAL '{{ var("incremental_lookback_hours") }} hours'
      FROM {{ this }}
  )
{% endif %}

Verify the delta is bounded, not the whole table:

-- compile the model, then inspect the row estimate of the incremental branch
EXPLAIN
SELECT count(*) FROM stg_events
WHERE event_timestamp > (SELECT MAX(event_timestamp) - INTERVAL '48 hours' FROM fct_events_enriched);
-- expect an estimate near one window of arrivals, not the full source cardinality

3. Push the delta filter into the join with incremental_predicates

A merge still scans the target to find matching keys. On large targets, restrict that scan too, so the merge does not read the entire history to update a day’s worth of rows.

{{ config(
    materialized='incremental',
    unique_key='event_id',
    incremental_strategy='merge',
    incremental_predicates=[
      "DBT_INTERNAL_DEST.event_timestamp > current_date - interval '3 days'"
    ]
) }}

Verify the merge touches a bounded slice of the target:

EXPLAIN (ANALYZE, BUFFERS)
-- run dbt with --log-level debug and read the emitted MERGE plan;
-- the destination scan should be date-bounded, not a full Seq Scan of the target.
SELECT 1;

4. Add a bounding-box pre-filter before the exact predicate

The spatial join is the costly part. Gate it behind a cheap && bounding-box test so the GiST index prunes candidates before any exact ST_Intersects runs — the same two-phase discipline used across the large-dataset patterns.

FROM {{ ref('stg_events') }} e
LEFT JOIN {{ ref('dim_zones') }} z
  ON e.geom && z.geom               -- index-eligible bounding-box pre-filter
 AND ST_Intersects(e.geom, z.geom)  -- exact topology on survivors only
{% if is_incremental() %}
  WHERE e.event_timestamp > (
      SELECT MAX(event_timestamp) - INTERVAL '{{ var("incremental_lookback_hours") }} hours'
      FROM {{ this }}
  )
{% endif %}

Verify the planner uses the index for the join:

EXPLAIN (ANALYZE, BUFFERS)
SELECT e.event_id, z.zone_id
FROM stg_events e JOIN dim_zones z
  ON e.geom && z.geom AND ST_Intersects(e.geom, z.geom);
-- expect "Index Scan using ..._geom_gist", not "Seq Scan", on the zone side

5. Handle late-arriving geometry explicitly

Some feeds deliver corrections — a geometry restated hours after its event time. Because the merge keys on event_id and the lookback re-scans the recent window, a corrected row overwrites the old one. Prove it with a targeted test rather than trusting it.

-- tests/assert_no_stale_geometry.sql
-- Returns rows where the mart geometry disagrees with the freshest source geometry.
SELECT f.event_id
FROM {{ ref('fct_events_enriched') }} f
JOIN {{ ref('stg_events') }} s USING (event_id)
WHERE NOT ST_Equals(f.geom, s.geom)
  AND s.event_timestamp > current_date - interval '{{ var("incremental_lookback_hours") }} hours' / 24

Verify by running a full refresh occasionally and diffing against the incremental output:

dbt run --select fct_events_enriched --full-refresh
# then compare row counts and a geometry checksum against the incremental build;
# a mismatch means the watermark or lookback is dropping corrections.

Configuration reference

Gotchas & edge cases

• A non-unique unique_key corrupts the merge. If duplicates exist in the source, merge either drops all but one or double-writes non-deterministically. Assert uniqueness in staging before the first incremental run.
• No lookback drops out-of-order rows forever. A strict ts > max(ts) watermark permanently skips any row whose timestamp arrives behind the high-water mark. The lookback window trades a little rescan for correctness; size it to the worst arrival delay you have observed.
• Geometry equality is not byte equality. Comparing corrected geometries with = can miss logically equal shapes with different vertex ordering or precision. Use ST_Equals for the topological comparison, and remember it is more expensive than a scalar check.
• Merge without incremental_predicates scans the whole target. On a billion-row table the destination scan, not the source delta, becomes the bottleneck. Bound it with a date predicate on DBT_INTERNAL_DEST.
• append strategy silently duplicates. It is fine for pure event logs that never restate, but any feed that corrects geometry needs merge or delete+insert; append will accumulate stale duplicates.
• Schema drift on geometry columns. A source that changes a column from GEOMETRY to GEOGRAPHY mid-stream breaks the merge type contract. Treat it as a schema event under versioning spatial schemas in dbt, not an on_schema_change auto-fix.

FAQ

Related

• Handling Large Geospatial Datasets — the full set of scaling controls this incremental pattern fits into.
• Partitioning Geospatial Tables with H3 and dbt — pair cell partitioning with the watermark so runs touch only relevant cells.
• Incremental Spatial Models with Bounding-Box Filters — tune the exact-predicate stage of the incremental spatial join.

Up: Part of Handling Large Geospatial Datasets.