Data Quality Gates & Monitoring for Field Datasets

The failure that costs the most in a field pipeline is not a crash. It is a run that succeeds, produces a container that opens cleanly, and contains four per cent of the features it should — because a source directory was empty, a filter matched nothing, or a schema change dropped a column and nobody looked at the count. Every automated check in the world for structural correctness passes on that file. It is structurally perfect and substantively wrong.

This guide is part of the CLI Automation & Offline Sync Pipelines section. It covers the checks that catch substantive failures: what a quality gate should assert, how to detect drift between cycles, and why comparing against the previous run finds more real problems than any absolute threshold.

Prerequisites #

• A pipeline with a publication step — a gate is only useful if something can be stopped
• sqlite3 and ogrinfo on the path, for structural and layer-level assertions
• Somewhere durable to keep a manifest — a JSON file beside the artefact is enough
• At least one previous successful run to compare against; the first run establishes the baseline
• Familiarity with the conformance checks in How to Validate GeoPackage OGC Compliance

Concept & Specification Reference #

Quality checks divide into three kinds, and they answer genuinely different questions.

Structural checks are objective and cheap, and they catch corruption and non-conformance. Absolute checks encode domain rules — every parcel has a reference, no observation is dated in the future, every geometry falls inside the survey extent — and they catch data that is wrong on its own terms. Relative checks compare against the previous successful run, and they catch the largest class of real failures: everything that is individually plausible and collectively unlike anything that has happened before.

Most pipelines implement the first kind, some implement the second, and the third is where the missing coverage almost always is.

Step-by-Step Implementation #

1. Emit a manifest from every run #

A relative check needs something to compare against, and that something has to be produced by every run whether or not anyone looks at it. A manifest is a few dozen lines of JSON and is the foundation everything else here builds on.

#!/usr/bin/env bash
# Emit a manifest describing what this run produced
set -euo pipefail
DB=${1:?usage: manifest.sh <container>}

layers=$(sqlite3 "$DB" \
    "SELECT group_concat(table_name) FROM gpkg_contents WHERE data_type='features';")

{
  printf '{\n'
  printf '  "container": "%s",\n' "$(basename "$DB")"
  printf '  "bytes": %s,\n' "$(stat -c%%s "$DB")"
  printf '  "layers": {\n'
  first=1
  IFS=',' read -ra names <<< "$layers"
  for name in "${names[@]}"; do
      n=$(sqlite3 "$DB" "SELECT count(*) FROM \"$name\";")
      [ $first -eq 0 ] && printf ',\n'
      printf '    "%s": %s' "$name" "$n"
      first=0
  done
  printf '\n  }\n}\n'
} > "${DB%.gpkg}.manifest.json"

Keep the manifest beside the artefact rather than in a log. Logs rotate; the manifest needs to survive as long as the container it describes, because the next run will read it.

2. Assert the absolute rules #

Absolute rules are domain statements, and the best of them are the ones a surveyor would recognise. Express them as queries that return the offending rows, not as booleans — a gate that says “failed” is much less useful than one that says which twelve rows failed.

-- Domain assertions: each returns the rows that violate the rule
-- Every parcel must carry a reference
SELECT fid, 'missing parcel_ref' AS problem
FROM parcels WHERE parcel_ref IS NULL OR trim(parcel_ref) = '';

-- No observation may be dated in the future
SELECT fid, 'future date: ' || surveyed_on AS problem
FROM observations WHERE surveyed_on > date('now');

-- Every geometry must fall inside the declared survey extent
SELECT fid, 'outside survey extent' AS problem
FROM parcels
WHERE ST_MinX(geom) < -8.2 OR ST_MaxX(geom) > 1.8
   OR ST_MinY(geom) < 49.9 OR ST_MaxY(geom) > 60.9;

-- No duplicate references
SELECT parcel_ref, 'duplicated ' || count(*) || ' times' AS problem
FROM parcels GROUP BY parcel_ref HAVING count(*) > 1;

The extent check is worth singling out. It is the cheapest possible detector for a whole family of projection and datum errors, because a coordinate that has been mangled almost always lands outside the area the survey covers — often by an enormous margin, since a projected coordinate read as degrees ends up far off the map.

3. Compare against the previous run #

This is the step that catches what the other two miss. The comparison does not need to be clever; a ratio against the last manifest finds nearly everything worth finding.

# Compare this run's manifest against the last successful one
import json
import sys
from pathlib import Path

TOLERANCE = 0.10   # a layer may move by 10% between runs without comment


