Data Freshness & SLA Monitoring: Freshness Budgets, Heartbeats & Anomaly Alerts

data freshness is the metric that erodes trust in a warehouse faster than any wrong number, because a stale dashboard looks right — the rows are there, the joins resolve, the totals sum — while quietly describing a world that stopped existing hours ago. Every table that feeds a decision has an implicit contract: the finance close needs yesterday's ledger by 6 a.m., the fraud model needs events within seconds, the executive dashboard can tolerate an hour. The failure mode is rarely a crash; it is a pipeline that silently stops emitting rows, an upstream API that starts returning empties, a MERGE that runs green but merges nothing — and the first person to notice is a stakeholder asking why the number "looks off," which is the most expensive way possible to discover a broken pipeline.

The discipline that prevents that is SLA monitoring built on three primitives: a freshness budget that says how old the data is allowed to be, a heartbeat that proves the pipeline is still alive even when there is no new data, and anomaly detection that learns the normal rhythm of load latency and volume so it can flag a deviation before it becomes a breach. This guide is the senior-data-engineering walkthrough of that observability layer — how to define staleness precisely, turn it into an SLI, SLO, and error budget, wire a dead-man's switch, replace brittle static thresholds with rolling statistics, and route deduplicated alerting to an owner with a compliance rollup. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse pipeline patterns on the ETL practice library →, and sharpen the checks with the data validation practice library →.

---

On this page

• Why data freshness is the SLA that breaks trust first
• Freshness budgets and SLOs
• Heartbeats and liveness signals
• Anomaly detection on freshness and volume
• Alerting, SLA reporting, and ownership
• Cheat sheet — data freshness & SLA monitoring recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why data freshness is the SLA that breaks trust first

Freshness is a measured lag governed by a budget — not a vibe, not "the job ran"

The one-sentence invariant: data freshness is the measured lag between when an event happened in the real world and when it becomes queryable downstream, and SLA monitoring is the practice of bounding that lag with a budget, detecting breaches with heartbeats and anomaly detection before a human does, and paging an owner only when the error budget is actually at risk. Everything else in this guide is a refinement of that sentence. The reason freshness breaks trust first — before correctness bugs, before schema drift — is that stale data is plausible: it passes every row-count and not-null check because the rows that exist are fine; they are simply old. A pipeline can be "green" on every operational metric (job succeeded, no errors, exit code 0) and still be shipping data that violates its freshness contract by six hours.

The four axes interviewers actually probe.

• What you measure. Freshness is not one number. There is event-time lag (now() - max(event_time)), ingestion lag (now() - max(loaded_at)), and end-to-end latency (source commit → queryable). A senior answer names which one the consumer cares about and measures that one; a weak answer says "we check if the table updated today." Interviewers open here because imprecise measurement is the root of every downstream mistake.
• How you bound it. A raw lag number is useless without a threshold. The senior framing is SLI → SLO → SLA → error budget: the SLI is the measured lag, the SLO is the internal target, the SLA is the external promise, and the error budget is how much breach you can spend before it matters. Candidates who conflate SLO and SLA — or who set the budget from "what the pipeline can do" instead of "what the consumer needs" — get marked down.
• How you detect breaches. Three complementary mechanisms: a threshold on the freshness SLI, a heartbeat that catches silent stalls (no rows and no error), and anomaly detection that flags a deviation from the learned baseline before the hard threshold trips. The trap is relying on the threshold alone — it never fires when a pipeline dies quietly, because a dead pipeline emits no signal at all.
• Who owns the page. Detection without ownership is noise. The senior answer routes each freshness breach to a named owner with a runbook, deduplicates repeated alerts, and pages a human only when the error-budget burn rate says the SLA is genuinely at risk. Alert fatigue — pages that everyone learned to ignore — is itself an outage waiting to happen.

The 2026 reality — tooling measures, judgment sets the policy.

• Observability platforms (dbt source freshness, Monte Carlo, Great Expectations freshness checks, Airflow SLAs, Prometheus + Grafana) compute the lag and store the history automatically. The mechanics of measuring freshness are solved.
• The budget is not. Deciding that the orders fact table may be at most 30 minutes stale during business hours but 4 hours overnight — and defending that against both the consumer who wants zero lag and the platform team who wants a relaxed SLO — is judgment, not tooling. This is the work interviews probe.
• The heartbeat is still hand-built more often than not. Most tools measure how old the newest row is; far fewer prove the job is still running when there is legitimately no new data. Distinguishing "no new orders at 3 a.m." from "the loader has been dead since midnight" is the single most common gap in a freshness setup.
• Anomaly detection and alert routing are where teams over- or under-invest. A static threshold is cheap and brittle; a full seasonal model is powerful and noisy if untuned. The senior move is a rolling, robust baseline plus severity tied to error-budget burn — enough to catch real breaches without paging on every Monday-morning traffic spike.

What interviewers listen for.

• Do you define freshness as a lag with a specific timestamp pair (event-time vs ingestion vs end-to-end) rather than "did the table update"? — required answer.
• Do you distinguish SLI, SLO, SLA, and error budget and set the budget from the consumer's need? — senior signal.
• Do you name the silent-failure problem and reach for a heartbeat / dead-man's switch, not just a threshold? — senior signal.
• Do you tie alert severity to error-budget burn and talk about alert fatigue as a real failure mode? — senior signal.
• Do you describe freshness as one pillar of data observability alongside volume, schema, and distribution — not a standalone hack? — required answer.

Worked example — the freshness observability map

Detailed explanation. The single most useful artifact for a freshness interview is a one-page map that names each layer of the system: the SLI you measure, the budget that bounds it, the detector that catches breaches, and the owner who acts. Every senior freshness discussion converges on this map within ten minutes; carrying it in your head is what turns a rambling answer into a crisp one. Walk through building the map for a hypothetical orders fact table feeding three consumers with very different tolerances.

• The table. analytics.fct_orders on Snowflake, loaded incrementally every 15 minutes from a CDC stream.
• The consumers. A fraud model (needs < 5 min), an executive dashboard (tolerates 1 h), a finance close (needs complete by 06:00 daily).
• The signals. Event-time lag, ingestion lag, row-count volume, and a per-run heartbeat.

Question. Lay out the freshness observability map: which SLI each consumer cares about, the budget, the detector, and the owner.

Input.

Layer	Definition	Example value
SLI	measured freshness lag	now() - max(event_time)
SLO	internal target	fraud: 5 min; dashboard: 60 min
SLA	external promise	finance: loaded by 06:00
Error budget	allowed breach	0.1% of 15-min cycles/month
Detector	how a breach is caught	threshold + heartbeat + anomaly

Code.

Freshness observability map (memorise this)
===========================================

  MEASURE            BOUND              DETECT                 ACT
  ┌──────────┐      ┌──────────┐       ┌──────────────┐       ┌──────────┐
  │  SLI     │─────▶│  SLO /   │──────▶│ threshold +  │──────▶│  owner + │
  │  lag =   │      │  budget  │       │ heartbeat +  │       │  runbook │
  │  now() − │      │  (from   │       │ anomaly      │       │  + page  │
  │  max(ev) │      │ consumer)│       │ detection    │       │  on burn │
  └──────────┘      └──────────┘       └──────────────┘       └──────────┘
       │                 │                    │                    │
   event-time vs     SLI < SLO?          hard breach OR        route by owner;
   ingestion vs      spend error         silent stall OR       dedup; severity
   end-to-end        budget on purpose   deviation from        from burn rate
                                         learned baseline

Step-by-step explanation.

1. The measure column forces you to pick the right timestamp pair per consumer. The fraud model cares about event-time lag (how old is the newest real-world event); the finance close cares about a wall-clock deadline (is the table complete by 06:00). Measuring the wrong one is the most common mistake — you report "the table updated 2 minutes ago" while the newest event in it is 3 hours old because upstream backfilled late.
2. The bound column is set backwards from the consumer, never forwards from the pipeline. The fraud model's 5-minute SLO drives the ingestion cadence, not the other way around. If the pipeline can only do 15 minutes, that is a gap to close, not a budget to accept.
3. The detect column runs all three mechanisms in parallel because each covers a different failure: the threshold catches slow drift, the heartbeat catches silent death, the anomaly detector catches the early warning (volume halved) before the hard threshold trips.
4. The act column is what makes the map operational rather than decorative. A breach with no owner is a Slack message everyone ignores. The map assigns an owner and a runbook and pages only when the error-budget burn rate is high enough to threaten the SLA.
5. In practice, one table has multiple rows in this map — one per consumer — because the same fct_orders is fresh enough for the dashboard and hours too stale for the fraud model at the exact same moment. Freshness is per-consumer, not per-table.

Output.

Consumer	SLI it cares about	Budget	Primary detector
Fraud model	event-time lag	5 min	anomaly + threshold
Executive dashboard	ingestion lag	60 min	threshold
Finance close	wall-clock completeness	done by 06:00	heartbeat / deadline

Rule of thumb. Never say "the table is fresh." Say "the table is within budget for consumer X on SLI Y." Freshness is a relationship between a table and a consumer, measured on a specific timestamp, bounded by a specific budget — write the map before you write a single alert.

Worked example — what interviewers actually probe

Detailed explanation. The senior freshness interview has a predictable shape: an ambiguous opener ("how would you know if this table is stale?"), then progressive narrowing to test whether you know the axes. Candidates who answer "we'd set up an alert if it doesn't update" score lowest; candidates who name the SLI, the budget, the silent-failure gap, and the ownership story score highest. Walk through the grading rubric.

• Ambiguous opener. "How do you know your warehouse tables are fresh?" — invites you to define freshness precisely.
• Follow-up 1. "Your alert checks the table updated in the last hour. The pipeline died but the last hour's data is still there — will it fire?" — probes the silent-failure / heartbeat gap.
• Follow-up 2. "There's a nightly traffic spike; your threshold pages every night — fix it." — probes anomaly detection vs static thresholds.
• Follow-up 3. "On-call is drowning in freshness pages — what do you change?" — probes alert fatigue, dedup, and severity-by-burn.

Question. Draft a 5-minute senior freshness answer that covers all four axes without waiting to be asked.

Input.

Interview signal	Weak answer	Senior answer
Freshness definition	"check it updated today"	"event-time lag: now() - max(event_time), per consumer"
Budget	"alert if > 1 hour"	"SLO from the consumer; error budget of 0.1% cycles/month"
Silent failure	"the update check covers it"	"no — add a heartbeat / dead-man's switch"
Anomaly	"raise the threshold"	"rolling baseline; z-score / MAD; seasonal by hour-of-day"
Alert fatigue	"mute the channel"	"dedup + group + severity by burn rate; page only on burn"

Code.

Senior freshness answer template (5 minutes)
============================================

Minute 1 — define it precisely
  "Freshness is a lag: now() minus the newest event_time, measured
   per consumer. I separate event-time lag, ingestion lag, and
   end-to-end latency — the fraud model cares about the first, the
   finance close about a wall-clock deadline."

Minute 2 — bound it with a budget
  "I set the SLO from the consumer's need, not the pipeline's
   capability. The SLI is the measured lag; the SLO is the target;
   the SLA is the external promise; the error budget is how many
   15-minute cycles per month may breach — say 0.1%."

Minute 3 — detect silent failure
  "A 'did it update in the last hour' check is blind to a pipeline
   that died with fresh-enough data still sitting there. I add a
   heartbeat table the job writes to every run and a dead-man's
   switch that fires when no beat arrives within 2x the cadence."

Minute 4 — anomaly, not static thresholds
  "Static thresholds page on every seasonal peak. I baseline load
   latency and row volume on a rolling window, flag deviations with
   a robust statistic (MAD, not raw std), and bucket the baseline by
   hour-of-day and day-of-week so Monday mornings don't page."

Minute 5 — alerting + ownership
  "I dedup and group alerts by table, set severity from error-budget
   burn rate (warn / high / page), route to a named owner with a
   runbook, and report monthly SLA compliance. On-call is paged only
   when the budget is actually burning fast."

Step-by-step explanation.

1. Minute 1 is the whole interview in miniature. Defining freshness as a lag with a named timestamp pair — and separating event-time from ingestion from end-to-end — signals you have measured it for real. Weak candidates say "check it updated," which cannot distinguish a late backfill from a healthy load.
2. Minute 2 pre-empts the "where does the number come from" follow-up by deriving the budget from the consumer. The SLI/SLO/SLA/error-budget vocabulary is borrowed from SRE for a reason: it makes freshness a quantified promise, not a gut feeling.
3. Minute 3 addresses the silent-failure gap before it is asked. The heartbeat is the single most senior thing you can name, because most candidates never realise a "table updated recently" check cannot detect a dead pipeline holding recent data.
4. Minute 4 shows you understand why static thresholds fail: they cannot tell a legitimate seasonal peak from an anomaly. A rolling, robust, seasonal baseline is the answer. Naming MAD over standard deviation (robust to the very outliers you are hunting) is a bonus senior signal.
5. Minute 5 closes the loop on operations. Detection is worthless without dedup, severity, ownership, and reporting. Tying the page to burn rate — not to a single breach — is what keeps on-call sane and the SLA defended.

