Alerting on geometry drift between runs

This page builds a geometry fingerprint that dbt records on every build — extent, vertex density, centroid distribution and null rate — and the tests that compare today’s fingerprint against recent history so a source change is caught by the build rather than by a reader noticing a map looks wrong.

When to use this approach

  • Your sources are third-party feeds you do not control. Boundary files, address gazetteers and infrastructure extracts get reissued, and a reissue can change everything about the geometry while leaving row counts intact.
  • A quality incident got through and nobody can say when it started. A fingerprint history answers that question retroactively the moment it exists.
  • Row-count monitoring is already in place and did not help. That is the normal experience, and the reason this fingerprint measures shape rather than volume — a point made in spatial observability and cost control.

Prerequisites

  • ST_Extent, ST_NPoints, ST_Centroid and ST_IsValid on the engine.
  • A history table for fingerprints, appended once per model per build.
  • At least a week of fingerprints before alerts are enabled; thresholds derive from history.
  • Source freshness already configured, so a stale feed is distinguished from a changed one — see source freshness for geometry feeds.

Step-by-step instructions

1. Define the fingerprint

Five numbers describe a geometry column well enough to detect almost any meaningful change, and all five are cheap to compute in one pass.

sql
-- macros/geometry_fingerprint.sql
{% macro geometry_fingerprint(relation, geom_column='geom') %}
select
    '{{ relation }}'                                        as model_name,
    '{{ run_started_at }}'::timestamp                       as fingerprinted_at,
    count(*)                                                as row_count,
    count(*) filter (where {{ geom_column }} is null)       as null_geometry_count,
    round(avg(st_npoints({{ geom_column }}))::numeric, 2)   as avg_vertices,
    round(sum(st_npoints({{ geom_column }}))::numeric, 0)   as total_vertices,
    round(st_xmin(st_extent({{ geom_column }}))::numeric, 6) as extent_xmin,
    round(st_ymin(st_extent({{ geom_column }}))::numeric, 6) as extent_ymin,
    round(st_xmax(st_extent({{ geom_column }}))::numeric, 6) as extent_xmax,
    round(st_ymax(st_extent({{ geom_column }}))::numeric, 6) as extent_ymax,
    round(avg(st_x(st_centroid({{ geom_column }})))::numeric, 6) as mean_centroid_x,
    round(avg(st_y(st_centroid({{ geom_column }})))::numeric, 6) as mean_centroid_y,
    count(*) filter (where not st_isvalid({{ geom_column }})) as invalid_count
from {{ relation }}
{% endmacro %}

Verify the fingerprint runs in a time you are willing to pay every build:

bash
dbt run-operation fingerprint_model --args '{model: mart_zones}'
# On a 40M-row table this is a full scan; if that is too slow, sample deterministically
The five fingerprint dimensions and the source change each one detects Five dimensions listed with what each detects. Row count detects a truncated or duplicated load. Null rate detects a parse failure upstream. Average vertices detects a reissued file at different detail. Extent detects a coordinate system or coverage change. Mean centroid detects a projection error or a swapped coordinate order. Each row notes whether row counts alone would have caught it, and only the first says yes. fingerprint dimension what it detects rows alone? row_count truncated or duplicated load yes null_geometry_count a parse failure upstream no avg_vertices a reissue at different detail no extent_xmin … ymax a CRS or coverage change no mean_centroid_x / y swapped lat/lon or bad projection no

2. Record it on every build

sql
-- models/ops/snap_geometry_fingerprint.sql
{{ config(materialized = 'incremental', unique_key = ['model_name', 'fingerprinted_at']) }}

{% set watched = ['stg_zones', 'stg_trip_pings', 'mart_zones'] %}

{% for model in watched %}
{{ geometry_fingerprint(ref(model)) }}
{% if not loop.last %}union all{% endif %}
{% endfor %}

Verify one row per watched model per build:

sql
select fingerprinted_at, count(*) as models
from {{ ref('snap_geometry_fingerprint') }}
group by 1 order by 1 desc limit 5;

3. Compare against recent history

Absolute thresholds age badly; ratios against a trailing median do not.

sql
-- models/ops/int_geometry_drift.sql
with history as (
    select
        model_name,
        fingerprinted_at,
        row_count, avg_vertices, null_geometry_count, invalid_count,
        extent_xmin, extent_ymin, extent_xmax, extent_ymax,
        mean_centroid_x, mean_centroid_y,
        row_number() over (partition by model_name order by fingerprinted_at desc) as recency
    from {{ ref('snap_geometry_fingerprint') }}
),

latest as (select * from history where recency = 1),

baseline as (
    select
        model_name,
        percentile_cont(0.5) within group (order by row_count)     as med_rows,
        percentile_cont(0.5) within group (order by avg_vertices)  as med_vertices,
        percentile_cont(0.5) within group (order by mean_centroid_x) as med_cx,
        percentile_cont(0.5) within group (order by mean_centroid_y) as med_cy,
        count(*) as observations
    from history
    where recency between 2 and 15
    group by model_name
)

select
    l.model_name,
    b.observations,
    round((l.row_count / nullif(b.med_rows, 0))::numeric, 3)        as row_ratio,
    round((l.avg_vertices / nullif(b.med_vertices, 0))::numeric, 3) as vertex_ratio,
    round(abs(l.mean_centroid_x - b.med_cx)::numeric, 6)            as centroid_x_shift,
    round(abs(l.mean_centroid_y - b.med_cy)::numeric, 6)            as centroid_y_shift,
    l.null_geometry_count,
    l.invalid_count
from latest l
join baseline b using (model_name)

Verify the ratios sit near 1.0 on a quiet day, which is the only way to know the comparison is wired correctly:

