Source Freshness for Geometry Feeds

Spatial feeds fail quietly. A GPS ingest job dies at 02:00, an AIS decoder stops committing after a broker reconnect, a nightly parcel export silently ships yesterday’s snapshot again — and nothing in the warehouse raises its hand. The geometries are still there, the joins still return rows, the dashboard still renders a map. What has actually happened is that every model downstream is now transforming a frozen picture of the world, and the first person to notice is usually a domain expert asking why a vessel has been parked in a shipping lane for three days. This page, part of the Spatial Data Architecture & Governance collection, covers how to wire dbt source freshness onto geometry sources so a lagging feed trips a loud, early alarm instead of a slow, invisible corruption.

The reason freshness deserves its own discipline for spatial data is that geometry feeds are unusually prone to silent staleness. A relational fact table that stops loading usually breaks something obvious — a count goes to zero, a join drops rows. A spatial feed that stops loading keeps returning perfectly valid polygons and points; only their timestamps betray the problem, and timestamps are exactly what nobody queries by default. Freshness monitoring inverts that: it makes the ingest timestamp the thing the pipeline asserts on, so the age of the newest geometry becomes a first-class, testable property of the source rather than a footnote nobody reads.

Prerequisites checklist

Before adding freshness rules to any spatial source, confirm the ingest layer actually records the signals freshness depends on:

• dbt-core ≥ 1.7 with any warehouse adapter. dbt source freshness is engine-agnostic — it issues a max() query over a timestamp column, so it works identically on PostGIS, DuckDB, BigQuery, or Snowflake. Adapter choice is covered in choosing the right spatial adapter.
• A reliable load-time column on every geometry source. Freshness measures the age of the newest row, so you need a monotonically increasing timestamp — an ingest/commit time, not an event time you cannot trust. The difference between the two is the single most important decision on this page and is discussed at length below.
• The timestamp stored in UTC, or a column whose timezone dbt can resolve. Freshness compares against “now” at run time; a naive local-time column drifts by your UTC offset and will fire false warnings around midnight.
• A scheduler or CI runner that can invoke dbt on a cadence at least as tight as your tightest freshness threshold. A ten-minute error_after is meaningless if freshness only runs once a day.
• An alerting channel — the exit code of dbt source freshness is the integration point. A non-zero exit on error is what a scheduled job, GitHub Actions step, or dbt Cloud job turns into a page or a Slack message.

Architecture context

Freshness sits at the very top of the spatial model dependency graph — it runs against raw sources before the first staging model reads them. That position is deliberate: catching a stale feed at the source boundary means dbt can refuse to build the DAG on top of it, rather than propagating stale geometry through staging, intermediate joins, and marts before anyone notices. The diagram below traces that flow, from the physical feed through the loaded_at_field that dbt reads, into the warn/error threshold comparison, and out to the alert.

The rest of this page works through that flow concretely: declaring the source with the right load-time column, choosing thresholds that fit the feed’s cadence, running the check in CI and on a schedule, and handling the awkward cases — high-frequency streams, slow monthly exports, and multi-region feeds that arrive on different clocks.

Configuration walkthrough

Freshness is declared entirely in a source YAML file. The two rules that matter are loaded_at_field — the timestamp column dbt measures against — and the freshness block with its warn_after and error_after sub-keys. Each threshold takes a count and a period (minute, hour, or day).

# models/staging/_sources.yml
version: 2

sources:
  - name: mobility
    database: raw
    schema: ingest
    # Project-wide default: applies to every table unless overridden below.
    loaded_at_field: ingested_at
    freshness:
      warn_after: {count: 2, period: hour}
      error_after: {count: 6, period: hour}

    tables:
      - name: gps_pings
        description: "High-frequency vehicle GPS fixes, one row per device per second."
        # Tighten the inherited default — this feed should never lag.
        freshness:
          warn_after: {count: 10, period: minute}
          error_after: {count: 30, period: minute}

      - name: parcel_boundaries
        description: "Nightly cadastral export, full-refresh snapshot."
        # Loosen it — a nightly feed is not late until well into the next day.
        loaded_at_field: export_committed_at
        freshness:
          warn_after: {count: 26, period: hour}
          error_after: {count: 30, period: hour}