Output.

Grading criterion	Weak score	Senior score
Defines freshness as a lag	rare	mandatory
SLI/SLO/SLA/error budget	rare	senior signal
Names the heartbeat gap	rare	senior signal
Anomaly over static threshold	occasional	mandatory
Severity by burn + ownership	rare	senior signal

Rule of thumb. The senior freshness answer is a 5-minute monologue: define the lag, bound it with a consumer-driven budget, detect with threshold + heartbeat + anomaly, and page on burn with a named owner. Rehearse it once; deploy it every time freshness comes up.

Senior interview question on data freshness fundamentals

A senior interviewer often opens with: "You own 200 warehouse tables. Leadership asks for 'a freshness SLA.' Walk me through how you'd define freshness for these tables, decide which ones get a strict SLO, and set up the first line of detection — and explain why a simple 'did it update in the last hour' check is not enough."

Solution Using a per-table freshness SLI view with consumer-driven budgets

-- 1. A freshness registry: one row per (table, consumer) with its budget.
--    The budget comes from the consumer's need, not the pipeline's cadence.
CREATE TABLE meta.freshness_slo (
    table_name        TEXT        NOT NULL,
    consumer          TEXT        NOT NULL,
    sli_kind          TEXT        NOT NULL,   -- 'event_time' | 'ingestion' | 'deadline'
    budget_minutes    INT,                    -- for lag-based SLIs
    deadline_local    TIME,                   -- for wall-clock deadline SLIs
    tier              TEXT        NOT NULL,    -- 'critical' | 'standard' | 'best_effort'
    owner             TEXT        NOT NULL,
    PRIMARY KEY (table_name, consumer)
);

-- 2. A freshness SLI view: current lag per table, computed from a
--    lightweight per-table watermark the loaders maintain.
CREATE OR REPLACE VIEW meta.freshness_sli AS
SELECT
    w.table_name,
    w.max_event_time,
    w.max_loaded_at,
    DATEDIFF('minute', w.max_event_time, CURRENT_TIMESTAMP()) AS event_lag_min,
    DATEDIFF('minute', w.max_loaded_at,  CURRENT_TIMESTAMP()) AS ingest_lag_min
FROM meta.table_watermark w;

-- 3. Join SLI to SLO to get in-budget / breached, per consumer.
SELECT
    s.table_name,
    s.consumer,
    s.tier,
    CASE s.sli_kind
        WHEN 'event_time' THEN f.event_lag_min
        WHEN 'ingestion'  THEN f.ingest_lag_min
    END                                        AS lag_min,
    s.budget_minutes,
    CASE
        WHEN s.sli_kind IN ('event_time','ingestion')
             AND (CASE s.sli_kind
                    WHEN 'event_time' THEN f.event_lag_min
                    WHEN 'ingestion'  THEN f.ingest_lag_min END) > s.budget_minutes
        THEN 'BREACHED' ELSE 'IN_BUDGET'
    END                                        AS status
FROM meta.freshness_slo s
JOIN meta.freshness_sli f USING (table_name)
ORDER BY (status = 'BREACHED') DESC, s.tier, lag_min DESC;

-- 4. WHY the "updated in the last hour" check is insufficient:
--    it looks at max_loaded_at only, which stays recent even when the
--    newest EVENT is hours old (late upstream) or when the loader ran
--    but merged zero rows. Contrast the two SLIs:
SELECT table_name,
       ingest_lag_min,          -- small: loader ran recently
       event_lag_min            -- large: newest real event is old
FROM   meta.freshness_sli
WHERE  event_lag_min > 4 * 60   -- event is > 4h old ...
  AND  ingest_lag_min < 60;     -- ... but the naive check says "fresh"

Step-by-step trace.

Input meta.table_watermark and meta.freshness_slo (3 tables):

table_name	max_event_time	max_loaded_at	consumer	sli_kind	budget_min
fct_orders	09:12	09:58	fraud	event_time	5
fct_orders	09:12	09:58	dashboard	ingestion	60
dim_customer	06:00	09:55	crm	event_time	240

Assume CURRENT_TIMESTAMP() = 10:00.

1. event_lag_min for fct_orders = 10:00 - 09:12 = 48 min; ingest_lag_min = 10:00 - 09:58 = 2 min.
2. Fraud consumer uses event_time SLI: 48 min vs a 5-min budget → BREACHED. The newest real order is 48 minutes old even though the loader ran 2 minutes ago.
3. Dashboard consumer uses ingestion SLI: 2 min vs a 60-min budget → IN_BUDGET. Same table, same instant, different verdict — freshness is per-consumer.
4. dim_customer event lag = 10:00 - 06:00 = 240 min vs a 240-min budget → IN_BUDGET (exactly at the edge; > not >=).
5. Query 4 exposes the naive check's blind spot: fct_orders has ingest_lag_min = 2 (naive check says "fresh") while event_lag_min = 48 — and if that gap were 4 h, the "updated in the last hour" check would be blind to a genuine event-time staleness.

Output:

table_name	consumer	tier	lag_min	budget_minutes	status
fct_orders	fraud	critical	48	5	BREACHED
fct_orders	dashboard	standard	2	60	IN_BUDGET
dim_customer	crm	standard	240	240	IN_BUDGET

Why this works — concept by concept:

• Freshness registry — one row per (table, consumer) makes the budget an explicit, version-controlled artifact rather than a magic number buried in an alert. Different consumers of the same table get different budgets, which is the whole point.
• Event-time vs ingestion SLI — computing both event_lag_min and ingest_lag_min lets each consumer bind to the timestamp that matters. Event-time catches late/backfilled upstream data; ingestion catches loader cadence.
• Consumer-driven budget — the SLO comes from budget_minutes in the registry, set from what the consumer needs. The pipeline's actual cadence is a separate fact; a gap between them is a roadmap item, not an acceptable budget.
• The naive-check blind spot — a "loaded in the last hour" test reads only max_loaded_at; it is structurally incapable of detecting event-time staleness or a zero-row merge. That is exactly why heartbeats and event-time SLIs (later sections) exist.
• Cost — the watermark table is O(tables) rows updated once per load; the SLI view is O(1) per table; the join is O(tables × consumers). Negligible compute for a warehouse-wide freshness verdict on every poll.

SQL
Topic — sql
SQL freshness and watermark problems

Practice →

ETL Topic — etl ETL problems on pipeline monitoring

Practice →

---

2. Freshness budgets and SLOs

A freshness budget turns a lag number into a promise — SLI measures, SLO targets, SLA promises, error budget spends

The one-sentence invariant: a freshness budget is the maximum staleness a consumer will tolerate, expressed as an SLO on a freshness SLI, backed by an error budget that quantifies how much breach you may spend before the external SLA is at risk — and the whole edifice is worthless unless the budget is derived from the consumer's decision, not from what the pipeline happens to deliver. The vocabulary is borrowed wholesale from site-reliability engineering, and for good reason: it converts "the dashboard feels slow" into "the p99 ingestion lag exceeded its 60-minute SLO in 0.4% of cycles this month, spending 40% of the error budget" — a statement you can act on, report on, and defend in a review.

Freshness SLIs — what you actually measure.

• Event-time lag. now() - max(event_time). How old is the newest real-world event that is queryable. This is the SLI most consumers think they want and often the right one for streaming / near-real-time use cases.
• Ingestion lag. now() - max(loaded_at). How long since the loader last wrote. Small even when data is late — it measures the pipeline's liveness, not the data's age. Useful, but never a substitute for event-time lag.
• End-to-end latency. Source commit timestamp → queryable timestamp, measured per row and aggregated (p50 / p95 / p99). The gold standard when you can propagate a source timestamp all the way through.
• Wall-clock completeness. A deadline SLI: "is the table complete by 06:00?" Not a lag at all — a boolean against a schedule. Correct for batch consumers like a finance close.

SLI vs SLO vs SLA vs error budget — the four-word ladder.

• SLI (indicator). The measured number. Freshness lag in minutes, or the fraction of cycles that were in budget. Objective, computed from data.
• SLO (objective). Your internal target on the SLI. "Ingestion lag < 60 min for 99.9% of 15-min cycles." Tighter than the SLA on purpose, so you notice before you break your promise.
• SLA (agreement). The external promise, often with consequences. "Data available by 06:00 or the finance team is notified." The SLA is what you signed; the SLO is what you hold yourself to.
• error budget. 1 - SLO. If the SLO is 99.9% of cycles in budget, the error budget is 0.1% — the number of breach-cycles you are allowed per window. You spend it on deploys, backfills, and known-risky changes; when it is exhausted, you freeze changes until it recovers.

Setting the budget — backwards from the consumer.

• Start from the decision. What breaks if the data is 1 hour old? If nothing breaks until 4 hours, the budget is 4 hours, not "as fresh as possible." Over-tight budgets manufacture false urgency and burn on-call goodwill.
• Different consumers, different budgets. The same table can be critical for fraud and best-effort for a quarterly report. Register the budget per (table, consumer), never per table alone.
• Tier the tables. critical (page on burn), standard (ticket), best_effort (dashboard only). Most tables are standard; a handful are critical. Spending equal effort on all of them is how freshness programs die.
• The pipeline capability is a separate fact. If the consumer needs 5 minutes and the pipeline does 15, that is a gap, tracked and roadmapped — not a budget you quietly relax to 15.

Common interview probes on freshness budgets.

• "What is the difference between an SLO and an SLA?" — SLO is internal and tighter; SLA is the external promise with consequences.
• "Where does the budget come from?" — the consumer's decision, backwards; never the pipeline's cadence.
• "What is an error budget and how do you use it?" — 1 - SLO; spend it deliberately, freeze changes when exhausted.
• "How do you measure freshness for a batch table with a 06:00 deadline?" — a wall-clock completeness SLI, not a lag.

Worked example — computing a freshness SLI in SQL

Detailed explanation. The foundation of every freshness budget is a query that turns raw watermarks into a lag and a verdict. Build it once, run it everywhere. The trick is computing all three lag flavours in one pass so each consumer can bind to the one it cares about, then comparing against the registered budget.

• Source. A table_watermark row per table with max_event_time and max_loaded_at, refreshed at the end of every load.
• SLI. Event-time lag, ingestion lag, and (when available) end-to-end p95.
• Verdict. IN_BUDGET / BREACHED against the registered budget_minutes.

Question. Write the SLI query that returns, per table, the freshness lag and whether it is within budget.

Input.

table_name	max_event_time	max_loaded_at	budget_min (event)
fct_orders	2026-08-18 09:12	2026-08-18 09:58	30
dim_product	2026-08-18 08:40	2026-08-18 09:59	120
fct_clicks	2026-08-18 09:57	2026-08-18 09:59	10

Code.

-- Freshness SLI + budget verdict (assume CURRENT_TIMESTAMP = 2026-08-18 10:00)
WITH sli AS (
    SELECT
        w.table_name,
        DATEDIFF('minute', w.max_event_time, CURRENT_TIMESTAMP()) AS event_lag_min,
        DATEDIFF('minute', w.max_loaded_at,  CURRENT_TIMESTAMP()) AS ingest_lag_min
    FROM meta.table_watermark w
)
SELECT
    s.table_name,
    s.event_lag_min,
    s.ingest_lag_min,
    b.budget_minutes,
    ROUND(100.0 * s.event_lag_min / b.budget_minutes, 1) AS pct_of_budget,
    CASE WHEN s.event_lag_min > b.budget_minutes
         THEN 'BREACHED' ELSE 'IN_BUDGET' END           AS status
FROM sli s
JOIN meta.freshness_budget b
  ON b.table_name = s.table_name AND b.sli_kind = 'event_time'
ORDER BY pct_of_budget DESC;

Step-by-step explanation.

1. The sli CTE computes both lags with DATEDIFF('minute', ...) against CURRENT_TIMESTAMP(). Doing it in a CTE keeps the outer query readable and lets you add p95 end-to-end latency later without rewriting the verdict logic.
2. The join to meta.freshness_budget on sli_kind = 'event_time' pulls the event-time budget specifically — a table can have separate rows for event-time and ingestion budgets. Binding the SLI to the matching budget is the whole game.
3. pct_of_budget normalises every table onto the same 0–100+ scale, so a dashboard can sort "closest to breach first" regardless of whether a table's budget is 10 minutes or 4 hours. 100% is exactly at the budget; over 100% is breached.
4. The CASE uses strict > so a table exactly at its budget is IN_BUDGET — the budget is the maximum tolerated, and equal-to is still tolerated. Getting the boundary right avoids flapping alerts at the edge.
5. Ordering by pct_of_budget DESC surfaces the most-at-risk tables first, which is exactly what an on-call engineer or a dashboard wants to see at a glance.

