Triaging a Freshness SLA Breach

The page has fired: a spatial layer has blown past its freshness window and someone is waiting on an answer. Triage is the narrow window between the alert and the fix, and it has exactly two jobs — localize which pipeline stage stalled the clock, then decide whether to remediate now or wait for the next scheduled run. Getting either wrong is expensive: chase the extraction schedule when a projection check is the real bottleneck and you burn the remediation cycle; force a manual reprocess when the source was merely early-late and you double the load for nothing. This guide is the focused triage procedure for a spatial freshness Service Level Agreement (SLA) breach — reading the spatial_freshness_* series to attribute lag to a stage, then making the remediate-versus-wait call with evidence. It sits under Tracking Spatial Data Freshness SLAs and within the broader Spatial Data Freshness & Quality Metrics program, for the data engineers, GIS platform administrators, SREs, and on-call responders who carry the MTTR.

Read the breach before you touch anything

The first minute is diagnosis, not action. A freshness alert reports that spatial_freshness_lag_seconds crossed the SLA window, but the raw number says nothing about why, and the most common triage error is treating every breach as a source-delay problem. Before opening a terminal on the extraction host, pull the companion series the freshness layer already emits: crs_validation_latency, temporal_partition_drift, and row_count_delta. Together they turn one lag number into a stage attribution. A breach with high crs_validation_latency is a projection stall wearing a freshness costume; a breach with a sharply negative row_count_delta is a truncated partition that ingested on time and is lying about being complete.

Establish three facts before deciding anything. Is the lag real or is it validation latency absorbed into the freshness budget? Is the breach whole-feed or a single partition hiding under a healthy average? And is this a first occurrence or a feed drifting past its own seasonal baseline? Each fact points at a different remediation, and answering them from the metrics is faster and safer than guessing from the pipeline logs. This same separation of event time from validation latency is what the deep dive in How to automate geometry validity checks in GDAL enforces upstream, so a stalled validity pass is measured explicitly rather than silently inflating lag.

Localize the stalled stage

Freshness lag is the sum of per-stage dwell times across extraction, transformation, and publication, and attribution means finding which stage owns the excess. Query the stage timestamps the sidecars capture at each emission boundary and difference them so the largest gap names the culprit:

-- Attribute total lag across the three emission boundaries for the breaching partition.
WITH stages AS (
  SELECT
    partition_key,
    srid,
    extract(epoch FROM (transform_start_ts - source_event_ts))  AS extract_dwell_s,
    extract(epoch FROM (publish_start_ts   - transform_start_ts)) AS transform_dwell_s,
    extract(epoch FROM (catalog_ready_ts   - publish_start_ts))   AS publish_dwell_s,
    extract(epoch FROM (catalog_ready_ts   - source_event_ts))    AS total_lag_s
  FROM freshness_stage_ledger
  WHERE dataset_id = 'flood_extent'
    AND catalog_ready_ts >= now() - interval '2 hours'
)
SELECT partition_key, srid,
       extract_dwell_s, transform_dwell_s, publish_dwell_s, total_lag_s,
       CASE greatest(extract_dwell_s, transform_dwell_s, publish_dwell_s)
         WHEN extract_dwell_s   THEN 'extraction'
         WHEN transform_dwell_s THEN 'transformation'
         ELSE 'publication'
       END AS dominant_stage
FROM stages
ORDER BY total_lag_s DESC
LIMIT 20;

The dominant_stage column is the triage verdict. If it reads transformation, cross-check crs_validation_latency for the same partition — a slow or failing projection check is the usual cause, and the fix belongs in Coordinate Reference System Validation, not the extraction schedule. If it reads publication, the geometry likely stalled a validity or topology pass, which points at Geometry Validity & Topology Checks. If it reads extraction, the source genuinely arrived late and no amount of pipeline tuning will help. Confirm the breach is not an aggregation artifact by dimensioning on partition_key, since a jurisdiction-wide average stays green while one boundary sits hours behind.

-- Is the breach whole-feed or one hiding partition? Never trust the mean.
SELECT partition_key,
       spatial_freshness_lag_seconds AS lag_s,
       row_count_delta,
       ST_SRID(sample_geom)          AS srid
FROM freshness_current
WHERE dataset_id = 'flood_extent'
ORDER BY lag_s DESC
LIMIT 10;

