Calculating Data Exposure Windows After a Bad Load

When a bad batch lands in a spatial layer, the first number the review needs is not how long the pipeline was red — it is how long wrong features were reachable, and by whom. That interval is the exposure window, and unlike service downtime it has to be recovered from the data rather than from the monitoring system, because the defect was silent while it was happening. This guide is the mechanical procedure for computing it: bisecting the ingestion history to find the first bad feature, bounding the end of the window on repair rather than on recovery, and turning the pair into a feature-hours figure the review can compare against other incidents. It belongs to post-incident review for geospatial data within the spatial incident response and tooling program.

Anatomy of an exposure window from first bad batch to reissued extracts A horizontal band shows an exposure window divided into three phases. The silent phase runs from the first bad batch to detection and is the longest. The known phase runs from detection to the halt of ingestion. The residual phase runs from the halt to the moment repaired features are published and dependent extracts are reissued. Below the band, three counters accumulate: features landed, features exposed, and downstream reads served from the affected layer. The window closes only when the residual phase ends, not when ingestion stops. The window closes at repair, not at the halt silent bad features landing, nobody knows known alert firing, triage running residual ingestion halted, bad rows still readable first bad batch detected ingestion halted extracts reissued features landed — stops accumulating at the halt features exposed — accumulates until the repair publishes Halting ingestion stops the bleeding; it does not end the exposure. Reviews that stop the clock at the halt understate it badly.

Problem framing: three boundaries you have to establish

The window has a start, an end, and a population, and each one is recovered differently.

The start is the ingestion timestamp of the first feature carrying the defect. It is almost never the alert time and is frequently much earlier — a projection break on a nightly feed can run for several nights before a coverage detector notices. Recovering it requires that landed features carry an ingestion timestamp and a batch identifier. If they do not, the single most valuable output of this incident is the change that makes them.

The end is the moment no consumer can still read a defective feature. That is later than the halt, later than the alert resolving, and later than the loader going green. It arrives when defective features have been repaired or quarantined out of the served layer, dependent derived layers rebuilt, caches invalidated, and any extract generated during the window reissued. Each of those is a separate clock and the window closes on the last of them.

The population is the set of features that were actually wrong, which is usually a subset of the batch. A projection fault affects every feature in the affected batches; a topology fault affects only those that fail validity; an attribute re-code affects only rows carrying the changed column value. Getting this right matters because feature-hours multiplies it by the interval, and an order-of-magnitude error in the population makes the figure meaningless.

Implementation: bisect, bound, and count

The start is a grouped scan over ingestion time with the defect predicate applied. Run it at hourly resolution first to find the region, then at batch resolution to name the exact batch.

-- Step 1 — locate the first hour containing the defect.
-- The predicate must match THIS incident's defect, not "anything invalid".
WITH defect AS (
  SELECT ingested_at, batch_id
  FROM prod.parcels
  WHERE ST_SRID(geom) <> 27700            -- the projection fault under review
)
SELECT date_trunc('hour', ingested_at) AS hour,
       COUNT(*)                        AS defective,
       MIN(batch_id)                   AS first_batch
FROM defect
GROUP BY 1
ORDER BY 1
LIMIT 1;

-- Step 2 — name the exact batch and its landing time.
SELECT batch_id,
       MIN(ingested_at) AS batch_start,
       MAX(ingested_at) AS batch_end,
       COUNT(*)         AS features
FROM prod.parcels
WHERE ST_SRID(geom) <> 27700
GROUP BY batch_id
ORDER BY batch_start
LIMIT 5;

Two cautions on the predicate. It must describe the defect, not the symptom that alerted — filtering on invalid geometry when the incident was a projection fault will find a different, older population and produce a wildly wrong start. And it must be applied to the served layer, not to staging, because features that never crossed the trust boundary were never exposed.

The end is bounded by the last repair action, which means the repair itself has to be timestamped. Recording repair events as rows makes the calculation mechanical rather than a matter of scrolling chat history.

-- Repair ledger — one row per action that shrinks the exposed population.
CREATE TABLE IF NOT EXISTS incident.repair_log (
  incident_id   text        NOT NULL,
  layer         text        NOT NULL,
  action        text        NOT NULL,   -- quarantine | repair | rebuild | reissue | invalidate
  features      bigint,
  completed_at  timestamptz NOT NULL DEFAULT now()
);

-- The window closes at the LAST action, across every dependent artefact.
SELECT MIN(completed_at) AS first_repair,
       MAX(completed_at) AS window_end,
       SUM(features) FILTER (WHERE action IN ('quarantine','repair')) AS features_fixed
FROM incident.repair_log
WHERE incident_id = 'INC-2026-0413';

With both ends established, the exposure figure is a product. Expressing it in feature-hours makes incidents of different shapes directly comparable.