Output.

table_name	event_lag_min	ingest_lag_min	budget_minutes	pct_of_budget	status
fct_clicks	3	1	10	30.0	IN_BUDGET
fct_orders	48	2	30	160.0	BREACHED
dim_product	80	1	120	66.7	IN_BUDGET

Rule of thumb. Compute every freshness SLI as a lag normalised to pct_of_budget. A dashboard sorted by percent-of-budget descending is the single most useful freshness artifact you can build — one glance tells you what is about to breach.

Worked example — error-budget burn over a rolling window

Detailed explanation. A single breach is not an incident; a pattern of breaches is. The error budget converts individual breach-cycles into a single spendable quantity, and the burn rate — how fast you are consuming it — is what should drive alerting. Compute the monthly error budget and the current burn from a log of per-cycle verdicts.

• SLO. 99.9% of 15-minute cycles in budget → 0.1% error budget.
• Window. Rolling 30 days = 2,880 cycles/table.
• Burn. Breached cycles ÷ allowed breach cycles.

Question. Given a log of per-cycle freshness verdicts, compute the error budget consumed and the burn rate for the last 30 days.

Input.

metric	value
cycles per day	96
window	30 days
total cycles	2,880
SLO	99.9% in budget
breached cycles (observed)	12

Code.

-- Error-budget burn from a per-cycle verdict log
WITH params AS (
    SELECT 0.999::NUMERIC AS slo,                 -- target fraction in budget
           2880          AS total_cycles          -- 96/day * 30 days
),
observed AS (
    SELECT COUNT(*) FILTER (WHERE status = 'BREACHED') AS breached_cycles,
           COUNT(*)                                    AS cycles_seen
    FROM   meta.freshness_verdict_log
    WHERE  table_name = 'fct_orders'
      AND  cycle_ts >= CURRENT_TIMESTAMP() - INTERVAL '30 days'
)
SELECT
    o.breached_cycles,
    p.total_cycles,
    ROUND((1 - p.slo) * p.total_cycles)                          AS budget_cycles,
    ROUND(100.0 * o.breached_cycles
          / NULLIF((1 - p.slo) * p.total_cycles, 0), 1)          AS budget_consumed_pct,
    CASE
        WHEN o.breached_cycles > (1 - p.slo) * p.total_cycles THEN 'EXHAUSTED'
        WHEN o.breached_cycles > 0.5 * (1 - p.slo) * p.total_cycles THEN 'FAST_BURN'
        ELSE 'HEALTHY'
    END                                                          AS burn_state
FROM observed o CROSS JOIN params p;

Step-by-step explanation.

1. params encodes the SLO (0.999) and the window size in cycles (2,880). Keeping them in a CTE makes the policy explicit and easy to change when the SLO is renegotiated.
2. observed counts breached cycles from the verdict log using COUNT(*) FILTER (WHERE status = 'BREACHED') — a clean way to conditionally count without a CASE sum. The 30-day filter defines the rolling window.
3. budget_cycles = (1 - slo) * total_cycles = 0.001 * 2880 = 2.88 — you are allowed roughly 2.88 breach-cycles in the window before the SLA is at risk. This is the concrete meaning of a 99.9% SLO.
4. budget_consumed_pct = 100 * breached / budget_cycles = 100 * 12 / 2.88 ≈ 416.7% — you have spent more than four times your entire monthly error budget. The number being over 100% is the alarm.
5. burn_state tiers the situation: over budget → EXHAUSTED (freeze risky changes, this is a real problem); over half → FAST_BURN (investigate); otherwise HEALTHY. This state, not a single breach, is what should drive escalation.

Output.

breached_cycles	total_cycles	budget_cycles	budget_consumed_pct	burn_state
12	2880	3	416.7	EXHAUSTED

Rule of thumb. Alert on burn rate, not on individual breaches. A single 15-minute breach at 3 a.m. is noise; consuming 400% of your monthly error budget is an incident. The error budget is the abstraction that lets you tell them apart.

Worked example — deriving the budget backwards from the consumer SLA

Detailed explanation. The most common budget mistake is setting the SLO from the pipeline's current latency ("we usually load in 20 minutes, so the SLO is 30"). The senior method runs the other direction: start from the consumer's decision deadline and subtract the downstream processing time to get the ingestion budget. Walk the derivation for a finance close.

• The decision. Finance close must be submitted by 08:00.
• Downstream. Close job takes 90 minutes; reconciliation takes 30 minutes before that.
• Therefore. The warehouse table must be complete no later than 08:00 - 90m - 30m = 06:00.

Question. Derive the freshness budget (a wall-clock deadline) for the fct_ledger table from the finance close SLA, and express the SLI that checks it.

Input.

step	duration	latest finish
Close submission (SLA)	—	08:00
Close job	90 min	08:00
Reconciliation	30 min	06:30
Warehouse must be complete by	—	06:00

Code.

-- Deadline SLI: is fct_ledger complete-and-fresh by its derived 06:00 deadline?
-- "Complete" = the day's partition is fully loaded (loader wrote its heartbeat)
-- AND the newest event is from the correct business date.
WITH target AS (
    SELECT DATE_TRUNC('day', CURRENT_TIMESTAMP())
             + INTERVAL '6 hours'                    AS deadline_ts,     -- 06:00 local
           CURRENT_DATE - 1                          AS business_date    -- yesterday's ledger
)
SELECT
    w.table_name,
    w.max_event_time,
    w.load_complete_at,
    t.deadline_ts,
    CASE
        WHEN w.load_complete_at IS NULL                       THEN 'MISSING'
        WHEN w.max_event_time::DATE < t.business_date         THEN 'INCOMPLETE'   -- wrong business day
        WHEN w.load_complete_at <= t.deadline_ts              THEN 'ON_TIME'
        ELSE 'LATE'
    END AS deadline_status
FROM meta.table_watermark w CROSS JOIN target t
WHERE w.table_name = 'fct_ledger';

Step-by-step explanation.

1. The derivation subtracts every downstream step from the SLA deadline: 08:00 - 90m (close) - 30m (recon) = 06:00. The budget is the 06:00 deadline, and it exists only because we walked backwards from the business decision.
2. The SLI is a deadline SLI, not a lag SLI, because the finance consumer cares about a wall-clock time, not a rolling staleness. The target CTE computes today's 06:00 and yesterday's business date.
3. load_complete_at IS NULL → MISSING: the loader never signalled completion. This is the heartbeat/liveness dimension bleeding into the freshness check — completeness is part of freshness for batch tables.
4. max_event_time::DATE < business_date → INCOMPLETE: the loader ran but the newest event is from the wrong day, meaning yesterday's ledger never fully landed. A pure "did it load" check would miss this.
5. Only when the load completed and on the right business date and before 06:00 is the verdict ON_TIME. Anything else is LATE or worse, and the finance owner is notified with margin to react before the 08:00 SLA.

Output.

table_name	max_event_time	load_complete_at	deadline_ts	deadline_status
fct_ledger	2026-08-17 23:59	2026-08-18 05:42	2026-08-18 06:00	ON_TIME

Rule of thumb. Derive every budget backwards from the consumer's decision, subtracting downstream processing time. A budget set forward from pipeline capability optimises the wrong thing — it protects the pipeline's convenience, not the consumer's decision.

Senior interview question on freshness budgets and SLOs

A senior interviewer might ask: "Design the freshness SLO and error-budget policy for a table that feeds both an hourly executive dashboard and a real-time fraud model. Show the SLI you'd measure, how you'd set two different budgets, how you'd compute error-budget burn over a rolling window, and when that burn would freeze deploys."

Solution Using a dual-consumer SLI with per-consumer budgets and a burn-rate policy

-- 1. Two budgets for one table, keyed by consumer.
INSERT INTO meta.freshness_budget (table_name, consumer, sli_kind, budget_minutes, slo, tier) VALUES
  ('fct_orders', 'fraud',     'event_time', 5,  0.999,  'critical'),
  ('fct_orders', 'dashboard', 'ingestion',  60, 0.99,   'standard');

-- 2. Per-cycle SLI + verdict, written to the verdict log every 15 minutes.
INSERT INTO meta.freshness_verdict_log (cycle_ts, table_name, consumer, lag_min, status)
SELECT
    CURRENT_TIMESTAMP()                                   AS cycle_ts,
    b.table_name,
    b.consumer,
    CASE b.sli_kind
        WHEN 'event_time' THEN DATEDIFF('minute', w.max_event_time, CURRENT_TIMESTAMP())
        WHEN 'ingestion'  THEN DATEDIFF('minute', w.max_loaded_at,  CURRENT_TIMESTAMP())
    END                                                   AS lag_min,
    CASE
        WHEN (CASE b.sli_kind
                WHEN 'event_time' THEN DATEDIFF('minute', w.max_event_time, CURRENT_TIMESTAMP())
                WHEN 'ingestion'  THEN DATEDIFF('minute', w.max_loaded_at,  CURRENT_TIMESTAMP())
              END) > b.budget_minutes
        THEN 'BREACHED' ELSE 'IN_BUDGET'
    END                                                   AS status
FROM meta.freshness_budget b
JOIN meta.table_watermark  w USING (table_name)
WHERE b.table_name = 'fct_orders';

-- 3. Burn-rate policy over a rolling 30-day window, per consumer.
WITH cfg AS (
    SELECT consumer, slo,
           CASE consumer WHEN 'fraud' THEN 2880 ELSE 720 END AS window_cycles  -- 15m vs hourly
    FROM meta.freshness_budget WHERE table_name = 'fct_orders'
),
obs AS (
    SELECT consumer,
           COUNT(*) FILTER (WHERE status = 'BREACHED') AS breached
    FROM   meta.freshness_verdict_log
    WHERE  table_name = 'fct_orders'
      AND  cycle_ts >= CURRENT_TIMESTAMP() - INTERVAL '30 days'
    GROUP BY consumer
)
SELECT
    c.consumer,
    o.breached,
    ROUND((1 - c.slo) * c.window_cycles, 2)                       AS budget_cycles,
    ROUND(100.0 * o.breached / NULLIF((1 - c.slo) * c.window_cycles, 0), 1) AS consumed_pct,
    CASE
        WHEN o.breached > (1 - c.slo) * c.window_cycles          THEN 'FREEZE_DEPLOYS'
        WHEN o.breached > 0.5 * (1 - c.slo) * c.window_cycles     THEN 'INVESTIGATE'
        ELSE 'OK'
    END                                                          AS action
FROM cfg c JOIN obs o USING (consumer)
ORDER BY consumed_pct DESC;

Step-by-step trace.

Input verdict log over 30 days for fct_orders (aggregated):

consumer	slo	window_cycles	breached
fraud	0.999	2880	5
dashboard	0.99	720	3

1. Fraud budget = (1 - 0.999) * 2880 = 2.88 breach-cycles allowed; observed 5 → consumed_pct = 100 * 5 / 2.88 ≈ 173.6%.
2. Fraud is over 100% → FREEZE_DEPLOYS: the critical consumer has blown its error budget; no risky changes until it recovers.
3. Dashboard budget = (1 - 0.99) * 720 = 7.2 breach-cycles allowed; observed 3 → consumed_pct = 100 * 3 / 7.2 ≈ 41.7%.
4. Dashboard is under 50% → OK: three hourly breaches this month are well within a looser standard-tier budget.
5. The same table, same window, produces two very different actions because the budgets and windows differ per consumer — FREEZE_DEPLOYS for fraud, OK for the dashboard. Ordering by consumed_pct DESC puts the actionable consumer on top.

Output:

consumer	breached	budget_cycles	consumed_pct	action
fraud	5	2.88	173.6	FREEZE_DEPLOYS
dashboard	3	7.20	41.7	OK

Why this works — concept by concept:

• Per-consumer budget rows — the same physical table carries two budgets keyed by consumer, so the strict fraud SLO and the relaxed dashboard SLO coexist without contradiction. Freshness policy lives in data, not in scattered alert configs.
• Verdict log — appending one row per cycle per consumer turns freshness into a time series you can aggregate, report, and compute burn from. Without the log there is no error budget, only instantaneous verdicts.
• Error budget = 1 - SLO — the 99.9% fraud SLO yields 2.88 allowed breach-cycles; the 99% dashboard SLO yields 7.2. The tighter the SLO, the smaller the budget, the sooner burn triggers action.
• Burn-rate action ladder — FREEZE_DEPLOYS / INVESTIGATE / OK maps consumed budget to an operational response. Tying deploy freezes to budget exhaustion is the SRE discipline that keeps a critical consumer's freshness defended.
• Cost — one appended verdict row per cycle per consumer (O(consumers) writes/cycle) and an O(window) aggregation for the burn query. Cheap enough to run on every cycle and cheap enough to keep 30–90 days of history for reporting.