def compare(current: Path, previous: Path) -> list[str]:
    now = json.loads(current.read_text())
    was = json.loads(previous.read_text())
    problems = []

    missing = set(was["layers"]) - set(now["layers"])
    if missing:
        problems.append(f"layers disappeared: {sorted(missing)}")

    added = set(now["layers"]) - set(was["layers"])
    if added:
        problems.append(f"layers appeared: {sorted(added)}")

    for name, n_was in was["layers"].items():
        n_now = now["layers"].get(name)
        if n_now is None or n_was == 0:
            continue
        drift = (n_now - n_was) / n_was
        if abs(drift) > TOLERANCE:
            problems.append(
                f"{name}: {n_was} -> {n_now} ({drift:+.1%})"
            )

    size_drift = (now["bytes"] - was["bytes"]) / max(was["bytes"], 1)
    if abs(size_drift) > 0.5:
        problems.append(f"container size moved {size_drift:+.1%}")

    return problems


if __name__ == "__main__":
    issues = compare(Path(sys.argv[1]), Path(sys.argv[2]))
    for line in issues:
        print("DRIFT:", line)
    sys.exit(1 if issues else 0)

A ten per cent tolerance is a starting point, not a law. A layer that grows steadily wants a tighter band; one that is genuinely bursty wants a looser one, or a comparison against a rolling median rather than the single previous run.

4. Detect schema drift #

A column that changes type, disappears, or appears unannounced is a break in the contract with every consumer. Comparing the schema between runs is cheap and catches it before the consumer does.

# Record and compare the column signature of every layer
import sqlite3


def schema_signature(path: str) -> dict[str, list[tuple[str, str]]]:
    conn = sqlite3.connect(f"file:{path}?mode=ro", uri=True)
    try:
        layers = [r[0] for r in conn.execute(
            "SELECT table_name FROM gpkg_contents WHERE data_type='features'"
        )]
        return {
            layer: [
                (row[1], row[2])            # (column name, declared type)
                for row in conn.execute(f'PRAGMA table_info("{layer}")')
            ]
            for layer in sorted(layers)
        }
    finally:
        conn.close()


def schema_diff(now: dict, was: dict) -> list[str]:
    out = []
    for layer, cols_was in was.items():
        cols_now = now.get(layer)
        if cols_now is None:
            out.append(f"{layer}: layer removed")
            continue
        set_was, set_now = set(cols_was), set(cols_now)
        for col in sorted(set_was - set_now):
            out.append(f"{layer}: column removed or retyped: {col}")
        for col in sorted(set_now - set_was):
            out.append(f"{layer}: column added or retyped: {col}")
    return out

Comparing name and declared type together means a column that silently changed from INTEGER to REAL — the dtype-drift failure described in How to Append a GeoDataFrame to a GeoPackage Layer — shows up as both a removal and an addition, which is exactly the signal you want.

5. Check that the indexes still agree with the data #

A stale spatial index is invisible to every other check here: the container is conforming, the counts are right, the schema is unchanged, and spatial queries return too few rows. Comparing entry counts is a single query per layer.

-- One row per indexed layer; any mismatch is a stale index
SELECT 'parcels' AS layer,
       (SELECT count(*) FROM rtree_parcels_geom) AS index_rows,
       (SELECT count(*) FROM parcels WHERE geom IS NOT NULL) AS geom_rows;

Where counts match but queries still misbehave, the extents are stale rather than the entries — the case described in How to Automate R-tree Index Rebuilds After Bulk Load, which a count comparison cannot detect and a recovery rebuild fixes unconditionally.

6. Wire the gate into the publication step #

A check that runs after publication is a report. A check that runs before it is a gate, and only the second one prevents anything.

#!/usr/bin/env bash
# Publish only if every band passes
set -euo pipefail
TMP=$1 DEST=$2 PREV_MANIFEST=$3

sqlite3 "$TMP" "PRAGMA integrity_check;" | grep -q '^ok$'   # structural
python3 assert_domain_rules.py "$TMP"                        # absolute
./manifest.sh "$TMP"
python3 compare_manifests.py "${TMP%.gpkg}.manifest.json" "$PREV_MANIFEST"  # relative

sqlite3 "$TMP" "PRAGMA wal_checkpoint(TRUNCATE); PRAGMA journal_mode=DELETE;"
mv -f "$TMP" "$DEST"
cp -f "${TMP%.gpkg}.manifest.json" "${DEST%.gpkg}.manifest.json"

With set -e, any failing band aborts before the mv, and the previously published artefact stays in place. That is the behaviour that matters: a failed run should leave consumers with yesterday’s good data rather than today’s bad data.

