SpatiaLite & GeoPackage Automation with Python

Building a Nightly Integrity Report

Read the last fourteen manifests, compare the newest against the trend rather than against yesterday alone, and emit a plain-text summary with the…

Read the last fourteen manifests, compare the newest against the trend rather than against yesterday alone, and emit a plain-text summary with the exceptions first. Everything the report needs already exists if the pipeline emits a manifest per run — the work is presentation, and the presentation is what decides whether anyone reads it.

This page belongs to the Data Quality Gates & Monitoring guide.

Why This Matters

A gate stops bad data. It says nothing about slow degradation: a layer whose growth rate has doubled, an index that has been stale for three nights running, a free-page fraction climbing week over week. None of those trips a threshold on any single night, and all of them matter.

The report is also where the gate’s own behaviour becomes visible. A gate that has fired four times this fortnight is telling you something about the upstream data that no individual failure message conveys, and a gate that has never fired is telling you something about the thresholds.

Prerequisites

  • A manifest per run, per How to Write a Data Quality Gate for a Field Dataset
  • Somewhere those manifests accumulate — a directory is entirely sufficient
  • Python 3.9+; no monitoring stack, database or agent is required
  • A delivery route that works without a network, if the pipeline runs somewhere isolated

Primary Method

python
# report.py — a readable summary from the manifest history
import json
import statistics
from pathlib import Path

HISTORY = Path("/srv/manifests")
WINDOW = 14


def load_history(limit: int = WINDOW) -> list[dict]:
    files = sorted(HISTORY.glob("*.manifest.json"))[-limit:]
    return [json.loads(f.read_text()) for f in files]


def layer_trend(history: list[dict], layer: str) -> dict | None:
    counts = [
        m["layers"][layer]["rows"] for m in history if layer in m.get("layers", {})
    ]
    if len(counts) < 3:
        return None
    prior, latest = counts[:-1], counts[-1]
    median = statistics.median(prior)
    return {
        "latest": latest,
        "median": median,
        "deviation": (latest - median) / median if median else 0.0,
        "run_to_run": statistics.median(
            abs(b - a) / a for a, b in zip(prior, prior[1:]) if a
        ) if len(prior) > 2 else 0.0,
    }

Comparing against a median of the window rather than against yesterday is what makes the report useful for trends. A single previous run is the right comparison for a gate, where the question is “is this publishable”; a window is the right comparison for a report, where the question is “is anything drifting”.

Comparing against yesterday against comparing against a windowComparing a run against the single previous one answers whether it is publishable, which is the gate's question, and is blind to a slow trend because each night's change is small. Comparing against the median of a two-week window answers whether anything is drifting, which is the report's question, and surfaces a gradual change that no single night would trip. The two comparisons serve different purposes and both are worth having.against yesterdaythe gate's questionis this publishable?blind to a slow trendeach night's change is smallagainst a two-week medianthe report's questionis anything drifting?surfaces gradual changeno single night would trip itthe same manifests answer both questions; only the comparison differs
Neither replaces the other, and both come free once the manifests exist.

Step-by-Step Walkthrough

1. Put the exceptions first

The single most consequential decision about a report is its ordering. Anything requiring attention goes at the top; the routine confirmation goes underneath, where it can be skimmed or ignored.

python
def render(history: list[dict]) -> str:
    latest = history[-1]
    exceptions, routine = [], []

    for layer in sorted(latest.get("layers", {})):
        t = layer_trend(history, layer)
        if t is None:
            routine.append(f"  {layer}: {latest['layers'][layer]['rows']:,} rows (new)")
            continue

        # Flag when the deviation is large relative to normal run-to-run movement
        normal = max(t["run_to_run"], 0.02)
        if abs(t["deviation"]) > normal * 4:
            exceptions.append(
                f"  {layer}: {t['latest']:,} rows, {t['deviation']:+.1%} against a "
                f"median of {t['median']:,.0f} (normal movement ±{normal:.1%})"
            )
        else:
            routine.append(f"  {layer}: {t['latest']:,} rows ({t['deviation']:+.1%})")

    out = [f"Integrity report — {latest['container']}", ""]
    if exceptions:
        out += ["NEEDS ATTENTION", *exceptions, ""]
    out += ["Steady", *routine]
    return "\n".join(out)

