Spatial Observability & Cost Control

Spatial models degrade differently from ordinary ones. A model that joins two tables on an integer key gets slower in proportion to its inputs; a model that joins on geometry can get ten times slower because one boundary file was reissued with more vertices, or because a table was rebuilt without its index, while its row counts barely move. Cost behaves the same way — geometry columns are wide, tile pyramids multiply, and a single unbounded predicate can outspend a month of ordinary queries in an afternoon.

This topic covers the instrumentation that makes those changes visible: a per-model run-time baseline built from dbt’s own artifacts, storage accounting that attributes bytes to geometry rather than to tables, drift detection that compares the shape of the data between builds, and the small set of alerts worth waking someone for. It is the operational counterpart to the design work in spatial data architecture and governance, and it is what stops a healthy pipeline from becoming an expensive one without anyone noticing.

Prerequisites checklist

• dbt artifacts retained between runs. run_results.json and manifest.json from every build, landed somewhere queryable; they are the cheapest telemetry available and most projects throw them away.
• A place to store history — one schema holding run history, storage snapshots and drift metrics, rebuilt by dbt like any other model.
• Warehouse metadata access: INFORMATION_SCHEMA.JOBS on BigQuery, QUERY_HISTORY on Snowflake, pg_stat_statements and pg_class on PostGIS.
• An owner per model. An alert that names a model but not a person becomes a filtered notification within two weeks.
• A cost ceiling already in place on consumption-priced engines, as described in warehouse-native GIS adapters. Observability tells you what happened; the ceiling stops it happening.

Architecture context: what to instrument

Nothing in that loop is spatial-specific except the metrics, and that is the point: the mechanism is ordinary dbt, and the judgement is in choosing what to measure. Four measurements carry almost all the value.

Row counts alone are the metric people reach for first and the least informative of the four here: a spatial pipeline can double its cost with identical row counts, because the change is inside the geometry.

Configuration walkthrough

Land the artifacts first. The dbt_artifacts package does this well, but a twenty-line seed of your own is enough to start and has no version constraints to manage.

# In the orchestration job, after every build
dbt build --target prod
python scripts/load_artifacts.py \
    --run-results target/run_results.json \
    --manifest    target/manifest.json \
    --table       ops.dbt_run_history

-- models/ops/int_model_runtime_baseline.sql
{{ config(materialized = 'table') }}

with runs as (
    select
        node_id,
        model_name,
        run_started_at::date as run_date,
        execution_time_seconds
    from {{ source('ops', 'dbt_run_history') }}
    where status = 'success'
      and run_started_at > current_date - interval '30 days'
)

select
    model_name,
    count(*)                                    as runs_observed,
    round(avg(execution_time_seconds)::numeric, 1)  as mean_seconds,
    round(stddev_samp(execution_time_seconds)::numeric, 1) as stddev_seconds,
    round(max(execution_time_seconds)::numeric, 1)  as worst_seconds,
    round((avg(execution_time_seconds) + 3 * coalesce(stddev_samp(execution_time_seconds), 0))::numeric, 1)
        as alert_threshold_seconds
from runs
group by model_name

A three-sigma threshold derived from the model’s own history is better than a fixed number for one reason: spatial models have wildly different natural run times, and a single global “alert above ten minutes” rule either misses a staging model that tripled or fires nightly on the one join that legitimately takes twenty.

Geometry storage needs its own accounting, because a table’s total size hides where the bytes are:

-- models/ops/int_geometry_storage.sql
select
    table_name,
    column_name,
    count(*)                                         as rows,
    round(sum(st_memsize(geom)) / 1024.0 / 1024, 1)  as geometry_mb,
    round(avg(st_npoints(geom)))                     as avg_vertices,
    max(st_npoints(geom))                            as max_vertices
from {{ ref('mart_zones') }}
group by 1, 2

Validation and testing

Observability models deserve tests as much as the models they watch — a broken metric is worse than no metric, because it is trusted.

models:
  - name: int_model_runtime_baseline
    tests:
      - dbt_utils.expression_is_true:
          expression: "runs_observed >= 5"
          config:
            severity: warn
    columns:
      - name: alert_threshold_seconds
        tests:
          - not_null
          - dbt_utils.accepted_range:
              min_value: 1

The runs_observed check is the one that matters: a threshold computed from two runs is noise, and it will fire on the third. Warn rather than error, so a newly added model does not fail the build while its history accumulates.

Then assert the thing the metrics exist to catch, directly:

-- tests/assert_no_model_exceeded_baseline.sql
select
    r.model_name,
    r.execution_time_seconds,
    b.alert_threshold_seconds