Validation & Verification #

The gate itself needs testing, and the test is to feed it something it must refuse. This is the same discipline the rest of this site applies to assertions — a check that has never rejected anything has not been shown to check anything.

# Prove each band actually rejects
cp good.gpkg broken.gpkg

# structural: corrupt a registry table
sqlite3 broken.gpkg "DROP TABLE gpkg_geometry_columns;"
./publish.sh broken.gpkg /srv/out.gpkg prev.json && echo "GATE FAILED TO STOP THIS"

# absolute: introduce a future date
cp good.gpkg broken.gpkg
sqlite3 broken.gpkg "UPDATE observations SET surveyed_on='2099-01-01' WHERE fid=1;"
./publish.sh broken.gpkg /srv/out.gpkg prev.json && echo "GATE FAILED TO STOP THIS"

# relative: delete most of a layer
cp good.gpkg broken.gpkg
sqlite3 broken.gpkg "DELETE FROM parcels WHERE fid > 100;"
./publish.sh broken.gpkg /srv/out.gpkg prev.json && echo "GATE FAILED TO STOP THIS"

Run this once when the gate is written, and again whenever a band is changed. It takes a couple of minutes and is the only evidence that any of it works.

Common Failure Modes & Fixes #

The gate fires constantly and gets disabled #

Diagnosis: Tolerances set from a single run rather than from observed variability, so ordinary fluctuation trips them. Fix: Collect manifests for a couple of weeks before enforcing, set the band from what actually happened, and separate warnings from failures. A gate that fires weekly and is right once a quarter will be switched off before that quarter ends.

A legitimate large change is blocked #

Diagnosis: A genuine bulk import or a deliberate reprocessing moved counts far outside the band. Fix: Provide an explicit override that records why — a flag that writes the reason into the manifest. An override with a reason is auditable; commenting out the check is not, and is what happens if no override exists.

Drift is detected but nobody can say what changed #

Diagnosis: The manifest records too little — a total row count and nothing else. Fix: Record per layer rather than per container, and include the extent and schema signature. The extra data costs nothing and turns “something moved” into “the parcels layer lost its western half”, which is a diagnosis rather than an alert.

The first run of a new pipeline cannot pass #

Diagnosis: There is no previous manifest to compare against, and the relative band treats absence as failure. Fix: Treat a missing baseline as a pass with a recorded note. The first run establishes the baseline by definition; only the second run onward can be compared.

Schema drift is reported for a column that did not change #

Diagnosis: The declared type string differs while the effective type does not — VARCHAR(32) against TEXT, or INT against INTEGER. SQLite’s type affinity treats these as equivalent and PRAGMA table_info does not. Fix: Normalise the declared type to its affinity before comparing, so the signature reflects behaviour rather than spelling.

Performance Notes #

Nearly everything here is cheap. Counts, schema signatures and registry queries are index reads or table-info lookups, and a manifest for a container with a dozen layers takes well under a second.

Two things are not cheap, and both are worth scoping deliberately. PRAGMA integrity_check reads every page of the container, so on a large file it is a full disk read — appropriate before publication, wasteful in a per-cycle sync check, and PRAGMA quick_check covers the common corruption cases at a fraction of the cost. Geometry-level assertions such as validity are also full scans with a topology test per row; where a change-log version exists, run them only over rows above the last checked watermark, using the incremental pattern from Tracking Row Changes for Incremental GeoPackage Sync.

The extent check deserves a note of its own: it is the cheapest high-value assertion available, because ST_MinX and its siblings read the geometry header rather than decoding coordinates. It catches a large family of projection errors for close to nothing.

Child Pages #

Pages in this section go deeper on individual monitoring tasks:

• How to Write a Data Quality Gate for a Field Dataset — the gate script end to end
• Detecting Schema Drift Between Sync Cycles — signatures, affinity, and what counts as a change
• Monitoring GeoPackage File Growth Over Time — free pages, sidecars, and when to vacuum
• Alerting on Stale Spatial Indexes — the failure no other check notices
• How to Diff Two GeoPackage Files — comparing containers when the manifest is not enough
• Building a Nightly Integrity Report — turning the checks into something a person reads

Frequently Asked Questions #

Related #

• CLI Automation & Offline Sync Pipelines — parent section: scripting, scheduling and the sync loop
• Offline-First Sync Strategies — the cycle these gates sit inside
• How to Validate GeoPackage OGC Compliance — the structural band in detail
• Testing & CI for Spatial Pipelines — the same assertions, applied to code rather than to data