SQL
Topic — sql
SQL SLO and rolling-window aggregation problems

Practice →

Data Validation Topic — data-validation Data validation problems on freshness budgets

Practice →

---

3. Heartbeats and liveness signals

A heartbeat proves the pipeline is alive when there is no new data — the one signal a freshness threshold can never provide

The one-sentence invariant: a heartbeat is a signal the pipeline emits every run regardless of whether it processed any rows, so that a dead-man's switch can distinguish "there is legitimately no new data" from "the pipeline stopped running," which is the exact failure a max-timestamp freshness check is structurally blind to. The freshness SLI from section 2 answers "how old is the newest data"; it cannot answer "is the job that produces this data still alive," because a job that dies while holding recent-enough data leaves the freshness lag looking fine right up until the data ages past the budget hours later. The heartbeat closes that gap by measuring liveness directly.

The silent-failure problem — why a threshold is not enough.

• The dead-but-fresh trap. A loader crashes at 00:00 after a successful 23:45 run. At 00:30 the freshness lag is 45 minutes; if the budget is 60, everything looks healthy. Only at 01:00 does the lag cross the budget — an hour of blind spot during which the pipeline was already dead.
• The zero-row trap. A MERGE runs green every 15 minutes but its source view silently returns empty (a broken upstream join). max_loaded_at advances (the job ran), but no new events arrive. Ingestion lag says "fresh"; event-time lag slowly grows; nobody is paged until it breaches.
• The legitimate-quiet trap. At 03:00 there are genuinely no new orders. Event-time lag grows, but nothing is wrong. A naive event-time threshold pages on this false positive. You need a signal that separates "quiet" from "broken" — and that signal is the heartbeat.

The heartbeat pattern — a sentinel the job always writes.

• What it is. A tiny table (pipeline_heartbeat) the job writes one row to at the end of every run — success or no-op — recording pipeline, beat_at, rows_processed, and run_status. The write happens even when rows_processed = 0.
• Why it works. Liveness is now a first-class fact: now() - max(beat_at) per pipeline is the liveness lag, completely independent of the data's age. A dead pipeline stops beating within one cadence; the switch fires.
• The dead-man's switch. A monitor that fires when no heartbeat has arrived within a tolerance of the expected cadence — typically 2 × cadence to absorb one skipped run without flapping. The name is exact: the alert fires when the "hand" is released (the beat stops), the opposite of a threshold that fires when a value crosses a line.
• Cadence awareness. The switch must know each pipeline's expected cadence (every 5 min, hourly, daily at 02:00). A daily job that hasn't beaten in 6 hours is fine; a 5-minute job that hasn't beaten in 6 hours is dead. Store the cadence next to the pipeline.

Absolute freshness vs liveness — you need both.

• Freshness (max event time). Answers "how old is the data." Catches slow drift and late upstream. Blind to a dead job holding fresh data.
• Liveness (heartbeat). Answers "did the job run on schedule." Catches silent death and zero-row runs. Blind to data that is old but still being loaded on time (e.g. upstream is late).
• Together. Freshness + liveness cover the full failure matrix: fresh+alive (healthy), stale+alive (upstream late — investigate source), fresh+dead (the dangerous blind spot the heartbeat catches), stale+dead (obvious outage). Monitoring only one leaves a quadrant uncovered.

Common interview probes on heartbeats.

• "Your freshness alert checks the table updated recently — what does it miss?" — a dead pipeline holding recent-enough data; you need a heartbeat.
• "How do you tell 'no new data' from 'pipeline down'?" — a heartbeat the job writes even on zero-row runs; liveness lag is independent of data age.
• "How do you set the dead-man's-switch timeout?" — 2 × cadence (or cadence + one grace run) so a single skipped run doesn't flap.
• "A cron job silently stopped firing — how would you know?" — the heartbeat stops; the switch fires on the missing beat.

Worked example — heartbeat table and a liveness monitor

Detailed explanation. The canonical heartbeat setup: a pipeline_heartbeat table, a one-line write at the end of every run, and a monitor that computes liveness lag against each pipeline's registered cadence. Build all three.

• Table. pipeline_heartbeat(pipeline, beat_at, rows_processed, run_status).
• Write. End of every run, unconditionally — including no-op runs with rows_processed = 0.
• Monitor. now() - max(beat_at) per pipeline, compared to 2 × cadence_minutes.

Question. Write the heartbeat DDL, the end-of-run insert, and the liveness monitor query.

Input.

pipeline	cadence_minutes	last beat_at	now
orders_loader	15	09:58	10:00
clicks_loader	5	09:41	10:00
ledger_daily	1440	02:05	10:00

Code.

-- 1. Heartbeat + cadence registry
CREATE TABLE meta.pipeline_heartbeat (
    pipeline        TEXT        NOT NULL,
    beat_at         TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
    rows_processed  BIGINT      NOT NULL,
    run_status      TEXT        NOT NULL          -- 'ok' | 'no_op' | 'error'
);
CREATE INDEX idx_hb_pipeline_beat ON meta.pipeline_heartbeat (pipeline, beat_at DESC);

CREATE TABLE meta.pipeline_cadence (
    pipeline         TEXT PRIMARY KEY,
    cadence_minutes  INT  NOT NULL,
    owner            TEXT NOT NULL
);