from {{ source('ops', 'dbt_run_history') }} r
join {{ ref('int_model_runtime_baseline') }} b using (model_name)
where r.run_started_at > current_date - interval '1 day'
  and b.runs_observed >= 5
  and r.execution_time_seconds > b.alert_threshold_seconds

Deciding what deserves an alert

Most observability projects fail by measuring well and alerting badly. The useful filter is to ask what a person would do in the next hour if the alert fired at three in the morning. For a spatial pipeline, only three answers survive that question.

The first is a build that failed or a data-quality test that went red, because a downstream consumer is about to read something wrong; this is the only category that belongs in a paging channel. The second is a cost event large enough that waiting until morning costs real money — a single query that blew through a byte ceiling, or a job whose credits are already several times the daily average. The third is a freshness breach on a feed that something operational depends on, which is a source problem rather than a model problem and is covered by source freshness for geometry feeds.

Everything else — a model 40 per cent slower than baseline, storage up 5 per cent this month, vertex counts creeping — belongs in a weekly review, where the trend is visible and the response is a piece of planned work rather than an interrupt. Splitting the two channels on that basis is what keeps the paging channel credible, and a paging channel that people trust is worth more than any individual metric in it.

Reading the metrics together

The four measurements are most useful read as a set, because their combinations identify the cause without further investigation. Run time up with vertices up is a source-detail change, and the fix is upstream simplification or a subdivision step. Run time up with vertices flat and bytes flat is almost always a physical-layout change — an index that was not recreated after a rebuild, or a clustering key that a full refresh did not reapply. Bytes up with run time flat points away from the DAG entirely and toward a consumer querying the models directly. And row counts flat with null rates up is a source feed degrading in place, which no performance signal will ever surface because the build gets marginally faster as the real data thins out.

Writing those four combinations into the runbook next to the dashboard turns a chart into a diagnosis. It also sets a useful bar for adding a fifth metric: a new measurement earns its place when it distinguishes two causes the existing four cannot tell apart, not when it is merely interesting.

Advanced patterns

Track vertices, not just rows. A single query summing ST_NPoints per model, stored per build, is the earliest warning of a source-data change that will make everything downstream slower. It costs a scan and it explains the run-time chart above.

Attribute cost to consumers, not just to models. A query tag per consumer — set in the profile or per model — turns “the warehouse costs X” into “the map costs X and the exports cost Y”, which is the only form in which the number is actionable. The serving-tier split in serving spatial data to consumers exists partly to make this attribution possible.

Alert on the derivative, not the level. “This model took eleven minutes” is not actionable; “this model takes three times as long as its own thirty-day baseline” is. The same applies to bytes scanned and to storage.

Keep a monthly snapshot of geometry storage per table. Storage grows quietly and nobody notices until a bill or a disk does. A snapshot model with one row per table per month makes the trend visible and the projection trivial — see estimating storage cost of geometry columns.

Compare the shape of the data, not only its size. Bounding-box extent, centroid distribution and null rates change when a source feed changes upstream, often before the row counts do; that comparison is developed in alerting on geometry drift between runs.

Keeping the history honest across environments

One detail decides whether a baseline stays trustworthy for more than a few weeks: what counts as a comparable run. A model built by a full refresh, a model built in a development target against a sampled dataset, and a model built by the nightly production job all appear in the artifacts as the same node with the same name, and averaging them produces a threshold that means nothing. Record the invocation context — target name, whether the run was a full refresh, and the selection string — alongside every row of run history, and compute baselines only within one context.

The same applies to backfills. A one-off rebuild of two years of history legitimately takes an hour and will inflate a thirty-day mean enough to hide a genuine regression for a month afterwards. Excluding full refreshes from the baseline is the simplest rule that works; a stricter version excludes any run whose selected node count differs materially from the nightly build’s, which also filters out the ad-hoc single-model runs that people do while debugging.

Retention deserves a decision rather than a default. Ninety days of per-model run history is small — a few thousand rows for most projects — and it is what lets you answer “was this always slow, or did it change in March?” Storage snapshots can be monthly and kept for years at negligible cost. Raw warehouse query history is the expensive one, and it is usually best summarized into per-model, per-day aggregates on ingest and then discarded at the source’s own retention period, since nobody re-reads individual query rows from six months ago.

Troubleshooting

FAQ

Related

• Monitoring Spatial Model Run Times in dbt — the baseline model end to end.
• Estimating Storage Cost of Geometry Columns — sizing geometry before and after it lands.
• Alerting on Geometry Drift Between Runs — comparing the shape of the data, not only its size.
• Handling Large Geospatial Datasets — what to change once the metrics say the data is the problem.

Up: Part of Spatial Data Architecture & Governance.