The Availability Report That Reads 104%

Ranking for distortion of the reported number: (c) missing heartbeats defaulted to up, then (a) interval double-counting, then (d) clock skew and NULL/0 collapsing, then (b) the re-run instability - which is a symptom of the other three rather than an independent defect.

(c) Missing = available is the most damaging because the bias is perfectly correlated with the outcome you care about. Silent failures, network isolation and agent crashes all suppress heartbeats, so the population you most need to measure is exactly the population you write down as healthy. A server dark for nine hours improves its own score.

(a) 104.2% is arithmetically impossible in a well-formed calculation, so it is a counting defect. The likely mechanism: retry duplicates create a second 'up' interval start without closing the first, and interval summation then credits overlapping minutes twice. Overlaps plus the seven-hour offset in (d) push rows across hour and month boundaries, so April minutes are credited to March.

(d) matters in three ways: rows land in the wrong hour bucket, the 0/NULL conflation treats 'sensor did not report' as a healthy status, and month-boundary drift silently moves volume between reporting periods.

(b) means the pipeline is not idempotent: a re-run should reproduce the previous number exactly.

Deduplicate: key on (device_id, event_hour_utc, metric_name), keep the row with the greatest ingest_ts, break ties on ingest_id. Write with MERGE or INSERT OVERWRITE on the (device_id, date_trunc('hour', event_hour_utc)) partition so retries are idempotent. Normalise time first: maintain a per-site offset table from iDRAC configuration, cross-check against the median of ingest_ts − event_ts, and raise an alert for any site with |offset| > 5 minutes.

Build a spine, not a join to observations:

-- spine: one row per device per hour in month
spine AS (
  SELECT d.device_id, h.hour_utc
  FROM dim_device d
  CROSS JOIN dim_hour h
  WHERE h.month = '2024-03'
    AND d.installed_at < h.hour_utc
    AND (d.decommissioned_at IS NULL OR d.decommissioned_at > h.hour_utc)
),
classified AS (
  SELECT s.device_id, s.hour_utc,
         CASE WHEN hb.device_id IS NULL      THEN 'unknown'
              WHEN hb.status_code = 1        THEN 'up'
              WHEN hb.status_code = 0        THEN 'down'
              ELSE 'unknown' END AS state
  FROM spine s
  LEFT JOIN heartbeats_dedup hb
    ON hb.device_id = s.device_id AND hb.event_hour_utc = s.hour_utc
)

Report three numbers, not one: observed availability = up/(up+down); coverage = (up+down)/all_spine_hours; worst-case availability = up/(all_spine_hours). Never impute 'up'. Contractual downtime should use worst case or an agreed carve-out for planned maintenance windows, which belong in the spine as excluded, not as downtime.

A re-run is only trustworthy if it is idempotent and auditable. Concretely: (1) run the backfill into a versioned table with a run_id, never in place; (2) reconcile row counts, distinct device counts and a checksum of availability by site against the prior run, and require an explanation for every delta above a threshold; (3) record which logic version produced each published month, and freeze the published figure until the reconciliation is signed off, so the number in circulation is never one that is about to change;

(4) mark the trend line at the definition change. Mixing pre-fix and post-fix months in one series creates a cliff that readers will interpret as an operational event; either restate prior months under the fixed logic or leave a visible break annotation; (5) publish coverage alongside availability for at least one quarter while trust is rebuilt, because a jump in availability after this fix will otherwise look like good news rather than the removal of a measurement bias. It is worth stating plainly to the director: after the fix, availability will probably fall, and that is the correction working, not the fleet deteriorating.