Monitoring spatial model run times in dbt

This page builds a run-time monitor out of dbt’s own artifacts: load run_results.json after every build, keep only comparable runs, compute a per-model threshold from thirty days of history, and fail a singular test when a spatial model takes materially longer than it ever has.

When to use this approach

  • A spatial model got slower and nobody can say when. Without history the investigation starts with guesses; with it, the step change has a date and usually a matching deployment.
  • Your build window is tightening and you need to know which models are responsible before they push it over.
  • You want regressions caught by the build, not by a person. A test that reads the baseline turns a performance regression into a red build, which is where the rest of the guardrails in spatial observability and cost control already live.

Prerequisites

  • dbt Core 1.5+ writing artifacts to target/, and an orchestration step that runs after the build regardless of its outcome.
  • A warehouse table to hold run history, owned by the project rather than by the orchestrator.
  • dbt-utils for the range tests.
  • At least a week of history before enabling alerts; the model below refuses to threshold on fewer than five observations.

Step-by-step instructions

1. Load the artifacts after every build

run_results.json holds one entry per node with its status, timing and the invocation’s metadata. Flatten the parts worth keeping.

python
# scripts/load_artifacts.py
import json, sys, datetime, psycopg2

def rows_from(run_results_path, manifest_path):
    rr = json.load(open(run_results_path))
    mf = json.load(open(manifest_path))
    invocation = rr["metadata"]["invocation_id"]
    started = rr["metadata"]["generated_at"]
    args = rr.get("args", {})
    for r in rr["results"]:
        node = mf["nodes"].get(r["unique_id"], {})
        yield (
            invocation,
            started,
            r["unique_id"],
            node.get("name"),
            node.get("config", {}).get("materialized"),
            r["status"],
            r.get("execution_time"),
            args.get("target"),
            bool(args.get("full_refresh")),
            " ".join(args.get("select") or []),
        )
bash
# Orchestration step — note the `|| true` so a failed build still records its timings
dbt build --target prod || true
python scripts/load_artifacts.py \
    --run-results target/run_results.json \
    --manifest target/manifest.json \
    --table ops.dbt_run_history

Verify the load captured the run you just did:

sql
select invocation_id, count(*) as nodes, max(execution_time) as slowest
from ops.dbt_run_history
group by invocation_id order by min(run_started_at) desc limit 3;

2. Keep only comparable runs

This is the step that decides whether the baseline means anything. A full refresh, a development target and an ad-hoc single-model run are not the same measurement.

sql
-- models/ops/stg_dbt_run_history.sql
{{ config(materialized = 'incremental', unique_key = ['invocation_id', 'node_id']) }}

select
    invocation_id,
    run_started_at,
    node_id,
    model_name,
    materialization,
    status,
    execution_time as execution_seconds,
    target_name,
    is_full_refresh,
    select_args
from {{ source('ops', 'dbt_run_history') }}
where status = 'success'
  and target_name = 'prod'
  and not is_full_refresh
  and coalesce(select_args, '') = ''      -- nightly full-DAG runs only
{% if is_incremental() %}
  and run_started_at > (select max(run_started_at) from {{ this }})
{% endif %}

Verify the filter did not remove almost everything, which is the usual sign that the orchestrator passes a --select on every run:

sql
select count(distinct invocation_id) as comparable_runs from {{ ref('stg_dbt_run_history') }};
-- Expect roughly one per night over the retained window
Which runs enter the baseline and which are filtered out Runs of the same model arrive from four sources: nightly production builds, full refreshes, developer target runs and ad-hoc selected runs. Only the nightly production builds pass the filter into the baseline. The other three are shown diverted, each labelled with why it would distort the threshold — a full refresh takes far longer, a developer run uses sampled data, and an ad-hoc run builds a different set of nodes. nightly prod build full refresh dev target run ad-hoc --select run comparability filter target · refresh · selection baseline history excluded, and why a full refresh runs many times longer a dev target reads sampled data an ad-hoc run builds different nodes each would move the mean without meaning

3. Compute the baseline

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

with recent as (
    select model_name, execution_seconds
    from {{ ref('stg_dbt_run_history') }}
    where run_started_at > current_date - interval '30 days'
)

select
    model_name,
    count(*)                                                   as runs_observed,
    round(avg(execution_seconds)::numeric, 1)                  as mean_seconds,
    round(coalesce(stddev_samp(execution_seconds), 0)::numeric, 1) as stddev_seconds,
    round(percentile_cont(0.95) within group (order by execution_seconds)::numeric, 1) as p95_seconds,
    round(greatest(
        avg(execution_seconds) + 3 * coalesce(stddev_samp(execution_seconds), 0),
        avg(execution_seconds) * 1.5,
        30
    )::numeric, 1)                                             as alert_threshold_seconds
from recent
group by model_name

The threshold takes the largest of three candidates deliberately. Three sigma catches a step change in a stable model; a 1.5× multiplier keeps the threshold sane for a model whose variance is near zero; and a 30-second floor stops fast models from alerting on scheduler jitter.

Verify the thresholds look reasonable before anything depends on them:

sql
select model_name, runs_observed, mean_seconds, alert_threshold_seconds
from {{ ref('int_model_runtime_baseline') }}
order by mean_seconds desc limit 15;
-- Thresholds should sit comfortably above the mean but well below "twice the build window"

