Data Investigation and Root Cause Analysis Questions
Techniques and a structured process for diagnosing an unexpected change in a metric, dataset, or system signal using quantitative evidence complemented by qualitative signals. Candidates should demonstrate how to validate that an observed change is a real signal and not noise, or a reporting, instrumentation, or pipeline problem, by checking data quality, event or record counts, sampling, schema stability, and pipeline or data-flow integrity. Describe slicing and decomposition strategies such as cohort or population segmentation, geography and platform segmentation, feature-level analysis, time series decomposition to separate trend and seasonality, funnel and velocity analysis, retention analysis, and variance analysis. Explain how to form, prioritize, and test hypotheses; design diagnostic queries and tests using structured query language or equivalent tooling; and correlate the change with plausible triggers such as releases or deployments, configuration or schema changes, experiments, campaigns, upstream system incidents, or external events. Include how to combine quantitative findings with qualitative evidence such as interviews, logs, session or trace replay, support tickets, or incident timelines to strengthen causal inference. Finally, cover communicating concise findings and actionable recommendations to stakeholders, creating reproducible queries and monitoring dashboards, alerts, or runbooks, and mentoring others on a systematic investigation approach. This applies broadly to investigating anomalies in business metrics, product data, system or service health signals, financial figures, or model performance, not only one of these domains.
HardSystem Design
51 practiced
Design an automated anomaly detection and root-cause pipeline for a platform processing ~1M events/day. The system should detect metric anomalies, run automated diagnostic slices, score likely root causes, and create alerts/tickets for ops. Describe components, data flow, state storage, how you would compute confidence scores, and how to integrate human review.
Sample Answer
Requirements:- Detect anomalies on ~1M events/day across metrics (latency, success rate, revenue, DAU).- Automated diagnostic “slices” (by region, product, user cohort, device).- Score likely root causes and surface alerts/tickets with confidence.- Human-in-the-loop review and feedback to improve models.- Low-latency detection (minutes), auditable history, integration with BI tools (Looker/Tableau) and ticketing (Jira/PagerDuty).High-level architecture:- Ingest: event stream (Kafka) → stream processor (Flink/Beam) to compute time-series aggregates and feature vectors per metric and slice (1-min / 5-min windows).- Feature store & OLAP: aggregated timeseries stored in columnar DB (BigQuery/Snowflake) for ad-hoc BI + a feature-store (Feast or Redis) for realtime features.- Anomaly detector: ensemble of detectors running in parallel: - Univariate: seasonal-ARIMA / Holt-Winters / Prophet for trend/seasonality. - Statistical: EWMA / z-score for sudden shifts. - ML: isolation forest or lightweight LSTM for complex patterns. Results emitted with raw anomaly score.- Diagnostics & slicing: upon anomaly, orchestration service launches diagnostic jobs that compute delta-by-slice (percentage change, concentration, spike propagation), correlation analysis (Pearson/Granger) and dependency graph traversal (service → downstream metrics).- Root-cause scorer: combines signals into confidence score using weighted Bayesian model: - Evidence types: magnitude, persistence, slice concentration, leading-lag relationship, service dependency match, historical recurrence. - Score = sigmoid(w1*magnitude_norm + w2*persistence + w3*slice_entropy + w4+correlation + w5*dependency_score). Weights initialized from historical labeled incidents and updated by human feedback.- State & lineage: metadata DB (Postgres) stores incidents, diagnostic outputs, model versions, feature lineage and feedback. Time-series stored in object store (Parquet) for replay.- Alerting & ticketing: when confidence > threshold, create ticket in Jira and page on PagerDuty; lower-confidence flagged into BI “Investigations” dashboard for analysts.- Human review loop: BI dashboard (Looker) surfaces anomaly, top-ranked root causes, diagnostic plots, and allows the analyst to: - Confirm root cause (labels that update model weights) - Reject/merge incidents (improves precision) - Annotate notes (stored in metadata) Feedback feeds supervised retraining and threshold tuning in CI pipeline.- Monitoring & reliability: metric for false-positive/negative rate, model drift alerts, canary rollouts of detector models, circuit breaker to pause automation during outages.Scalability & trade-offs:- Use stream aggregation to avoid storing raw events for realtime detection; retain raw for replay windows.- Ensemble ensures robustness; trade-off is complexity—start with statistical + simple ML and iterate.- Confidence interpretable by combining normalized signals; human feedback important to reduce noise.This design lets BI analysts get actionable alerts with transparent diagnostics, integrate into existing dashboards, and continuously improve detection quality via human-in-the-loop labeling.
HardTechnical
46 practiced
Design SLAs and a runbook for urgent metric anomalies (e.g., major payment failures). Provide a template runbook that includes roles, response timelines, immediate diagnostics, communication templates for stakeholders, escalation criteria, and postmortem steps.
Sample Answer
SLA SUMMARY (for urgent metric anomalies like major payment failures)- Severity definitions: - S1 (Critical): ≥50% payment fail rate or business-impacting outage. Target: detection → acknowledge ≤15 min; mitigation (temporary fix) ≤1 hour; full resolution ≤8 hours; stakeholder updates every 30 minutes until mitigation, then hourly. - S2 (High): 10–50% degradation. Ack ≤30 min; mitigation ≤4 hours; resolution ≤24 hours; updates every 2 hours. - S3 (Medium): <10% impact or local issue. Ack ≤4 hours; resolution ≤72 hours; updates daily.RUNBOOK TEMPLATE (to be filled per incident)1) Incident metadata - Incident ID: - Detected at (UTC): - Metric / Dashboard: - Severity (S1/S2/S3): - Trigger type (alert/manual):2) Roles & responsibilities - Incident Commander (IC) — BI Manager or senior BI analyst: lead incident, coordinate steps, own stakeholder comms. - BI Owner / Dashboard Author: validate metrics, check calculations and filters. - Data Engineer: check ETL jobs, data pipelines, lag, schema changes. - SRE / Platform Eng: verify infra, DB performance, queues, logging. - Payments SME / Product Manager: validate gateway health, business impact. - Customer Ops / Support Lead: prepare customer messaging and hold lines. - Communications (Comms/PR): external stakeholder messaging if needed.3) Immediate response (T+0 to T+60) - T+0: IC acknowledges and posts Incident channel (Slack/MS Teams). - Step 1: Confirm alert validity (compare raw data vs dashboard aggregates; check timestamp alignment). - Step 2: Scope impact: timeframe, segments (region, product), sample TXNs. - Step 3: Check data pipeline status: last successful run, job errors, backfills. - Step 4: Query source systems (payments DB/payment gateway API) for success/fail counts. - Step 5: Check infra: DB CPU, connection pool, error logs, message queues. - Step 6: If analytics layer suspected, validate SQL / LookML / calculated fields. - Step 7: Apply temporary mitigation (reroute jobs, restart jobs, toggle dashboard caching) and measure effect.4) Diagnostics checklist (ordered) - Is the alert caused by data latency or by real payment failures? - ETL job failures: review logs, restart + re-run. - Schema or model changes pushed in last 24 hours? - Upstream system (gateway) health: API errors, rate limits. - DB slow queries / locks / disk pressure. - Deployments around incident window. - Business config changes (pricing, promo) that affect transactions.5) Escalation criteria - Escalate to Head of Data & CTO if S1 persists >1 hour without mitigation. - Escalate to Payments vendor support immediately if gateway errors appear. - Engage Legal/Comms if customer financial impact or regulatory exposure suspected.6) Communications (templates) - Initial (T+15 / S1): "Incident [ID]: We observed elevated payment failure rate (approx X%) starting at [time]. Severity S1. IC: [name]. Actions underway: validating source data, checking pipelines and payment gateway. Next update in 30 minutes." - Update: "Update [ID] @ [time]: Scope narrowed to [region/product]/Cause suspected: [ETL|gateway|DB]. Mitigation in progress: [action]. Current failure rate: X%. Next update in 30 minutes." - Resolution: "Resolved [ID] @ [time]: Root cause: [short summary]. Mitigation: [steps]. Customer impact: [estimates]. Next steps: postmortem scheduled [date/time]."7) Postmortem & follow-up - Within 24 hours: draft timeline of events, decisions, and actions. - Within 72 hours: deliver RCA with root cause, contributing factors, and at least 3 corrective actions (owner + due date). - Verify fixes with monitoring for 7 days; add automated tests/alerts where gaps existed. - Update runbooks, dashboards, and SLAs as needed. - Communicate learnings to stakeholders and close the incident.CHECKLISTS TO ADD PROACTIVELY- Automated alert playbooks (runbooks linked to alerts) for common failures.- Synthetic transaction tests and monitoring to detect upstream failures earlier.- Access matrix: who can restart jobs, run backfills, contact vendors.WHY THIS WORKS- Clear SLAs set expectations for detection, mitigation, and communication.- Defined roles avoid confusion; IC centralizes decisions.- Diagnostics separate data-layer vs business-layer failures to avoid unnecessary escalations.- Communication templates keep stakeholders informed and reduce noise.- Postmortems ensure durable fixes and continuous improvement.
MediumTechnical
48 practiced
A marketing campaign launched last week and a core metric rose, then fell. Design diagnostic tests to determine whether the campaign caused the change. Include cohort construction, pre-post comparisons, difference-in-differences design, and what metrics you'd examine to rule out confounders.
Sample Answer
Framework / goal: determine if the campaign causally drove the temporary rise/fall in the core metric (e.g., conversion rate, revenue per user). I’d run a sequence of diagnostics from simple to causal.1) Cohort construction- Treatment cohort: users exposed to the campaign (impression/click/logged exposure).- Control cohort candidates: users not exposed during the same period; or geo/users randomized holdout if available.- Stratify cohorts by acquisition channel, device, prior activity (active vs dormant), and user value (LTV deciles) to avoid composition shifts.2) Pre‑post comparisons- Compare metric trend for treatment cohort pre-launch vs post-launch (visualize daily/weekly with 95% CIs).- Do the same for control cohort. Use paired tests (paired t-test or Wilcoxon) on user‑level metrics aggregated to avoid population-size confounding.3) Difference‑in‑Differences (DiD)- Model: metric_it = α + β1*Post_t + β2*Treated_i + β3*(Treated_i * Post_t) + controls + ε_it. β3 estimates causal effect under parallel trends.- Check parallel trends: plot pre-period trends; run placebo DiD on pre-period windows.- Use user fixed effects or cohort fixed effects and cluster SEs by user/geo.4) Metrics to detect confounders / rule-outs- Exposure quality: impression share, click rates, viewability — ensure exposure definition is accurate.- Traffic sources: shifts in organic vs paid traffic proportions.- Bot/traffic anomalies: sudden spikes in low-engagement users, session duration, bounce rate.- Product changes: releases, pricing, A/B tests overlapping the period.- Market effects: competitor activity, seasonality, holidays — add time fixed effects or external controls (search trends).- User composition: new vs returning user mix, geography, device — control or stratify.- Backend logs: attribution windows, tracking outages.5) Robustness checks- Vary pre/post windows (short/long), run event-study (dynamic DiD) to see timing and persistence.- Regression with covariates (prior behavior, channel) and inverse-propensity weighting to balance covariates.- Subgroup analyses: high-LTV users, loyal users — check heterogeneity.- Synthetic control if a single geo treated.Implementation notes- Use SQL to build user‑level panel, export to R/Python for DiD/regressions and plotting; surface key visuals in BI (Looker/Tableau) with cohorts, event-study plots, and diagnostics.- Report effect size, CI, p-values, and practical significance, plus possible alternative explanations and recommended next steps (e.g., run randomized holdout next campaign).
HardSystem Design
50 practiced
Design a metric-lineage and observability system to quickly detect data-quality issues such as schema changes, sudden null spikes, and freshness regressions. Describe metadata to store (data owner, lineage, freshness timestamps), tests to run, alerting rules, and how to surface likely root cause to on-call engineers.
Sample Answer
Requirements & constraints:- Detect schema changes, null spikes, freshness regressions quickly for metrics and reporting tables used by BI (Looker/Tableau/PowerBI).- Provide lineage so alerts surface upstream root causes and owners.- Low operational overhead; integrate with existing ETL, warehouse (Snowflake/BigQuery/Redshift).High-level architecture:- Metadata & Lineage Store (graph DB or relational + graph layer): table/column metadata, owners, freshness timestamps, last successful ETL, DAG/task ids, computed-metric formulas, downstream dashboards.- Telemetry collectors: lightweight jobs that sample table stats after each pipeline or periodically (row counts, null% per column, distinct counts, schema hashes, data timestamps, sample rows).- Alerting & Rules Engine: evaluates tests (see below), applies anomaly detection, suppression, and escalation.- Investigation UI + On-call Pane: shows alert, lineage graph, diffs, sample rows, suggested root causes, owner/contact and runbook links.- Integrations: CI/CD/Orchestration (Airflow/DBT), Slack/PagerDuty, BI tools (to mark impacted dashboards).Metadata to store:- Entity: dataset/table, column, metric- Ownership: data steward, on-call, team contact, SLAs- Lineage: upstream datasets, ETL jobs, transformation logic (dbt models/sql)- Freshness: last_ingest_time, expected_ingest_frequency, SLA window- Quality baselines: historical null%, count, distribution percentiles, schema hash- Dashboard dependencies: list of dashboards/reports using metricTests to run (on ingest and periodically):- Schema-consistency: compare schema hash; fail on added/removed/renamed columns- Null-rate guardrails: column null% > baseline + X sigma or absolute threshold- Row-count/freshness: count < expected_min OR last_ingest_time > SLA- Cardinality checks: distinct count outside expected bounds- Referential integrity: FK violation rates- Distribution drift: KL-divergence or PSI on key columns/metrics- Metric-contract checks: recompute metric from source and compare to published value (tolerance)- Sanity checks: negative values for non-negative metrics, duplicatesAlerting rules & prioritization:- Severity tiers: P0 (freshness failure for executive dashboards within SLA), P1 (major null spike for key metric), P2 (non-critical schema change), P3 (minor drift).- Rule types: deterministic (schema/freshness thresholds) and statistical (anomaly detection with sliding-window baselines).- Multi-signal correlation: suppress noisy alerts and promote when multiple upstream signals align (e.g., pipeline failure + freshness + row-count drop).- Escalation: notify owner via Slack; if not acknowledged within N minutes escalate to on-call team/PagerDuty.Surface likely root cause:- Automated root-cause heuristics using lineage traversal: - Start from impacted metric/table → traverse upstream to find first node with failing test (freshness/schema/null). - Rank candidate causes by proximity (graph distance), last-failure timestamp, magnitude of anomaly.- Provide investigation snapshot in UI/alert: - Affected metric, downstream dashboards, owners - Lineage graph highlighting first-failed upstream nodes - Diff view of schema (before/after), sample rows, query to reproduce - Suggested next steps: rollback ETL, contact owner, re-run job, apply cast/defaults - Link to runbooks and related CI job logsImplementation notes & trade-offs:- Start small: implement core metadata + schema/freshness/null checks, integrate with orchestration and Slack. Expand to statistical drift later.- Use DBT to capture transformations and lineage automatically; store metadata in a central catalog (e.g., Amundsen/Marquez, or a simple Postgres + Graph).- For scalability, store lightweight stats incrementally; compute heavy comparisons asynchronously.- Balance sensitivity: tune thresholds and use multi-signal correlation to reduce false positives.This design gives BI analysts quick detection, actionable alerts, and clear paths to the causing pipeline/owner so reports remain reliable and trusted.
EasyTechnical
51 practiced
Describe in plain language what trend, seasonality, and noise mean in a time series. Name two simple techniques you would use to separate seasonality from trend when investigating a weekly metric change, and explain what artifact you would look for to detect a holiday effect.
Sample Answer
Trend: the long-term direction of the metric — increasing, decreasing, or flat over months/years. It reflects underlying growth or decline (e.g., more users over time).Seasonality: regular, repeating patterns tied to the calendar or cycle — daily, weekly, monthly. For a weekly metric, seasonality is the predictable difference between weekdays and weekends (e.g., traffic higher on Mondays).Noise: the irregular, unpredictable fluctuations not explained by trend or seasonality (random variation, measurement error, one-off events).Two simple techniques to separate seasonality from trend:1) Moving-window smoothing + subtraction — compute a centered 7- or 28-day rolling mean (or rolling median) to estimate the trend, subtract it from the series to reveal weekly seasonal patterns in the detrended residuals. This is easy to implement in SQL/Python and robust to short-term spikes.2) Seasonal decomposition (additive STL or classical decomposition) — decomposes the series into trend, seasonal (period = 7 for weekly), and remainder components. STL is flexible, handles changing seasonality, and produces clean components for dashboarding.Artifact to detect a holiday effect:- Look for consistent outliers in the residuals that align to specific calendar dates (e.g., big negative dip every Dec 25 or spike each Black Friday) or a sudden jump in variance around known holidays. Also check that the holiday-day behavior differs from the usual weekday pattern (e.g., a Monday pattern broken when a holiday falls on Monday). If found, add holiday dummy variables or a holiday calendar to the model/dashboard to avoid misattributing those effects to trend/seasonality.
Unlock Full Question Bank
Get access to hundreds of Data Investigation and Root Cause Analysis interview questions and detailed answers.