-- 2. End-of-run write — ALWAYS, even when zero rows were processed
--    (call this from the loader's finally-block)
INSERT INTO meta.pipeline_heartbeat (pipeline, rows_processed, run_status)
VALUES ('orders_loader', :rows_processed,
        CASE WHEN :rows_processed = 0 THEN 'no_op' ELSE 'ok' END);

-- 3. Liveness monitor — dead-man's switch at 2x cadence
WITH latest AS (
    SELECT pipeline, MAX(beat_at) AS last_beat
    FROM   meta.pipeline_heartbeat
    GROUP  BY pipeline
)
SELECT
    c.pipeline,
    c.owner,
    l.last_beat,
    EXTRACT(EPOCH FROM (clock_timestamp() - l.last_beat)) / 60.0 AS liveness_lag_min,
    c.cadence_minutes,
    CASE
        WHEN l.last_beat IS NULL                                          THEN 'NEVER_RAN'
        WHEN clock_timestamp() - l.last_beat
             > (c.cadence_minutes * 2) * INTERVAL '1 minute'             THEN 'DOWN'
        WHEN clock_timestamp() - l.last_beat
             > c.cadence_minutes * INTERVAL '1 minute'                   THEN 'LATE'
        ELSE 'ALIVE'
    END AS liveness_status
FROM meta.pipeline_cadence c
LEFT JOIN latest l USING (pipeline)
ORDER BY liveness_lag_min DESC NULLS FIRST;

Step-by-step explanation.

1. The heartbeat table is deliberately tiny — four columns — because it is written on every run and read on every monitor tick; keeping it small keeps both cheap. The (pipeline, beat_at DESC) index makes MAX(beat_at) per pipeline an index-only lookup.
2. The end-of-run insert is the crux: it runs in the loader's finally block so it fires whether the run processed 10,000 rows or zero. Recording run_status = 'no_op' on empty runs is what lets the monitor distinguish quiet from dead — the job proved it is alive even with nothing to do.
3. The monitor's latest CTE reduces the heartbeat log to one row per pipeline (MAX(beat_at)). This is the liveness watermark; everything downstream compares against it.
4. liveness_lag_min is now - last_beat in minutes — completely independent of the data's age. A pipeline can have a 3-hour event-time lag (upstream is quiet) and a 2-minute liveness lag (it is beating perfectly), and the monitor correctly reports it ALIVE.
5. The CASE ladder is cadence-aware: DOWN at 2 × cadence, LATE at 1 × cadence, NEVER_RAN when there is no beat at all (a LEFT JOIN NULL — a registered pipeline that never wrote a heartbeat, often a deploy that never started).

Output.

pipeline	liveness_lag_min	cadence_minutes	liveness_status
clicks_loader	19.0	5	DOWN
orders_loader	2.0	15	ALIVE
ledger_daily	475.0	1440	ALIVE

Rule of thumb. Write the heartbeat unconditionally at the end of every run, including no-op runs, and size the dead-man's switch at 2 × cadence. clicks_loader at 19 minutes on a 5-minute cadence is DOWN; ledger_daily at 475 minutes on a daily cadence is perfectly ALIVE — cadence is what makes the switch correct.

Worked example — distinguishing "no new data" from "pipeline down"

Detailed explanation. The subtle case is a pipeline that is beating (alive) but not advancing event time (no new data). Is that a broken upstream, or a genuinely quiet period? The answer lives in the combination of liveness and freshness plus the rows_processed history. Build a 2×2 classifier.

• Alive + fresh. Healthy.
• Alive + stale, recent no-op runs. Genuinely quiet upstream — do not page.
• Alive + stale, recent runs had rows but event time stuck. Upstream is emitting stale data — investigate source.
• Dead (no heartbeat). Pipeline down — page.

Question. Write a classifier that combines liveness and freshness to label each pipeline's true state, so on-call is only paged for real failures.

Input.

pipeline	liveness	event_lag_min	budget_min	last 3 runs rows
orders_loader	ALIVE	8	30	120, 90, 140
clicks_loader	DOWN	22	10	0, 0, 0
feed_loader	ALIVE	95	60	0, 0, 0
price_loader	ALIVE	180	60	500, 480, 0

Code.

-- Combine liveness (heartbeat) + freshness (event lag) + recent volume
WITH recent AS (
    SELECT pipeline,
           SUM(rows_processed) FILTER (
               WHERE beat_at >= clock_timestamp() - INTERVAL '45 minutes') AS rows_45m
    FROM   meta.pipeline_heartbeat
    GROUP  BY pipeline
)
SELECT
    m.pipeline,
    m.liveness_status,
    f.event_lag_min,
    b.budget_minutes,
    r.rows_45m,
    CASE
        WHEN m.liveness_status IN ('DOWN','NEVER_RAN')          THEN 'PAGE: pipeline down'
        WHEN f.event_lag_min <= b.budget_minutes                THEN 'OK'
        WHEN r.rows_45m = 0                                     THEN 'QUIET: no upstream data (no page)'
        ELSE 'INVESTIGATE: upstream emitting stale data'
    END AS true_state
FROM v_liveness       m           -- from the previous monitor
JOIN meta.freshness_sli f  USING (pipeline_table)   -- table's event lag
JOIN meta.freshness_budget b ON b.table_name = m.pipeline_table AND b.sli_kind = 'event_time'
LEFT JOIN recent r USING (pipeline)
ORDER BY (true_state LIKE 'PAGE%') DESC, f.event_lag_min DESC;

Step-by-step explanation.

1. The recent CTE sums rows_processed over the last 45 minutes per pipeline. This is the signal that separates "quiet" from "broken": a pipeline that is alive but has processed zero rows recently is probably genuinely quiet, not failing.
2. The classifier checks liveness first: any DOWN / NEVER_RAN short-circuits to PAGE regardless of freshness, because a dead pipeline is unambiguous. This is why the heartbeat is the top of the decision tree.
3. If the pipeline is alive and within its freshness budget → OK. No further analysis needed; both dimensions are healthy.
4. If alive but stale with rows_45m = 0 → QUIET: the upstream genuinely has nothing to send (3 a.m. lull, a source that batches). This is the false positive a naive event-time threshold would page on — and the classifier correctly suppresses it.
5. If alive but stale and rows were flowing → INVESTIGATE: the pipeline is running and moving data, yet event time is stuck. That points at the source (a clock issue, a stuck partition, a backfill of old data), not the pipeline — a different runbook, routed to a different owner.

Output.

pipeline	liveness_status	event_lag_min	rows_45m	true_state
clicks_loader	DOWN	22	0	PAGE: pipeline down
price_loader	ALIVE	180	980	INVESTIGATE: upstream emitting stale data
feed_loader	ALIVE	95	0	QUIET: no upstream data (no page)
orders_loader	ALIVE	8	350	OK

Rule of thumb. Never page on event-time staleness alone. Combine it with liveness and recent volume: DOWN → page; alive + stale + zero rows → quiet (suppress); alive + stale + rows flowing → investigate the source. This one classifier eliminates the majority of false-positive freshness pages.

Senior interview question on heartbeats and liveness

A senior interviewer might ask: "A stakeholder reports a dashboard was stale for six hours over the weekend, but none of your freshness alerts fired. The pipeline had crashed Friday night with fresh-enough data already loaded. Design the heartbeat and dead-man's switch that would have caught this, handle a pipeline with a legitimate weekend pause, and make sure a single skipped run doesn't flap."

Solution Using a heartbeat table, a cadence-aware dead-man's switch, and a schedule calendar

-- 1. Heartbeat written every run (finally-block); cadence + schedule registry
CREATE TABLE meta.pipeline_heartbeat (
    pipeline       TEXT        NOT NULL,
    beat_at        TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
    rows_processed BIGINT      NOT NULL,
    run_status     TEXT        NOT NULL
);

CREATE TABLE meta.pipeline_schedule (
    pipeline         TEXT PRIMARY KEY,
    cadence_minutes  INT  NOT NULL,
    grace_runs       INT  NOT NULL DEFAULT 1,     -- absorb N skipped runs before firing
    active_days      TEXT NOT NULL DEFAULT 'MON-SUN',  -- e.g. 'MON-FRI' for weekday-only
    owner            TEXT NOT NULL
);

-- 2. Dead-man's switch: fire only when a beat is expected but missing,
--    honouring active_days and grace_runs.
WITH latest AS (
    SELECT pipeline, MAX(beat_at) AS last_beat
    FROM   meta.pipeline_heartbeat
    GROUP  BY pipeline
),
expected AS (
    SELECT s.*,
           -- is a run expected right now? (skip inactive weekend days)
           CASE
             WHEN s.active_days = 'MON-FRI'
                  AND EXTRACT(DOW FROM clock_timestamp()) IN (0, 6) THEN FALSE
             ELSE TRUE
           END AS run_expected_now
    FROM meta.pipeline_schedule s
)
SELECT
    e.pipeline,
    e.owner,
    l.last_beat,
    EXTRACT(EPOCH FROM (clock_timestamp() - l.last_beat)) / 60.0 AS liveness_lag_min,
    (e.cadence_minutes * (e.grace_runs + 1))                    AS tolerance_min,
    CASE
        WHEN NOT e.run_expected_now                                 THEN 'PAUSED_OK'
        WHEN l.last_beat IS NULL                                    THEN 'NEVER_RAN'
        WHEN clock_timestamp() - l.last_beat
             > (e.cadence_minutes * (e.grace_runs + 1)) * INTERVAL '1 minute'
                                                                   THEN 'DOWN'
        ELSE 'ALIVE'
    END AS switch_state
FROM expected e
LEFT JOIN latest l USING (pipeline)
WHERE e.run_expected_now                        -- only evaluate pipelines that should be beating
ORDER BY liveness_lag_min DESC;

# 3. The loader wraps its body so the heartbeat ALWAYS fires
import psycopg2

def run_loader(conn, pipeline: str, body) -> None:
    rows, status = 0, "error"
    try:
        rows = body()                       # do the actual load; returns row count
        status = "no_op" if rows == 0 else "ok"
    finally:
        with conn, conn.cursor() as cur:    # heartbeat commits even if body() raised
            cur.execute(
                "INSERT INTO meta.pipeline_heartbeat(pipeline, rows_processed, run_status) "
                "VALUES (%s, %s, %s)", (pipeline, rows, status))

Step-by-step trace.

Input schedule + heartbeats (assume now = Saturday 10:00, orders_loader last beat Friday 23:45):

pipeline	cadence_min	grace_runs	active_days	last_beat
orders_loader	15	1	MON-SUN	Fri 23:45
weekday_etl	60	1	MON-FRI	Fri 23:00

1. tolerance_min for orders_loader = 15 * (1 + 1) = 30 minutes — one skipped run is absorbed by grace_runs = 1.
2. orders_loader liveness lag = Sat 10:00 - Fri 23:45 ≈ 615 min, far over its 30-min tolerance → DOWN. This is the crash the original freshness alert missed — the heartbeat catches it within 30 minutes of the last beat, not six hours later.
3. weekday_etl has active_days = 'MON-FRI'; EXTRACT(DOW) for Saturday is 6 → run_expected_now = FALSE → PAUSED_OK, and the WHERE run_expected_now clause excludes it entirely. Its 11-hour silence is expected, not an outage.
4. The grace_runs term (grace_runs + 1 multiplier) means a single skipped 15-minute run leaves orders_loader ALIVE at 20 minutes — no flap — but a genuine crash sails past 30 minutes into DOWN.
5. The Python wrapper's finally block guarantees the heartbeat fires whether body() returned rows, returned zero, or raised — so a crash that kills the load still records run_status = 'error', and even a silent hang is caught by the absence of the next beat.

Output:

pipeline	liveness_lag_min	tolerance_min	switch_state
orders_loader	615.0	30	DOWN
weekday_etl	(excluded)	120	PAUSED_OK

Why this works — concept by concept:

• Heartbeat in the finally-block — the beat is written on success, no-op, and error, so liveness is recorded independently of the data write. This is what turns a silent Friday-night crash into a 30-minute alert instead of a six-hour blind spot.
• Dead-man's switch — the alert fires on the absence of an expected beat, the inverse of a threshold. A dead process cannot emit a "healthy" signal, so absence is the only reliable death detector.
• Cadence + grace runs — sizing the tolerance at cadence × (grace_runs + 1) absorbs one skipped run without flapping while still catching a real crash within one extra cadence. Flap-free liveness is what keeps the switch trusted.
• active_days schedule — honouring MON-FRI and excluding weekend evaluation prevents the classic false page on a legitimately paused pipeline. Liveness must be schedule-aware or it cries wolf every weekend.
• Cost — one tiny insert per run (O(1)) and an O(pipelines) monitor query per tick. The heartbeat table is trivially small and trivially cheap; it is the highest-leverage freshness signal per byte you will ever add.

SQL
Topic — sql
SQL heartbeat and liveness-monitor problems

Practice →

ETL Topic — etl ETL problems on dead-man's-switch monitoring

Practice →

---

4. Anomaly detection on freshness and volume

anomaly detection replaces the static threshold that pages on every seasonal peak with a baseline that learns the pipeline's normal rhythm

The one-sentence invariant: anomaly detection sets the alert boundary from a rolling, robust, seasonality-aware baseline of the metric's own history rather than a fixed number, so it flags a genuine deviation — a load latency spike, a volume collapse — without paging on the predictable peaks and lulls that a static threshold cannot distinguish from failures. A static threshold is the right first tool and the wrong last one: "alert if latency > 30 min" pages every Monday when the weekend backlog drains, and stays silent when a table that normally loads 10 million rows quietly loads 200,000 because 200,000 is still "above zero." Anomaly detection asks a better question — is this value unusual for this metric, at this time, given its own recent behaviour — and that question catches the failures a threshold misses.

Why static thresholds fail — and where they still belong.

• Seasonality breaks them. Traffic, and therefore load latency and volume, follows hour-of-day and day-of-week patterns. A threshold tuned for the trough over-pages at the peak; one tuned for the peak is blind at the trough.
• They can't catch relative drops. A volume threshold of "> 0 rows" never fires when a 10M-row load shrinks to 200K. The failure is relative to normal, which a fixed floor cannot express.
• They still belong as a floor/ceiling. Keep a static hard limit as a safety net (e.g. "latency > the SLA budget is always an alert" and "zero rows for a normally-busy table is always an alert"). Anomaly detection sits on top to catch the subtler deviations; it does not replace the budget breach from section 2.

The rolling baseline — a moving definition of "normal".

• Rolling mean + standard deviation (z-score). z = (x - mean) / std over a trailing window (e.g. last 30 same-hour observations). Flag |z| > 3. Simple, effective for roughly-normal metrics, but the mean and std are themselves distorted by the outliers you are hunting.
• Median + MAD (robust). MAD = median(|x - median|); robust_z = 0.6745 * (x - median) / MAD. Median and MAD are barely moved by a single huge spike, so the detector stays sensitive even after an outlier enters the window. Prefer MAD for spiky pipeline metrics.
• Seasonal buckets. Compute the baseline within a bucket — same hour-of-day, same day-of-week — so the "normal" for Monday 09:00 is compared against prior Monday 09:00s, not against 3 a.m. Sundays. This is the single biggest false-positive killer.
• Window size trade-off. Short windows react fast but are noisy; long windows are stable but slow to adapt to a real regime change. 4–8 weeks of same-bucket history is a common sweet spot for daily/hourly pipelines.

Volume as a leading indicator of freshness.

• Why volume moves first. When an upstream source degrades, row volume usually drops before freshness breaches — the loader is still running (liveness OK) and recently loaded (ingestion OK), but it loaded far fewer rows than normal. Volume anomaly is the early warning; the freshness breach is the confirmation an hour later.
• Row-count and distribution. Watch row_count per load, and optionally the distribution of a key column (a sudden null-rate spike, a category that vanished). These are data observability signals adjacent to freshness that frequently co-fire.
• Pair the two. A volume anomaly + a rising event-time lag is a high-confidence "upstream is degrading" signal — far stronger than either alone, and worth a higher severity.

Common interview probes on anomaly detection.

• "Your threshold pages every Monday morning — fix it." — seasonal buckets (same hour-of-day / day-of-week) in a rolling baseline.
• "Why MAD instead of standard deviation?" — MAD is robust; the outliers you hunt don't inflate the baseline and mask the next one.
• "How would you catch a 10M→200K row drop that stays above your floor?" — a relative volume anomaly against the rolling baseline, not a fixed floor.
• "Volume vs freshness — which fires first?" — volume, usually; it is the leading indicator of an upstream degradation.

Worked example — rolling z-score on load latency

Detailed explanation. The starting detector: a rolling z-score on per-load latency. Compute the trailing mean and standard deviation over a window of prior loads, then flag any load whose z-score exceeds a threshold. It is the simplest useful anomaly detector and the baseline every other method is compared against.

• Metric. load_latency_min per 15-minute load.
• Window. Trailing 20 loads (excluding the current one).
• Rule. Flag when z > 3.

Question. Write a windowed SQL query that computes the rolling z-score of load latency and flags anomalies.

Input.

load_ts	latency_min
09:00	12
09:15	11
09:30	13
... (steady ~12)	...
09:45	12
10:00	47

Code.

-- Rolling z-score on load latency (window = 20 prior loads, current row excluded)
WITH stats AS (
    SELECT
        load_ts,
        latency_min,
        AVG(latency_min) OVER w  AS roll_mean,
        STDDEV_SAMP(latency_min) OVER w AS roll_std
    FROM   meta.load_latency
    WINDOW w AS (
        ORDER BY load_ts
        ROWS BETWEEN 20 PRECEDING AND 1 PRECEDING   -- exclude the current row
    )
)
SELECT
    load_ts,
    latency_min,
    ROUND(roll_mean, 1)                                   AS baseline,
    ROUND((latency_min - roll_mean) / NULLIF(roll_std, 0), 2) AS z_score,
    CASE
        WHEN roll_std IS NULL                             THEN 'WARMUP'
        WHEN (latency_min - roll_mean) / NULLIF(roll_std, 0) > 3 THEN 'ANOMALY'
        ELSE 'NORMAL'
    END AS verdict
FROM stats
ORDER BY load_ts DESC
LIMIT 5;

Step-by-step explanation.

1. The named WINDOW w frames the trailing 20 loads with ROWS BETWEEN 20 PRECEDING AND 1 PRECEDING — crucially ending at 1 PRECEDING so the current load is excluded from its own baseline. Including it would let a huge spike inflate the very mean it is being compared against.
2. AVG(...) OVER w and STDDEV_SAMP(...) OVER w compute the rolling mean and sample standard deviation of the baseline window. These define "normal" as of just before the current load.
3. The z-score (latency_min - roll_mean) / roll_std measures how many standard deviations the current latency sits above the baseline. NULLIF(roll_std, 0) guards the flat-window divide-by-zero (a perfectly steady metric has zero variance).
4. The WARMUP branch handles the first ~20 rows where the window isn't full and roll_std is NULL — you cannot flag anomalies before you have a baseline. Emitting WARMUP (not NORMAL) makes the cold-start explicit.
5. For the 10:00 load: baseline ≈ 12, std ≈ 1, so z = (47 - 12) / 1 = 35, far over 3 → ANOMALY. The steady ~12-minute history makes the 47-minute spike unmistakable.

Output.

load_ts	latency_min	baseline	z_score	verdict
10:00	47	12.1	34.90	ANOMALY
09:45	12	12.0	0.00	NORMAL
09:30	13	11.9	0.92	NORMAL
09:15	11	11.8	-0.73	NORMAL
09:00	12	11.9	0.10	NORMAL

Rule of thumb. Always exclude the current observation from its own baseline window (ROWS ... 1 PRECEDING), and emit an explicit WARMUP state until the window is full. A z-score detector that includes the current point in its baseline is a detector that hides its own anomalies.

Worked example — MAD-based robust detection

Detailed explanation. The z-score's weakness is that one large outlier inflates both the mean and the standard deviation, desensitising the detector to the next outlier. The robust fix is the median and the median absolute deviation (MAD), which a single spike barely moves. Rebuild the detector on MAD.

• Baseline. Rolling median and MAD over the same window.
• Robust z. 0.6745 * (x - median) / MAD (the 0.6745 makes it comparable to a normal z under Gaussian data).
• Rule. Flag robust_z > 3.5.

Question. Compute a MAD-based robust anomaly score and contrast it with the plain z-score when an outlier is already in the window.

Input.

load_ts	latency_min	note
prior 19 loads	~12	steady
09:45	60	earlier spike, now in the window
10:00	45	current load to score

Code.

-- MAD-based robust detector (median + median-absolute-deviation)
WITH win AS (   -- baseline window for the 10:00 load: prior 20 loads incl. the 60 spike
    SELECT latency_min
    FROM   meta.load_latency
    WHERE  load_ts <  TIMESTAMP '2026-08-18 10:00'
    ORDER  BY load_ts DESC
    LIMIT  20
),
med AS (
    SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY latency_min) AS median_lat
    FROM   win
),
mad AS (
    SELECT m.median_lat,
           PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ABS(w.latency_min - m.median_lat)) AS mad_lat
    FROM   win w CROSS JOIN med m
    GROUP  BY m.median_lat
)
SELECT
    45                                          AS current_latency,
    ROUND(mad.median_lat, 1)                    AS robust_baseline,
    ROUND(mad.mad_lat, 2)                        AS mad,
    ROUND(0.6745 * (45 - mad.median_lat)
          / NULLIF(mad.mad_lat, 0), 2)           AS robust_z,
    CASE WHEN 0.6745 * (45 - mad.median_lat) / NULLIF(mad.mad_lat, 0) > 3.5
         THEN 'ANOMALY' ELSE 'NORMAL' END        AS verdict