Three things in this file are load-bearing. First, loaded_at_field is declared once at the source level and inherited by every table, then overridden per table where the ingest column differs — parcel_boundaries records export_committed_at rather than ingested_at. Second, thresholds are per feed: a GPS stream that emits every second warrants a 10-minute warning, while a nightly parcel export is not even late until 26 hours have passed. Applying the streaming threshold to the parcel feed would page someone every single night. Third, setting freshness: null on a table opts it out entirely — useful for a static reference layer such as a country-boundary lookup that legitimately never changes.

Ingest time versus event time — the decision that makes or breaks freshness

The most consequential choice is which timestamp loaded_at_field points at. Spatial feeds almost always carry two: the moment the observation happened in the real world (an AIS position report’s own timestamp, a GPS fix’s device clock) and the moment the row landed in your warehouse. Freshness must measure the ingest time, not the event time.

The reason is specific to spatial data. Event-time clocks on GPS and AIS sources are notoriously unreliable — devices with dead batteries backdate, cheap GPS units drift, and AIS transponders are trivially spoofable. If loaded_at_field points at a device-supplied event time, a single vessel broadcasting a wrong clock can make an actively-flowing feed look stale (a backdated timestamp) or, worse, make a dead feed look fresh (a device broadcasting a future timestamp). Ingest time is a clock you control and trust: it advances if and only if your pipeline is actually writing rows.

-- models/staging/stg_gps_pings.sql
-- Ingest time is stamped by the loader, never by the device.
select
    device_id,
    ST_SetSRID(ST_MakePoint(lon, lat), 4326) as geom,
    event_time,                         -- device clock: kept for analysis, NOT for freshness
    ingested_at                         -- warehouse clock: this is loaded_at_field
from {{ source('mobility', 'gps_pings') }}

If your loader does not already stamp an ingest time, add a default current_timestamp column at the landing table, or — on dbt 1.8+ — use loaded_at_query to compute a trustworthy load signal (for example max(ingested_at) after a coalesce) without adding a column.

Core implementation

Running the check

dbt source freshness is a first-class command, separate from dbt test. It queries max(loaded_at_field) per source table, subtracts it from the run-time clock, and compares the result to the thresholds.

# Check every source that has a freshness block
dbt source freshness

# Check just the mobility feeds
dbt source freshness --select source:mobility

# Check a single high-frequency table
dbt source freshness --select source:mobility.gps_pings

The command writes a machine-readable target/sources.json and returns an exit code that encodes the worst state found: 0 when everything passes or only warns, and non-zero when any source breaches its error_after. That exit code is the entire integration surface — everything downstream keys off it.

Filtering the freshness query for partitioned feeds

Large geometry feeds are frequently append-only and partitioned by ingest date. Running max(ingested_at) over the full history forces a scan of every partition, which is both slow and needless — you only care about the most recent one. The filter key injects a WHERE clause into the freshness query so it touches only hot data. This matters most on the same high-volume tables that motivate handling large geospatial datasets.

      - name: ais_positions
        description: "Real-time AIS vessel positions, ~40M rows/day."
        loaded_at_field: ingested_at
        freshness:
          warn_after: {count: 15, period: minute}
          error_after: {count: 45, period: minute}
          # Only scan the last day of partitions when measuring lag.
          filter: ingested_at > dateadd('day', -1, current_timestamp)

The filter expression is warehouse SQL, so match it to your engine’s date arithmetic. Without it, a busy AIS table’s freshness check can take longer than the freshness window it is trying to protect.

Per-feed thresholds as a policy, not a guess

