Schema & Attribute Drift Detection

Spatial tables rarely break at the geometry column first — they break at the columns beside it. A third-party parcel feed quietly renames owner_type to ownertype, a sensor vendor widens a smallint status code to text, or a jurisdiction re-codes its land-use domain from two-letter abbreviations to numeric classes, and every one of these changes passes ingestion with a healthy row count and a valid geometry. This guide treats structural schema change and statistical attribute change as measured, alertable signals, distinct from the datum and topology faults handled elsewhere, and it sits under the broader Spatial Data Freshness & Quality Metrics program. The audience is the data engineers, GIS platform administrators, and compliance teams who own the non-geometric payload that spatial joins, cartography, and regulatory reporting all depend on.

Why schema and attribute drift is its own signal

Coordinate reference system drift moves a feature to the wrong place on the earth; geometry drift corrupts its structure. Schema and attribute drift does neither — the geometry stays valid and correctly located while the meaning of the row erodes. A land_use column that silently re-codes R1 (single-family residential) as 1 breaks every categorical filter, choropleth legend, and compliance rollup that keyed on the old vocabulary, yet ST_IsValid returns true and ST_SRID is unchanged. Because the defect is orthogonal to the datum checks in Coordinate Reference System Validation, it needs its own instrumentation, its own baseline, and its own alert tier.

There are three mechanically distinct classes to separate, because they have different blast radii and different remediations:

• Structural drift — a column is added, dropped, renamed, reordered, or its declared type changes (smallint → integer, varchar(8) → text, NOT NULL relaxed to nullable). This is a schema-contract break: it can crash a downstream cast, break a view, or make an INSERT ... SELECT silently misalign columns. It must fail the run.
• Attribute-value distribution drift — the schema is unchanged but the values shift: nulls creep up in a formerly complete column, a numeric range slides, or a category’s frequency collapses. This is usually a source-quality regression, not a break, so it warns rather than halts.
• Domain-code drift — the set of distinct values in a coded column changes: a new class appears that no downstream lookup table maps, or an existing code disappears. This straddles the two — a new unmapped code is often a hard failure, a shifted frequency is a warning.

Distribution and domain drift on slowly-moving reference data is subtle enough to deserve its own focused treatment; the baseline-snapshot and population-stability techniques for catching it are covered in Detecting Attribute Drift in Slowly Changing Layers.

Architecture

Drift detection is a stateful gate — unlike a validity check, it has nothing to compare against without a persisted baseline. The reference design keeps two baselines per monitored table: a structural fingerprint (the ordered set of column names, types, nullability, and domain constraints, hashed) and a statistical profile (per-column null ratio, distinct-value count, min/max for numerics, and a top-k category histogram for coded columns). Both are captured at a known-good trust boundary and version-controlled so a legitimate schema migration is an explicit, reviewed baseline bump rather than a silent drift.

The gate runs immediately after ingestion into staging and before promotion to the curated layer, so a contract break is a one-partition skip rather than a published regression. It reuses the same boundary telemetry namespace as the rest of the measurement plane, attaching spatial attributes to spans using the conventions in the Geospatial Metric Taxonomy for ETL, so a gis.spatial.schema_drift_total counter incremented by a Python operator and one incremented by a PostGIS trigger land in one series. Structural fingerprinting is cheap and runs every batch; statistical profiling is heavier, so on large tables it samples partitions rather than scanning the whole layer, following the scoping rules in Observability Scoping Rules for Vector Data.

This gate is the attribute-side complement to the row-count reconciliation described in Automated Row Count & Attribute Sync: row-count sync answers “did every row survive the hop between layers,” while drift detection answers “did the shape and meaning of each row’s attributes stay within contract.” Run together, they close the gap where a feed hits its row-count SLA while quietly dropping a column or nulling a field.

Metric Specification