FROM mad;

Step-by-step explanation.

1. The win CTE grabs the 20-load baseline for the 10:00 point — and it includes the earlier 60-minute spike from 09:45, which is exactly the situation that breaks a plain z-score.
2. med computes the median with PERCENTILE_CONT(0.5). With nineteen ~12s and one 60, the median is still ~12 — the spike barely moves it, because the median cares about rank, not magnitude.
3. mad computes the median of the absolute deviations from that median. Again the single 60 contributes one large deviation but does not dominate the median deviation, so mad_lat stays small (~1–2).
4. robust_z = 0.6745 * (45 - 12) / mad. With a small MAD, the current 45-minute load scores a large robust z (well over 3.5) → ANOMALY. Contrast the plain z-score: the 60 spike would have inflated the mean toward ~14 and the std toward ~11, giving z = (45 - 14) / 11 ≈ 2.8, under the threshold — a missed anomaly.
5. The lesson is quantitative: the same 45-minute load is ANOMALY under MAD and NORMAL under a contaminated z-score. For spiky pipeline metrics where outliers cluster, MAD keeps the detector honest.

Output.

current_latency	robust_baseline	mad	robust_z	verdict
45	12.0	1.48	15.04	ANOMALY

Rule of thumb. Use median + MAD, not mean + std, whenever the metric is spiky or outliers can enter the baseline window. MAD is robust to ~50% contamination; a single earlier spike that would blind a z-score leaves a MAD detector fully sensitive.

Worked example — a seasonality-aware baseline

Detailed explanation. The final refinement: bucket the baseline by season so "normal for Monday 09:00" is learned from prior Monday 09:00s. This kills the false positives from predictable peaks and troughs that even a robust rolling detector flags when the window straddles a regime change.

• Bucket. (day_of_week, hour_of_day).
• Baseline. Robust stats within the bucket over the last 8 weeks.
• Rule. Score the current observation against its own bucket's baseline.

Question. Write a seasonality-aware detector that scores today's 09:00 row-count against prior same-bucket history.

Input.

bucket (dow, hour)	prior 8 same-bucket counts	today's count
(Mon, 09)	~10.0M steady	2.1M
(Sun, 03)	~0.3M steady	0.35M

Code.

-- Seasonality-aware volume anomaly: compare to same (dow, hour) bucket history
WITH hist AS (
    SELECT
        EXTRACT(DOW  FROM load_ts) AS dow,
        EXTRACT(HOUR FROM load_ts) AS hod,
        row_count
    FROM meta.load_volume
    WHERE load_ts >= CURRENT_TIMESTAMP() - INTERVAL '56 days'   -- 8 weeks
),
med AS (
    SELECT dow, hod,
           PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY row_count) AS med_cnt
    FROM hist GROUP BY dow, hod
),
baseline AS (
    SELECT h.dow, h.hod, m.med_cnt,
           PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ABS(h.row_count - m.med_cnt)) AS mad_cnt
    FROM hist h JOIN med m USING (dow, hod)
    GROUP BY h.dow, h.hod, m.med_cnt
),
current_load AS (
    SELECT EXTRACT(DOW FROM load_ts) AS dow, EXTRACT(HOUR FROM load_ts) AS hod, row_count
    FROM   meta.load_volume
    WHERE  load_ts = DATE_TRUNC('hour', CURRENT_TIMESTAMP())
)
SELECT
    c.dow, c.hod, c.row_count,
    ROUND(b.med_cnt)                              AS bucket_baseline,
    ROUND(0.6745 * (c.row_count - b.med_cnt) / NULLIF(b.mad_cnt, 0), 2) AS robust_z,
    CASE WHEN ABS(0.6745 * (c.row_count - b.med_cnt) / NULLIF(b.mad_cnt, 0)) > 3.5
         THEN 'ANOMALY' ELSE 'NORMAL' END          AS verdict
FROM current_load c
JOIN baseline b USING (dow, hod);

Step-by-step explanation.

1. hist pulls 8 weeks of loads and tags each with its (dow, hour) bucket. Eight weeks gives ~8 observations per hourly bucket — enough for a rough robust baseline without reaching so far back that a genuine regime change contaminates it.
2. med then baseline compute the median and MAD of row_count within each bucket. Monday-09:00's baseline is built only from prior Monday-09:00 loads, so its ~10M normal is never compared against a 0.3M Sunday-03:00 trough.
3. current_load isolates the load for the current hour and tags it with its bucket, ready to be scored against the matching baseline.
4. The join on (dow, hod) compares the current load to its own bucket's history. Today's Monday-09:00 count of 2.1M against a 10M baseline yields a large negative robust z → ANOMALY (a volume collapse), even though 2.1M is a perfectly normal count for a busy hour and would sail past any fixed floor.
5. The Sunday-03:00 load of 0.35M against a 0.3M baseline yields a small robust z → NORMAL. A non-seasonal detector, seeing 0.35M as "low," might have flagged it; the seasonal bucket correctly recognises it as a normal quiet hour.

Output.

dow	hod	row_count	bucket_baseline	robust_z	verdict
1	9	2,100,000	10,000,000	-53.28	ANOMALY
0	3	350,000	300,000	1.12	NORMAL

Rule of thumb. Bucket the baseline by (day_of_week, hour_of_day) before you compute any anomaly score. Seasonality is the number-one source of false positives; comparing like-for-like buckets removes it and lets a volume collapse stand out even when the absolute number looks ordinary.

Senior interview question on anomaly detection

A senior interviewer might ask: "Your freshness threshold pages every Monday morning and misses a volume collapse where a 10-million-row load quietly dropped to 200,000. Design an anomaly detector that fixes both: no false positives on the Monday peak, and a caught anomaly on the relative volume drop. Cover the baseline, the robustness choice, seasonality, and how volume anomalies relate to freshness breaches."

Solution Using a rolling MAD detector with seasonal buckets and volume-as-leading-indicator

-- 1. One metrics table stores per-load latency AND row_count
--    (freshness signal + volume signal); the detector reads its own history.
CREATE TABLE meta.load_metrics (
    table_name    TEXT        NOT NULL,
    load_ts       TIMESTAMPTZ NOT NULL,
    latency_min   NUMERIC     NOT NULL,
    row_count     BIGINT      NOT NULL,
    PRIMARY KEY (table_name, load_ts)
);

-- 2. Seasonal robust baseline per (table, dow, hour) over 8 weeks.
CREATE OR REPLACE VIEW meta.seasonal_baseline AS
WITH h AS (
    SELECT table_name,
           EXTRACT(DOW  FROM load_ts) AS dow,
           EXTRACT(HOUR FROM load_ts) AS hod,
           latency_min, row_count
    FROM   meta.load_metrics
    WHERE  load_ts >= CURRENT_TIMESTAMP() - INTERVAL '56 days'
)
SELECT table_name, dow, hod,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY row_count)   AS med_rows,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY latency_min) AS med_lat
FROM h GROUP BY table_name, dow, hod;

-- 3. Score the latest load: latency anomaly AND volume anomaly, combined.
WITH cur AS (
    SELECT table_name, load_ts, latency_min, row_count,
           EXTRACT(DOW FROM load_ts) AS dow, EXTRACT(HOUR FROM load_ts) AS hod
    FROM   meta.load_metrics
    WHERE  (table_name, load_ts) IN (
             SELECT table_name, MAX(load_ts) FROM meta.load_metrics GROUP BY table_name)
),
dev AS (   -- relative deviations from the seasonal baseline
    SELECT c.table_name, c.load_ts, c.latency_min, c.row_count, b.med_rows, b.med_lat,
           (c.row_count   - b.med_rows) / NULLIF(b.med_rows, 0) AS rows_rel_dev,
           (c.latency_min - b.med_lat)  / NULLIF(b.med_lat, 0)  AS lat_rel_dev
    FROM cur c JOIN meta.seasonal_baseline b USING (table_name, dow, hod)
)
SELECT
    table_name,
    ROUND(100 * rows_rel_dev, 1)  AS volume_pct_delta,
    ROUND(100 * lat_rel_dev, 1)   AS latency_pct_delta,
    CASE
        WHEN rows_rel_dev < -0.5 AND lat_rel_dev > 0.5 THEN 'HIGH: volume drop + latency spike'
        WHEN rows_rel_dev < -0.5                       THEN 'MED: volume drop (leading indicator)'
        WHEN lat_rel_dev  >  0.5                        THEN 'MED: latency spike'
        ELSE 'OK'
    END AS anomaly_state
FROM dev
ORDER BY (anomaly_state <> 'OK') DESC, volume_pct_delta;

Step-by-step trace.

Input latest loads scored against their seasonal baselines:

table_name	dow, hod	row_count	med_rows	latency_min	med_lat
fct_orders	Mon, 09	2,000,000	10,000,000	14	12
fct_clicks	Mon, 09	9,800,000	10,000,000	40	12
dim_geo	Sun, 03	320,000	300,000	11	10

1. fct_orders: rows_rel_dev = (2M - 10M)/10M = -0.8 (an 80% volume drop) and lat_rel_dev = (14-12)/12 ≈ 0.17. Volume drop < -0.5 but latency not spiking → MED: volume drop (leading indicator). Caught despite 2M being far above any fixed floor.
2. fct_clicks: rows_rel_dev = -0.02 (normal volume) but lat_rel_dev = (40-12)/12 ≈ 2.33 (a latency spike) → MED: latency spike. The Monday-09:00 seasonal baseline of 12 minutes makes the 40-minute load stand out — and because the baseline is seasonal, the normal Monday peak itself does not trip.
3. dim_geo: rows_rel_dev = (0.32M-0.30M)/0.30M ≈ +0.07 and lat_rel_dev ≈ 0.10 → OK. The Sunday-03:00 quiet hour is compared to its own bucket, so a small count is correctly normal — no Monday-morning-style false positive.
4. Ordering surfaces the two anomalies above the healthy row and, within anomalies, the biggest volume drop first — so on-call sees fct_orders' 80% collapse at the top.
5. Had fct_orders shown both a volume drop and a latency spike, it would escalate to HIGH — the co-firing of the leading indicator (volume) and the confirmation (latency) is the highest-confidence "upstream is degrading" signal.

Output:

table_name	volume_pct_delta	latency_pct_delta	anomaly_state
fct_orders	-80.0	16.7	MED: volume drop (leading indicator)
fct_clicks	-2.0	233.3	MED: latency spike
dim_geo	6.7	10.0	OK

Why this works — concept by concept:

• Seasonal (table, dow, hour) baseline — comparing every load to its own hour-of-week history removes the predictable peak/trough that makes static thresholds page on Monday mornings. Like-for-like is the false-positive killer.
• Robust median baseline — using the median (not the mean) as the baseline keeps a prior spike from dragging the "normal" upward and masking the next anomaly. MAD/median is the robustness choice for spiky pipeline metrics.
• Relative deviation, not absolute floor — scoring (x - baseline) / baseline catches a 10M→2M collapse that a > 0 rows floor never would, because the failure is relative to normal, not a crossing of a fixed line.
• Volume as leading indicator — a volume drop typically precedes a freshness breach because the loader is still alive and recent but processing fewer rows; flagging it early buys time before event-time lag confirms the problem. Co-firing with a latency spike escalates to HIGH.
• Cost — the baseline view is an O(history) aggregation refreshed on a schedule (or materialised); scoring the latest load is O(tables). Keep 8 weeks of per-load metrics; the storage is trivial and the seasonal signal is worth far more than the bytes.

