Data SRE Playbook: SLO-Driven Pipelines, Error Budgets & On-Call Rotations for DE Teams

A data sre playbook is the operating manual senior data teams reach for the day they stop shipping "the DAG turned green" as a reliability claim and start owning user-visible pipeline reliability the same way SRE teams own web-service reliability — with measurable SLIs, explicit SLOs, monthly error budgets, burn-rate paging, follow-the-sun on-call rotations, linked runbooks, and blameless postmortems that compound into a reliability roadmap. The reason every senior data engineer needs one in 2026 is that the number of downstream consumers of an operational data pipeline — dashboards, ML models, feature stores, reverse-ETL sync targets, customer-facing APIs — has grown to the point where a two-hour freshness gap on the orders table is a customer-visible outage, and "we'll fix it on the next DAG run" is not an incident response.

This guide is the senior-DE walkthrough you wished existed the first time an executive asked "what's our reliability commitment on the warehouse?", or an interviewer probed "walk me through the SLIs, SLOs, and error budgets you'd set for a streaming pipeline," or a director cornered you with "why did that data outage take three hours to notice and eleven hours to resolve?" It covers the four failure axes every data platform must instrument — data freshness slo (max age of the latest record), completeness (fraction of source rows landed), volume (row-count within an expected band), and quality (fraction of rows passing schema and constraint checks) — the error budget data engineering math that translates a 99.9% freshness target into a 43-minute monthly ceiling, the multi-window multi-burn-rate pipeline sli alerts modelled on the Google SRE workbook, the data on-call rotation shape and handoff ritual, the 5-field data runbook template every alert links to, and the blameless postmortem walk from detect through mitigate, resolve, and review. Every 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 ETL practice library →, rehearse on the design practice library →, and sharpen the reliability axis with the data-validation practice library →.

---

On this page

• Why data teams need SRE practices in 2026
• SLIs and SLOs for data pipelines
• Error budgets and burn-rate alerts
• On-call rotations and incident runbooks
• Post-incident review and reliability roadmap
• Cheat sheet — Data SRE recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why data teams need SRE practices in 2026 — the four failure axes

Data pipelines fail differently from web services — the same SRE principles apply, the SLIs do not

The one-sentence invariant: a data platform is reliable when the datasets its consumers depend on are fresh, complete, correctly-sized, and quality-checked within an explicitly agreed-upon target — and pipeline reliability engineering is the practice of translating those four consumer-visible axes into measurable SLIs, monthly SLOs, error budgets, burn-rate alerts, on-call rotations, and blameless postmortems, exactly like SRE teams do for web services but with pipeline-shaped inputs instead of HTTP-shaped ones. Every senior data-engineering interview in 2026 probes this territory because the number of teams claiming "SRE for data" without actually shipping a single measurable SLI has exploded, and the interviewers you'll face have been burned by fluent-sounding candidates whose reliability practice starts and stops at "we monitor DAG status."

The four failure axes interviewers actually probe.

• Freshness. The maximum age of the latest record in a target table relative to when it should have arrived. If a hourly orders pipeline runs at :05 past the hour, the freshness target might be "no consumer query should ever see MAX(ingested_at) < now() - 65 min." Freshness is the axis that hurts business dashboards first; it's the most common SLO to write.
• Completeness. The fraction of expected source rows that have landed in the target within the SLO window. If the source produced 10,000 orders in the hour and only 9,850 arrived, completeness is 98.5% for that window. Completeness catches silent drops — filter bugs, schema mismatches, network truncation.
• Volume. Whether the row count of the target table sits within an expected band (usually min_expected < count < max_expected from a rolling 7- or 28-day baseline). Volume catches "we ingested but the count is wrong" — a duplicated upstream feed doubling counts, a broken filter halving them.
• Quality. The fraction of rows that pass schema, constraint, and business-rule checks (nulls in required columns, invalid enums, out-of-range values, orphan foreign keys). Quality catches "we ingested the right count but the data is garbage."

Why "did the DAG run green" is not an SLI.

• DAG green ≠ data correct. An Airflow task can succeed while writing an empty file, writing to the wrong partition, or writing stale data because an upstream watermark stalled. The status of the job is a proxy for reliability at best; the status of the dataset is what consumers experience.
• DAG green ignores latency-of-detection. A pipeline that fails at 09:00 and is caught at 15:00 has still burned six hours of business value. Job-status monitoring wakes you up when a task raises; SLI monitoring wakes you up when the dataset stops meeting its contract, which is often earlier and sometimes later.
• DAG green ignores upstream reality. If the source system stopped producing rows at 08:00, every downstream DAG will still turn green (they read an empty delta and write an empty file). SLI-based monitoring on volume + freshness catches this class of "silent zero" failure that job monitoring cannot.
• DAG green ignores partial success. A Spark job with spark.sql.files.ignoreCorruptFiles = true succeeds while silently dropping bad Parquet files. A dbt run with --fail-fast off finishes with 2 of 200 tests failing but a green run icon. Only per-dataset SLIs make partial-success failures visible.

The SRE contract: user-visible reliability, not job status.

• User-visible metrics only. An SLI is measured on the output artifact (the target table, the served view, the exported file) — not on the process that produced it. This constraint forces the metric to align with what a downstream consumer cares about.
• Target expressed as an SLO. "99.9% of hourly windows have freshness < 65 minutes." "99.5% of daily runs have completeness > 99% of expected count." SLOs are always a percentage of measurement windows meeting a threshold, never "we always meet it."
• Error budget = 1 − SLO. A 99.9% freshness SLO means 0.1% of hourly windows can miss — that's 43 minutes per 30-day month. The budget is the operational contract with the rest of the organisation: within it, releases proceed; outside it, releases halt.
• Alerting off the SLI, not the pipeline. Burn-rate alerts fire when the SLI degrades faster than the budget will tolerate. The alert wakes a human because the user-visible contract is at risk, not because a job status changed.

What interviewers listen for.

• Do you name all four axes (freshness, completeness, volume, quality) without prompting? — senior signal.
• Do you say "DAG status is not an SLI" when asked what to monitor? — required answer.
• Do you cite the error-budget number (99.9% ≈ 43 min/month) without pulling out a calculator? — senior signal.
• Do you describe a burn-rate alert as "multi-window multi-rate" and cite the Google SRE workbook? — senior signal.
• Do you frame postmortems as "blameless, focused on contributing factors" rather than root-cause five-whys? — senior signal.

Worked example — the four-axis reliability table for an orders pipeline

Detailed explanation. The single most useful artifact for a data SRE interview is a memorised 4×4 axis table. Every senior reliability discussion converges on this table within the first ten minutes; having it in your head is what separates a fluent answer from a stumbling one. Walk through building the table for a hypothetical hourly orders pipeline that lands into a Snowflake warehouse.

• Source. production.public.orders (Postgres OLTP).
• Target. analytics.orders (Snowflake, updated hourly by an Airflow DAG at :05 past the hour).
• Consumers. Executive dashboard (Looker), ML fraud model (feature store), reverse-ETL sync to Salesforce.
• User contract. All three consumers depend on data being no more than ~1 hour behind, complete against the source, correctly-sized, and quality-checked.

Question. Build the four-axis SLI/SLO table for the analytics.orders pipeline and pick a numeric target for each.

Input.

Axis	Measurement	SLO target	Budget / month
Freshness	max(ingested_at) age at query time	99.9% of minutes < 65 min	~43 min
Completeness	target rowcount / source rowcount per hour	99.5% of hours ≥ 99%	~3.6 hours
Volume	rowcount vs 7-day median band ±30%	99.5% of hours within band	~3.6 hours
Quality	rows passing schema + constraint checks	99.9% of daily runs ≥ 99%	~1 day/quarter

Code.

-- Freshness SLI — computed on the target table at query time
SELECT
    CASE
        WHEN MAX(ingested_at) >= now() - INTERVAL '65 minutes' THEN 1
        ELSE 0
    END AS meets_freshness_slo,
    now() - MAX(ingested_at) AS current_age
FROM analytics.orders;

-- Completeness SLI — computed hourly by comparing target to source
WITH source_hour AS (
    SELECT date_trunc('hour', event_ts) AS h, COUNT(*) AS n_src
    FROM   production.orders
    WHERE  event_ts >= now() - INTERVAL '24 hours'
    GROUP  BY 1
),
target_hour AS (
    SELECT date_trunc('hour', event_ts) AS h, COUNT(*) AS n_tgt
    FROM   analytics.orders
    WHERE  event_ts >= now() - INTERVAL '24 hours'
    GROUP  BY 1
)
SELECT s.h,
       s.n_src,
       COALESCE(t.n_tgt, 0)                              AS n_tgt,
       COALESCE(t.n_tgt, 0)::float / NULLIF(s.n_src, 0)  AS completeness_ratio,
       CASE WHEN COALESCE(t.n_tgt, 0)::float / NULLIF(s.n_src, 0) >= 0.99
            THEN 1 ELSE 0 END                            AS meets_completeness_slo
FROM   source_hour s
LEFT JOIN target_hour t USING (h)
ORDER  BY s.h DESC;

-- Volume SLI — computed hourly against a 7-day rolling median band
WITH baseline AS (
    SELECT date_trunc('hour', event_ts)::time AS hour_of_day,
           percentile_cont(0.5) WITHIN GROUP (ORDER BY hourly_count) AS median_ct
    FROM (
        SELECT date_trunc('hour', event_ts) AS h,
               date_trunc('hour', event_ts)::time AS hour_of_day,
               COUNT(*) AS hourly_count
        FROM   analytics.orders
        WHERE  event_ts >= now() - INTERVAL '7 days'
          AND  event_ts <  now() - INTERVAL '1 hour'
        GROUP  BY 1, 2
    ) t
    GROUP BY 1
)
SELECT t.h,
       t.hourly_count,
       b.median_ct,
       CASE WHEN t.hourly_count BETWEEN b.median_ct * 0.7 AND b.median_ct * 1.3
            THEN 1 ELSE 0 END AS meets_volume_slo
FROM   (SELECT date_trunc('hour', event_ts) AS h,
               date_trunc('hour', event_ts)::time AS hour_of_day,
               COUNT(*) AS hourly_count
        FROM   analytics.orders
        WHERE  event_ts >= now() - INTERVAL '24 hours'
        GROUP  BY 1, 2) t
JOIN   baseline b USING (hour_of_day)
ORDER  BY t.h DESC;

Step-by-step explanation.

1. The freshness SLI is a single per-query check — MAX(ingested_at) on the target table. If the answer is younger than the SLO threshold, the minute counts as "meeting"; otherwise it burns budget. Computing this in the warehouse rather than in Airflow means it reflects the dataset state, not the pipeline state.
2. The completeness SLI joins hourly source counts against hourly target counts. Ratio < 0.99 for the hour means the hour "misses" its SLO. The 5-minute look-back safety window (not shown; add WHERE event_ts < now() - INTERVAL '5 min') prevents in-flight rows from being counted as missing.
3. The volume SLI compares each hour's target count to the median of the same hour-of-day across the last 7 days. The ±30% band absorbs normal weekly variance while catching the "3x volume" and "0.1x volume" outliers that indicate upstream duplication or a broken filter.
4. The quality SLI (not shown in code — computed via Great Expectations or dbt tests) is the fraction of rows passing a battery of expectations: orders.order_id NOT NULL, orders.status IN ('pending','shipped','delivered','cancelled'), orders.total_cents > 0, orders.customer_id REFERENCES customers.id. Aggregate to "% of rows passing all checks" for the day.
5. All four SLIs feed into a single reliability dashboard and a single error-budget accounting table. When any one SLI's budget approaches exhaustion, the on-call receives a burn-rate page. When any one SLI's budget is exhausted, feature releases against the pipeline are halted until budget replenishes at the start of the next month.

Output.

Consumer	Axis they care most about	SLO target	Why
Executive dashboard	Freshness	99.9% < 65 min	dashboards are read at any moment; stale = wrong decisions
ML fraud model	Completeness	99.5% ≥ 99%	model retrained hourly; missing rows = biased scoring
Reverse-ETL to Salesforce	Volume	99.5% within band	wrong volume = wrong sales pipeline forecast
All	Quality	99.9% daily ≥ 99%	broken schema = downstream crashes

Rule of thumb. Never pick an SLI based on "what the pipeline emits." Pick it based on (freshness × completeness × volume × quality) — the four axes a downstream consumer actually experiences. Write the table on a whiteboard first; the SLI selection falls out of the consumer contract.

Worked example — what interviewers actually probe

Detailed explanation. The senior data-SRE interview has a predictable structure: the interviewer opens with an ambiguous question ("how do you monitor a data pipeline?"), then progressively narrows to test whether you know the axes. The candidates who name the SLIs in sentence one score highest; the candidates who describe "Airflow alerts on failure" score lowest. Walk through the interview grading rubric.

• Ambiguous opener. "How would you monitor an hourly ETL pipeline?" — invites you to name SLIs.
• Follow-up 1. "What's an SLI vs an SLO?" — probes definitions.
• Follow-up 2. "How much downtime does 99.9% freshness allow?" — probes error-budget math.
• Follow-up 3. "When do you page vs ticket?" — probes burn-rate alerting.
• Follow-up 4. "Walk me through your last data incident postmortem." — probes maturity.

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

Input.

Interview signal	Weak answer	Senior answer
SLI named	"we alert on Airflow failures"	"freshness, completeness, volume, quality — each with an SLO"
SLO stated	"we aim for 99% uptime"	"99.9% freshness < 65 min = 43 min/month error budget"
Alert semantics	"we page on task failure"	"multi-window multi-burn-rate on the freshness SLI"
On-call shape	"the person who wrote the DAG gets paged"	"primary + secondary weekly rotation with runbook links"
Postmortem	"we write a Slack thread"	"blameless review, contributing factors, tracked action items"

Code.

Senior data-SRE answer template (5 minutes)
============================================

Minute 1 — name the SLIs up front
  "I'd instrument four SLIs per pipeline: freshness (max age of latest row),
   completeness (target/source rowcount), volume (rowcount vs baseline band),
   and quality (rows passing schema + constraint checks). Each gets an SLO
   like 99.9% of hourly windows meeting the threshold."

Minute 2 — error-budget math
  "99.9% freshness means 0.1% of the month can miss, which is 43 minutes.
   That's the operational contract with the rest of the org: within the
   budget, releases proceed; outside it, releases halt until the budget
   resets at month start."

Minute 3 — burn-rate alerts
  "Instead of alerting on any missed minute, I use multi-window
   multi-burn-rate: a fast-burn window (1 h) at 14x normal spend pages
   immediately, a slow-burn window (6 h) at 6x tickets the team. This
   catches both cliffs and creeping degradation without false pages."

Minute 4 — on-call + runbook
  "Weekly primary + secondary rotation with follow-the-sun handoff for
   global teams. Every alert links to a 5-field runbook: symptom,
   detection, mitigation, escalation, owner. No runbook link = alert
   isn't allowed to fire."

Minute 5 — postmortem + roadmap
  "Blameless postmortem within 5 business days: timeline, contributing
   factors (not a single root cause), action items with owners and due
   dates. Action items feed the reliability roadmap; SLO trends drive
   the SLI backlog next quarter."

Step-by-step explanation.

1. Minute 1 is the crucial framing. Naming the four SLIs immediately — freshness, completeness, volume, quality — signals you've operated a data platform under real reliability targets, not just written DAGs. Weak candidates start with tools ("we use Airflow and Datadog") before naming a metric.
2. Minute 2 does the error-budget math out loud. Interviewers listen for whether you can convert an SLO percentage into an operational number without hesitation. "99.9% is 43 minutes per month" is the answer you should have burned into muscle memory.
3. Minute 3 introduces multi-window multi-burn-rate. This is the SRE-workbook-canonical alerting pattern; naming it, and citing the fast-vs-slow-burn distinction, is a senior signal that separates you from candidates who alert on task-failure.
4. Minute 4 addresses on-call operationally. The 5-field runbook (symptom, detection, mitigation, escalation, owner) is the concrete template; refusing to allow alerts without runbook links is the enforcement mechanism. Both are things you'd expect a mature platform to have.
5. Minute 5 covers postmortems and the reliability roadmap. "Blameless, contributing factors, tracked action items, feed the roadmap" is the four-part cycle that turns incidents into reliability improvements over quarters. This is the axis mid-level candidates skip entirely.

Output.

Grading criterion	Weak score	Senior score
Names SLIs in minute 1	rare	mandatory
States error-budget number	rare	required
Names burn-rate alerting	rare	senior signal
Names runbook fields	rare	senior signal
Names postmortem cycle	rare	senior signal