Drift observability rests on a compact vocabulary under the gis.spatial.* namespace, split cleanly between the structural signals (which are event-like and mostly counters) and the statistical signals (which are level-like gauges sampled per profiling window). Every series carries a table and a dataset dimension so a single alert rule generalizes across a cadastral layer, a sensor-status table, and an administrative-boundary reference set.

The structural fingerprint match is deliberately a single binary gauge per table: a schema either matches its contract or it does not, and any mismatch decomposes into one or more gis.spatial.schema_drift_total increments tagged with the specific change_type and column. For the statistical side, population stability index (PSI) is the workhorse for comparing a current category or bin histogram against the baseline. With baseline proportion $b_i$ and current proportion $c_i$ across $k$ bins:

$$\text{PSI} = \sum_{i=1}^{k} (c_i - b_i)\,\ln\!\left(\frac{c_i}{b_i}\right)$$

The conventional reading holds well for spatial reference attributes: $\text{PSI} < 0.1$ is stable, $0.1 \le \text{PSI} < 0.25$ is moderate drift worth a warning, and $\text{PSI} \ge 0.25$ is a significant re-coding that warrants investigation. Null-ratio drift is simpler and reported directly as a delta against baseline $\rho_0$: $\Delta_\rho = \rho_{\text{now}} - \rho_0$, alerting when the increase exceeds a per-column tolerance. Because these series share the table and dataset dimensions with freshness, a null-ratio spike can be correlated against an ingestion-lag spike to distinguish a genuine source regression from a partially-loaded partition, feeding the variance thresholds in Tracking Spatial Data Freshness SLAs.

Detection & Configuration

Structural drift is detected from the catalog, not the data, which is why it is cheap. In PostgreSQL/PostGIS the authoritative source is information_schema.columns joined against the geometry-column registry; hashing the ordered projection of that catalog gives the fingerprint, and diffing it against the pinned baseline names the exact change.

-- Structural fingerprint: ordered column contract for one spatial table
WITH live_schema AS (
  SELECT
    c.column_name,
    c.data_type,
    c.is_nullable,
    c.character_maximum_length,
    c.ordinal_position
  FROM information_schema.columns c
  WHERE c.table_schema = 'curated'
    AND c.table_name   = 'parcels'
)
SELECT
  md5(string_agg(
        column_name || ':' || data_type || ':' || is_nullable ||
        ':' || COALESCE(character_maximum_length::text, '-'),
        '|' ORDER BY ordinal_position)) AS fingerprint,
  count(*) AS column_count
FROM live_schema;

Diffing a live catalog against the stored baseline turns a fingerprint mismatch into an itemized change list — the input a gis.spatial.schema_drift_total counter needs:

-- Itemize structural drift vs a pinned baseline snapshot table
SELECT
  COALESCE(l.column_name, b.column_name) AS column_name,
  CASE
    WHEN b.column_name IS NULL THEN 'ADDED'
    WHEN l.column_name IS NULL THEN 'DROPPED'
    WHEN l.data_type   <> b.data_type   THEN 'TYPE_CHANGED'
    WHEN l.is_nullable <> b.is_nullable THEN 'NULLABILITY_CHANGED'
    ELSE 'UNCHANGED'
  END AS change_type,
  b.data_type AS baseline_type,
  l.data_type AS live_type
FROM baseline_schema.parcels_columns b
FULL OUTER JOIN information_schema.columns l
  ON l.table_name = 'parcels'
 AND l.column_name = b.column_name
WHERE l.data_type   IS DISTINCT FROM b.data_type
   OR l.is_nullable IS DISTINCT FROM b.is_nullable
   OR l.column_name IS NULL
   OR b.column_name IS NULL;

The statistical profile is a single-pass aggregate scan per column, cheap enough to run per batch on sampled partitions. The null ratio, distinct ratio, and coded-value histogram all fall out of one query:

-- Attribute profile for null-ratio and domain-code drift on a coded column
SELECT
  count(*)                                            AS n_rows,
  count(*) FILTER (WHERE land_use IS NULL)::numeric
    / NULLIF(count(*), 0)                             AS null_ratio,
  count(DISTINCT land_use)::numeric
    / NULLIF(count(*), 0)                             AS distinct_ratio,
  jsonb_object_agg(land_use, freq)
    FILTER (WHERE land_use IS NOT NULL)               AS code_histogram
FROM (
  SELECT land_use, count(*) AS freq
  FROM curated.parcels
  GROUP BY land_use
) h, curated.parcels;

A hardening step many teams miss: for a table whose values are supposed to be immutable between publications (a versioned reference layer), a whole-column checksum catches any silent edit that a histogram might average away. Hashing the sorted non-geometry attributes gives a single comparable value per snapshot:

-- Content checksum over non-geometry attributes for a versioned reference layer
SELECT md5(string_agg(row_hash, '' ORDER BY row_hash)) AS layer_content_hash
FROM (
  SELECT md5(parcel_id || '|' || COALESCE(land_use, '') || '|'
             || COALESCE(zone_code, '') || '|' || COALESCE(owner_type, '')) AS row_hash
  FROM curated.parcels
) r;

On the producer side, the profiling job records the metrics through the OpenTelemetry SDK at the promotion boundary, and the collector deployment is the same contrib build used across the measurement plane, wiring detailed in OpenTelemetry Integration for GIS Pipelines:

from opentelemetry import metrics

meter = metrics.get_meter("gis.spatial.schema_drift")
schema_drift = meter.create_counter("gis.spatial.schema_drift_total", unit="1")
null_ratio   = meter.create_gauge("gis.spatial.attribute_null_ratio", unit="ratio")
psi_gauge    = meter.create_gauge("gis.spatial.attribute_psi", unit="1")

def record_structural(table: str, changes: list[dict]) -> None:
    for ch in changes:                     # ch = {"column": ..., "change_type": ...}
        if ch["change_type"] != "UNCHANGED":
            schema_drift.add(1, {"table": table,
                                 "column": ch["column"],
                                 "change_type": ch["change_type"]})

def record_profile(table: str, column: str, ratio: float, psi: float) -> None:
    null_ratio.set(ratio, {"table": table, "column": column})
    psi_gauge.set(psi,    {"table": table, "column": column})

Threshold Design & Alerting

Thresholds separate the two lanes deliberately, because they carry different severities. Any structural break is immediately actionable — a dropped or type-changed column can crash a downstream cast — so it pages, whereas distribution drift is graded by magnitude. The dynamic baseline for null-ratio deltas compares against a per-column rolling average rather than a global constant, since a formerly-sparse optional column and a mandatory identifier have very different “normal” null levels.

These map onto tiered PromQL rules firing against the remote-write store. Note the ratios and dynamic baselines — never raw counts — so a large legitimate backfill does not self-page:

groups:
  - name: spatial-schema-attribute-drift
    rules:
      # Any structural fingerprint break is a hard stop
      - alert: SchemaContractBroken
        expr: gis_spatial_schema_fingerprint_match == 0
        for: 0m
        labels: { severity: critical }
        annotations:
          summary: "Schema contract broken on {{ $labels.table }}"

      # A dropped or retyped column can crash downstream casts
      - alert: DestructiveSchemaChange
        expr: |
          increase(gis_spatial_schema_drift_total{change_type=~"DROPPED|TYPE_CHANGED"}[10m]) > 0
        labels: { severity: critical }
        annotations:
          summary: "{{ $labels.change_type }} on {{ $labels.table }}.{{ $labels.column }}"

      # Null-ratio creep beyond 3x the 7-day per-column baseline
      - alert: AttributeNullCreep
        expr: |
          gis_spatial_attribute_null_ratio
            > 3 * avg_over_time(gis_spatial_attribute_null_ratio[7d]) + 0.02
        for: 30m
        labels: { severity: warning }
        annotations:
          summary: "Null ratio drift on {{ $labels.table }}.{{ $labels.column }}"

      # Significant category re-coding
      - alert: AttributeDistributionDrift
        expr: gis_spatial_attribute_psi >= 0.25
        for: 15m
        labels: { severity: warning }
        annotations:
          summary: "PSI {{ $value }} on {{ $labels.table }}.{{ $labels.column }} — re-coding likely"