Data Validation
Topic — data-validation
Data validation problems on anomaly detection

Practice →

SQL Topic — sql SQL window-function and rolling-statistics problems

Practice →

---

5. Alerting, SLA reporting, and ownership

alerting turns detection into action — dedup the noise, set severity from the error budget, route to an owner, and report compliance

The one-sentence invariant: alerting is the operational layer that decides which freshness breaches become a human interruption, and the senior design deduplicates and groups related breaches, sets severity from the error-budget burn rate rather than from a single crossing, routes each alert to a named owner with a runbook, suppresses during known maintenance, and rolls the whole thing up into a monthly SLA compliance report — because detection without disciplined alerting produces alert fatigue, and a page everyone ignores is functionally the same as no monitoring at all. Every freshness program dies the same way: it starts noisy, on-call learns to mute the channel, and then a real breach scrolls by unread. The fix is not fewer checks — it is fewer, better pages.

Dedup, grouping, and suppression — cutting the noise.

• Deduplication. The same table breaching for six consecutive cycles is one incident, not six pages. Suppress repeat alerts for the same (table, breach_kind) within a window until it resolves or escalates.
• Grouping. When an upstream source fails, twenty downstream tables breach at once. Group them into one alert keyed on the shared root (the source, the run, the schema) rather than firing twenty times. On-call sees "source X is down, 20 tables affected," not twenty separate pages.
• Maintenance suppression. A known backfill or a scheduled migration will breach freshness on purpose. A maintenance window suppresses alerts for the affected tables so planned work doesn't page — and, crucially, does not suppress everything (an unrelated real breach during the window still fires).
• Symptom vs cause. Alert on the cause where you can (the source is down) and suppress the symptoms (the twenty stale tables). Paging on every symptom is the fastest route to alert fatigue.

Severity from burn rate — not from a single breach.

• Warn. Error budget < 50% burned, or a single isolated breach. A ticket, a dashboard flag — no page. Most breaches live here.
• High. Fast burn — the budget is being consumed quickly enough to be exhausted well within the window. A page during business hours; a ticket overnight for non-critical tiers.
• Page. Budget exhausted on a critical-tier table, or a hard SLA about to be missed (finance close deadline approaching with the table still stale). Wake a human.
• The burn-rate window. Compute burn over both a short window (fast burn = imminent) and a long window (slow burn = chronic). A short-window fast burn pages immediately; a long-window slow burn opens a ticket to fix the chronic problem.

Ownership, runbooks, and the freshness dashboard.

• Named owner per table. Every table in the freshness registry has an owner; every alert routes to that owner's on-call, never to a generic firehose channel. Unrouted alerts are ignored alerts.
• Runbook per breach kind. "Loader down" → restart + check the source connection. "Upstream stale" → escalate to the source team. "Volume drop" → check the extract query and source row counts. The runbook link travels with the alert.
• The dashboard. A single freshness dashboard sorted by percent-of-budget (section 2) is the passive counterpart to active alerts — the place stakeholders self-serve "is my data fresh right now" without pinging you.
• The compliance report. A monthly rollup of SLA attainment per table/consumer — "99.4% of cycles in budget, 2 breaches, both during a known incident" — is what turns freshness from an engineering hobby into a reported, defensible service.

Common interview probes on alerting.

• "On-call is drowning in freshness pages — what do you change?" — dedup + group + severity by burn; page only on burn.
• "An upstream source fails and 20 tables go stale — how many alerts fire?" — one, grouped on the root cause; symptoms suppressed.
• "How do you handle a planned backfill that breaks freshness on purpose?" — a maintenance-window suppression scoped to the affected tables only.
• "How do you report freshness SLA compliance to stakeholders?" — a monthly rollup: % cycles in budget, breach count, budget consumed, annotated with incidents.

Worked example — alert deduplication and grouping

Detailed explanation. The core noise-reduction primitive: collapse a stream of raw breach events into a small set of actionable alerts. Dedup repeats for the same table within a window, and group simultaneous breaches that share a root cause into a single alert. Build the query that produces the alert set from the raw breach log.

• Dedup key. (table_name, breach_kind) within a 10-minute window.
• Group key. Shared root_cause (e.g. the upstream source) across tables.
• Output. One alert per group, listing affected tables.

Question. From a raw breach-event log, produce the deduplicated, grouped alert set for the current window.

Input.

breach_ts	table_name	breach_kind	root_cause
10:00	fct_orders	event_lag	source:oltp_repl
10:02	fct_orders	event_lag	source:oltp_repl
10:03	fct_payments	event_lag	source:oltp_repl
10:04	dim_promo	volume_drop	source:promo_api

Code.

-- Dedup within 10 min per (table, kind); then group by shared root cause
WITH deduped AS (
    SELECT DISTINCT ON (table_name, breach_kind)
           table_name, breach_kind, root_cause, breach_ts
    FROM   meta.breach_event
    WHERE  breach_ts >= CURRENT_TIMESTAMP() - INTERVAL '10 minutes'
    ORDER  BY table_name, breach_kind, breach_ts       -- keep the FIRST breach per key
),
grouped AS (
    SELECT
        root_cause,
        MIN(breach_ts)                                  AS first_seen,
        COUNT(*)                                        AS tables_affected,
        STRING_AGG(table_name || ' (' || breach_kind || ')', ', '
                   ORDER BY table_name)                 AS detail
    FROM deduped
    GROUP BY root_cause
)
SELECT
    root_cause,
    first_seen,
    tables_affected,
    detail,
    CASE WHEN tables_affected >= 3 THEN 'GROUPED_INCIDENT' ELSE 'SINGLE' END AS alert_shape
FROM grouped
ORDER BY tables_affected DESC;

Step-by-step explanation.

1. DISTINCT ON (table_name, breach_kind) with ORDER BY ... breach_ts keeps only the first breach event per (table, kind) in the 10-minute window. The two fct_orders event_lag events at 10:00 and 10:02 collapse to one — that is the dedup.
2. The window filter (>= now() - 10 minutes) scopes dedup to a rolling window, so a breach that persists into the next window can re-alert (or escalate) rather than being silently swallowed forever.
3. grouped aggregates the deduped breaches by root_cause. The two OLTP-replication breaches (fct_orders, fct_payments) share source:oltp_repl and collapse into one group; dim_promo has a different root cause and forms its own group.
4. STRING_AGG builds a human-readable detail list of affected tables and kinds, so the single grouped alert carries the full blast radius ("fct_orders (event_lag), fct_payments (event_lag)") without twenty separate pages.
5. alert_shape marks groups of 3+ tables as a GROUPED_INCIDENT — a signal to the router that this is a likely source-level outage warranting one high-signal page rather than per-table noise.

Output.

root_cause	first_seen	tables_affected	detail	alert_shape
source:oltp_repl	10:00	2	fct_orders (event_lag), fct_payments (event_lag)	SINGLE
source:promo_api	10:04	1	dim_promo (volume_drop)	SINGLE

Rule of thumb. Dedup on (table, breach_kind) within a window, then group on root_cause. Four raw breach events became two alerts here; at real scale this collapses hundreds of symptom events into a handful of cause-level alerts — the difference between a usable pager and a muted channel.

Worked example — severity from error-budget burn rate

Detailed explanation. Severity should come from how fast the budget is burning, computed over two windows: a short one (is this an imminent emergency?) and a long one (is this a chronic slow leak?). Fast burn pages; slow burn tickets. Build the multi-window burn classifier.

• Short window. 1 hour — fast burn detection.
• Long window. 30 days — chronic burn detection.
• Rule. Fast burn on a critical table → PAGE; slow burn → TICKET; neither → WARN.

Question. Classify severity for a table from its short-window and long-window error-budget burn.

Input.

metric	value
tier	critical
1h cycles	4
1h breached	4
30d budget cycles	2.88
30d breached	5

Code.

-- Multi-window burn-rate severity
WITH burn AS (
    SELECT
        table_name,
        tier,
        -- short window: fraction of the last hour's cycles that breached
        COUNT(*) FILTER (WHERE status = 'BREACHED'
                         AND cycle_ts >= CURRENT_TIMESTAMP() - INTERVAL '1 hour')::NUMERIC
            / NULLIF(COUNT(*) FILTER (WHERE cycle_ts >= CURRENT_TIMESTAMP() - INTERVAL '1 hour'), 0)
                                                                    AS short_breach_frac,
        -- long window: budget consumed over 30 days
        COUNT(*) FILTER (WHERE status = 'BREACHED'
                         AND cycle_ts >= CURRENT_TIMESTAMP() - INTERVAL '30 days')
            / NULLIF(budget_cycles, 0)                              AS long_budget_consumed
    FROM meta.freshness_verdict_log
    JOIN meta.freshness_budget USING (table_name)
    GROUP BY table_name, tier, budget_cycles
)
SELECT
    table_name, tier,
    ROUND(short_breach_frac, 2)  AS short_breach_frac,
    ROUND(long_budget_consumed, 2) AS long_budget_consumed,
    CASE
        WHEN tier = 'critical' AND short_breach_frac >= 0.75 THEN 'PAGE'
        WHEN long_budget_consumed > 1.0                      THEN 'PAGE'
        WHEN long_budget_consumed > 0.5                      THEN 'TICKET'
        WHEN short_breach_frac    > 0.0                      THEN 'WARN'
        ELSE 'OK'
    END AS severity
FROM burn
ORDER BY long_budget_consumed DESC;

Step-by-step explanation.

1. The short-window fraction counts what share of the last hour's cycles breached. Four breached out of four cycles → 1.0 — a full-hour outage in progress, the definition of a fast burn.
2. The long-window term divides 30-day breached cycles by the allowed budget_cycles. Five breached against a 2.88 budget → ≈1.74 — the monthly budget is already blown, a chronic problem on top of the acute one.
3. The severity CASE checks the critical-tier fast burn first: tier = 'critical' AND short_breach_frac >= 0.75 → PAGE. A critical table breaching most of the last hour wakes a human regardless of the monthly picture.
4. If not an acute critical page, a long-window consumed > 1.0 also pages (the budget is fully spent), > 0.5 opens a ticket (investigate the slow leak), and any nonzero short-window breach is a WARN. The ladder degrades gracefully from page to ticket to warn.
5. This table hits both conditions — acute (short frac 1.0 on a critical tier) and chronic (budget consumed 1.74) — and resolves to PAGE. The two windows agree, which is itself confirmation this is real and worth the interruption.

Output.

table_name	tier	short_breach_frac	long_budget_consumed	severity
fct_orders	critical	1.00	1.74	PAGE

Rule of thumb. Compute burn over a short and a long window. Short-window fast burn catches the acute outage; long-window burn catches the chronic slow leak that never trips a single-window alarm. Page on fast burn of a critical table; ticket the slow leaks.

Senior interview question on alerting, SLA reporting, and ownership

A senior interviewer might ask: "Your freshness monitoring fires 300 alerts a day and on-call has muted the channel. Redesign the alerting: dedup and group the noise, set severity from error-budget burn, route to owners with runbooks, suppress planned maintenance, and produce the monthly SLA compliance report leadership keeps asking for. Show how a single upstream outage should surface."

Solution Using a dedup-group-severity alert gate plus a monthly SLA compliance rollup

-- 1. Alert gate: dedup -> group by root cause -> severity by burn -> route to owner,
--    honouring an active maintenance window.
WITH maint AS (   -- currently-suppressed tables
    SELECT table_name FROM meta.maintenance_window
    WHERE  CURRENT_TIMESTAMP() BETWEEN starts_at AND ends_at
),
deduped AS (
    SELECT DISTINCT ON (table_name, breach_kind)
           table_name, breach_kind, root_cause, breach_ts
    FROM   meta.breach_event
    WHERE  breach_ts >= CURRENT_TIMESTAMP() - INTERVAL '10 minutes'
      AND  table_name NOT IN (SELECT table_name FROM maint)   -- suppress maintenance
    ORDER  BY table_name, breach_kind, breach_ts
),
grouped AS (
    SELECT root_cause,
           COUNT(*)                                   AS tables_affected,
           STRING_AGG(DISTINCT table_name, ', ')      AS tables,
           MAX(b.tier)                                AS worst_tier,
           MAX(bl.long_budget_consumed)               AS worst_burn
    FROM deduped d
    JOIN meta.freshness_budget b USING (table_name)
    JOIN meta.burn_log bl        USING (table_name)
    GROUP BY root_cause
)
SELECT
    root_cause,
    tables_affected,
    tables,
    CASE
        WHEN worst_tier = 'critical' AND worst_burn > 1.0 THEN 'PAGE'
        WHEN worst_burn > 0.5                             THEN 'TICKET'
        ELSE 'WARN'
    END                                                   AS severity,
    o.owner,
    o.runbook_url