4. Fail the build on a regression

sql
-- tests/assert_no_runtime_regression.sql
with latest as (
    select distinct on (model_name)
        model_name, execution_seconds, run_started_at
    from {{ ref('stg_dbt_run_history') }}
    order by model_name, run_started_at desc
)

select
    l.model_name,
    l.execution_seconds,
    b.alert_threshold_seconds,
    round((l.execution_seconds / nullif(b.mean_seconds, 0))::numeric, 2) as times_baseline
from latest l
join {{ ref('int_model_runtime_baseline') }} b using (model_name)
where b.runs_observed >= 5
  and l.execution_seconds > b.alert_threshold_seconds

Because the test reads the previous run’s timing, it fails the build after the regression rather than during it — which is the correct trade. Blocking a build on its own duration would mean killing a run to report that it was slow.

Verify the test fires by temporarily lowering a threshold:

bash
dbt test --select assert_no_runtime_regression --vars '{runtime_threshold_multiplier: 0.5}'
# Expect a failure listing the models that exceeded the artificially low bar
How the three threshold candidates behave for models with different variance Three models compared. A stable model with almost no variance would get a threshold barely above its mean from the three-sigma rule alone, so the 1.5 times multiplier governs. A noisy model gets a sensible band from three sigma. A very fast model would alert on scheduler jitter, so the thirty-second floor governs. Each bar shows the mean, the three candidate thresholds, and which one wins. stable model mean 240 s 3σ = 246 1.5× = 360 wins noisy model mean 180 s 3σ = 470 wins 1.5× = 270 very fast model mean 4 s 30 s floor wins Taking the largest of the three is what makes one rule work for models whose run times differ by orders of magnitude.

5. Report the trend, not just the breach

sql
-- models/ops/mart_slowest_models_weekly.sql
select
    date_trunc('week', run_started_at) as week,
    model_name,
    round(avg(execution_seconds)::numeric, 1) as mean_seconds,
    round((avg(execution_seconds) / nullif(lag(avg(execution_seconds)) over (
        partition by model_name order by date_trunc('week', run_started_at)), 0))::numeric, 2)
        as week_over_week
from {{ ref('stg_dbt_run_history') }}
where run_started_at > current_date - interval '90 days'
group by 1, 2

Verify by sorting on week_over_week — a model creeping up 10 per cent a week never trips a three-sigma threshold and is exactly what a weekly review should catch.

Gradual creep versus a step change, and which one the threshold catches Two run-time series over twelve weeks. The first jumps abruptly above the alert threshold in week seven and is caught immediately by the singular test. The second rises about eight percent per week, staying under the threshold the whole time while doubling overall, and is caught only by the weekly week-over-week report. Both end at a similar run time. alert threshold caught the same night never trips the threshold twelve weeks of nightly builds → Both models doubled. Only one of them told you.

Configuration reference

Parameter Where Typical value Note
baseline window int_model_runtime_baseline 30 days Long enough for a stable mean, short enough to track real change
minimum observations test predicate 5 Below this a threshold is noise
sigma multiplier threshold formula 3 Lower it only if you enjoy alerts
relative multiplier threshold formula 1.5 Governs for near-zero-variance models
absolute floor threshold formula 30 s Stops fast models alerting on scheduler jitter
retention history model 90 days Small; keeps “was it always slow?” answerable

Gotchas & edge cases

  • execution_time includes queueing on some warehouses. A spike caused by a busy warehouse is not a model regression; if your engine exposes queue time separately, subtract it before computing the baseline.
  • Incremental models and their first full build are different measurements. The comparability filter excludes full refreshes for exactly this reason; without it every model looks like it regressed the day after a backfill.
  • A model renamed is a model with no history. The baseline keys on model_name; keep the node id too, so a rename can be stitched rather than restarting the clock.
  • Artifacts are deleted by default in ephemeral CI runners. Upload them as part of the job, or the history has holes exactly on the days something interesting happened.
  • A faster model can be the bad news. A spatial model that halves its run time overnight has usually lost rows, not gained efficiency — pair this monitor with the drift checks in alerting on geometry drift between runs.

FAQ

Why compute the baseline in dbt rather than in a monitoring tool?

Because the threshold then lives next to the models it describes, moves with the repository, and can be read by a dbt test that fails the build. External monitoring is excellent at charts and poor at blocking a release; this is a case where the check belongs where the code is.

Should the test fail the build or only warn?

Warn for the first few weeks while thresholds settle, then fail for the handful of models on the critical path and warn for the rest. A build that goes red because a non-critical model took twenty per cent longer trains people to re-run rather than investigate.

How does this interact with the warehouse's own query history?

They answer different questions. dbt artifacts tell you which model was slow, which is the actionable unit; warehouse history tells you which query and what it cost, which is where you go next. Load both, key them on the invocation id, and the join gives you a per-model cost trend as well as a run-time one.

What about tests and snapshots — should they be baselined too?

Yes, and they are already in run_results.json. Spatial tests in particular can become the slow part of a build: an ST_IsValid sweep over a large geometry table is a full scan, and it grows with the table. Baseline them the same way; the fix when one regresses is usually to sample rather than to sweep.

Up: Part of Spatial Observability & Cost Control.