Scaling the threshold to each layer’s own normal movement is what stops a bursty layer producing an exception every night while a steady one has to move enormously before anything is said.

2. Include the things a gate cannot express

The gate is a pass or fail on one run. The report can carry state that only makes sense over time.

python
def durable_conditions(history: list[dict]) -> list[str]:
    latest, notes = history[-1], []

    # An index stale for several consecutive nights is a process problem
    stale_runs = 0
    for m in reversed(history):
        states = {v.get("state") for v in m.get("index_health", {}).values()}
        if states & {"count mismatch", "stale extents"}:
            stale_runs += 1
        else:
            break
    if stale_runs >= 2:
        notes.append(f"spatial index stale for {stale_runs} consecutive runs")

    # A free-page fraction climbing week over week
    frac = [
        m["size"]["free_bytes"] / max(m["size"]["bytes_on_disk"], 1)
        for m in history if "size" in m
    ]
    if len(frac) >= 7 and frac[-1] > frac[-7] * 1.5 and frac[-1] > 0.15:
        notes.append(f"free-page fraction rising: {frac[-7]:.0%} -> {frac[-1]:.0%}")

    # How often the gate has intervened
    overrides = sum(1 for m in history if m.get("override_reason"))
    if overrides:
        notes.append(f"{overrides} gate override(s) in the last {len(history)} runs")

    return notes

The override count is the one people find most useful in practice. A gate overridden three times in a fortnight is either mistuned or is repeatedly being told to ignore something real, and neither is visible from any single run.

Conditions only a report can seeAn index stale on a single night is a gate failure. An index stale on three consecutive nights is a process problem — something is not rebuilding, and nobody has noticed. A free-page fraction that is high once may be a bulk delete; one rising week over week is a maintenance gap. A gate override once is a decision; three in a fortnight is a mistuned threshold or a real problem being waved through.index stale for several consecutive runsone night is a gate failure · three nights is a process that is not rebuildingfree-page fraction rising week over weeka single high reading is a bulk delete · a rising trend is a maintenance gaprepeated gate overridesonce is a decision · three in a fortnight is a threshold or a problem being waved through
None of the three is visible from a single run, and all three are cheap to compute from manifests that already exist.

3. Emit plain text

python
if __name__ == "__main__":
    history = load_history()
    if not history:
        raise SystemExit("no manifests found")

    body = render(history)
    notes = durable_conditions(history)
    if notes:
        body += "\n\nTrends\n" + "\n".join(f"  {n}" for n in notes)

    print(body)
    Path("/srv/reports/latest.txt").write_text(body)

Plain text reads in a terminal, in an email, in a chat message and in a log, and it survives being copied into a ticket. Anything richer buys formatting and costs reach — and reach is what decides whether the report is read.

4. Say when nothing happened

python
if not exceptions and not notes:
    out.append("\nNothing requiring attention.")

A report that is silent on a quiet night is indistinguishable from a report that failed to run. One that says “nothing requiring attention” confirms both that the pipeline ran and that it found nothing — which is genuinely useful information and costs one line.

5. Deliver it where it will be seen

bash
# Whatever the environment supports, in order of preference
python3 report.py > /srv/reports/$(date -I).txt

command -v mail >/dev/null && \
    mail -s "Integrity report $(date -I)" ops@example.org < /srv/reports/$(date -I).txt

# Isolated environments: leave it beside the artefact, where an operator will find it
cp /srv/reports/$(date -I).txt /srv/artifacts/latest.report.txt

For a pipeline that runs somewhere without a network — which is common in this domain — writing the report next to the artefact is the delivery mechanism. The operator collecting the container gets the report with it.