Rule of thumb. The senior data-SRE answer is a 5-minute monologue that covers SLIs, error budgets, burn-rate alerts, on-call rotation, and postmortems without waiting for the follow-ups. Rehearse it once; deploy it every time.

Worked example — the "SRE maturity" decision tree for a data team

Detailed explanation. Given a data team, the senior architect runs a 4-question maturity check in their head. Codifying the check makes the interview answer reproducible: any interviewer can hand you a team description and you can walk the tree out loud. Walk through the tree with three canonical scenarios: a startup with one DE and no SLIs, a mid-market team with green DAGs only, and a mature platform team with per-dataset SLOs.

• Q1. Do the pipelines have measurable SLIs on the four axes (freshness, completeness, volume, quality)? → no = start there.
• Q2. Are the SLOs written down and agreed with downstream consumers? → no = do the SLO-negotiation exercise.
• Q3. Are burn-rate alerts wired to a rotation with runbook links? → no = the SLIs are decorative.
• Q4. Are postmortems blameless and tracked as action items on a reliability roadmap? → no = incidents don't compound into improvements.

Question. Walk the maturity tree for the three scenarios and record the recommended next step for each.

Input.

Scenario	Q1 SLIs?	Q2 SLOs?	Q3 alerts + runbook?	Q4 postmortems?
Startup, 1 DE, no SLIs	no	no	no	no
Mid-market, green DAGs only	partial (freshness)	no	partial	no
Mature platform	yes	yes	yes	yes

Code.

# Data-SRE maturity classifier (illustrative)
def data_sre_maturity(has_slis: bool,
                       has_slos: bool,
                       has_burn_rate_alerts: bool,
                       has_blameless_postmortems: bool) -> str:
    """Return the recommended next step on the SRE maturity ladder."""
    if not has_slis:
        return "Level 0 — instrument SLIs on all four axes first"
    if not has_slos:
        return "Level 1 — negotiate SLO targets with downstream consumers"
    if not has_burn_rate_alerts:
        return "Level 2 — wire multi-window burn-rate alerts + runbook links"
    if not has_blameless_postmortems:
        return "Level 3 — adopt blameless postmortems and a reliability roadmap"
    return "Level 4 — mature; iterate on SLI selection and chaos drills"


# Walk the three scenarios
print(data_sre_maturity(False, False, False, False))
# → 'Level 0 — instrument SLIs on all four axes first'

print(data_sre_maturity(True, False, False, False))
# → 'Level 1 — negotiate SLO targets with downstream consumers'

print(data_sre_maturity(True, True, True, True))
# → 'Level 4 — mature; iterate on SLI selection and chaos drills'

Step-by-step explanation.

1. Scenario 1 — the one-DE startup has no SLIs, no SLOs, no alerts, no postmortems. The next step is not "adopt SRE" but "instrument the SLIs" — you cannot manage what you don't measure. Start by adding freshness on the top three most-consumed tables; expand from there.
2. Scenario 2 — the mid-market team has partial SLIs (freshness only, computed as a metric but never with an agreed target). The next step is negotiating SLO numbers with consumers: sit with the dashboard owner and agree "99.9% < 65 min," write it in a doc, and only then wire alerts.
3. Scenario 3 — the mature platform team is at Level 4 and iterating. The next step is not "climb the ladder" but "expand SLI coverage" (from 20 tables to 200 tables), "reduce false-positive alert rate," and "run chaos drills quarterly to verify the runbooks."
4. The maturity ladder is strict — you cannot jump levels. A team that adds burn-rate alerts without agreed SLOs will burn out on false pages. A team that writes blameless postmortems without burn-rate alerts will find nothing to write about because incidents are silently ignored.
5. If none of Q1-Q4 pass, the correct answer to "how do we adopt data SRE?" is "start with SLIs on your three most-consumed tables and iterate." Refuse to endorse tools ("buy Monte Carlo," "adopt Datadog") until Q1 is passing.

Output.

Scenario	Level	Next step
Startup, no SLIs	0	Instrument freshness on top 3 tables
Mid-market, green DAGs	1	Negotiate SLO targets with consumers
Mature platform	4	Expand SLI coverage; chaos drills

Rule of thumb. The four-level maturity ladder is a whiteboard-friendly answer. Practice walking it end-to-end so an interviewer can hand you any team description and get a level + next step in under 60 seconds.

Senior interview question on data SRE adoption

A senior interviewer often opens with: "You join a 40-engineer data platform team that has 200 Airflow DAGs, no SLIs, and monitors reliability by 'did the DAG turn green.' Design the 90-day plan to introduce SRE practices without breaking the team's ability to ship. Cover the SLI you'd start with, the SLO negotiation, the alerting rollout, the on-call bootstrapping, and the metric you'd use to measure your own success."

Solution Using a 90-day SLI-first rollout with staged SLO negotiation and phased alert cutover

# 90-day data-SRE rollout plan (illustrative)
rollout:
  weeks_0_2:
    goal: instrument SLIs on top 10 tables
    steps:
      - identify top 10 tables by downstream query count (via warehouse audit logs)
      - implement freshness SLI (SELECT MAX(ingested_at) FROM t)
      - implement completeness SLI (source rowcount vs target rowcount per hour)
      - export both as Prometheus gauges (label: table_name)
      - build a "Data reliability" Grafana dashboard (no alerts yet)

  weeks_2_6:
    goal: negotiate SLO targets with downstream consumers
    steps:
      - schedule 30-min meetings with owner of each top-10 dashboard/service
      - propose default SLOs (99.9% freshness < 2x pipeline cadence)
      - agree per-table SLO in writing (Confluence doc)
      - publish SLO catalogue link from every table in the data catalogue

  weeks_6_10:
    goal: wire burn-rate alerts + runbooks
    steps:
      - draft 5-field runbook per SLO (symptom, detection, mitigation, escalation, owner)
      - configure multi-window multi-burn-rate alert rules in Prometheus/Alertmanager
      - route to Slack (warning) and PagerDuty (critical) with runbook link
      - run a game-day simulation before enabling paging

  weeks_10_13:
    goal: on-call rotation + first postmortems
    steps:
      - set up weekly primary + secondary rotation in PagerDuty
      - write handoff-doc template (open incidents, budget status, changes)
      - schedule blameless postmortem for every SLO breach within 5 business days
      - track action items in Jira with "reliability" label

success_metric:
  primary: fraction of top-10 tables with a live SLI + agreed SLO + alerting = 10/10 by day 90
  secondary: mean time to detect (MTTD) reduced from ~4 h to < 15 min on top-10 tables
  guardrail: false-positive alert rate < 20% of total pages

# Freshness SLI exporter — Python + prometheus_client
# Run every 60 s as a sidecar or scheduled task
import psycopg2
import time
from prometheus_client import start_http_server, Gauge

g_age_seconds = Gauge('data_freshness_age_seconds',
                      'age in seconds of newest row',
                      ['table_name'])

TABLES = [
    'analytics.orders',
    'analytics.customers',
    'analytics.shipments',
    'analytics.payments',
    'analytics.sessions',
    'analytics.pageviews',
    'analytics.events',
    'analytics.line_items',
    'analytics.refunds',
    'analytics.reviews',
]

def scrape(conn):
    with conn.cursor() as cur:
        for t in TABLES:
            cur.execute(f"SELECT extract(epoch FROM (now() - MAX(ingested_at))) FROM {t}")
            (age,) = cur.fetchone()
            g_age_seconds.labels(t).set(age or 0)

if __name__ == '__main__':
    start_http_server(9200)
    conn = psycopg2.connect('host=warehouse port=5432 dbname=analytics user=slo_reader')
    conn.autocommit = True
    while True:
        scrape(conn)
        time.sleep(60)

Step-by-step trace.

Week	Milestone	Deliverable
0–2	SLIs live on top 10 tables	freshness + completeness gauges + dashboard
2–6	SLO targets agreed	10 SLO docs signed by consumer + owner
6–10	Burn-rate alerts wired	Prometheus rules + runbook links
10–13	On-call rotation + first postmortems	rotation live; first postmortem doc
Day 90	Success metrics reported	MTTD < 15 min; false-page < 20%

After the rollout, the team has 10 tables under measurable reliability contracts, a weekly on-call rotation with runbook-linked alerts, and a blameless postmortem cadence. The next 90 days extend SLI coverage to the top 50 tables and start iterating on false-positive alert reduction.

Output:

Metric	Baseline	Day 90
Tables with live SLI	0	10
Tables with agreed SLO	0	10
Mean time to detect	~4 h	< 15 min
Blameless postmortems / month	0	1–3
False-positive alert rate	n/a	< 20%

Why this works — concept by concept:

• SLI-first rollout — measurement before alerting. Instrumenting the metric first, running a dashboard for a few weeks, then adding alerts on the same metric is the SRE-canonical sequence. It gives you baseline data to set realistic SLO targets and avoids the "alerts firing before we knew normal" trap.
• SLO negotiation — the SLO is a contract, not a target unilaterally set by the platform team. Sitting with the consumer of each dataset and agreeing the number in writing turns "we aim for 99.9%" into "the fraud team accepted 99.5% freshness with 65-minute recovery." Writing it down is what makes it enforceable.
• Multi-window multi-burn-rate alerts — the standard SRE-workbook pattern for turning "we burnt some budget in the last window" into a paging decision. Fast-burn wakes you at cliff-fall; slow-burn wakes you at creep. Both fire on the same SLI.
• 5-field runbook link per alert — no runbook, no alert. Enforcing this at the alert-config level is the strongest lever for reducing "what do I do now?" MTTR. Every alert that pages a human must link to a document that tells them what to do.
• Cost — one platform engineer week 0-2 to build the exporter, half an engineer weeks 2-6 for SLO negotiation meetings, one engineer weeks 6-10 to wire alerts and runbooks, half an engineer weeks 10-13 to bootstrap on-call. Net ~2.5 engineer-months for a 90-day rollout that puts 10 tables under reliability contracts. Compared to a $200k/year "data observability" tool that doesn't do SLO negotiation, this is 3–4x more valuable in interviewer eyes.

Design
Topic — design
Design problems on data platform reliability

Practice →

ETL Topic — etl ETL problems on pipeline observability

Practice →

---

2. SLIs and SLOs for data pipelines — freshness, completeness, volume, quality

The four SLIs every data platform must instrument — one per axis of user-visible reliability

The mental model in one line: an SLI for a data pipeline is a numeric measurement of a single user-visible reliability axis — freshness, completeness, volume, or quality — computed on the output artifact (the target table, the served view, the exported file) rather than on the process that produced it, and an SLO is the target percentage of measurement windows in which the SLI meets its threshold. Every senior data engineer must know how to derive an SLI from a consumer contract, express it as a rolling percentage, and translate it into a monthly error budget without hesitation.

The four axes as concrete SLIs.

• Freshness SLI. Measurement: now() - MAX(ingested_at) on the target table, or MAX(ingested_at) - MAX(source.event_ts) for end-to-end lag. SLO: e.g. 99.9% of one-minute observation windows have age < 65 min for an hourly pipeline. Freshness is the axis every dashboard user notices first.
• Completeness SLI. Measurement: target rowcount / source rowcount, computed per hour or per day (or per micro-batch for streaming). SLO: e.g. 99.5% of hours have completeness_ratio ≥ 0.99. Completeness catches silent drops from schema mismatches, filter bugs, or truncated network transfers.
• Volume SLI. Measurement: target rowcount vs a rolling 7-day median band. SLO: e.g. 99.5% of hours have count ∈ [0.7 × median, 1.3 × median]. Volume catches duplication (2× median) and silent zero (0.1× median) failures that completeness alone misses when both source and target are wrong.
• Quality SLI. Measurement: fraction of rows passing a battery of schema and business-rule checks (nulls in required columns, invalid enums, out-of-range values, orphan foreign keys). SLO: e.g. 99.9% of daily runs have pass_rate ≥ 0.99. Quality catches "we ingested the right count but the data is garbage."

Why each axis is separate.

• Freshness alone is insufficient. A pipeline that runs on time but writes an empty file has freshness = 0 (age of latest row = 0 seconds… because there is no latest row). Volume + completeness catch this.
• Completeness alone is insufficient. A pipeline that mirrors the source 1:1 but the source itself is broken (upstream feed cut in half) will pass completeness. Volume catches this.
• Volume alone is insufficient. A pipeline whose count matches the baseline exactly but whose rows are semantically wrong (all total_cents = 0) will pass volume. Quality catches this.
• Quality alone is insufficient. A pipeline that passes every quality check but arrives 4 hours late fails freshness. Freshness catches this.

The measurement window — the second axis of every SLI.

• Observation cadence. How often the SLI is measured. Freshness is typically measured per minute (cheap query). Completeness is per hour. Volume per hour. Quality per daily run.
• SLO window. The period over which the percentage is computed. Standard: rolling 28 days ("SLO calculated over the trailing 28-day window").
• Error budget window. Same as SLO window. 99.9% over 28 days = 40.32 minutes of budget.
• Alert window. Shorter periods used for burn-rate detection: 1 hour (fast burn), 6 hours (slow burn).

How to derive an SLI target from a consumer contract.

• Ask the consumer. "How stale is too stale?" The dashboard owner says "I check it every 5 minutes; if it's more than an hour behind, my meeting is derailed." That's your freshness target: 65 minutes (add a small buffer above the natural 60-minute cadence).
• Sanity-check against current performance. Compute the SLI over the last 28 days. If current performance is 99.7%, target 99.9% requires a 3x improvement in bad-minute count. If current is 99%, target 99.9% requires a 10x improvement — that's a project, not a target.
• Trade cost against reliability. Higher SLOs cost more (extra headroom in compute, faster recovery, more on-call attention). Explicit trade-off: "we could hit 99.99% freshness by adding a hot-standby DAG, at a cost of 2x compute." The business decides.
• Publish and version the SLO. Confluence doc + Git-tracked SLO catalogue. Every SLO has an owner (platform team) and a consumer (dashboard or service owner). Reviewed quarterly.

Common interview probes on SLIs and SLOs.

• "What's the difference between an SLI and an SLO?" — required answer: SLI is the measurement, SLO is the target.
• "How do you pick a freshness target?" — required answer: derive from consumer contract; sanity-check against current performance.
• "What's an SLA?" — required answer: SLA is the external customer-facing commitment (with penalties); SLO is the internal target (typically stricter).
• "How do you handle multiple consumers with different needs?" — required answer: pick the strictest requirement; publish that as the SLO; slower consumers benefit for free.

Worked example — freshness SLI over a Snowflake table

Detailed explanation. The canonical freshness SLI setup: a per-minute query that compares MAX(ingested_at) against the SLO threshold, exported as a Prometheus gauge and rolled up into a 28-day SLO percentage. Walk through the pieces.

• Metric definition. data_freshness_meets_slo{table="analytics.orders"} = 1 if now() - MAX(ingested_at) < 65 min, else 0.
• Cadence. Every 60 seconds.
• SLO percentage. sum_over_time(data_freshness_meets_slo[28d]) / count_over_time(data_freshness_meets_slo[28d]).
• Target. 99.9% (= 40.32 min budget over 28 days).

Question. Implement the freshness SLI query, the Prometheus exporter, and the SLO rollup rule.

Input.

Component	Value
Target table	analytics.orders
Threshold	65 minutes
Cadence	60 s
SLO window	28 days
SLO target	99.9%

Code.

-- Freshness check query — runs every 60 s from an exporter
SELECT
    'analytics.orders'::text AS table_name,
    extract(epoch FROM (now() - MAX(ingested_at))) AS age_seconds,
    CASE WHEN now() - MAX(ingested_at) < INTERVAL '65 minutes'
         THEN 1 ELSE 0 END AS meets_slo
FROM analytics.orders;

# Freshness exporter — psycopg2 + prometheus_client
import psycopg2
import time
from prometheus_client import start_http_server, Gauge

g_meets = Gauge('data_freshness_meets_slo', '1 if within threshold', ['table_name'])
g_age   = Gauge('data_freshness_age_seconds', 'age of newest row',   ['table_name'])

CFG = [
    # (table_name, threshold_seconds)
    ('analytics.orders',    65 * 60),
    ('analytics.customers', 24 * 3600),
    ('analytics.events',    15 * 60),
]

def scrape(conn):
    for tbl, threshold in CFG:
        with conn.cursor() as cur:
            cur.execute(f"SELECT extract(epoch FROM (now() - MAX(ingested_at))) FROM {tbl}")
            (age,) = cur.fetchone()
            g_age.labels(tbl).set(age or 0)
            g_meets.labels(tbl).set(1 if (age or 0) < threshold else 0)