A current timestamp paired with a sharply negative row_count_delta is the truncated-partition signature: the data is on time and incomplete, which is a completeness breach masquerading as freshness and should route to Automated Row Count & Attribute Sync rather than a reprocess.

Decide: remediate now or wait

Attribution tells you what stalled; the remediate-versus-wait decision weighs the cost of acting against the cost of waiting. The governing quantity is the remaining SLA budget against the next scheduled run. If the next run lands inside the budget and will carry corrected data, waiting is cheaper and lower-risk than a manual reprocess that competes for the same compute. If it does not, or if the breach is a completeness fault that the next run will simply repeat, remediate now.

$$\text{wait if} \quad t_{\text{next run}} + \overline{d_{\text{pipeline}}} \; < \; t_{\text{event}} + T_{\text{sla}}$$

where $t_{\text{next run}}$ is the next scheduled trigger, $\overline{d_{\text{pipeline}}}$ the typical end-to-end dwell, $t_{\text{event}}$ the breaching partition’s event time, and $T_{\text{sla}}$ the contractual window. When the inequality holds and the dominant stage is extraction, wait — the source was late, the next pull will catch up, and forcing a reprocess of data you do not yet have accomplishes nothing. When it fails, or the dominant stage is transformation or publication with a stall that will recur, remediate: the defect is inside your pipeline and the next run inherits it.

A quick decision table for the common cases:

While remediating a transformation or publication stall, keep serving rather than publishing a stale layer as current — degrade through the tiers in Fallback Chains for Spatial API Failures so consumers get a bounded-approximation instead of a hole or a lie.

When triage escalates to an incident

Triage is bounded: it localizes and decides. The moment the fix touches more than a single rerun — a cascading CRS failure across partitions, a topology corruption that fails republication, a replication lag spanning regions — hand off to the full procedure in the freshness SLA breach runbook, which carries the escalation contacts, rollback steps, and post-incident reconciliation that a triage pass deliberately omits. The signal to escalate is simple: if two remediation attempts do not clear the lag, or the breach spreads to a second dataset, you are past triage and into incident response. Record the stage attribution and the remediate-versus-wait rationale in the ticket before you hand off, so the runbook owner starts from evidence rather than re-deriving it.

After any breach clears, reconcile the baseline so the incident’s bad data does not poison the trailing statistics the dynamic-baseline alerts depend on. A breach left in the 30-day window quietly raises the bar for the next page.

FAQ

The lag is high but the source arrived on time — where did the time go?

Almost always a transformation-stage stall, most often CRS validation. Query crs_validation_latency for the breaching partition alongside spatial_freshness_lag_seconds; if validation time accounts for the excess, the lag is real dwell inside your pipeline, not a source delay. Fix the projection contract rather than nudging the extraction schedule, and confirm the sidecar is reading source acquisition time rather than a wall-clock fallback, which makes every stale layer look artificially fresh.

Should I reprocess immediately or wait for the next run?

Compare the next scheduled run plus typical dwell against the remaining SLA budget. If the next run lands inside the window and will carry corrected data, waiting is cheaper and avoids compute contention. Reprocess now only when the next run would breach anyway, or when the breach is a completeness fault the next run will simply repeat. An extraction-stage breach with a healthy row delta is the classic wait case.

How do I tell a real breach from an aggregation artifact?

Re-run the lag query dimensioned by partition_key and read the worst partition, not the mean. A jurisdiction-wide average stays green while one administrative boundary sits hours behind, because the fresh majority dilutes the stale minority. If the top partition is far above the rest, the breach is localized and the fix is scoped to that shard, not the whole feed.

When does triage become a full incident?

When the fix exceeds a single rerun. Two failed remediation attempts, a breach that spreads to a second dataset, or a stall rooted in a cross-region or topology failure all mean you are past triage. Hand off to the freshness SLA breach runbook with the stage attribution and your remediate-versus-wait rationale already recorded, so the escalation starts from evidence.

Related

• Tracking Spatial Data Freshness SLAs — the parent guide defining the spatial_freshness_* metrics this triage reads.
• How to automate geometry validity checks in GDAL — the sibling deep dive that measures validation latency so it does not inflate lag.
• Freshness SLA breach runbook — the full incident procedure triage hands off to when a fix exceeds one rerun.
• Coordinate Reference System Validation — the usual home of a transformation-stage stall surfaced during attribution.
• Spatial Data Freshness & Quality Metrics — the program this triage procedure belongs to.