Failure Modes & Edge Cases

1. A rename read as a drop-plus-add. When a source renames owner_type to ownertype, a naive column-name diff reports one DROPPED and one ADDED, and a blind pipeline may map the new column to nothing while silently retaining a now-empty old one. Detect the pattern by matching a dropped and added column with identical type and near-identical value distributions, and treat it as a rename requiring an explicit baseline update rather than two independent changes.

2. Type widening that passes structurally but breaks statistics. A smallint status code widened to integer is a benign structural change, but if the source simultaneously starts emitting codes above the old range, downstream lookup tables built for the narrow domain silently drop rows. Pair the type-change signal with a domain_code_drift check so a widening plus an out-of-range value is caught together, not separately.

3. Null creep hidden by averaging. A column that is 0% null in half its partitions and 40% null in the other half averages to a moderate 20%, masking that an entire source is contributing nothing. Profile per partition, not just per table, so a localized null collapse surfaces before it dilutes into an acceptable-looking global mean.

4. Category re-coding with a stable distinct count. If a source swaps its entire land-use vocabulary one-for-one (R1 → 1, C2 → 2), the distinct-value count is unchanged and only PSI against the labelled baseline histogram catches it. Never substitute a cardinality check for a value-level histogram comparison on coded columns.

5. Silent edits to a “frozen” reference layer. A versioned cadastral layer is assumed immutable between publications, so no one watches its values — until an out-of-band correction edits a handful of rows in place. Only the content checksum catches this; a row-count and freshness check both stay green. This is the class of defect examined in depth in Detecting Attribute Drift in Slowly Changing Layers.

Troubleshooting Checklist

1. Confirm the baseline is current. A false drift storm almost always means the pinned fingerprint or profile is stale after an approved migration. Verify the baseline snapshot’s timestamp against the last reviewed schema change before chasing a phantom regression.
2. Separate structural from statistical. Read gis.spatial.schema_fingerprint_match first. A 0 is a contract break to resolve before any distribution analysis is meaningful — a retyped column poisons every histogram computed after it.
3. Localize the drift to a partition. Query gis.spatial.attribute_null_ratio grouped by partition to distinguish a genuine source-wide regression from one bad ingest window; only the former warrants a source escalation.
4. Distinguish a rename from a drop. When a DROPPED and ADDED pair share a type and distribution, treat it as a rename and update the column mapping plus the baseline, rather than accepting silent data loss.
5. Cross-check against row-count sync. If attribute drift fires but the geometry and SRID are clean, reconcile against Automated Row Count & Attribute Sync to confirm whether rows were lost or merely re-valued — the two have different fixes.
6. Extend the lookup before un-blocking. For a domain_code_drift critical, add the new code to the downstream mapping table and re-run the batch; never widen the alert threshold to make an unmapped code pass silently.

By treating schema structure and attribute distribution as measured contracts rather than assumed constants, spatial platforms catch the class of defect that leaves geometry and datum untouched but quietly rewrites what every row means — giving data engineers and compliance teams an auditable answer to the question that a valid geometry can never answer on its own: is this attribute still the thing it was yesterday.

Related

• Spatial Data Freshness & Quality Metrics — the parent program this attribute-contract gate reports into.
• Detecting Attribute Drift in Slowly Changing Layers — baseline snapshots and PSI for slow distribution drift in reference layers.
• Automated Row Count & Attribute Sync — the row-survival complement to this value-level contract check.
• Coordinate Reference System Validation — the orthogonal datum gate; drift detection assumes geometry is already correctly located.
• Tracking Spatial Data Freshness SLAs — correlates a null-ratio spike against ingestion lag to separate a source regression from a partial load.