if __name__ == '__main__':
    start_http_server(9200)
    conn = psycopg2.connect('host=warehouse dbname=analytics user=slo_reader')
    conn.autocommit = True
    while True:
        scrape(conn)
        time.sleep(60)

# Prometheus recording rule — roll up to a 28-day SLO percentage
groups:
- name: data_slo_rollups
  interval: 60s
  rules:
    - record: slo:freshness:28d
      expr: |
        sum_over_time(data_freshness_meets_slo[28d])
          / count_over_time(data_freshness_meets_slo[28d])

    - record: slo:freshness:budget_remaining_28d
      expr: |
        (slo:freshness:28d - 0.999) / (1 - 0.999)
      # value > 0 means budget remaining; 0 means exhausted; < 0 means overspent

Step-by-step explanation.

1. The SLI query runs on the warehouse and computes now() - MAX(ingested_at) — the age of the newest row. This is a cheap query when ingested_at is indexed; sub-100ms on any modern warehouse. Running it every 60 seconds is negligible cost.
2. The exporter converts the raw age into two Prometheus gauges: data_freshness_age_seconds (the continuous measurement) and data_freshness_meets_slo (the 0/1 binary check against the threshold). Both are labelled by table name.
3. The recording rule slo:freshness:28d averages the binary meets_slo gauge over the trailing 28 days. If 99.9% of one-minute observations were "1", the SLO percentage is 0.999. Recording it as a rule (rather than computing at query time) makes dashboards and alerts cheap.
4. The budget_remaining_28d rule normalises the SLO into a "how much budget is left" number: (current − target) / (1 − target). A value of 0.5 means half the budget remains; 0 means exhausted; negative means overspent. This is the number you show in a big-bold-number Grafana panel.
5. The 28-day window is a rolling window — new minutes enter, old minutes fall off. A team that recovers reliability today sees the budget replenish gradually over the next 28 days, not resurrect instantly. This aligns incentives correctly: no "we'll fix it Monday and pretend nothing happened."

Output.

Table	age_seconds	meets_slo	28-day SLO	Budget remaining
analytics.orders	1247	1	0.9994	60%
analytics.customers	43200	1	1.0000	100%
analytics.events	923	1	0.9987	30%

Rule of thumb. Every table under a freshness SLO gets two gauges (age_seconds, meets_slo), one recording rule (slo:freshness:28d), and one big-bold-number Grafana panel showing budget remaining. This is the minimum viable freshness SLO stack.

Worked example — completeness SLI via source-target reconcile

Detailed explanation. The completeness SLI compares source and target row counts per hour and computes a ratio. It requires read access to both source and target and a shared time-column for windowing. Walk through the pattern for the orders pipeline.

• Metric definition. completeness_ratio{table="orders", hour="…"} = target_rowcount / source_rowcount.
• SLO. 99.5% of hours have ratio ≥ 0.99.
• Cadence. Compute at the top of each hour, once the safety window has passed.
• Storage. Persist per-hour to a slo_measurements table for the 28-day rolling calculation.

Question. Write the completeness reconcile SQL and the daily rollup that produces the SLO percentage.

Input.

Component	Value
Source	production.orders (event_ts)
Target	analytics.orders (event_ts, ingested_at)
Safety window	5 minutes
SLO threshold	ratio ≥ 0.99
SLO target	99.5%

Code.

-- Completeness reconcile — computed hourly, one row per (table, hour)
INSERT INTO slo_measurements(sli_name, table_name, window_start, value_num, meets_slo)
WITH windows AS (
    SELECT generate_series(
        date_trunc('hour', now() - INTERVAL '2 hours'),
        date_trunc('hour', now() - INTERVAL '1 hour'),
        INTERVAL '1 hour'
    ) AS window_start
),
src AS (
    SELECT w.window_start,
           COUNT(*) AS n_src
    FROM   windows w
    LEFT JOIN production.orders o
      ON o.event_ts >= w.window_start
     AND o.event_ts <  w.window_start + INTERVAL '1 hour'
    GROUP BY w.window_start
),
tgt AS (
    SELECT w.window_start,
           COUNT(*) AS n_tgt
    FROM   windows w
    LEFT JOIN analytics.orders o
      ON o.event_ts >= w.window_start
     AND o.event_ts <  w.window_start + INTERVAL '1 hour'
    GROUP BY w.window_start
)
SELECT
    'completeness'::text                             AS sli_name,
    'analytics.orders'::text                         AS table_name,
    src.window_start,
    tgt.n_tgt::numeric / NULLIF(src.n_src, 0)        AS value_num,
    CASE WHEN tgt.n_tgt::numeric / NULLIF(src.n_src, 0) >= 0.99
         THEN 1 ELSE 0 END                           AS meets_slo
FROM src
JOIN tgt USING (window_start);

-- Rollup — the 28-day completeness SLO percentage
SELECT
    table_name,
    AVG(meets_slo)::numeric(6, 5) AS slo_28d,
    COUNT(*) FILTER (WHERE meets_slo = 0)      AS bad_hours,
    COUNT(*)                                    AS total_hours,
    ROUND((AVG(meets_slo) - 0.995) / (1 - 0.995), 3) AS budget_remaining_frac
FROM slo_measurements
WHERE sli_name = 'completeness'
  AND window_start >= now() - INTERVAL '28 days'
GROUP BY table_name;

Step-by-step explanation.

1. The reconcile query uses generate_series to enumerate the specific hour windows to check — we deliberately go 1–2 hours back so the safety window (5 min) has already passed. This prevents counting in-flight rows as missing.
2. Left-joining source and target per hour gives us n_src and n_tgt even when a table is empty for the window. NULLIF(n_src, 0) avoids division-by-zero when the source itself was silent.
3. The ratio n_tgt::numeric / n_src is stored in value_num; the binary meets_slo records whether the ratio met the 0.99 threshold. Persisting both lets you back-fill dashboards and audit historical breaches.
4. The rollup computes AVG(meets_slo) over the trailing 28 days — that's the SLO percentage. bad_hours / total_hours gives you the human-readable version ("42 bad hours out of 672 = 93.75%").
5. The budget_remaining_frac column normalises the SLO into a "how much room is left" number. Positive = budget remaining; zero = exhausted; negative = you've overspent (and if this is a paying-customer SLA, credits are owed). Publish this to the Grafana dashboard next to the freshness budget.

Output.

table_name	slo_28d	bad_hours	total_hours	budget_remaining
analytics.orders	0.99702	2	672	40.4%
analytics.customers	0.99851	1	672	70.2%
analytics.events	0.99405	4	672	-19.0%

Rule of thumb. For every completeness SLO, persist the per-window measurement to a slo_measurements table — never recompute the SLO from raw source/target counts at query time. This gives you an auditable, backfill-tolerant, dashboard-cheap SLO history.

Worked example — quality SLI via dbt tests / Great Expectations

Detailed explanation. The quality SLI is the fraction of rows passing a battery of schema and business-rule checks. Modern data teams express these as dbt tests, Great Expectations expectations, or SQL data-diff assertions. Walk through wiring dbt tests to a quality SLO.

• Test suite. unique(order_id), not_null(order_id, customer_id, total_cents, status), accepted_values(status), relationships(customer_id) → customers.id, range(total_cents, 0, 10_000_000).
• SLI definition. quality_pass_rate = rows_passing_all / total_rows, computed per daily dbt run.
• SLO. 99.9% of daily runs have pass_rate ≥ 0.99.

Question. Configure the dbt tests, compute the quality pass rate, and roll it up to a monthly SLO.

Input.

Component	Value
Test framework	dbt tests (Postgres/Snowflake adapter)
Test batch	daily post-build
SLI pass threshold	≥ 0.99
SLO target	99.9% of daily runs

Code.

# dbt schema.yml — quality tests on the orders model
version: 2
models:
  - name: orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: customer_id
        tests:
          - not_null
          - relationships:
              to: ref('customers')
              field: id
      - name: total_cents
        tests:
          - not_null
          - dbt_utils.accepted_range:
              min_value: 0
              max_value: 10000000
      - name: status
        tests:
          - not_null
          - accepted_values:
              values: ['pending', 'shipped', 'delivered', 'cancelled', 'refunded']

-- Post-run macro — compute pass rate + insert into slo_measurements
{% macro record_quality_sli() %}
    INSERT INTO slo_measurements(sli_name, table_name, window_start, value_num, meets_slo)
    WITH results AS (
        SELECT model_name,
               SUM(CASE WHEN status = 'pass' THEN row_count ELSE 0 END) AS passed,
               SUM(row_count) AS total
        FROM   dbt_run_results
        WHERE  run_started_at >= date_trunc('day', now())
        GROUP  BY model_name
    )
    SELECT 'quality'                                 AS sli_name,
           'analytics.' || model_name                AS table_name,
           date_trunc('day', now())                  AS window_start,
           passed::numeric / NULLIF(total, 0)        AS value_num,
           CASE WHEN passed::numeric / NULLIF(total, 0) >= 0.99
                THEN 1 ELSE 0 END                    AS meets_slo
    FROM results;
{% endmacro %}

-- Monthly rollup
SELECT
    table_name,
    AVG(meets_slo)::numeric(6, 5) AS quality_slo_30d,
    COUNT(*) FILTER (WHERE meets_slo = 0) AS bad_days,
    COUNT(*)                              AS total_days
FROM slo_measurements
WHERE sli_name = 'quality'
  AND window_start >= now() - INTERVAL '30 days'
GROUP BY table_name;

Step-by-step explanation.