SELECT
  b.features                                                        AS features_exposed,
  EXTRACT(epoch FROM (r.window_end - b.batch_start)) / 3600.0       AS exposure_hours,
  ROUND((b.features * EXTRACT(epoch FROM (r.window_end - b.batch_start)) / 3600.0)::numeric, 1)
                                                                    AS feature_hours
FROM (SELECT MIN(ingested_at) AS batch_start, COUNT(*) AS features
      FROM prod.parcels WHERE ST_SRID(geom) <> 27700) b
CROSS JOIN (SELECT MAX(completed_at) AS window_end
            FROM incident.repair_log WHERE incident_id = 'INC-2026-0413') r;
Choosing the defect predicate that defines the exposed population Three nested sets are drawn as concentric rounded rectangles. The outermost set is every feature in the affected batches. Inside it sits the set matching the incident defect predicate, such as a wrong spatial reference identifier. Inside that sits the set that also crossed the trust boundary into the served layer, which is the population that was genuinely exposed. Annotations warn that using the outer set overstates exposure and that using a symptom predicate rather than the defect predicate selects a different population entirely. Exposed population = defect predicate ∩ crossed the trust boundary all features in the affected batches matches the defect predicate (ST_SRID ≠ 27700) exposed published to the served layer during the window this is the number that multiplies the interval Overstating counting the whole batch inflates feature-hours by an order of magnitude Selecting the wrong set filtering on the symptom that alerted (invalid geometry) instead of the defect (wrong projection) finds a different, usually older population

Verification: sanity-check the window before it goes in the report

Three cross-checks catch the errors that matter.

Compare the computed start against the upstream export history. The first bad batch should coincide with a change on the source side — a new export version, a schema edit, a firmware roll. If it does not, the predicate is probably selecting pre-existing defects that were never part of this incident.

Compare the exposed population against the layer’s total. A projection fault that reports 0.3% of the layer as exposed is suspicious: projection faults are usually batch-wide. Conversely a topology fault reporting 90% exposure suggests the predicate is too broad.

Confirm the end by re-running the defect predicate against the served layer now. It must return zero. If it returns rows, the window has not actually closed and the review is being written prematurely — a surprisingly common finding, usually because a derived layer or a cached tile pyramid was missed.

Feature-hours across ten incidents, ranked Ten incidents are ranked by feature-hours of exposure. The largest is a projection fault on a parcel layer that ran for two days. The second is a topology corruption caught within hours but affecting a large layer. Several mid-sized incidents follow. The smallest are short freshness breaches on small layers. A median line is drawn, and the note states that comparing against the median rather than against an absolute threshold is what makes the figure useful. Compare against your own median, not against an absolute threshold projection fault · parcels · 2 days 612 000 feature-hours topology corruption · 6 h 251 000 coverage loss · 18 h 178 000 attribute re-code · 3 days 134 000 median of all ten 86 000 — the comparison line freshness breach · small layer 12 000

Gotchas

No ingestion timestamp on landed features. Without ingested_at the start is unrecoverable and the best you can do is bound it by the batch load history. Record the gap as the incident’s primary finding and add the column; every future review depends on it.

Soft-deleted rows excluded by a default filter. Many served views filter out superseded rows. Running the bisect against the view rather than the table can hide the earliest defective features entirely. Query the base table.

Counting the halt as the end. Halting ingestion stops new bad features from landing; the ones already published stay readable. Using the halt as the window end typically understates exposure by the majority of its duration, since repair and reissue routinely take longer than detection did.

Ignoring derived artefacts. A tile pyramid built from the affected features is itself exposed, and it does not fix itself when the source rows are repaired. The lineage traversal described in how to map geospatial data lineage for observability is what makes the dependent set enumerable rather than guessed.

FAQ

What if the defect predicate cannot be expressed in SQL?

Some defects — a subtly wrong attribute re-code, a shifted timestamp — are only detectable by comparison against a baseline snapshot rather than by a predicate. In that case diff the affected batches against the last known-good snapshot and treat the differing rows as the population. If no snapshot exists, that absence is the finding, and snapshot retention becomes the corrective action.

Should exposure include consumers who never actually read the data?

No. Exposure measures reachability, and feature-hours is deliberately a reachability measure rather than a harm measure, because reads are usually not fully logged. Where you do have read logs, record actual reads as a separate figure alongside it — it is a stronger number when you have it and a misleading one when partially available.

How do I handle an incident spanning several layers?

Compute a window per layer and report both the per-layer figures and their sum. Layers repair at different times and a single merged window hides the one that took longest, which is usually the one worth fixing.

Does a rollback reset the start of the window?

No. A rollback ends the window; it does not retroactively unexpose. Anything read during the window was read, and any extract generated from it still needs reissuing. The rollback timestamp is the window end, recorded in the repair ledger like any other action.

What is a reasonable feature-hours figure to treat as significant?

There is no universal threshold, because it scales with layer size. Calibrate against your own history: compute it for the last ten incidents, and treat anything above the median as warranting a corrective action with a named owner. The value of the metric is comparative, not absolute.