This guide is part of Monitoring & Observability for Spatial Pipelines, within the broader Orchestrating Spatial ETL Pipelines reference.

Alerting on Spatial Data Freshness SLAs

Freshness is the one alert that catches every way a spatial pipeline can quietly stop producing useful output — provided it is measured on the data rather than on the job, and from somewhere the job cannot take down with it.

Why the Job-Level Alert Is Not Enough

  • A dead scheduler emits nothing. No failed task means no alert from anything that watches tasks.
  • A stalled source produces successful runs. Conditional requests return 304, the load writes zero rows, everything is green.
  • A partially failed backfill leaves gaps. The most recent partition is fresh while three older ones never materialised.
  • A consumer notices before you do. Which is the actual failure being prevented — not the staleness, but learning about it from someone else.

Version and Environment Compatibility

Component Version Note
SQLAlchemy >=2.0 Typed connection API used below
pyarrow >=14 Reading partition metadata for object-store datasets
Python 3.10+ datetime.UTC-style timezone handling 
pip install "sqlalchemy>=2.0" "pyarrow>=14"

Two Clocks, Not One

Reading the two freshness clocks together A two by two matrix of source age against output age. Both fresh means healthy. A fresh source with a stale output means the pipeline stopped and is the responsibility of the pipeline owner. A stale source with an output that matches it means the publisher stopped and needs an external conversation. Both stale but diverging indicates a partial backfill. what the pair of clocks tells you output fresh output stale source fresh healthy nothing to do the pipeline stopped page the pipeline owner source stale the publisher stopped ticket, and ask them both, or a partial backfill check partition coverage first

The freshness_report Recipe

from __future__ import annotations

import logging
from dataclasses import dataclass
from datetime import datetime, timedelta, timezone

import sqlalchemy

logger = logging.getLogger(__name__)


@dataclass(frozen=True)
class Freshness:
    dataset: str
    output_age: timedelta
    source_age: timedelta | None
    threshold: timedelta
    partitions_missing: int

    @property
    def breached(self) -> bool:
        return self.output_age > self.threshold or self.partitions_missing > 0

    @property
    def diagnosis(self) -> str:
        if self.partitions_missing:
            return "partial coverage — some partitions never materialised"
        if not self.breached:
            return "healthy"
        if self.source_age is not None and self.source_age > self.threshold:
            return "source has not published — escalate to the publisher"
        return "pipeline has stalled — the source is current"


def freshness_report(
    engine: sqlalchemy.Engine,
    dataset: str,
    table: str,
    cadence: timedelta,
    processing_budget: timedelta,
    timestamp_column: str = "updated_at",
    source_column: str = "source_published_at",
    expected_partitions: int | None = None,
) -> Freshness:
    """Measure output and source age, and count partitions that never arrived."""
    now = datetime.now(timezone.utc)
    threshold = cadence + processing_budget + cadence  # allow exactly one missed cycle

    with engine.connect() as connection:
        newest_output = connection.execute(
            sqlalchemy.text(f"SELECT max({timestamp_column}) FROM {table}")
        ).scalar_one_or_none()
        newest_source = connection.execute(
            sqlalchemy.text(f"SELECT max({source_column}) FROM {table}")
        ).scalar_one_or_none()
        partitions_present = connection.execute(
            sqlalchemy.text(f"SELECT count(DISTINCT partition_key) FROM {table}")
        ).scalar_one()

    if newest_output is None:
        raise ValueError(f"{dataset}: serving table is empty — treat as a breach, not a gap")

    def _age(value) -> timedelta:
        if value.tzinfo is None:
            value = value.replace(tzinfo=timezone.utc)
        return now - value

    report = Freshness(
        dataset=dataset,
        output_age=_age(newest_output),
        source_age=_age(newest_source) if newest_source else None,
        threshold=threshold,
        partitions_missing=max(0, (expected_partitions or partitions_present) - partitions_present),
    )
    logger.info("%s freshness: output %s, source %s, %s",
                dataset, report.output_age, report.source_age, report.diagnosis)
    return report