sql
select model_name, row_ratio, vertex_ratio from {{ ref('int_geometry_drift') }};
-- Expect values within a few per cent of 1.0 when nothing has changed

4. Set thresholds that reflect the data’s own volatility

sql
-- tests/assert_no_geometry_drift.sql
select *
from {{ ref('int_geometry_drift') }}
where observations >= 5
  and (
        row_ratio      not between {{ var('drift_row_min', 0.9) }} and {{ var('drift_row_max', 1.1) }}
     or vertex_ratio   not between {{ var('drift_vertex_min', 0.5) }} and {{ var('drift_vertex_max', 2.0) }}
     or centroid_x_shift > {{ var('drift_centroid_degrees', 0.05) }}
     or centroid_y_shift > {{ var('drift_centroid_degrees', 0.05) }}
     or invalid_count   > 0
  )

The vertex band is deliberately wide and the centroid band deliberately narrow. Vertex counts move for legitimate reasons — a supplier resamples, a simplification step is tuned — while a mean centroid that jumps by more than a rounding error means the data moved on the earth, which is never routine.

Why each fingerprint dimension gets a differently shaped tolerance band Three tolerance bands drawn to scale around a value of one. Row count has a narrow band of plus or minus ten percent because volume is usually stable. Vertex count has a wide band from half to double, because resampling by a supplier is legitimate. Centroid shift has an extremely narrow band, since any real movement of the mean position indicates a coordinate problem. Each band is annotated with an example of a change that should and should not fire. row count ratio 0.9 – 1.1 a 30% drop is a truncated load vertex ratio 0.5 – 2.0 suppliers resample legitimately centroid shift ± 0.05° data does not move on the earth by accident Band width is a statement about what is normal for that dimension — not a uniform sensitivity setting. A reissued boundary file seen through four fingerprint dimensions across one week Four small series over seven builds. Row count stays flat throughout. Average vertices jumps sharply on build five. Extent stays flat. Invalid geometry count rises from zero to a small non-zero value on the same build. The combination is annotated as identifying a supplier reissue at higher detail that also introduced self-intersections, which row counts alone would not have shown. row_count flat — no signal avg_vertices jumps on build 5 extent unchanged — same coverage invalid_count 0 → 214 on build 5 Same coverage, same feature count, far more detail and new self-intersections — a supplier reissue.

5. Make the alert explain itself

An alert that says “drift detected” starts an investigation; one that says which dimension moved and by how much usually ends it.

sql
-- models/ops/mart_drift_report.sql
select
    model_name,
    case
        when invalid_count > 0                       then 'invalid geometry appeared'
        when centroid_x_shift > 0.05
          or centroid_y_shift > 0.05                 then 'features moved — check CRS and coordinate order'
        when vertex_ratio > 2.0                      then 'source reissued with more detail — expect slower joins'
        when vertex_ratio < 0.5                      then 'source reissued with less detail — check simplification upstream'
        when row_ratio < 0.9                         then 'rows missing — check the feed'
        when row_ratio > 1.1                         then 'rows gained — check for duplicate load'
        else 'within tolerance'
    end                                              as diagnosis,
    row_ratio, vertex_ratio, centroid_x_shift, centroid_y_shift, invalid_count
from {{ ref('int_geometry_drift') }}

Verify each branch by replaying a fingerprint with a modified value — the diagnosis text is the part people read, and a wrong branch is worse than no branch.

Configuration reference

Parameter Where Default Note
drift_row_min / max project vars 0.9 / 1.1 Widen for genuinely seasonal feeds
drift_vertex_min / max project vars 0.5 / 2.0 Wide by design; vertex counts move legitimately
drift_centroid_degrees project var 0.05 Roughly 5 km at the equator; tighten for small extents
baseline window drift model runs 2–15 Median of the last fortnight, excluding today
minimum observations test predicate 5 Below this the median is not a baseline
watched models fingerprint model 3–10 Fingerprinting is a full scan; watch sources and key marts, not everything

Gotchas & edge cases

  • A full scan per build is not free. Fingerprint sources and a few key marts, not every model. For very large tables, sample deterministically with tablesample system (1) repeatable (42) so the fingerprint is comparable between runs.
  • The median must exclude today. Including the current run in its own baseline dampens exactly the signal you are looking for.
  • A drifting fingerprint on a model with a filter is often a filter change. Check the model’s own where clause before blaming the source.
  • Centroid means are not robust to outliers. One feature placed at the origin by a bad parse moves the mean noticeably — which is useful for detection but means the diagnosis should be checked before it is believed.
  • A fingerprint over an incremental model measures the whole table, not the new slice. For append-only models the ratios will be dominated by history and will barely move; fingerprint the incoming slice separately if that is what you want to watch.

FAQ

Why the median rather than the mean for a baseline?

Because one bad build should not move the baseline. A single reload that doubles the row count shifts a mean enough to hide the next real change; the median of the last fourteen runs ignores it. The same reasoning applies to the extent and vertex baselines.

Should drift fail the build or warn?

Fail for invalid geometry and centroid movement, warn for vertex-ratio changes. The first two mean something is wrong; the third usually means something changed upstream that you now need to know about, which is a conversation rather than an emergency.

How does this differ from a source-freshness check?

Freshness answers “did new data arrive?”, drift answers “is the data that arrived the same shape as before?”. A feed can be perfectly fresh and completely wrong — reissued in a different projection, for instance — which is precisely the case freshness cannot see and this fingerprint can.

Can the fingerprint detect a change in a single feature?

No, and it should not try — aggregate fingerprints detect population-level change. For per-feature change detection, snapshot the geometry with a hash per feature and diff the hashes; that is a heavier mechanism worth adding only where individual features are contractually significant, such as a regulated boundary set.

Up: Part of Spatial Observability & Cost Control.