1. The dbt schema.yml declares the tests: uniqueness, null-ness, range, accepted values, and referential integrity. Each is a first-class dbt test that emits a pass or fail result with a row count of failing records.
2. The post-run macro (invoked via on-run-end in dbt_project.yml) aggregates the results into a per-model pass rate. Passed rows / total rows, computed per model, per day. This becomes the quality SLI.
3. The meets_slo binary records whether the day's pass rate cleared 99%. Persisting both the ratio (value_num) and the binary (meets_slo) lets you show both trend charts and SLO percentages.
4. The monthly rollup — same shape as freshness and completeness — computes AVG(meets_slo) over the trailing 30 days. A 99.9% SLO with 30 daily runs allows exactly 0.03 bad days per month — effectively "never miss." A 99% SLO allows ~1 bad day per quarter.
5. The reason quality SLOs are typically stricter (99.9% vs freshness's 99.9% of minutes) is that a daily run has far fewer observation windows than a per-minute freshness check. The same SLO percentage translates to very different bad-window counts based on cadence.

Output.

table_name	quality_slo_30d	bad_days	total_days
analytics.orders	1.00000	0	30
analytics.customers	0.96667	1	30
analytics.events	1.00000	0	30

Rule of thumb. For every table with a dbt or Great Expectations test suite, wire the pass-rate metric into the same slo_measurements table as freshness and completeness. Unified table + unified rollup rule = one dashboard for all four SLIs.

Senior interview question on SLIs and SLOs

A senior interviewer might ask: "You inherit a 500-table warehouse where 'monitoring' means Airflow email alerts on task failure. Design the SLI/SLO framework — how you'd pick the top tables to instrument, what SLIs you'd add per table, how you'd store measurements, and how you'd derive per-consumer SLO targets."

Solution Using a top-N table-prioritisation approach with a unified slo_measurements table and consumer-driven SLO derivation

-- 1. Prioritise tables by downstream query volume (warehouse audit log)
CREATE OR REPLACE VIEW slo_candidate_tables AS
SELECT
    table_schema || '.' || table_name AS table_name,
    COUNT(DISTINCT user_name)          AS distinct_users_7d,
    COUNT(*)                           AS queries_7d,
    ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS rn
FROM   snowflake.account_usage.access_history
WHERE  query_start_time >= now() - INTERVAL '7 days'
GROUP  BY 1;

-- The top 20 tables cover ~80% of downstream reads (Pareto)
SELECT * FROM slo_candidate_tables WHERE rn <= 20;

-- 2. Unified SLO measurement table
CREATE TABLE slo_measurements (
    sli_name         TEXT        NOT NULL,     -- 'freshness' | 'completeness' | 'volume' | 'quality'
    table_name       TEXT        NOT NULL,
    window_start     TIMESTAMPTZ NOT NULL,
    value_num        NUMERIC,                   -- raw measurement
    meets_slo        SMALLINT    NOT NULL,      -- 0 or 1
    recorded_at      TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
    PRIMARY KEY (sli_name, table_name, window_start)
);

CREATE INDEX idx_slo_meas_rollup
  ON slo_measurements (table_name, sli_name, window_start DESC);

-- 3. SLO catalogue — one row per (table, sli) with the agreed target
CREATE TABLE slo_catalogue (
    table_name       TEXT NOT NULL,
    sli_name         TEXT NOT NULL,
    slo_target       NUMERIC NOT NULL,      -- e.g. 0.999
    consumer_team    TEXT NOT NULL,          -- who signed the SLO
    threshold_json   JSONB NOT NULL,          -- SLI-specific parameters
    approved_at      TIMESTAMPTZ NOT NULL,
    approved_by      TEXT NOT NULL,
    PRIMARY KEY (table_name, sli_name)
);

# 4. SLO rollup job — runs hourly; recomputes 28-day percentages
import psycopg2

def rollup_slos(conn):
    with conn.cursor() as cur:
        cur.execute("""
            SELECT c.table_name, c.sli_name, c.slo_target,
                   AVG(m.meets_slo)::numeric(6, 5) AS current_slo_28d
            FROM   slo_catalogue c
            LEFT JOIN slo_measurements m
              ON m.table_name = c.table_name
             AND m.sli_name   = c.sli_name
             AND m.window_start >= now() - INTERVAL '28 days'
            GROUP BY c.table_name, c.sli_name, c.slo_target
        """)
        rows = cur.fetchall()

    for table, sli, target, current in rows:
        budget_remaining = (current - target) / (1 - target) if current else 0
        # Write to Prometheus pushgateway (or expose as Grafana table)
        push_metric('slo_current_28d',    current, table=table, sli=sli)
        push_metric('slo_budget_remaining', budget_remaining, table=table, sli=sli)

        # Ticket if any SLO's budget has dropped below 20%
        if 0 < budget_remaining < 0.2:
            open_ticket(f"SLO warning: {sli} on {table} at {budget_remaining:.0%} budget")

Step-by-step trace.

Step	Value	Reasoning
Prioritisation	top-20 tables by 7-day query count	Pareto — 20 tables = 80% of consumption
Unified table	slo_measurements with (sli_name, table_name, window) PK	one query for all four SLIs
SLO catalogue	slo_catalogue with per-(table, sli) target + consumer	contract, not target
Rollup cadence	hourly	recompute 28-day percentages cheaply
Ticket trigger	budget remaining < 20%	early warning before breach
Alert wiring	separate — burn-rate on the per-SLI meets_slo gauge	see next H2

After the framework is in place, adding an SLI for a new table is a two-step operation: add a row to slo_catalogue (with the agreed target and consumer), and wire the SLI-specific measurement into slo_measurements. All dashboards, rollups, and burn-rate alerts pick up the new SLI automatically because they iterate the catalogue.

Output:

Metric	Value
Tables covered by any SLI	top 20 (rising to 100 by Q2)
SLIs per table (target)	4 (freshness, completeness, volume, quality)
Rollup query cost	< 500 ms per hour
Budget-warning ticket latency	~1 hour of degradation
SLO catalogue reviewed	quarterly

Why this works — concept by concept:

• Top-N prioritisation — you cannot instrument 500 tables on day one. Ranking by downstream query volume (from the warehouse's own audit log) gives you the 20 tables that carry 80% of the reliability impact. Start there; extend as budget allows.
• Unified slo_measurements table — one wide-format table with sli_name as a column beats one narrow-format table per SLI. It means one dashboard, one rollup query, one alerting scheme covers all four axes. Adding a new SLI type is a data change, not a schema change.
• slo_catalogue as a contract — the catalogue is a Git-tracked, code-reviewed source of truth: which tables have which SLOs, what the targets are, who agreed to them, and when. An SLO that doesn't appear in the catalogue does not exist.
• Consumer-signed targets — the consumer_team and approved_by columns enforce the contract-not-target discipline. The platform team cannot unilaterally set a 99.9% SLO on orders; the fraud model owner (or whoever depends on it) must agree in writing.
• Cost — one 20-column slo_measurements row per (table, sli, window) — for 100 tables × 4 SLIs × per-hour cadence over 28 days, that's ~270k rows/month. Sub-gigabyte at rest, sub-second at query. The Prometheus/Grafana stack costs ~$50/month at this scale. Compared to $200k/year "data observability" tools that don't do SLO negotiation, this is 200x cheaper and 3x more valuable in interview answers.

Data validation
Topic — data-validation
Data validation and SLI problems

Practice →

SQL Topic — sql SQL reconcile and freshness problems

Practice →

---

3. Error budgets and burn-rate alerts

99.9% freshness = 43 minutes per month — spend it deliberately

The mental model in one line: an error budget is the operational contract that turns a percentage SLO into a concrete quantity of "bad time" the team is permitted to incur before feature releases must halt — 99.9% over a 30-day month is exactly 43 minutes and 12 seconds, and every SRE-style alerting decision (page now, ticket later, ignore) is grounded in how fast the team is spending against that budget, not on whether an individual event happened. Every senior data engineer must be able to derive the budget from the SLO percentage in their head, describe the multi-window multi-burn-rate alerting pattern, and defend the policy that halts releases when the budget is exhausted.

The budget math every senior DE knows without hesitation.

• 99% over 30 days = 30 × 24 × 60 × 0.01 = 432 minutes ≈ 7.2 hours.
• 99.5% = 216 minutes ≈ 3.6 hours.
• 99.9% = 43.2 minutes.
• 99.95% = 21.6 minutes.
• 99.99% = 4.32 minutes — "four nines" is aggressive for a data pipeline; usually reserved for the streaming layer where the source-of-truth is upstream.

Burn rate — the derivative of the budget.

• Definition. Burn rate = the rate at which the SLI is spending budget divided by the sustainable rate that would exhaust the budget exactly at month-end.
• Sustainable = 1x. If you spend budget at 1x, you'll use it all over 30 days — you'll hit exactly 99.9%.
• 14x = disaster. Spending at 14x means the whole month's budget would be gone in ~2 days. This is the fast-burn threshold that pages immediately.
• 6x = concerning. Spending at 6x means the budget is gone in ~5 days. This is the slow-burn threshold that tickets the team.
• 1x = normal. Spending at 1x is exactly on-plan — no alert.

The multi-window multi-burn-rate alerting pattern (Google SRE workbook).

• Fast-burn (short window). Compute burn rate over a 1-hour window. If it exceeds 14x, page immediately. Detects cliffs quickly with minimal delay.
• Slow-burn (long window). Compute burn rate over a 6-hour window. If it exceeds 6x, ticket the team. Detects creeping degradation without false pages.
• Both windows use the same SLI. The alert doesn't fire on an event; it fires on the rate of budget consumption over a rolling window.
• Why multi-window. A single 5-minute burn spike shouldn't page (transient); a 1-hour sustained burn should. A 6-hour slow burn shouldn't page but must be seen (the ticket).

Why not alert on every SLI dip?

• Alert fatigue. A hair-trigger alert on any missed minute produces dozens of pages per week, each of which the on-call resolves by acknowledging and going back to sleep. The signal-to-noise ratio drops to zero.
• False-positive cost. Every false page has a human cost (interrupted sleep, distraction, morale). The Google SRE book estimates false-page cost at ~$100 per event in engineering productivity; a team with 10 false pages per week costs the org $50k/year.
• Missed-signal cost. Under-alerting means the team learns about incidents from the CEO, not the pager. Balance is via the multi-window burn-rate mechanism — page when the budget consumption rate is genuinely alarming.

The release-halt policy.

• When the budget is exhausted. No feature releases against that pipeline until the budget replenishes at month-start. Only reliability fixes ship.
• Why the halt is non-negotiable. The alternative — "release anyway" — invalidates every future SLO promise. Consumers cannot trust "99.9% freshness" if the platform team ships changes when the budget is empty and freshness is already at 98%.
• How the halt is enforced. CI check that reads the SLO status; blocks the deploy if budget < 0. Emergency override with director-level approval only.
• What "release" means. Any change to the pipeline code, DAG DSL, transformation SQL, source-schema contract, or infrastructure. Not just "new feature" — includes "innocuous refactor" too.

Common interview probes on error budgets.

• "How many minutes of downtime does 99.9% allow per month?" — required answer: 43.
• "What's a burn-rate alert?" — required answer: multi-window multi-rate on the SLI, from the SRE workbook.
• "Why don't you page on every SLI dip?" — required answer: alert fatigue; false-page cost.
• "What happens when the error budget is exhausted?" — required answer: release halt; only reliability fixes ship.

Worked example — freshness burn-rate PromQL against a freshness metric

Detailed explanation. The canonical multi-window multi-burn-rate alert setup: two rules, one fast (1-hour window, 14x threshold) and one slow (6-hour window, 6x threshold). Both reference the same data_freshness_meets_slo gauge. Walk through the PromQL.

• SLI gauge. data_freshness_meets_slo{table="analytics.orders"} — 0/1 per minute.
• Target. 99.9% (bad-minute rate = 0.001).
• Fast alert. 1-hour window, 14x burn = 1.4% bad in the hour = page.
• Slow alert. 6-hour window, 6x burn = 0.6% bad over the 6 hours = ticket.

Question. Write the Prometheus alert rules for the freshness SLI on analytics.orders.

Input.

Component	Value
SLI	data_freshness_meets_slo
SLO target	0.999
Fast window	1 h
Slow window	6 h
Fast burn threshold	14× normal = ≥ 1.4% bad
Slow burn threshold	6× normal = ≥ 0.6% bad

Code.

# Prometheus alerting rules — multi-window multi-burn-rate for freshness
groups:
- name: freshness_burn_rate
  rules:
    # Fast burn — page
    - alert: FreshnessBudgetFastBurn
      expr: |
        (
          1 - avg_over_time(data_freshness_meets_slo{table="analytics.orders"}[1h])
        ) > 0.014       # 14 × 0.001
        and
        (
          1 - avg_over_time(data_freshness_meets_slo{table="analytics.orders"}[5m])
        ) > 0.014
      for: 2m
      labels:
        severity: critical
        table: analytics.orders
        sli: freshness
      annotations:
        summary: "Freshness burning 14× normal — page on-call"
        runbook: "https://runbooks.internal/data/freshness-orders"
        current_burn: "{{ $value | humanizePercentage }}"

    # Slow burn — ticket
    - alert: FreshnessBudgetSlowBurn
      expr: |
        (
          1 - avg_over_time(data_freshness_meets_slo{table="analytics.orders"}[6h])
        ) > 0.006       # 6 × 0.001
        and
        (
          1 - avg_over_time(data_freshness_meets_slo{table="analytics.orders"}[30m])
        ) > 0.006
      for: 15m
      labels:
        severity: warning
        table: analytics.orders
        sli: freshness
      annotations:
        summary: "Freshness burning 6× normal for 6h — file ticket"
        runbook: "https://runbooks.internal/data/freshness-orders"
        current_burn: "{{ $value | humanizePercentage }}"

Step-by-step explanation.

1. The avg_over_time(...meets_slo[1h]) computes the fraction of minutes in the last hour that met the SLO. Subtracting from 1 gives the bad fraction. If bad fraction > 1.4%, we're spending at 14x the sustainable rate — the fast-burn threshold.
2. The and clause with a 5-minute window is the "double window" guard: the burn rate must be high in both the 1-hour and the 5-minute view. This prevents an old spike (already resolved) from re-firing the alert as it slowly ages out of the 1-hour window.
3. for: 2m is the Prometheus stability duration — the alert must be true for 2 minutes before firing. This absorbs metric-scrape jitter and prevents flapping. Combined with the burn-rate math, the fast-burn alert fires ~7 minutes after a cliff-fall begins.
4. The slow-burn rule mirrors the structure but with a 6-hour window and 6x threshold. for: 15m is longer because slow burns are inherently slower-changing; the extra stability window prevents ticket noise.
5. The annotations.runbook link is mandatory — no runbook link = alert doesn't ship. The on-call reads the runbook the moment the page arrives; without it, MTTR balloons while they figure out what to do.

Output.

Burn rate	Time-to-fire	Alert path	Human action
14× sustained 7 min	~7 min after cliff	fast-burn → PagerDuty → phone	page; on-call runs runbook
6× sustained 15 min in 6h window	~15 min into slow burn	slow-burn → PagerDuty → Slack	ticket; team triages
< 6×	never	no alert	budget dashboard shows draw

Rule of thumb. Every freshness (and completeness, volume, quality) SLO ships with two burn-rate alerts: one fast, one slow, sharing the same SLI. Skipping either creates a gap — fast-only misses creeping degradation; slow-only misses cliff-falls.

Worked example — monthly budget accounting spreadsheet template

Detailed explanation. The team needs a monthly view of every SLO's budget consumption to hold quarterly reviews and decide next-quarter SLO adjustments. Build the accounting template.

• Columns. Table, SLI, Target, Actual, Budget spent (min), Budget remaining (min), Status.
• Cadence. Refreshed daily; reviewed weekly at the platform team standup; formally reviewed monthly.
• Source. The slo_measurements + slo_catalogue tables from H2 2.

Question. Build the monthly budget-accounting SQL that produces one row per (table, SLI) with budget spent and remaining.

Input.

Component	Value
Source tables	slo_measurements, slo_catalogue
Window	rolling 30 days
Cadence	daily refresh
Output columns	table, sli, target, actual, spent_min, remaining_min, status

Code.

-- Monthly SLO budget accounting
WITH observed AS (
    SELECT
        c.table_name,
        c.sli_name,
        c.slo_target,
        AVG(m.meets_slo)::numeric AS actual_slo,
        COUNT(*)                    AS observation_windows,
        COUNT(*) FILTER (WHERE m.meets_slo = 0) AS bad_windows
    FROM   slo_catalogue c
    JOIN   slo_measurements m
      ON   m.table_name = c.table_name
     AND   m.sli_name   = c.sli_name
     AND   m.window_start >= now() - INTERVAL '30 days'
    GROUP  BY c.table_name, c.sli_name, c.slo_target
),
budget AS (
    SELECT
        table_name,
        sli_name,
        slo_target,
        actual_slo,
        observation_windows,
        bad_windows,
        -- convert to minutes assuming per-minute observation windows
        ROUND((1 - slo_target) * observation_windows) AS budget_min_total,
        bad_windows                                    AS budget_min_spent,
        GREATEST(0, ROUND((1 - slo_target) * observation_windows) - bad_windows) AS budget_min_remaining
    FROM observed
)
SELECT
    table_name,
    sli_name,
    slo_target,
    actual_slo,
    budget_min_total,
    budget_min_spent,
    budget_min_remaining,
    CASE
        WHEN budget_min_remaining = 0 AND actual_slo < slo_target THEN 'EXHAUSTED — halt releases'
        WHEN budget_min_remaining::float / budget_min_total < 0.2 THEN 'CRITICAL — 20% left'
        WHEN budget_min_remaining::float / budget_min_total < 0.5 THEN 'WARNING — 50% left'
        ELSE                                                            'HEALTHY'
    END AS status
FROM budget
ORDER BY budget_min_remaining::float / budget_min_total ASC;

Step-by-step explanation.

1. The observed CTE joins the catalogue against the measurements over the last 30 days, aggregating to per-(table, SLI) actual SLO percentage, count of observation windows, and count of bad windows.
2. The budget CTE computes three quantities: budget_min_total (total minutes of budget the SLO allows), budget_min_spent (already-consumed bad minutes), budget_min_remaining (what's left, floored at 0). The math is: total = (1 − target) × windows; if target is 0.999 and windows is 43200 minutes/month, total = 43.2 minutes.
3. The status column translates numeric budget-remaining into an operational label. EXHAUSTED triggers the release-halt; CRITICAL and WARNING are ticket-level heads-ups.
4. Ordering by "% budget remaining ASC" puts the most at-risk SLOs at the top of the table — the review meeting focuses on those first.
5. The monthly review meeting walks this table row by row: for each CRITICAL or EXHAUSTED row, discuss what happened, what action was taken, whether the SLO target is still reasonable. For each HEALTHY row, discuss whether the target could be tightened next quarter.

Output.

table	sli	target	actual	total	spent	remaining	status
analytics.orders	freshness	0.999	0.9993	43	30	13	CRITICAL — 20% left
analytics.events	freshness	0.999	0.9987	43	56	0	EXHAUSTED — halt releases
analytics.customers	freshness	0.999	1.0000	43	0	43	HEALTHY
analytics.orders	completeness	0.995	0.9970	216	130	86	WARNING — 50% left

Rule of thumb. The monthly budget accounting table is the single most useful reliability artifact for a senior data-platform team. Review it every Monday standup; walk it row-by-row every month-end; use it to decide whether to tighten or loosen each SLO next quarter.

Worked example — release-halt policy enforced in CI

Detailed explanation. When an SLO's budget is exhausted, releases against the affected pipeline must halt. The most effective enforcement is a CI check that reads the SLO status and fails the deploy if any relevant SLO is EXHAUSTED. Walk through the check.

• Check inputs. Which tables the PR touches (from git diff of dbt models / DAG code), current SLO status of each.
• Fail condition. Any touched table has an exhausted budget.
• Emergency override. A director-level approver adds the budget-override label to the PR; the check is skipped for that PR.
• Reliability fixes exempt. PRs labelled reliability-fix are exempt.

Question. Write the CI check script that halts a release when any relevant SLO is exhausted.

Input.

Component	Value
Trigger	PR to main branch
Data source	slo_measurements view or Prometheus
Fail condition	budget_min_remaining = 0 for touched tables
Override	PR label budget-override (director sign-off)
Exempt	PR label reliability-fix

Code.

# release_halt_check.py — runs in CI
import os
import subprocess
import sys
import psycopg2

DB_DSN     = os.environ['SLO_DB_DSN']
PR_LABELS  = os.environ.get('PR_LABELS', '').split(',')

# Exempt labels
if 'reliability-fix' in PR_LABELS or 'budget-override' in PR_LABELS:
    print('Skipping SLO check due to label')
    sys.exit(0)

# 1. Identify touched tables from the PR diff
changed = subprocess.check_output(['git', 'diff', '--name-only', 'main...HEAD']).decode()
touched_tables = set()
for path in changed.splitlines():
    # dbt models under models/analytics/orders.sql -> analytics.orders
    if path.startswith('models/'):
        parts = path.replace('models/', '').replace('.sql', '').split('/')
        if len(parts) == 2:
            touched_tables.add(f'{parts[0]}.{parts[1]}')

# 2. Query current SLO status for those tables
conn = psycopg2.connect(DB_DSN)
with conn.cursor() as cur:
    cur.execute("""
        SELECT table_name, sli_name, budget_min_remaining, status
        FROM   v_slo_budget_status
        WHERE  table_name = ANY(%s)
    """, (list(touched_tables),))
    rows = cur.fetchall()

# 3. Fail if any SLO is exhausted
exhausted = [r for r in rows if r[3] == 'EXHAUSTED — halt releases']
if exhausted:
    print('SLO release halt triggered:')
    for r in exhausted:
        print(f'  {r[0]} · {r[1]} · remaining={r[2]} min · {r[3]}')
    print('\nAdd label `reliability-fix` to ship a reliability fix,')
    print('or `budget-override` (director sign-off) for an emergency override.')
    sys.exit(1)

print('SLO check passed — all touched tables within budget.')
sys.exit(0)

# .github/workflows/deploy.yml — invoke the check
name: Deploy pipeline changes
on:
  pull_request:
    branches: [main]

jobs:
  slo-check:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
        with: { fetch-depth: 0 }
      - name: SLO release-halt gate
        env:
          SLO_DB_DSN: ${{ secrets.SLO_DB_DSN }}
          PR_LABELS:  ${{ join(github.event.pull_request.labels.*.name, ',') }}
        run: python3 release_halt_check.py

Step-by-step explanation.

1. The CI check first inspects PR labels — reliability-fix and budget-override are exempt. This is the escape hatch for legitimate reliability work and for director-approved emergencies. The label check is the first gate because the queries below are unnecessary if we're exempt.
2. The script parses the git diff to extract which tables the PR touches. dbt convention is models/<schema>/<table>.sql → <schema>.<table>. Extend for other frameworks (Airflow DAGs → touched table names via a dag_to_tables.yaml mapping).
3. The v_slo_budget_status view (built on top of the earlier budget-accounting query) returns the current status per SLO. Only the SLOs on touched tables are relevant to this PR.
4. Any EXHAUSTED SLO fails the check. The error message names the exact SLO(s) and points to the two override labels. This is the contract the release-halt policy makes with engineers — no surprises, always a documented path forward.
5. The workflow file wires the check into every PR. GitHub required-checks makes this gate mandatory for merge; only admin override can bypass. This is the mechanism by which the SLO becomes an operational constraint, not a decorative percentage.

Output.

PR touches	Any SLO exhausted?	Label	CI result
analytics.orders (freshness EXHAUSTED)	yes	(none)	fails; blocks merge
analytics.orders (freshness EXHAUSTED)	yes	reliability-fix	passes
analytics.orders (freshness EXHAUSTED)	yes	budget-override	passes; requires director
analytics.customers (all HEALTHY)	no	(none)	passes

Rule of thumb. The release-halt policy without CI enforcement is a suggestion. With CI enforcement — required check + labelled exemptions + audit log of overrides — it becomes the operational reality that keeps SLO promises credible.

Senior interview question on error budgets

A senior interviewer might ask: "You have a freshness SLO of 99.9% on the orders warehouse table. Halfway through the month, an upstream schema change caused a 6-hour outage, burning most of the monthly budget. What alerts should have fired earlier, what's the release policy for the rest of the month, and how do you use this incident to negotiate a saner SLO next quarter?"

Solution Using multi-window burn-rate alerts, immediate release-halt, and a postmortem-driven SLO renegotiation

# 1. Alerts that should have fired (see H2 3 above) — verify these are wired
alerts_that_should_fire:
  fast_burn_1h:
    window: 1h
    threshold: 14x normal
    action: PagerDuty (page)
    expected_fire_time: ~15 min into outage

  slow_burn_6h:
    window: 6h
    threshold: 6x normal
    action: Slack + Jira ticket
    expected_fire_time: ~1 h into outage

  budget_low_warning:
    trigger: budget_min_remaining < 20% of total
    action: ticket to platform-team lead
    expected_fire_time: on next hourly rollup after threshold crossed

  budget_exhausted:
    trigger: budget_min_remaining = 0
    action: CI release-halt engaged; email leadership
    expected_fire_time: on next hourly rollup after breach

# 2. Post-incident policy runner — activates the release halt automatically
import os
import psycopg2
import requests

def enforce_release_halt():
    conn = psycopg2.connect(os.environ['SLO_DB_DSN'])
    with conn.cursor() as cur:
        cur.execute("""
            SELECT table_name, sli_name, budget_min_remaining
            FROM   v_slo_budget_status
            WHERE  budget_min_remaining = 0
        """)
        exhausted = cur.fetchall()

    if not exhausted:
        return

    # Update the shared release-gate flag consumed by CI
    with conn.cursor() as cur:
        for table, sli, _ in exhausted:
            cur.execute("""
                INSERT INTO release_gate(table_name, sli_name, halted_at, reason)
                VALUES (%s, %s, now(), 'budget_exhausted')
                ON CONFLICT (table_name, sli_name) DO NOTHING
            """, (table, sli))
        conn.commit()

    # Notify leadership + on-call
    body = 'Release halt engaged: ' + ', '.join(f'{t}·{s}' for t, s, _ in exhausted)
    requests.post(os.environ['SLACK_WEBHOOK'], json={'text': body})

if __name__ == '__main__':
    enforce_release_halt()

<!-- 3. Postmortem-driven SLO renegotiation template -->
# Incident: 6h freshness breach on analytics.orders — 2026-07-14

## SLO status this month
| SLI | Target | Actual (day 15) | Budget spent | Remaining |
|---|---|---|---|---|
| freshness | 99.9% | 98.7% | 388 min | 0 min (overspent by 345 min) |

## Root policy question
The SLO was set at 99.9% based on the dashboard-owner's stated tolerance
(~1h/month). Actual pipeline reliability over the last 90 days averaged
99.85% — the target was already tighter than achievable.

## Recommendation
Renegotiate to 99.5% freshness (216 min/month = 3.6 hours), which:
  • Reflects observed pipeline reliability
  • Still constrains bad hours to < 4/month
  • Allows the team to invest in one reliability project per quarter
    rather than burning weekly cycles on false-alert cleanup

## Investments required to sustain 99.9%
  • Hot-standby DAG on backup schedule (~2 engineer-weeks build, +25% compute)
  • Automatic upstream-schema-drift detection (~1 sprint)
  • Recommend: only invest if executive requires 99.9%

Step-by-step trace.

Concern	Answer	Reasoning
Alerts fired	fast + slow + budget-low	multi-window catches both cliff and creep
Release halt	automatic on budget = 0	CI-enforced; policy = credible
Mid-month spend	388 min out of 43	overspent by 8x — SLO not achievable at current reliability
Postmortem action	renegotiate to 99.5% or invest to hit 99.9%	contract must be sustainable
Next quarter	99.5% freshness or 2 sprints of reliability work	executive trade-off

After the incident, the release halt is enforced automatically; leadership is notified; the postmortem produces a concrete SLO-renegotiation recommendation grounded in observed reliability, not aspiration. Next quarter's SLO catalogue is updated with the new target and the reasoning.

Output:

Metric	Value
Incident duration	6 h
Budget consumption	388 min (~9× monthly budget)
SLO breached	freshness on analytics.orders
Release halt engaged	yes; auto-lifted at month-start
Postmortem recommendation	renegotiate to 99.5% freshness
SLO catalogue updated	next quarter

Why this works — concept by concept:

• Multi-window multi-burn-rate — the fast-burn alert paged within 15 minutes of the cliff-fall; the slow-burn alert ticketed within an hour. No incident should ever be "found" hours after it started when both alerts are wired.
• Automatic release halt — the release_gate table + CI check enforces the policy without human intervention. This is the mechanism that turns the error budget from an accounting fiction into an operational reality.
• Postmortem-driven renegotiation — the incident revealed that 99.9% freshness was not achievable at current reliability. The postmortem is the vehicle for surfacing this and forcing a decision: renegotiate the SLO or invest in reliability. Either is fine; the wrong answer is "keep the target and hope."
• Overspend accounting — the budget-remaining column goes negative when overspent (-345 min in this case). This visible number is the argument in the renegotiation meeting: "we set a target we systematically miss by 8x."
• Cost — one PagerDuty page, one Jira ticket, one leadership email, one 1-hour postmortem meeting, one SLO catalogue update. Compared to the alternative — silently missing the SLO every month while consumers lose trust — this cycle costs about half an engineer-day per incident and buys credibility with every downstream team.

Time series
Topic — time-series
Time-series and burn-rate problems

Practice →

SQL Topic — time-series/sql SQL time-series and window problems

Practice →

---

4. On-call rotations and incident runbooks for data

Rotation × escalation × runbook — the three anchors of data on-call that scale past 10 pipelines

The mental model in one line: a data on-call rotation is the scheduled, documented ownership of who wakes up when an SLI breach pages, structured as a primary + secondary weekly rotation (or follow-the-sun for global teams) with a defined handoff ritual, a three-tier escalation ladder for when the primary cannot mitigate alone, and a 5-field runbook linked from every alert that turns "what do I do now?" into a scripted playbook — the three anchors together are what make data reliability sustainable past 10 pipelines and 3 on-call engineers. Every senior data engineer must be able to describe the rotation shape, the handoff protocol, the escalation matrix, and the runbook template in a single interview breath.

The rotation shape.

• Primary + secondary. Two people on rotation each week. Primary gets paged first (5 min); secondary escalated to at 15 min if primary doesn't ack. Both are compensated for on-call.
• Weekly cadence. One-week shifts. Shorter (daily) creates handoff overhead; longer (bi-weekly) creates burnout.
• Follow-the-sun for global teams. A US team + an EU team + an APAC team can each own their business-hours 8-hour block and hand off at the boundary. Sub-daily rotations only in this model.
• Fair rotation length. Aim for on-call every 4–8 weeks per engineer. More frequent burns people out; less frequent means they forget the muscle memory.

The handoff ritual.

• When. Monday morning at a fixed time (e.g. 10:00 local for the incoming on-call).
• Duration. 15–20 minutes.
• Attendees. Outgoing primary, incoming primary, incoming secondary. Team lead optional.
• Content. Open incidents, this-week's SLO/budget status, upcoming known changes (planned deploys, schema migrations), any custom alerts snoozed and why.
• Artefact. Handoff doc committed to a handoffs/YYYY-MM-DD.md folder — permanent record for postmortems and audit.

The escalation ladder — three tiers.

• Tier 1 (immediate, 0–5 min). Primary on-call. Pager wakes them. They own initial triage and mitigation.
• Tier 2 (15 min if unresolved). Secondary + team lead. Extra hands, additional context. Common when Tier 1 has diagnosed but needs help executing (privileged access, unfamiliar system).
• Tier 3 (30 min if unresolved). Engineering manager + platform SRE + downstream consumer contact. When incident is affecting external commitments or requires cross-team coordination.
• Tier 4 (60 min). VP-level. Reserved for outages affecting revenue-generating customer flows or regulatory obligations.

The runbook — 5 fields, no more.

• Symptom. What the alert looks like when it fires. "Freshness burn rate > 14× on analytics.orders for 5+ minutes."
• Detection. Where to look to confirm the symptom is real. "Check the Grafana freshness_age_seconds panel; check the Airflow DAG status; check upstream Postgres pg_stat_replication."
• Mitigation. The steps to stop the bleeding. "Restart the DAG. If still failing, disable the upstream connector to prevent backlog. If disk full, page storage team."
• Escalation. When and to whom. "If not mitigated in 15 min, escalate to secondary + team lead. If cross-team required, engage Tier 3."
• Owner. Who owns this runbook and reviews it quarterly. "@platform-team, reviewed 2026-Q2."

Why the runbook is mandatory.

• Fresh 3 AM brain. On-call brains are 30% as effective at 3 AM. The runbook turns novel problem-solving into script-following.
• Newer team members. A rotation with 6 engineers means each person owns 1/6 of the deep knowledge. The runbook is how the other 5/6 gets used.
• Post-incident continuity. Runbooks improve after every incident (see H2 5 postmortem). A team that treats runbooks as living documents converges on incident-proof playbooks over quarters.

Common interview probes on on-call.

• "What does your on-call rotation look like?" — required answer: primary + secondary + weekly + handoff ritual.
• "Walk me through your escalation ladder." — required answer: three tiers with times and roles.
• "What's in a runbook?" — required answer: 5 fields (symptom, detection, mitigation, escalation, owner).
• "How do you keep runbooks up to date?" — required answer: reviewed quarterly + updated after every postmortem.

Worked example — runbook for "freshness SLO breach on orders table"

Detailed explanation. Build a concrete runbook for the freshness SLO breach on analytics.orders. Every alert on that SLI links to this runbook.

• Alert. FreshnessBudgetFastBurn{table="analytics.orders", sli="freshness"}.
• On-call target. Primary DE. Escalate to secondary + platform lead after 15 min.
• Common causes. Upstream Postgres connector stalled, DAG task failure, warehouse merge queue backed up.

Question. Write the 5-field runbook for the freshness breach.

Input.

Field	Content type
Symptom	one-line description of the alert
Detection	3–5 diagnostic commands or dashboard checks
Mitigation	ordered list of remediation steps
Escalation	when and who
Owner	team + last-reviewed date

Code.

# Runbook — Freshness SLO breach on analytics.orders

## Symptom
Alert: `FreshnessBudgetFastBurn{table="analytics.orders", sli="freshness"}`.
Freshness burning at 14× normal for 5+ minutes. Downstream dashboards
(Looker "Sales Ops") will display stale data older than 65 minutes.

## Detection
1. **Grafana** — open the "Data reliability · analytics.orders" dashboard.
   Confirm `freshness_age_seconds` is rising and above 3900 s (65 min).
2. **Airflow** — check `dag_orders_hourly` in Airflow UI. Any red tasks?
   Any tasks stuck in `queued` for > 10 min?
3. **Upstream Postgres** — SSH to db-primary and run:
   `SELECT * FROM pg_replication_slots WHERE slot_name = 'debezium_orders';`
   Check `active = true` and `lag_bytes < 100MB`.
4. **Warehouse** — check Snowflake `WAREHOUSE_LOAD_HISTORY` for the merge
   warehouse. Is queue depth > 20 for the last 10 minutes?

## Mitigation
Try in order; move to next step if previous doesn't resolve within 5 min:

1. **Restart the DAG.** Airflow → dag_orders_hourly → Clear failed task.
   If succeeds, watch freshness metric for 15 min to confirm recovery.
2. **Restart the Debezium connector** (if replication slot is stale):
   `kubectl -n data rollout restart deploy/debezium-orders`.
   Watch slot lag drop; freshness should catch up within 5 min.
3. **Scale the merge warehouse** (if Snowflake queue is deep):
   `ALTER WAREHOUSE merge_wh SET WAREHOUSE_SIZE = 'LARGE';` for 1 h,
   then revert. Cost is +$4/h for one hour.
4. **Disable the upstream connector to protect backlog** (last resort):
   `kubectl -n data scale deploy/debezium-orders --replicas=0`.
   This stops new WAL consumption but preserves the slot. Only do this
   after Tier 2 approval — the pipeline will require manual catch-up.

## Escalation
- 15 min without resolution → page **secondary** + **@platform-lead**.
- 30 min → engage **@eng-manager-data** + notify **@sales-ops-owner**
  (dashboard consumer) via Slack #sales-ops.
- 60 min affecting revenue reporting → engage **@vp-data**.

## Owner
`@platform-team`. Last reviewed: 2026-Q2. Next review: 2026-Q3.
Update this runbook after every postmortem involving analytics.orders.

Step-by-step explanation.

1. The Symptom section names the exact alert label and describes what the reader will see. On-call arrives at the runbook 30 seconds after the page fires; they need to confirm "yes, this is my situation" in one glance.
2. The Detection section is a sequential checklist of dashboards and commands to run. Each step is copy-pasteable. Ordered from most-likely-to-be-the-issue (Airflow, upstream Postgres) to least-likely (warehouse queue). This ordering compresses average diagnosis time.
3. The Mitigation section is ordered remediation steps, each with a "stop if this works" guard. The order goes from lowest-risk (restart the DAG) to highest-risk (disable the upstream connector). Each step includes success criteria — "watch freshness metric for 15 min."
4. The Escalation section is time-bounded — 15 min, 30 min, 60 min. Escalation targets are named with @-handles so the on-call can page them directly from the runbook. The 60-min VP-level escalation is only for revenue-affecting incidents; naming the criterion prevents over-escalation.
5. The Owner section records who maintains the runbook and when it was last reviewed. Runbooks that go unreviewed for a year are stale; quarterly review + post-postmortem update keeps them alive. A runbook with no owner is a runbook no-one improves.

Output.

Field	Content
Symptom	2-sentence alert description + consumer impact
Detection	4-step checklist with copy-paste commands
Mitigation	4-step ordered try-list with success criteria
Escalation	3 time-bounded tiers with named targets
Owner	team + last-reviewed date + review cadence

Rule of thumb. Every runbook fits on one screen. If the mitigation section runs past 5 numbered steps, split it into sub-runbooks. On-call brains at 3 AM cannot follow a 20-step checklist.

Worked example — runbook for "volume SLI < 50% of 7-day median"

Detailed explanation. The volume SLI catches "row count is wildly wrong." Build the runbook for the alert that fires when the target table's count drops below 50% of the rolling 7-day median for the hour of day.

• Alert. VolumeBurnRateFastBurn{table="analytics.orders"} — sustained bad-volume ratio in the 1-hour window.
• Common causes. Upstream feed truncated, source query WHERE clause bug, backfill job overwriting live data.

Question. Write the runbook for the volume alert.

Input.

Field	Content type
Symptom	volume dropped below 50% of baseline
Detection	compare source vs target counts
Mitigation	quarantine target; investigate upstream
Escalation	product-owner + platform-lead
Owner	platform-team

Code.

# Runbook — Volume SLI breach on analytics.orders

## Symptom
Alert: `VolumeBurnRateFastBurn{table="analytics.orders"}`.
Hourly row count on analytics.orders is < 50% of the 7-day median for
this hour-of-day. Dashboards showing "orders per hour" will display a
misleading dip; downstream ML models may retrain on biased data.

## Detection
1. **Grafana** — "Data reliability · analytics.orders volume" panel.
   Confirm actual count is < 50% of expected baseline.
2. **Warehouse count query** — run:
   `SELECT date_trunc('hour', event_ts), COUNT(*) FROM analytics.orders
    WHERE event_ts >= now() - INTERVAL '3 hours' GROUP BY 1 ORDER BY 1;`
   Confirm the drop is real (not a rendering issue).
3. **Source count query** — run against production.orders:
   `SELECT date_trunc('hour', event_ts), COUNT(*) FROM production.orders
    WHERE event_ts >= now() - INTERVAL '3 hours' GROUP BY 1 ORDER BY 1;`
   Is the drop in the source too?
4. **Recent deploys** — check the changelog for any deploy in the last
   24 h that touched the orders pipeline or the source query.

## Mitigation

### Case A: source is also low (upstream issue)
1. Notify **@service-orders-owner** (upstream service team).
2. Do NOT modify the pipeline — it's correctly reflecting reality.
3. Post to #incidents: "Upstream orders source is producing X% of normal;
   downstream is accurate; investigating with @service-orders-owner."

### Case B: source is normal, target is low (pipeline issue)
1. **Quarantine the affected partitions**:
   `ALTER TABLE analytics.orders RENAME PARTITION FOR (h='2026-07-30 14:00')
    TO analytics.orders_quarantine_20260730_14;`
2. **Backfill** by running:
   `airflow tasks run dag_orders_hourly backfill --start-date=... --end-date=...`
3. Verify count matches source before dropping the quarantine partition.

## Escalation
- 20 min without root cause → page **secondary** + **@platform-lead**.
- If Case A and upstream unresponsive within 30 min → engage
  **@eng-manager-orders** and **@product-orders**.
- If Case B backfill takes > 2 h → notify dashboard consumers via
  #sales-ops Slack.

## Owner
`@platform-team`. Last reviewed: 2026-Q2. Next review: 2026-Q3.

Step-by-step explanation.

1. The symptom section again names the exact alert and calls out the consumer impact — the "misleading dip" language forces the on-call to understand what dashboards will show, not just what a metric says. This impact framing drives urgency.
2. The detection section is Case-A-vs-Case-B in disguise: comparing source and target counts is the diagnostic that branches the mitigation. This is a common pattern for volume runbooks — is it a data issue or a pipeline issue?
3. The mitigation section is branched into two cases based on the diagnostic. Case A (upstream issue) requires no pipeline change — a common on-call trap is to "fix" a pipeline that's correctly reflecting a broken source. Case B (pipeline issue) requires quarantine + backfill.
4. The quarantine step (RENAME PARTITION ... TO ..._quarantine_...) is a reversible mitigation — it removes bad data from the live table without deleting it. Backfill then produces the correct data; the quarantine partition can be dropped after verification.
5. Escalation names different people for Case A vs Case B — Case A escalates to the service-owner (upstream), Case B stays in the platform team. This differentiation is what stops the wrong team from being paged repeatedly for an incident they can't fix.

Output.

Case	Diagnostic	Mitigation	Escalation
A: source low	source count ≈ target count	no pipeline change; notify service-owner	@eng-manager-orders
B: source normal	source count normal; target low	quarantine + backfill	@platform-lead

Rule of thumb. For any SLI with more than one common cause, branch the mitigation section by diagnostic. A single monolithic mitigation list encourages the on-call to run every step in order regardless of what actually broke.

Worked example — escalation matrix and comms template

Detailed explanation. The escalation matrix documents who is paged at each tier for each incident class. The comms template gives the on-call ready-made language for Slack, status pages, and executive updates. Build both.

• Matrix. Per-service, per-tier, named on-call groups.
• Comms template. Fill-in-the-blank Slack message + hourly update cadence.

Question. Build the escalation matrix for the data platform and the standard comms template for a Sev-1 incident.

Input.

Component	Value
Services	orders, customers, events, payments
Tiers	1 (primary), 2 (+lead), 3 (+eng-mgr), 4 (+VP)
Comms channels	#incidents (Slack), status.internal (page), executive email
Update cadence	every 30 min while incident is live

Code.

# escalation_matrix.yml — versioned in Git; referenced by every runbook
services:
  orders:
    tier_1: '@data-oncall-primary'
    tier_2: '@data-oncall-secondary,@platform-lead'
    tier_3: '@eng-manager-data,@platform-sre,@service-orders-owner'
    tier_4: '@vp-data,@vp-eng'
  customers:
    tier_1: '@data-oncall-primary'
    tier_2: '@data-oncall-secondary,@platform-lead'
    tier_3: '@eng-manager-data,@platform-sre,@service-customers-owner'
    tier_4: '@vp-data,@vp-eng'
  events:
    tier_1: '@streaming-oncall-primary'
    tier_2: '@streaming-oncall-secondary,@streaming-lead'
    tier_3: '@eng-manager-streaming,@platform-sre'
    tier_4: '@vp-data,@vp-eng'
  payments:
    tier_1: '@data-oncall-primary'
    tier_2: '@data-oncall-secondary,@platform-lead,@security-oncall'
    tier_3: '@eng-manager-data,@security-lead,@finance-ops'
    tier_4: '@vp-data,@vp-eng,@cfo'   # PCI-scope; finance leadership involved

<!-- comms_template.md — copy-paste for the on-call -->
## Incident: {sev} — {short_title}

**Start:** {utc_timestamp}
**Impact:** {who_is_affected_and_how}
**Current status:** {investigating|mitigating|monitoring|resolved}
**Next update:** {timestamp}
**On-call:** {@primary}, {@secondary}
**Ticket:** {link_to_incident_doc}

### Timeline
- {t+00:00} Alert fired: {alert_name}
- {t+00:04} On-call acked; runbook opened
- {t+00:12} Detection confirmed: {finding}
- {t+00:22} Mitigation applied: {action}
- {t+00:35} Recovery observed on {metric}
- {t+01:00} Incident resolved; postmortem scheduled for {date}

### Consumer notification
| Consumer | Notified | Method |
|---|---|---|
| Sales Ops | yes | #sales-ops Slack + email to VP-Sales |
| Fraud ML | yes | #ml-fraud Slack |
| Reverse ETL | yes | #revops Slack |

Step-by-step explanation.

1. The escalation matrix is a single YAML file per data platform. Every runbook references it by service name (escalation: matrix.orders.tier_1) so name changes propagate. When a person leaves the team, one edit updates every runbook.
2. The tier structure is the same across services — 4 tiers, defined times. Different services have different Tier 2/3/4 populations (payments adds security + finance because of PCI scope), but the tier concept is stable.
3. The comms template gives the on-call a fill-in-the-blank Slack post. This matters because at 3 AM, composing an incident comms message from scratch is error-prone; the template ensures nothing critical is omitted (impact, next update, on-call, ticket).
4. The timeline section is populated in real time during the incident, not written afterwards. This becomes the raw material for the postmortem. t+00:00 timestamps are relative to the alert firing.
5. The consumer notification table forces the on-call to think about downstream consumers. Even a fully-mitigated incident has a comms obligation: "sales-ops, your dashboard was stale between 09:04 and 10:15." Under-communication is the surest way to erode trust in the platform team.

Output.

Artefact	Purpose	Cadence
escalation_matrix.yml	who to page per service per tier	reviewed quarterly
comms_template.md	Slack/email template for incidents	updated after major incidents
runbook links	detailed diagnostic + mitigation	reviewed per postmortem
handoff_docs/YYYY-MM-DD.md	weekly rotation handoff	one per Monday

Rule of thumb. The escalation matrix, comms template, runbooks, and handoff docs are the four operational artefacts that make on-call sustainable. All four live in Git alongside the pipeline code; all four are reviewed at defined cadences; all four are updated after every incident.

Senior interview question on data on-call

A senior interviewer might ask: "You lead a 6-engineer data platform team. Design the on-call rotation, escalation matrix, and runbook coverage that supports 24×7 SLO enforcement across 50 pipelines. Cover the rotation shape, handoff protocol, runbook backlog prioritisation, and how you'd measure the on-call load on each engineer over time."

Solution Using a weekly primary+secondary rotation with follow-the-sun handoff, tiered escalation, prioritised runbook backlog, and on-call load tracking

# 1. Rotation shape — weekly primary + secondary; follow-the-sun over EU/US
rotation:
  cadence: weekly
  handoff_day: Monday
  handoff_time: 10:00 local (incoming)
  primary_count: 1
  secondary_count: 1
  min_gap_between_shifts: 4 weeks
  compensation: 1 day off per week on-call + on-call stipend

  regions:
    - name: US
      hours: 08:00-20:00 US-Central
      pool: [alice, bob, carol, dan]
    - name: EU
      hours: 08:00-20:00 CET
      pool: [emily, frank]
    - name: overnight
      hours: 20:00-08:00 US-Central
      strategy: secondary in EU takes overnight for the US half of the week; vice versa

# 2. Escalation matrix (see previous example)

# 3. Runbook backlog — prioritise by SLO coverage + incident frequency
runbook_backlog_priority:
  ranking_formula: (downstream_users × pageable_alerts_per_month) / runbook_maturity
  # runbook_maturity: 0=none, 1=draft, 2=reviewed, 3=battle-tested
  target: every pageable alert has a runbook at maturity 2+

-- 4. On-call load tracking — pages per engineer per week
CREATE OR REPLACE VIEW oncall_load AS
SELECT
    engineer,
    date_trunc('week', page_time) AS week,
    COUNT(*)                       AS total_pages,
    COUNT(*) FILTER (WHERE hour < 8 OR hour > 20) AS off_hours_pages,
    AVG(EXTRACT(EPOCH FROM (resolved_time - page_time)) / 60.0)::int AS avg_resolve_min
FROM (
    SELECT engineer,
           page_time,
           resolved_time,
           EXTRACT(HOUR FROM page_time) AS hour
    FROM   pagerduty_incidents
    WHERE  page_time >= now() - INTERVAL '90 days'
) t
GROUP BY engineer, date_trunc('week', page_time)
ORDER BY week DESC, total_pages DESC;

-- Alert on team-lead if any engineer exceeds 10 off-hours pages / week
SELECT engineer, week, off_hours_pages
FROM   oncall_load
WHERE  off_hours_pages > 10
  AND  week >= now() - INTERVAL '4 weeks';

# 5. Runbook backlog auto-updater — runs monthly
def prioritise_runbook_backlog(conn):
    with conn.cursor() as cur:
        cur.execute("""
            SELECT c.table_name, c.sli_name,
                   COALESCE(rb.maturity, 0) AS runbook_maturity,
                   COUNT(p.id) AS pages_last_90d,
                   COALESCE(ct.downstream_users, 1) AS users
            FROM   slo_catalogue c
            LEFT JOIN runbooks rb USING (table_name, sli_name)
            LEFT JOIN pagerduty_incidents p
              ON p.alert_labels @> jsonb_build_object('table', c.table_name, 'sli', c.sli_name)
             AND p.page_time >= now() - INTERVAL '90 days'
            LEFT JOIN table_consumers ct USING (table_name)
            GROUP BY 1, 2, 3, 5
        """)
        rows = cur.fetchall()

    scored = []
    for table, sli, maturity, pages, users in rows:
        score = (users * pages) / (maturity + 1)   # higher = higher priority
        scored.append((table, sli, maturity, pages, users, score))

    scored.sort(key=lambda r: -r[5])
    for row in scored[:20]:
        print(f'{row[0]:30s} {row[1]:15s} maturity={row[2]} pages={row[3]} users={row[4]} score={row[5]:.1f}')

Step-by-step trace.

Component	Design choice	Reasoning
Rotation cadence	weekly	balance handoff overhead vs burnout
Handoff	Monday 10:00 local	fresh brains; not weekend
Follow-the-sun	US + EU pools	reduces overnight pages by 50%
Escalation matrix	Git-tracked YAML	one source of truth
Runbook priority	(users × pages) / maturity	quantitative backlog
Load tracking	off-hours pages per engineer	detect burnout early

After the design is deployed, the team has a documented rotation, a searchable escalation matrix, a scored runbook backlog, and a load-tracking dashboard. The next quarterly review adjusts the rotation pool as engineers join and leave, retargets the runbook backlog based on incident patterns, and reviews the load-tracking dashboard for engineers approaching burnout thresholds.

Output:

Metric	Value
On-call engineers	6 (rotation every 6 weeks)
Handoff cadence	weekly Monday
Follow-the-sun coverage	US + EU pool splits overnight
Runbook backlog target	100% pageable alerts at maturity 2+
Off-hours page threshold	10/week per engineer
Escalation matrix format	Git YAML

Why this works — concept by concept:

• Weekly primary + secondary — the rotation shape that trades off handoff overhead (frequent enough) against burnout (not too frequent). Adding secondary decouples "primary is asleep / in a meeting" from "no-one is responding."
• Follow-the-sun overnight coverage — a US-only rotation means every on-call gets some overnight pages. Splitting overnight with an EU pool cuts off-hours pages roughly in half — the single biggest on-call quality-of-life lever.
• Escalation matrix as YAML — human-readable, Git-tracked, referenced by name from every runbook. One edit updates every alert; roles are role-based (@platform-lead) not person-based, so team changes don't cascade.
• Runbook backlog prioritised by (users × pages) / maturity — the runbook you write next should be the one whose gap costs the most and whose maturity is lowest. This is a quantitative backlog ranker; without it, teams write runbooks for the wrong alerts.
• Cost — 6 engineers × ~2 hours/week on-call meta-work (handoff + runbook maintenance) = ~12 engineer-hours/week to sustain the rotation. Compared to the "hero on-call" alternative (one senior engineer paged every incident, burns out in 6 months), this is the operationally sane approach that keeps teams together for years.

Design
Topic — design
Design problems on operational tooling

Practice →

Log processing Topic — log-processing Log-processing and alert-signal problems

Practice →

---

5. Post-incident review and reliability roadmap

Blameless postmortem → action items → reliability roadmap — the loop that compounds

The mental model in one line: a post-incident review is the structured, blameless meeting held within 5 business days of every SLO breach that produces a written timeline, an explicit list of contributing factors (not a single root cause), a set of tracked action items with owners and due dates, and an update to the reliability roadmap — and the roadmap itself is the quarterly plan that turns incidents into a compounding investment in SLI coverage, runbook maturity, and chaos-drill practice. Every senior data engineer must be able to describe the postmortem template, defend the "contributing factors, not root cause" preference, and explain how action items feed the reliability roadmap.

The postmortem template — one document per incident.

• Header. Incident title, severity, start time, end time, MTTA (mean time to acknowledge), MTTM (mitigate), MTTR (resolve).
• Impact. Which SLIs breached, budget consumed, downstream consumers affected, revenue impact (if any).
• Timeline. Minute-by-minute log of what happened, ideally populated in real time from the comms template.
• Contributing factors. A list — not one root cause. Every incident has 3–7 factors that together enabled it; ignoring the softer contributors misses future prevention.
• What went well. Non-negotiable section. Reinforces successful mitigations; motivates the team.
• What went badly. What we wish we'd done differently.
• Action items. Concrete tickets, each with owner + due date + SLI impact.

Blameless — what it actually means.

• Focus on systems, not people. "The on-call missed the alert" → "The alert routing was mis-configured and did not reach the on-call phone." The system is the target of improvement, not the human.
• Assume rational actors. Everyone involved was doing their best with the information they had. If a decision looks bad in hindsight, ask "what information was missing?" not "who was careless?"
• Language matters. Ban words like "should have" ("the on-call should have checked X"). Prefer "we observed that…" and "in future we can…"
• Author is the incident owner. Not the on-call themselves — someone one step removed writes the postmortem to reduce self-blame framing.

"5 whys" vs "contributing factors" — the SRE preference.

• 5 whys. Sequential drill-down to a single root cause. Popular in classical incident response.
• Why SRE prefers contributing factors. Real incidents have multiple simultaneous causes: an upstream schema change and a stale runbook and a missing alert and a confused escalation. Picking one "root" misses the other three; fixing only the root leaves the other three primed to re-fire.
• The alternative. List 3–7 contributing factors; assign an action item per factor. Some may be "no action" (accepted risk), but all are named.
• Example. Freshness breach: (1) upstream connector stalled on schema change, (2) burn-rate alert threshold too high to catch early, (3) runbook didn't include the schema-change check, (4) on-call was in a meeting and secondary was on PTO, (5) dashboard consumer wasn't notified for 45 min. Fix all five.

The reliability roadmap.

• Cadence. Reviewed quarterly at platform-team offsite.
• Inputs. Postmortem action items, SLI-coverage backlog, on-call load metrics, chaos-drill results.
• Structure. One-quarter view (in-flight), next-quarter view (planned), 6-month view (aspirational).
• Sizing. Each item sized as small (1 sprint), medium (2 sprints), large (a quarter). Aim for ~30% capacity on reliability work per engineer.
• Owner. Platform-team lead; reviewed with engineering director.

Common interview probes on postmortems.

• "What's in a postmortem template?" — required answer: header, impact, timeline, contributing factors, what went well, what went badly, action items.
• "Why blameless?" — required answer: focus on systems not people; assume rational actors; language matters.
• "Root cause vs contributing factors — which do you prefer?" — required answer: contributing factors; explain why single root misses the softer factors.
• "How do action items become reliability improvements?" — required answer: feed the quarterly reliability roadmap.

Worked example — postmortem for a 2-hour freshness SLO breach

Detailed explanation. A concrete postmortem for a real-shape incident on analytics.orders. The freshness SLO (99.9% < 65 min) breached at 09:15 and did not recover until 11:12 — 117 minutes over the threshold, consuming ~117 min of the 43 min monthly budget (overspent by ~2.7x).

• Root event. An upstream Postgres schema change added a discount_cents column; the Debezium connector required a schema-history sync that briefly stalled.
• Compounding factors. Fast-burn alert threshold was too coarse; runbook did not include the schema-change check; on-call was in a meeting; consumer was not notified until minute 45.

Question. Write the postmortem for the incident using the standard template.

Input.

Field	Value
Incident	freshness breach on analytics.orders
Duration	117 min above threshold
Budget consumed	117 min (277% of month's total)
Severity	Sev-2 (business-hours; single dashboard affected)
Consumers affected	Sales Ops dashboard

Code.

# Postmortem — Freshness SLO breach on analytics.orders (2026-07-30)

## Header
- **Incident ID:** INC-2026-07-30-001
- **Severity:** Sev-2
- **Start:** 2026-07-30 09:15 UTC
- **End:** 2026-07-30 11:12 UTC
- **Duration:** 117 min above SLO threshold
- **MTTA:** 12 min (alert fired 09:15, acked 09:27)
- **MTTM:** 42 min (mitigation applied 09:57)
- **MTTR:** 117 min (fully recovered 11:12)

## Impact
- **SLIs breached:** freshness on analytics.orders
- **Budget consumed:** 117 min (277% of monthly 43-min budget; overspent by 74 min)
- **Downstream consumers:** Sales Ops dashboard (staleness visible to VP-Sales)
- **Revenue impact:** none directly; delayed sales-pipeline update by 2 h

## Timeline
- **09:12** Upstream deployed `ALTER TABLE production.orders ADD COLUMN discount_cents INT`
- **09:15** Debezium connector paused for schema-history sync
- **09:15** Freshness metric began rising above threshold
- **09:27** FreshnessBudgetFastBurn fired; on-call (Alice) acked (was in team meeting)
- **09:35** Alice opened runbook; ran Airflow + Postgres checks — nothing obvious
- **09:47** Alice noticed Debezium connector was paused
- **09:57** Alice restarted connector; freshness metric began recovering
- **10:00** Alice posted to #sales-ops (45 min after alert): "Data delay affecting orders dashboard"
- **10:45** Freshness reached below threshold intermittently; still burning budget
- **11:12** Freshness stable under threshold; incident resolved
- **11:30** Alice paged secondary + platform-lead to write postmortem

## Contributing factors (not root cause)
1. **Upstream schema change was not coordinated with the platform team.** The service team deployed the ALTER TABLE without notifying data platform. Downstream systems (including Debezium) require a heads-up for schema changes.
2. **Debezium connector required manual restart after schema-history sync.** The connector paused for ~40 min waiting for schema propagation; auto-restart on schema change is not configured.
3. **Runbook did not include the "check Debezium connector" step.** The on-call spent 20 min investigating Airflow and Postgres before checking the connector — this should have been step 1.
4. **On-call was in a team meeting when alert fired.** Meeting was internal (skippable), but the on-call did not have "leave meeting on page" muscle memory.
5. **Consumer (Sales Ops) was notified 45 min into the incident.** Comms template exists but was not invoked until after mitigation was underway.

## What went well
- Fast-burn alert fired within 12 min of SLI degradation — the alerting worked.
- Alice correctly identified Debezium as the cause once she looked at it.
- Mitigation (connector restart) was fast and clean once identified.
- Team switched to secondary/lead promptly to write the postmortem.

## What went badly
- MTTM (42 min) was too high because the runbook did not point at Debezium.
- Consumer comms delayed 45 min — dashboard viewers had no explanation for the stale data.
- Monthly error budget was consumed 2.7x in a single incident.

## Action items
| ID | Owner | Due | SLI impact |
|---|---|---|---|
| AI-1: Coordinate schema-change protocol with service teams (RFC + Confluence page) | @platform-lead | +14 d | prevents recurrence |
| AI-2: Add "check Debezium connector" as step 1 in freshness runbook | @alice | +3 d | reduces MTTM to ~15 min |
| AI-3: Configure Debezium auto-restart on schema-history sync | @bob | +21 d | eliminates cause |
| AI-4: Add "leave meetings on page" to on-call onboarding doc | @platform-lead | +7 d | reduces MTTA |
| AI-5: Add consumer-comms trigger to fast-burn alert (auto-Slack to dashboard channel) | @carol | +14 d | consumer comms lag → 0 |
| AI-6: Re-negotiate freshness SLO from 99.9% to 99.7% (achievable at current infra) | @platform-lead | +30 d | sustainable SLO |

## Reliability roadmap update
- Add "Debezium schema-change hardening" to Q3 platform roadmap (from AI-3).
- Add "schema-change protocol" to Q3 cross-team RFC list (from AI-1).
- Track AI-2, AI-4, AI-5 in weekly platform standup until closed.

Step-by-step explanation.

1. The header captures the four operational metrics — MTTA, MTTM, MTTR, duration — that every postmortem should quantify. These become the trend metrics the team watches over quarters ("MTTM is dropping — the runbooks are getting better").
2. The impact section names which SLIs breached and by how much. "Freshness on analytics.orders, 117 min over threshold, 277% of monthly budget." This is the language leadership understands; vague "the pipeline was slow" is uselessly abstract.
3. The timeline is populated from the comms template — the Slack messages posted during the incident become the raw timeline. This is why the comms template matters: it produces documentation as a byproduct of communication.
4. The contributing factors section lists 5 concrete factors — not one root cause. "The upstream schema change" is a factor, but "the runbook didn't include the check" and "the on-call was in a meeting" are equally load-bearing. Fixing only the schema-change misses the softer factors.
5. Every action item is a Jira ticket-shape: ID, owner, due date, SLI impact. This is what turns a postmortem from a document into an operational improvement. The postmortem author's job is to enforce that action items are entered into Jira before the postmortem meeting closes.

Output.

Postmortem section	Word count target	Purpose
Header	~50 words	quantitative facts
Impact	~100 words	consumer + budget framing
Timeline	~200 words	minute-by-minute
Contributing factors	~200 words	3–7 named factors
What went well	~100 words	motivation + reinforcement
What went badly	~100 words	honest self-critique
Action items	table of 5–10 rows	traceable improvement backlog

Rule of thumb. Every postmortem is authored by someone one step removed from the on-call, sized to fit on 2 pages, and finalised within 5 business days of the incident. Every action item is entered into Jira before the review meeting adjourns.

Worked example — action-item tracker with owner + due date + SLI impact

Detailed explanation. Action items from every postmortem land in a single tracker that the platform-team lead reviews weekly. The tracker feeds the quarterly reliability roadmap. Build the tracker.

• Schema. ID, source postmortem, owner, due date, status, SLI impact, size.
• Cadence. Reviewed at weekly platform standup.
• Escalation. Overdue items surfaced to eng-manager at the +2 week mark.

Question. Design the action-item tracker table and the weekly-review query.

Input.

Component	Value
Storage	Jira project RELIAB with custom fields
Or	Postgres reliability_action_items table
Weekly review	Monday standup
Overdue escalation	+2 weeks past due date

Code.

-- reliability_action_items — tracker table
CREATE TABLE reliability_action_items (
    id              BIGSERIAL   PRIMARY KEY,
    postmortem_id   TEXT        NOT NULL,      -- e.g. INC-2026-07-30-001
    title           TEXT        NOT NULL,
    owner           TEXT        NOT NULL,       -- @-handle
    created_at      TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
    due_date        DATE        NOT NULL,
    status          TEXT        NOT NULL DEFAULT 'open',   -- open|in-progress|done|dropped
    sli_impact      TEXT        NOT NULL,       -- 'prevents recurrence' | 'reduces MTTM by 30 min' | ...
    size            TEXT        NOT NULL,       -- 'small' | 'medium' | 'large'
    closed_at       TIMESTAMPTZ,
    dropped_reason  TEXT
);

CREATE INDEX idx_action_items_status_due
  ON reliability_action_items (status, due_date);

-- Weekly review query — what's open, what's overdue
SELECT
    id,
    postmortem_id,
    title,
    owner,
    due_date,
    (due_date - CURRENT_DATE) AS days_until_due,
    status,
    sli_impact,
    CASE
        WHEN status = 'done' THEN 'closed'
        WHEN due_date < CURRENT_DATE - INTERVAL '14 days' THEN 'ESCALATE'
        WHEN due_date < CURRENT_DATE THEN 'overdue'
        WHEN due_date < CURRENT_DATE + INTERVAL '7 days' THEN 'due soon'
        ELSE 'on-track'
    END AS review_status
FROM   reliability_action_items
WHERE  status IN ('open', 'in-progress')
ORDER  BY due_date ASC;

# Overdue-escalation cron — emails the eng-manager weekly
import psycopg2
import os
from email.message import EmailMessage
import smtplib

def escalate_overdue():
    conn = psycopg2.connect(os.environ['DB_DSN'])
    with conn.cursor() as cur:
        cur.execute("""
            SELECT id, postmortem_id, title, owner, due_date
            FROM   reliability_action_items
            WHERE  status IN ('open', 'in-progress')
              AND  due_date < CURRENT_DATE - INTERVAL '14 days'
        """)
        rows = cur.fetchall()

    if not rows:
        return

    body = 'The following reliability action items are >2 weeks overdue:\n\n'
    for aid, pm, title, owner, due in rows:
        body += f'  [{aid}] {title} · owner {owner} · due {due} · from {pm}\n'
    body += '\nPlease follow up in Monday standup.'

    msg = EmailMessage()
    msg['Subject'] = f'{len(rows)} reliability action items overdue >2w'
    msg['From']    = 'reliability-bot@internal'
    msg['To']      = 'eng-manager-data@internal'
    msg.set_content(body)

    with smtplib.SMTP('mail-relay:25') as s:
        s.send_message(msg)

if __name__ == '__main__':
    escalate_overdue()

Step-by-step explanation.

1. The tracker table stores one row per action item with the four fields that make follow-through possible: owner, due date, status, and SLI impact. Every item comes from a postmortem, so postmortem_id links back to the source incident document.
2. The weekly review query surfaces open items by due date. The review_status column translates raw dates into review categories: on-track / due soon / overdue / ESCALATE. The Monday standup walks the query results top-down.
3. The ESCALATE status (>2 weeks past due) triggers the escalation cron — an email to the eng-manager. This is the mechanism that prevents action items from silently rotting; overdue items get management visibility on a fixed cadence.
4. The sli_impact column forces the postmortem author to name why the action item matters. "Prevents recurrence" is stronger than "improves reliability"; "reduces MTTM by 30 min on freshness alerts" is stronger still. Vague impacts predict items that never get done.
5. The dropped_reason column exists for items that get explicitly abandoned. Sometimes a postmortem action turns out to be wrong ("we thought we needed to buy X, but we found a cheaper fix"). Recording why it was dropped preserves the reasoning for future postmortems.

Output.

Item ID	Title	Owner	Due	Status	Review
42	Add Debezium check to runbook	@alice	2026-08-02	in-progress	on-track
43	Configure Debezium auto-restart	@bob	2026-08-20	open	on-track
41	Schema-change protocol RFC	@platform-lead	2026-07-25	in-progress	overdue
38	Volume runbook v2	@carol	2026-07-12	open	ESCALATE

Rule of thumb. Every postmortem action item must have (owner, due date, SLI impact, size) fields populated before the postmortem meeting closes. Weekly review + 2-week auto-escalation is what keeps the tracker alive.

Worked example — quarterly reliability roadmap tied to SLO trend

Detailed explanation. The reliability roadmap is a quarterly plan that turns the action-item backlog into a prioritised sequence of platform investments. Build the Q3 roadmap for the data platform team.

• Inputs. Open action items, SLO-trend data, on-call load, chaos-drill gaps.
• Output. Prioritised list of ~10 items sized to consume 30% of team capacity.
• Cadence. Reviewed at quarter-start offsite; revisited at mid-quarter checkpoint.

Question. Draft the Q3 2026 reliability roadmap for the data platform team.

Input.

Component	Value
Team size	6 engineers
Reliability capacity	30% = 1.8 engineer-quarters
Roadmap items	~10
Prioritisation input	SLO trend, incident frequency, on-call load

Code.

# Q3 2026 — Data Platform Reliability Roadmap

## Context
- Q2 saw 4 Sev-2 incidents; 2 were freshness-related on analytics.orders.
- SLO trend: freshness slipping from 99.94% → 99.87% quarter-over-quarter.
- On-call load: 2 engineers over 10 off-hours pages/week threshold.
- Q3 goal: hit 99.9% freshness on top-10 tables, reduce off-hours pages 30%.

## Q3 Investments (prioritised — 30% team capacity ≈ 1.8 engineer-quarters)

### Tier 1 — must ship this quarter (0.8 EQ)
1. **Debezium schema-change hardening** (@bob, medium, from AI-3 INC-2026-07-30-001)
   → SLI impact: eliminates freshness cliffs from upstream ALTER TABLE.
2. **Runbook coverage pass on top-10 tables** (@alice, medium)
   → SLI impact: raises runbook maturity from 1.4 avg to 2.5+.
3. **Consumer-comms auto-trigger on fast-burn alerts** (@carol, small, from AI-5)
   → SLI impact: consumer comms lag 45 min → 0.

### Tier 2 — plan to ship (0.6 EQ)
4. **Hot-standby DAG for orders pipeline** (@dan, large)
   → SLI impact: freshness recovery time p99 from 30 min to 5 min.
5. **Quarterly chaos drills — kill Debezium mid-hour** (@emily, small)
   → SLI impact: verifies runbooks + on-call muscle memory.
6. **On-call load rebalance — recruit 2 more into pool** (@platform-lead, medium)
   → SLI impact: off-hours pages per engineer 12/wk → 7/wk.

### Tier 3 — best-effort (0.4 EQ)
7. **Volume SLI on top-20 tables** (@frank, medium)
   → SLI coverage: 10 → 20 tables under volume SLO.
8. **Automated backfill on volume breach** (@bob, medium)
   → SLI impact: volume MTTR 90 min → 30 min.
9. **Postmortem author rotation** (@platform-lead, small)
   → team practice: spread postmortem-writing skill.

### Stretch (if capacity allows)
10. **Great Expectations integration for quality SLI** (@carol, large)
    → SLI coverage: quality SLI on all top-20 tables (currently 5).

## Success metrics for Q3
- Freshness SLO on top-10 tables: 99.87% → ≥ 99.9%.
- Off-hours pages per engineer: 12/wk → ≤ 8/wk median.
- Runbook coverage on pageable alerts: 60% → 100% at maturity 2+.
- Reliability action-item completion rate: 60% → 80% within due date.

## Mid-quarter checkpoint
- Week 6 offsite: review Tier 1 progress; re-prioritise Tier 2/3 if needed.

Step-by-step explanation.

1. The context section grounds the roadmap in quantitative Q2 data: incident count, SLO trend, on-call load. Prioritisation reasoning is transparent — a director reviewing the roadmap can see why Debezium hardening is Tier 1 (2 incidents in Q2, causing 74% of budget overspend).
2. Investments are sized in engineer-quarters (EQ) and grouped into three tiers by priority. Tier 1 (0.8 EQ) is roughly half a person's quarter — must-ship items where cutting is not an option. Tier 2 is planned; Tier 3 is best-effort. Stretch is opportunistic.
3. Every item names owner, size, source postmortem (where applicable), and SLI impact. The SLI-impact line is the "why this matters" that survives roadmap review. Items without a clear SLI impact are candidates for de-prioritisation.
4. The success metrics section commits to measurable outcomes for the quarter. "Freshness SLO 99.87% → 99.9%" is testable; "improve reliability" is not. These become the Q4-review inputs.
5. The mid-quarter checkpoint is non-negotiable — real quarters produce surprises (a new incident, a person leaving, a business priority shift). The checkpoint is when the roadmap is re-scoped honestly rather than silently over-committing.

Output.

Roadmap section	Items	EQ	Success metric
Tier 1 must-ship	3	0.8	freshness ≥ 99.9%, comms lag = 0
Tier 2 planned	3	0.6	recovery ≤ 5 min, pages ≤ 8/wk
Tier 3 best-effort	3	0.4	volume coverage → 20
Stretch	1	opportunistic	quality SLI on 20 tables

Rule of thumb. The reliability roadmap is quantitative (EQ, SLI impact, target metrics), time-bounded (one quarter, with a mid-quarter checkpoint), and traceable back to postmortems (every Tier 1 item cites a source incident). Roadmaps that lack these three properties become wish lists that never ship.

Senior interview question on postmortems

A senior interviewer might ask: "You had a 4-hour freshness SLO breach that consumed the entire monthly budget in one incident. Walk me through your postmortem process, the contributing factors you'd expect to find, the action items you'd extract, and how those action items become platform reliability improvements over the next quarter."

Solution Using a blameless postmortem with contributing-factors analysis, tracked action items, and quarterly-roadmap integration

# Postmortem — 4h Freshness SLO Breach (INC-2026-08-14-001)

## Header
- **Severity:** Sev-1 (revenue-affecting)
- **Duration:** 4 h 12 min above threshold
- **MTTA / MTTM / MTTR:** 8 min / 2 h 15 min / 4 h 12 min
- **Budget consumed:** 252 min (585% of monthly)

## Impact
- Freshness SLO on analytics.orders and analytics.line_items breached.
- Executive dashboard, ML fraud model, and reverse-ETL to Salesforce all
  showed stale data.
- Sales-ops meeting at 14:00 was held with stale numbers; revised the
  next morning.

## Contributing factors
1. **Postgres primary failed over unexpectedly.** Hardware issue on the
   AZ; failover to replica took 90 s but Debezium slot did not follow.
2. **Debezium reconnect logic did not handle failover cleanly.** Slot on
   the new primary did not exist; connector entered CrashLoopBackoff.
3. **Fast-burn alert fired quickly (8 min MTTA) but runbook did not
   include the "failover" case.** On-call spent 90 min investigating
   other causes.
4. **Debezium slot re-creation required schema-registry manual sync.**
   Documented procedure existed but was in an unfamiliar Confluence page.
5. **Consumer notifications were manual and delayed.** Reverse-ETL owner
   found out via customer complaint, not our comms.

## What went well
- MTTA of 8 min shows the fast-burn alert is properly tuned.
- Team collaboration was fast — 3 engineers on the call within 30 min.
- Root cause identified correctly once on-call opened the connector logs.

## What went badly
- MTTM of 2h 15m was way too high; the runbook missed the failover case.
- Consumer comms delayed; reverse-ETL owner learned via customer.
- 252 min of budget consumed = 5.85× monthly allowance.

## Action items
| ID | Title | Owner | Due | Size |
|---|---|---|---|---|
| AI-1 | Add "check for Postgres failover" as detection step 1 in freshness runbook | @primary-oncall | +3d | small |
| AI-2 | Configure Debezium slot re-creation on connect failure | @platform-lead | +21d | medium |
| AI-3 | Move slot re-creation procedure from Confluence into runbook itself | @primary-oncall | +7d | small |
| AI-4 | Auto-Slack the consumer channel when fast-burn fires | @carol | +14d | small |
| AI-5 | Add "Postgres failover" scenario to Q3 chaos drills | @emily | +30d | medium |
| AI-6 | Post-mortem: renegotiate freshness SLO given failover risk | @platform-lead | +45d | small |

## Roadmap update
- Elevate AI-2 to Q3 Tier 1 (was Tier 2).
- Move AI-5 chaos-drill to Q3 Tier 1 (was Tier 2).
- Adjust Q3 success metric: freshness p99 recovery ≤ 15 min (was 30 min).

-- Reliability roadmap-update trigger: any Sev-1 auto-elevates related items
UPDATE reliability_action_items
SET    tier = 'Tier 1'
WHERE  postmortem_id = 'INC-2026-08-14-001'
  AND  tier IN ('Tier 2', 'Tier 3');

-- Auto-notify the roadmap owner
NOTIFY roadmap_update, 'INC-2026-08-14-001 auto-elevated to Tier 1';

Step-by-step trace.

Step	Value	Reasoning
Postmortem written	within 5 business days	fresh memory
Contributing factors	5 named	avoids single-root reductionism
Action items	6 with owner + due date	traceable improvement
Roadmap elevation	AI-2, AI-5 → Q3 Tier 1	Sev-1 forces re-prioritisation
SLO renegotiation	tabled as AI-6	data-driven, not reactive

After the postmortem, the platform team has 6 concrete tracked items, two of which have been elevated to the current-quarter must-ship tier. The Q3 roadmap is republished with the updated priorities; the SLO catalogue is scheduled for renegotiation in 45 days when AI-2 (Debezium hardening) is complete and we have new failover-recovery data.

Output:

Metric	Value
Postmortem authored	day +3
Contributing factors identified	5
Action items opened	6
Roadmap items elevated	2
SLO renegotiation scheduled	day +45
Q3 goal adjusted	recovery p99 30 → 15 min

Why this works — concept by concept:

• Blameless framing — no individual is named as at-fault. The contributing factors are all system properties (missing runbook step, missing Debezium logic, missing comms automation). This preserves psychological safety while producing sharper improvements.
• Contributing factors, not root cause — the Postgres failover is a factor, but the Debezium reconnect logic, the missing runbook step, the buried procedure, and the manual comms are equally load-bearing. Fixing all five is what prevents the next 4-hour breach.
• Action items with owner + due date — every factor becomes a concrete ticket. AI-1 and AI-3 are small (3–7 days); AI-2 and AI-5 are medium (2–4 weeks). The distribution keeps the postmortem operational: small items ship fast; medium items feed the roadmap.
• Roadmap auto-elevation — the postmortem doesn't just recommend roadmap changes; it executes them via the SQL update. This is the operational mechanism that prevents postmortem findings from being ignored between quarterly offsites.
• Cost — one postmortem meeting (~90 min), one 2-page document, 6 Jira tickets, one roadmap re-shuffle. Compared to the alternative — quiet Slack apology, informal Slack thread, no tracked improvements — this is what turns a bad incident into a compounding reliability investment over quarters.

Design
Topic — design
Design problems on reliability engineering

Practice →

API integration
Topic — api-integration
API-integration and comms-flow problems

Practice →

---

Cheat sheet — Data SRE recipes

• The four axes as the SLI menu. Every pipeline gets up to four SLIs: freshness (now() - MAX(ingested_at) on the target table), completeness (target rowcount / source rowcount per hour), volume (target rowcount vs 7-day median band ±30%), quality (fraction of rows passing schema + constraint checks). Freshness is per-minute; completeness and volume are per-hour; quality is per-daily-run. Skip any axis that doesn't have a downstream consumer who cares — SLIs cost engineer-time to maintain.
• Freshness SLI PromQL. data_freshness_meets_slo{table="..."} — 1 if within threshold, 0 otherwise. Roll up: sum_over_time(...[28d]) / count_over_time(...[28d]). Budget remaining: (current_slo − target) / (1 − target). Show the last one as a big-bold-number Grafana panel per top-10 table.
• Completeness SLI SQL. WITH src AS (SELECT hour, COUNT(*) n_src FROM source GROUP BY hour), tgt AS (SELECT hour, COUNT(*) n_tgt FROM target GROUP BY hour) SELECT hour, n_tgt::float / n_src FROM src LEFT JOIN tgt USING (hour) WHERE hour < now() - INTERVAL '5 min'; — persist per-hour to slo_measurements.
• Error-budget monthly template. Total = (1 − slo_target) × observation_windows_per_month. For per-minute observations: 99% = 432 min, 99.5% = 216 min, 99.9% = 43 min, 99.95% = 21.6 min, 99.99% = 4.3 min. Track remaining as total − spent. Show as a fuel-tank icon on the reliability dashboard; halt releases at 0.
• Multi-window multi-burn-rate alert. Fast burn: (1 - avg_over_time(sli[1h])) > 14 × (1 - slo_target) AND (1 - avg_over_time(sli[5m])) > 14 × (1 - slo_target) for 2m → page. Slow burn: [6h] > 6× AND [30m] > 6× for 15m → ticket. Both wired against the same SLI; both link to the same runbook.
• 5-field runbook template. ## Symptom (2 sentences), ## Detection (3–5 commands or dashboards), ## Mitigation (ordered try-list with success criteria), ## Escalation (time-bounded tiers with @-handles), ## Owner (team + last-reviewed date). One screen max. No runbook link = alert doesn't ship.
• On-call rotation shape. Weekly primary + secondary; handoff Monday 10:00 local (incoming); minimum 4 weeks between shifts; follow-the-sun with EU/US pools for overnight coverage. Compensate with one day off per week on-call plus stipend. Track pages per engineer per week; escalate to eng-manager if any engineer exceeds 10 off-hours pages weekly.
• Escalation matrix as YAML. One escalation_matrix.yml in Git; per-service tier 1/2/3/4 as @-handles. Every runbook references by name (matrix.orders.tier_1). Change once, propagates everywhere. Role-based (@platform-lead) not person-based to survive team changes.
• Comms template. Fill-in-the-blank Slack post: {sev}, {start}, {impact}, {status}, {next_update}, {on_call}. Timeline populated real-time (t+00:00 alert fired, t+00:04 acked, ...). Consumer notification table forces the on-call to acknowledge downstream consumers exist.
• Postmortem sections. Header (MTTA/MTTM/MTTR), Impact (SLIs + budget + consumers), Timeline (from comms template), Contributing factors (3–7, not one root), What went well, What went badly, Action items (owner + due + SLI impact + size). 2 pages; finalised within 5 business days.
• Contributing factors, not root cause. Real incidents have multiple simultaneous causes. Listing all 3–7 and assigning an action per factor catches the "soft" contributors (missing runbook step, missed comms) that a single-root-cause analysis silently ignores.
• Action-item tracker fields. ID, postmortem_id, title, owner, due_date, status, sli_impact, size (small/medium/large). Weekly standup review; auto-escalate to eng-manager at +2 weeks past due. Track completion rate; aim for >80% within due date.
• Reliability roadmap. Quarterly, sized in engineer-quarters, prioritised Tier 1/2/3/stretch. Tier 1 ≈ 40% of reliability capacity (must-ship); Tier 2 ≈ 35% (planned); Tier 3 ≈ 25% (best-effort). Mid-quarter checkpoint mandatory. Every Tier 1 item cites a source postmortem.
• SLO renegotiation trigger. If the current 28-day SLO is below target for three consecutive months and the platform team has already shipped two Tier 1 reliability fixes without moving the metric, renegotiate the SLO downward. The alternative — keeping an aspirational SLO the team cannot hit — destroys the credibility of every SLO in the catalogue.

Frequently asked questions

What is a data SLO in one sentence?

A data SLO (service-level objective) is an explicit, agreed-upon target for a data SLI (service-level indicator) computed over a rolling measurement window — for example, "99.9% of one-minute freshness observations on analytics.orders have MAX(ingested_at) within 65 minutes of now() over the trailing 28 days." The SLO is a contract between the platform team producing the dataset and the downstream consumers depending on it; the SLO percentage translates to an error budget (1 − target) that governs the operational trade-off between shipping features and investing in reliability. Every senior data-engineering interview probes SLOs because they are the load-bearing mechanism that turns "we monitor pipelines" into "we own user-visible reliability."

SLI vs SLO vs error budget — what's the difference?

An SLI (service-level indicator) is the measurement — a numeric fact about the pipeline's output artifact, computed on the dataset rather than on the job that produced it. Examples: "age in seconds of the newest row in analytics.orders," "target rowcount / source rowcount for the last hour," "fraction of rows passing dbt tests today." An SLO (service-level objective) is the target expressed as a percentage of measurement windows in which the SLI meets a threshold — for example, "99.9% of one-minute windows have freshness under 65 minutes." An error budget is the mathematical complement of the SLO: 1 − SLO, expressed in minutes or days per month. A 99.9% freshness SLO is a 43-minute monthly error budget. The three concepts stack: SLI → SLO → error budget → burn-rate alerts → release-halt policy. Getting each definition right is the first thing senior interviewers probe.

How do I pick freshness targets for my pipelines?

Derive the target from the downstream consumer who cares most, then sanity-check against current observed performance. Start with an interview: sit with the dashboard owner or service owner who consumes the dataset and ask "how stale is too stale?" Their answer is usually anchored to a business cadence ("I check it every 5 minutes; more than an hour behind and my meeting is derailed") — that becomes your freshness target with a small buffer (add ~5 minutes above the natural cadence). Then compute the SLI over the last 28 days: if current freshness is 99.7% and the consumer wants 99.9%, the gap is a 3× improvement in bad-minute count — that's a project, not a target. If the gap is 10× or larger, either invest in specific reliability improvements first, or renegotiate the target down to something achievable. Publish the SLO in a Git-tracked catalogue with the consumer's name against it; review quarterly. The wrong pattern is to unilaterally invent a 99.99% target because it "sounds professional" — an aspirational SLO no team can hit destroys the credibility of every SLO in your catalogue.

What does a data on-call rotation actually do?

A data on-call rotation is the documented schedule of who receives pages when a data SLI breaches, structured around three anchors: rotation shape (primary + secondary weekly cadence with follow-the-sun handoff for global teams), escalation ladder (tier 1 primary in 5 min → tier 2 secondary+lead in 15 min → tier 3 eng-manager+SRE in 30 min → tier 4 VP in 60 min for revenue-affecting incidents), and linked runbooks (every alert points to a 5-field document: symptom, detection, mitigation, escalation, owner). The on-call's job during a shift is to acknowledge pages within 5 minutes, open the linked runbook, execute the detection and mitigation steps, escalate on the defined timeline if unresolved, communicate to affected consumers using the standard comms template, and author (or receive) the postmortem within 5 business days if an SLO is breached. Between shifts, the on-call attends the Monday handoff standup, contributes to runbook updates from the last week's incidents, and participates in the quarterly reliability-roadmap review. Rotation should recur no more often than every 4 weeks per engineer to prevent burnout; follow-the-sun EU/US splits cut off-hours pages roughly in half — the single biggest quality-of-life lever available.

What is a burn-rate alert and why do I need multi-window?

A burn-rate alert fires when an SLI is spending its error budget faster than the sustainable rate that would exhaust the budget over the full SLO window. For a 99.9% freshness SLO with 43 minutes of monthly budget, the sustainable burn rate is 1× (1.43 min/day). A burn rate of 14× means the whole month's budget would be gone in about 2 days — that's the fast-burn threshold that pages the on-call immediately. A burn rate of 6× means the budget is gone in about 5 days — the slow-burn threshold that files a ticket. Multi-window multi-burn-rate (from the Google SRE workbook) computes burn on both a short window (typically 1 hour) and a long window (typically 6 hours), and alerts only when both windows agree the burn is high — this catches cliff-falls quickly via the short window while catching creeping degradation via the long window, and it prevents old resolved spikes from re-firing as they age out of a single long window. Without multi-window burn-rate, teams either over-alert on transient spikes (alert fatigue, false-page cost) or under-alert on slow creeping degradations (silent SLO breaches, discovered from consumer complaints). Multi-window is the standard SRE pattern; naming it in a senior interview is a strong signal.

How do I run a blameless postmortem for a data incident?

A blameless postmortem is a structured written review of an incident, authored within 5 business days by someone one step removed from the on-call, following a template that captures impact (which SLIs breached, budget consumed, downstream consumers affected), timeline (populated in real-time from the incident comms template — t+00:00 alert fired, t+00:04 acked, etc.), contributing factors (a list of 3–7 named factors, not a single root cause — a schema change and a missing runbook step and a delayed comms and an on-call in a meeting are all load-bearing), what went well (reinforces successful mitigations), what went badly (honest self-critique), and action items (each with owner, due date, SLI impact, and size — small, medium, or large). The blameless discipline is enforced by focusing every finding on system properties rather than individual people ("the alert routing didn't reach on-call" not "the on-call missed the alert"), banning "should have" language, and assuming rational actors doing their best with available information. Action items become Jira tickets before the postmortem meeting closes; the tracker is reviewed weekly; overdue items auto-escalate to the eng-manager at +2 weeks; and the reliability roadmap absorbs the medium/large items into the next quarter's plan. The whole loop — detect → mitigate → resolve → review → improve → roadmap — is what turns individual incidents into a compounding reliability investment over quarters.

Practice on PipeCode

• Drill the ETL practice library → for pipeline observability, incremental-load, and reconcile problems senior interviewers love.
• Rehearse on the design practice library → for the reliability, on-call, and platform-architecture scenarios that show up in senior data-platform loops.
• Sharpen the reliability axis with the data-validation practice library → for the SLI-shaped quality and schema-check questions.
• Add the time-series practice library → and the SQL time-series subset → for burn-rate, rolling-window, and freshness-metric SQL patterns.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the four-axis SLI framework against real graded inputs.

Own reliability like an SRE, not a task-runner

Docs explain SLOs. PipeCode drills explain the decision — how to pick freshness targets a consumer will actually sign, when a burn-rate alert should page vs ticket, how to write a runbook the on-call reads at 3 AM, and how to convert a postmortem into a quarter of reliability improvement. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face.

Practice ETL problems →
Practice design problems →