FROM grouped g
JOIN meta.root_cause_owner o USING (root_cause)
ORDER BY tables_affected DESC;

-- 2. Monthly SLA compliance rollup — per table/consumer, for leadership.
SELECT
    table_name,
    consumer,
    COUNT(*)                                                    AS cycles,
    COUNT(*) FILTER (WHERE status = 'IN_BUDGET')                AS in_budget,
    ROUND(100.0 * COUNT(*) FILTER (WHERE status = 'IN_BUDGET')
          / COUNT(*), 2)                                        AS compliance_pct,
    COUNT(*) FILTER (WHERE status = 'BREACHED')                 AS breaches,
    STRING_AGG(DISTINCT incident_ref, ', ')
        FILTER (WHERE incident_ref IS NOT NULL)                 AS incidents
FROM meta.freshness_verdict_log
WHERE cycle_ts >= DATE_TRUNC('month', CURRENT_DATE)
GROUP BY table_name, consumer
ORDER BY compliance_pct ASC;      -- worst compliance first

Step-by-step trace.

Input: 300 raw breach events in the window, an active maintenance window on stg_backfill, and a month of verdicts.

1. maint finds stg_backfill inside an active maintenance window; the deduped CTE's NOT IN (SELECT ... maint) drops its breaches entirely — the planned backfill does not page.
2. deduped collapses the 300 raw events to one per (table, breach_kind) in the 10-minute window — say 20 distinct breaches, all sharing root_cause = source:oltp_repl.
3. grouped rolls those 20 into one row keyed on source:oltp_repl with tables_affected = 20, the worst tier among them (critical), and the worst burn (1.74).
4. Severity: worst_tier = 'critical' AND worst_burn > 1.0 → PAGE, routed to the OLTP-replication owner with their runbook URL. One page for the whole outage — not 300, not 20.
5. The rollup query aggregates the month's verdict log per (table, consumer): compliance_pct = 100 * in_budget / cycles, breach counts, and the annotated incident refs — the exact table leadership wants, sorted worst-compliance-first.

Output:

Alert gate (one grouped page):

root_cause	tables_affected	severity	owner	runbook_url
source:oltp_repl	20	PAGE	team-ingest-oncall	/runbooks/oltp-repl-down

Monthly SLA compliance rollup:

table_name	consumer	cycles	in_budget	compliance_pct	breaches	incidents
fct_orders	fraud	2880	2863	99.41	17	INC-4821
fct_orders	dashboard	720	719	99.86	1	(none)

Why this works — concept by concept:

• Dedup then group — DISTINCT ON collapses repeats and GROUP BY root_cause collapses simultaneous symptoms into one cause-level alert, turning 300 raw events into a single actionable page. This is the entire cure for alert fatigue.
• Maintenance suppression — the NOT IN maint filter scopes suppression to exactly the tables under planned work, so a backfill doesn't page while an unrelated real breach in the same window still does. Blanket muting would hide real incidents; scoped suppression does not.
• Severity from worst tier + worst burn — the group's page/ticket/warn decision comes from the most-critical affected table and the fastest burn, so one relaxed table in the blast radius never downgrades a critical outage.
• Owner + runbook routing — joining root_cause_owner attaches a named on-call and a runbook link to every alert; an alert with an owner and a first step is one that gets fixed, not ignored.
• Cost — the gate is an O(breach events in window) aggregation per tick; the rollup is an O(month of verdicts) aggregation run once a day or on demand. Both are cheap, and the compliance report is the artifact that makes the entire freshness program legible to leadership. O(1) pages per real incident — the metric that actually matters.

Design
Topic — design
Design problems on alerting and SLA systems

Practice →

Data Validation
Topic — data-validation
Data validation problems on SLA compliance reporting

Practice →

---

Cheat sheet — data freshness & SLA monitoring recipes

• Define freshness as a lag, per consumer. Three SLIs: event-time lag now() - max(event_time) (how old the newest real event is), ingestion lag now() - max(loaded_at) (loader liveness, not data age), and end-to-end latency (source commit → queryable, aggregated p50/p95/p99). For batch tables use a wall-clock completeness SLI ("done by 06:00"), not a lag. Never say "the table is fresh" — say "within budget for consumer X on SLI Y."
• SLI → SLO → SLA → error budget. SLI is the measured number; SLO is your internal target (tighter than the SLA on purpose); SLA is the external promise with consequences; error budget is 1 - SLO — the breach-cycles you may spend per window. A 99.9% SLO over 2,880 fifteen-minute cycles/month = 2.88 allowed breach-cycles. Set the budget backwards from the consumer's decision, never forward from pipeline capability.
• Freshness SLI query. DATEDIFF('minute', max_event_time, CURRENT_TIMESTAMP()) normalised to pct_of_budget = 100 * lag / budget_minutes; verdict IN_BUDGET/BREACHED on strict >; sort a dashboard by pct_of_budget DESC to see what is about to breach.
• Error-budget burn. budget_cycles = (1 - slo) * total_cycles; consumed_pct = 100 * breached / budget_cycles. Alert on burn rate (short window = acute, long window = chronic), not on individual breaches. > 100% consumed → freeze risky deploys until it recovers.
• Heartbeat + dead-man's switch. pipeline_heartbeat(pipeline, beat_at, rows_processed, run_status) written in the loader's finally block on every run — success, no-op (zero rows), and error. Monitor now() - max(beat_at) per pipeline; fire DOWN at cadence × (grace_runs + 1). Honour an active_days schedule so weekend-paused jobs don't false-page. Freshness says how old; the heartbeat says whether it is still running — you need both.
• Silent-failure classifier. DOWN/NEVER_RAN → page; alive + within budget → OK; alive + stale + rows_45m = 0 → quiet upstream (suppress); alive + stale + rows flowing → investigate the source. This one classifier kills most false-positive freshness pages.
• Anomaly detector. Rolling z-score (x - mean)/std over a trailing window excluding the current row (ROWS 20 PRECEDING AND 1 PRECEDING); prefer robust median + MAD (0.6745 * (x - median) / MAD, flag > 3.5) so a prior spike can't blind the next detection. Bucket the baseline by (day_of_week, hour_of_day) over 4–8 weeks to kill seasonality false positives. Score relative deviation (x - baseline)/baseline so a 10M→2M collapse trips even though it clears any fixed floor.
• Volume as leading indicator. A row-count drop usually precedes a freshness breach (loader still alive and recent, just fewer rows). Volume anomaly + rising event-time lag co-firing = high-confidence "upstream degrading" — escalate severity.
• Alert gate. Dedup on (table, breach_kind) within a window (DISTINCT ON), group simultaneous breaches by shared root_cause (one page for a 20-table source outage), suppress scoped maintenance windows (not everything), and alert on the cause, not the symptoms. Severity from worst tier + worst burn: critical + fast burn → PAGE, slow burn → TICKET, else WARN.
• SLA compliance rollup. Monthly per (table, consumer): compliance_pct = 100 * in_budget / cycles, breach count, and annotated incident refs, sorted worst-first. This is the artifact that makes freshness a reported, defensible service rather than an engineering hobby.
• Ownership. Every table has an owner in the freshness registry; every alert routes to that owner's on-call with a runbook link; a single percent-of-budget dashboard lets stakeholders self-serve. Unrouted alerts are ignored alerts.
• Coverage matrix. Fresh+alive = healthy; stale+alive = upstream late (investigate source); fresh+dead = the dangerous blind spot (the heartbeat catches it); stale+dead = obvious outage. Monitoring only freshness or only liveness leaves a quadrant uncovered.

Frequently asked questions

What is data freshness in one sentence?

Data freshness is the measured lag between when an event happened in the real world and when it becomes queryable downstream — most precisely expressed as now() - max(event_time) for the newest event, or as now() - max(loaded_at) for the loader's liveness, or as a wall-clock deadline ("complete by 06:00") for a batch table. It is a relationship between a table and a consumer, not a property of the table alone: the same fct_orders can be perfectly fresh for an hourly dashboard and hours too stale for a real-time fraud model at the exact same instant. Freshness breaks trust faster than any other data-quality dimension because stale data is plausible — every row-count and not-null check passes; the rows are simply old.

Freshness SLI vs SLO vs SLA — what is the difference?

The SLI (service level indicator) is the measured number — the freshness lag in minutes, or the fraction of cycles that were in budget. The SLO (objective) is your internal target on that SLI, deliberately tighter than the external promise: "ingestion lag under 60 minutes for 99.9% of cycles." The SLA (agreement) is the external, consequence-bearing promise you signed with the consumer: "data available by 06:00 or finance is notified." The error budget is 1 - SLO — the amount of breach you are allowed to spend per window before the SLA is at risk. You measure the SLI, hold yourself to the SLO, promise the SLA, and spend the error budget on deploys and backfills; when the budget is exhausted you freeze risky changes until it recovers.

What is a data freshness heartbeat and why do I need one?

A heartbeat is a tiny signal your pipeline writes to a pipeline_heartbeat table at the end of every run — including no-op runs that processed zero rows — recording the pipeline name, timestamp, row count, and status. You need it because a max-timestamp freshness check is structurally blind to a pipeline that dies while holding recent-enough data: at 00:30 a table loaded at 23:45 still looks fresh even though the loader crashed at midnight, and you stay blind until the lag finally crosses the budget an hour later. The heartbeat measures liveness directly and independently of data age, so a "dead-man's switch" can fire when no beat arrives within a tolerance (typically 2 × cadence) — catching a silent crash in minutes instead of hours. Freshness tells you how old the data is; the heartbeat tells you whether the job is still running.

Static thresholds vs anomaly detection for freshness — when do I use each?

Use a static threshold as a hard safety net — "latency above the SLA budget is always an alert" and "zero rows for a normally busy table is always an alert" — because it is simple and unambiguous. Use anomaly detection on top of it to catch the subtler failures a fixed number cannot express: a load latency spike that is only abnormal for this hour of this weekday, or a volume collapse from 10 million rows to 200,000 that still clears any "> 0 rows" floor. The senior recipe is a rolling baseline computed within seasonal buckets (same hour-of-day, same day-of-week) using a robust statistic — median and MAD rather than mean and standard deviation — so a single earlier spike does not inflate the baseline and mask the next anomaly. Static thresholds page on every Monday-morning peak; a seasonal, robust anomaly detector does not.

How do I stop freshness alert fatigue?

Fire fewer, better pages. First, deduplicate: the same table breaching for six consecutive cycles is one incident, not six pages — suppress repeats for the same (table, breach_kind) within a window. Second, group by root cause: when an upstream source fails and twenty downstream tables go stale, emit one alert ("source X down, 20 tables affected") instead of twenty. Third, set severity from error-budget burn rate rather than from a single breach — page only on fast burn of a critical-tier table; open tickets for slow chronic leaks; leave isolated breaches as dashboard warnings. Fourth, suppress scoped maintenance windows so planned backfills don't page while unrelated real breaches still do. A page everyone learned to ignore is functionally identical to no monitoring at all, so the goal is signal, not volume.

How do I report freshness SLA compliance to stakeholders?

Produce a monthly rollup per (table, consumer) from your per-cycle verdict log: total cycles, cycles in budget, compliance_pct = 100 * in_budget / cycles, the breach count, and the error budget consumed — annotated with the incident references for any breaches, and sorted worst-compliance-first. A line like "fct_orders for the fraud model: 99.41% of cycles in budget, 17 breaches all attributable to INC-4821" turns freshness from a vague engineering concern into a reported, defensible service with an owner and a trend. Pair the report with a live dashboard sorted by percent-of-budget so stakeholders can self-serve "is my data fresh right now" between reports, and you have closed the loop from measurement to accountability.

Practice on PipeCode

• Drill the SQL practice library → for the freshness-lag, watermark, rolling-window, and z-score problems that show up in data-observability interviews.
• Rehearse on the ETL practice library → for the heartbeat, dead-man's-switch, and pipeline-liveness monitoring patterns.
• Harden the checks with the data validation practice library → for anomaly detection, SLA compliance, and freshness-budget scenarios.
• Stress-test the alerting and ownership design with the design practice library →, then anchor the whole system against PipeCode's broader 450+ data-engineering catalogue.

Lock in data freshness muscle memory

Docs explain freshness. PipeCode drills explain the decision — which SLI a consumer actually needs, when a heartbeat catches a silent crash a threshold misses, why MAD beats standard deviation on a spiky metric, and when error-budget burn should page a human. Pipecode.ai is Leetcode for Data Engineering — observability-first practice tuned for the production trade-offs senior data engineers actually face.

Practice SQL problems →
Practice data validation problems →