Verification

The report must be shown to surface a condition it should.

python
def test_report_flags_a_count_collapse(tmp_path):
    """Fourteen steady runs then a collapse must appear under NEEDS ATTENTION."""
    history = [
        {"container": "field.gpkg", "layers": {"parcels": {"rows": 12_000 + i * 30}}}
        for i in range(13)
    ]
    history.append({"container": "field.gpkg", "layers": {"parcels": {"rows": 600}}})

    body = render(history)
    assert "NEEDS ATTENTION" in body
    assert "parcels" in body.split("NEEDS ATTENTION")[1].split("Steady")[0]


def test_report_is_quiet_on_a_steady_history():
    history = [
        {"container": "field.gpkg", "layers": {"parcels": {"rows": 12_000 + i * 30}}}
        for i in range(14)
    ]
    body = render(history)
    assert "NEEDS ATTENTION" not in body

Both directions matter. A report that flags everything is ignored within a week, and one that flags nothing was never doing anything — the second test is what catches the second failure.

The shape of a report people readExceptions come first, so the reader knows within a second whether anything needs them. Trends come second, carrying the conditions that span several runs. The routine per-layer figures come last, where they can be skimmed or ignored. When nothing at all needs attention the report says so explicitly, which distinguishes a quiet night from a report that never ran.1 · needs attentionthe reader knows in one second whether to keep reading2 · trendsconditions spanning several runs, which no gate can see3 · steadyper-layer figures · skimmable, and there when needed4 · the all-clear linea quiet night must not look like a report that never ran
The ordering is the design. A report whose first line is a table of unchanged figures gets skipped.

Alternative Approaches or Edge Cases

Feeding a monitoring system. Where one exists, emitting the manifest figures as metrics gives dashboards and alerting for free. It does not replace the report: a dashboard answers “what is the value of this number” and a report answers “what should I do this morning”, and in a field-data context the second question is usually the one being asked.

Per-deployment reports. A fleet producing one container per region wants one report per region, plus a roll-up naming the regions with exceptions. The per-region reports are the same code with a different manifest directory; the roll-up is a few more lines and is what somebody actually opens.

Retaining manifests. Fourteen runs is enough for the comparisons here and small enough to keep indefinitely — a manifest is a few kilobytes. Keeping a year costs almost nothing and makes a question like “when did this layer start growing” answerable, which is worth more than the storage.

Troubleshooting

The report flags something every night

Cause: The threshold is not scaled to each layer’s normal movement, so a bursty layer trips it constantly. Fix: Use the run-to-run median as the baseline, as in step 1, rather than a fixed percentage across all layers.

The report is empty

Cause: Fewer than three manifests, so layer_trend returns nothing for every layer. Fix: Fall back to printing the raw figures with a note that no trend is available yet. An empty report is indistinguishable from a broken one.

Nobody reads it

Cause: Almost always the ordering — routine figures first, exceptions buried. Fix: Exceptions at the top, and an explicit “nothing requiring attention” when there are none. A report whose first line is unchanged numbers teaches the reader that the first line is not worth reading.

Frequently Asked Questions

Is a report worth building if a gate already exists?

Yes, because they answer different questions. The gate is binary and per-run: it stops something. The report is comparative and cumulative: it tells you what is drifting, how often the gate has intervened, and whether a condition has persisted. Most of the incidents worth preventing in a field pipeline are visible in the second long before they trip the first.

How long should the history window be?

Long enough to establish what normal looks like and short enough to reflect current behaviour — two weeks is a good default for a nightly pipeline. For a weekly one, use a quarter. The number that matters is how many runs, not how many days, because the comparison is against the distribution of runs.

Should the report include the gate's failures?

Yes, and the overrides especially. A run that failed the gate produced no artefact and no manifest, so the report should note the absence rather than silently skipping it — a gap in the history is itself information. Recording gate outcomes in a small separate log, keyed by date, is enough to make that visible.