Thresholds should be derived from each feed’s known cadence plus a tolerance, not picked by feel. A defensible rule of thumb: set warn_after to roughly two to three times the feed’s normal inter-arrival gap, and error_after to the point at which downstream data is genuinely untrustworthy.

The pattern is consistent: fast feeds get tight windows in minutes, slow feeds get generous windows in hours or days, and the gap between warn and error reflects how much lag the downstream consumer can absorb before its output is wrong.

Validation and testing

Freshness rules are themselves worth validating — a threshold that never fires is as useless as no threshold at all. Two checks prove the configuration is live.

First, confirm dbt is measuring the column you think it is. Compile is not enough here; run the check and read the report:

dbt source freshness --select source:mobility.gps_pings
# In the log, confirm the "max_loaded_at" timestamp matches a real recent
# ingest, and that "age" is a small number of minutes on a healthy feed.

Second, prove the alarm actually trips. The cleanest way is to point the check at a source you know is stale — a fixture or a paused sandbox feed — and confirm a non-zero exit:

# Against a deliberately stale table, error_after should be breached.
dbt source freshness --select source:mobility.stale_fixture
echo "exit code: $?"   # expect non-zero — the ERROR state

Because this exit-code behavior is exactly what CI depends on, it belongs in the same suite as your other spatial checks. Wiring freshness alongside geometry validity and SRID assertions is covered in spatial testing in CI pipelines; freshness is the one check that runs before the build rather than after it, so it gates the whole DAG.

Advanced patterns

Freshness as a build gate. The strongest use of freshness is to refuse the run entirely when a critical feed is stale. Chain the commands so a stale source aborts before any model materializes on top of it:

# Abort the whole run if a source is stale — no point transforming frozen data.
dbt source freshness --select source:mobility && dbt build --select source:mobility+

Because && short-circuits on the non-zero exit, dbt build never runs when a mobility feed has breached its error_after. The source:mobility+ selector then builds exactly the models fed by those sources.

Splitting warn and error into different responses. A warning and an error usually deserve different reactions — a warning is a Slack note, an error is a page. Parse target/sources.json to route them separately:

# scripts/route_freshness.py
import json, sys

with open("target/sources.json") as f:
    results = json.load(f)["results"]

errors = [r["unique_id"] for r in results if r["status"] == "error"]
warns  = [r["unique_id"] for r in results if r["status"] == "warn"]

if warns:
    print(f"::warning::stale-ish feeds: {', '.join(warns)}")
if errors:
    print(f"::error::STALE feeds breaching error_after: {', '.join(errors)}")
    sys.exit(1)   # page on-call only for true errors

Handling feeds that stop cleanly versus feeds that go empty. A feed that stops writing is caught by max(loaded_at) going stale. A feed that keeps writing but writes nothing new of interest — for example an AIS decoder that reconnects and re-emits the same last-known positions — can fool a naive freshness check because ingest time keeps advancing. For those, pair freshness with a downstream row-count or distinct-geometry test so both “no new rows” and “same rows re-stamped” are covered.

Documenting the freshness contract. The chosen thresholds encode a real service-level expectation about each feed and belong in the source’s description and the project’s lineage documentation, so a consumer three models downstream can see how fresh their inputs are guaranteed to be. That contract sits naturally alongside the broader work of documenting geometry columns in dbt YAML.

Troubleshooting

FAQ

Related

• Configuring dbt Source Freshness for Spatial Feeds — the step-by-step build: declare the source, set thresholds, schedule, and alert.
• Handling Large Geospatial Datasets — partition and filter strategies that keep the freshness max() query cheap on high-volume feeds.
• Spatial Testing in CI Pipelines — run freshness alongside geometry validity and SRID checks on every pull request.
• Spatial Model Dependency Graphs — where source freshness sits at the top of the DAG and gates everything below it.
• Spatial Data Architecture & Governance — the wider governance program freshness monitoring is one cornerstone of.

↑ Back to Spatial Data Architecture & Governance