Key Implementation Notes

  • The threshold allows exactly one missed cycle. cadence + processing + cadence means a single late run is not an incident and two consecutive ones are, which matches how people actually want to be interrupted.
  • Source and output age come from the same table. Carrying source_published_at through to the serving rows costs one column and turns “whose fault is it” into a computed field rather than a conversation.
  • An empty table is a breach, not an error case. A dataset with no rows has infinite staleness; treating it as a missing value is how a truncation goes unnoticed.
  • Partition coverage is checked alongside age. The newest partition being fresh says nothing about the three that failed, which is the failure mode a single max timestamp cannot see.
  • The diagnosis is computed, not written by the alert author. The responder gets “source has not published” rather than “freshness breach”, which is most of the triage already done.
  • Naive timestamps are coerced to UTC. Mixed-awareness datetimes are endemic in spatial databases and produce comparisons that fail at 1 a.m. on a clock-change weekend.
How the threshold is composed A timeline showing the three components of a freshness threshold for a daily source. Twenty-four hours of publication cadence, two hours of processing budget, and a further twenty-four hours allowing one missed cycle, giving fifty hours in total. A single late run stays below the line; two consecutive misses cross it. daily source · 2 h processing · one missed cycle tolerated cadence · 24 h 2 h one missed cycle · 24 h alert at 50 h one late run peaks around 26 h — silent, correctly two consecutive misses reach 50 h — alert, correctly Thresholds built this way need no tuning: they follow from the cadence, and they change automatically when the cadence does.

Sources That Publish Irregularly

Not every source has a cadence. Satellite scenes arrive when the orbit and the cloud allow; a municipal portal updates when someone remembers. For these, a threshold derived from a nominal schedule fires constantly.

Deriving a threshold from observed arrival gaps A histogram of gaps between successive publications for an irregular source. Most gaps are two to six days, a smaller group runs to twelve, and a thin tail reaches twenty. The ninety-fifth percentile sits at fourteen days, which is where the freshness threshold belongs — above ordinary variation and below a genuine outage. gaps between publications · 8 months observed p95 = 14 days → the threshold 2 d 10 d 20 d A nominal cadence from the documentation would have put the threshold at 7 days and fired most weeks.

The workable approach is empirical. Record the arrival time of each new source version for a few months, take the distribution of inter-arrival gaps, and set the threshold above a high percentile — the 95th is a reasonable starting point. The alert then means “this gap is longer than 95% of the gaps we have seen”, which is a statement someone can act on.

Two refinements help. Seasonality matters for optical imagery: a threshold derived from summer gaps will fire every winter in cloudy latitudes, so bucket the distribution by month. And a hard ceiling regardless of the distribution catches a source that has genuinely been retired, which no percentile will flag because the retirement looks like an unusually long gap until it does not end.

Where the Check Should Run

The freshness check must not live inside the pipeline it watches. A scheduler that stopped running produces no tasks, so a check implemented as a pipeline task goes silent at exactly the moment it should fire.

Three placements work. A separate scheduled job on independent infrastructure is the simplest and covers a dead scheduler. A monitoring-system query against the metric emitted by the pipeline works if the metric store alerts on absence as well as on value — many do not by default, and “no data” must be configured as a breach rather than as unknown. A consumer-side check in the system that reads the data is the most honest of all, because it measures what the consumer actually experiences.

Whichever is chosen, the check needs read access to the serving table and nothing else, which makes it cheap to run somewhere the pipeline cannot break.

Troubleshooting Freshness Alerts

Symptom Likely cause Fix
Alert fires every weekend Threshold derived from weekday cadence Bucket the cadence by day of week
No alert during a full outage Check runs inside the stopped pipeline Move it to independent infrastructure
Alert fires while data is current Timestamp column records ingestion, not observation Alert on the observation timestamp
Freshness fine, data incomplete Only the max timestamp is checked Add the partition-coverage count
Constant alerts on an irregular source Nominal cadence rather than observed gaps Derive the threshold from the arrival distribution

Integration Note

Emit the freshness numbers from the same emitter used in emitting metrics for spatial record counts, so the alerting rule reads one series rather than querying the database directly on every evaluation. Keep the SLA itself — cadence, processing budget, owner — in the same configuration as the pipeline, because a freshness promise that lives only in a monitoring tool drifts from the pipeline that has to keep it.