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.

MediumTechnical

50 practiced

Describe a simple prioritization framework (e.g., ICE, RICE) you would use to create a hypothesis backlog during an investigation. Show how you'd score at least three hypothetical hypotheses (e.g., UI regression, payment gateway issue, increased competition) and how this influences your investigative order.

MediumTechnical

48 practiced

A major product release coincided with a traffic drop. Outline how you would build a timeline to correlate metric changes with releases, deploy logs, marketing pushes, and external events. What data sources would you include and how would you test causality?

Sample Answer

Situation / goal: Build a forensic timeline that links a major product release to a traffic drop and test whether the release (or other events) caused it.

Approach — timeline construction:1. Align timestamps to a single timezone and bucket (minute/hour/day) depending on signal granularity.2. Plot baseline metrics for windows: pre-release (e.g., −30d), release day, post-release (+30d).

Data sources to include:- Product: deploy logs (commit id, feature flags, rollout %), release notes, canary/gradual rollout windows.- Traffic & engagement: pageviews, sessions, unique users, conversion funnel steps (from analytics/warehouse: GA/Segment/BigQuery/Redshift).- Backend: error rates, latency, availability, API/DB logs (ELK/CloudWatch).- Marketing: campaign start/stop, paid spend, email send times, referrer changes.- External: DNS/CDN incidents, third-party outages (Status pages, social mentions), seasonality/calendar events.- User feedback: support tickets, NPS spikes, app-store reviews.

Implementation details:- Create a combined event timeline table (SQL) joining timestamps to event metadata and annotate metric time series in dashboards (Tableau/Power BI).- Use changepoint detection (e.g., Ruptures or Prophet) to detect abrupt metric shifts and annotate with nearest events.

Testing causality:- Short term: difference-in-differences using unaffected cohorts (e.g., regions or user segments not exposed to the release) to see differential impact.- Time-series: interrupted time-series analysis to test level/slope change post-deploy controlling for trends and seasonality.- Instrumental checks: correlate deploys with backend errors, referrer shifts, or marketing spend to rule in/out confounders.- A/B / rollback: if possible, roll back or re-target a small cohort and observe metric reversion; use canary comparisons.- Robustness: run Granger causality for predictive precedence, and sensitivity analyses with varied windows.

Deliverable:- A dashboard with annotated timeline, statistical test results, and recommended next steps (rollback, hotfix, communicate to marketing/support) plus reproducible SQL/Python notebooks for audit.

MediumTechnical

59 practiced

You're concerned that multiple overlapping A/B tests might be contaminating each other's metrics. Describe the diagnostic queries and log checks you would run to detect overlap and estimate contamination, and explain mitigation strategies you could recommend.

Sample Answer

Start by clarifying randomization unit (user, cookie, device) and exposure windows. Then run diagnostic queries and log checks to detect overlapping assignments and estimate contamination.

1) Overlap detection — who got two experiments:

sql

-- count users assigned to >1 experiment in same window
SELECT user_id, COUNT(DISTINCT experiment_id) as n_exps
FROM assignments
WHERE assignment_time BETWEEN '2025-01-01' AND '2025-01-14'
GROUP BY user_id
HAVING n_exps > 1;

2) Exposure vs. assignment mismatch — assignments where no exposure or late exposure:

sql

SELECT a.user_id, a.experiment_id, a.assignment_time, e.event_time
FROM assignments a
LEFT JOIN impressions e 
  ON a.user_id = e.user_id AND a.experiment_id = e.experiment_id
WHERE e.event_time IS NULL OR e.event_time > a.assignment_time + INTERVAL '1 day';

3) Cross-experiment imbalance — check treatment/control ratios conditional on other experiments:

sql

SELECT x.exp_a_group, COUNT(*) as users,
       SUM(CASE WHEN y.exp_b_group='treatment' THEN 1 ELSE 0 END) as exp_b_treated
FROM
 (SELECT user_id, exp_a_group FROM assignments WHERE experiment_id='A') x
JOIN
 (SELECT user_id, exp_b_group FROM assignments WHERE experiment_id='B') y
ON x.user_id = y.user_id
GROUP BY x.exp_a_group;

4) Outcome contamination estimate — compare metric lift for users only in A vs users in A & B:

sql

-- compute metric means
WITH cohorts AS (
  SELECT u.user_id,
         MAX(CASE WHEN exp='A' THEN group END) as a_group,
         MAX(CASE WHEN exp='B' THEN group END) as b_group
  FROM assignments a
  CROSS JOIN LATERAL (VALUES (a.experiment_id,a.group)) AS t(exp,group)
  GROUP BY u.user_id
)
SELECT a_group, b_group, AVG(metric) as avg_metric, COUNT(*) 
FROM cohorts c JOIN metrics m ON c.user_id=m.user_id
GROUP BY a_group, b_group;

If A’s lift differs substantially between users with and without B, that's evidence of contamination or interaction.

5) Randomization integrity — verify hash-based assignment consistent:- Recompute assignment hash: hash(user_id + experiment_salt) mod 100 and compare to stored bucket.- Check for correlated buckets across experiments (e.g., same salt or bug causing identical hashing).

6) Log checks:- Verify timestamps (assignment before exposure).- Check for multiple impressions in same session (double-dosing).- Inspect AB test service logs for failures (retries, overrides).- Confirm traffic sources distribution unchanged.

Mitigation strategies- Short-term: In analysis, exclude users assigned to multiple concurrent experiments or restrict to “single-experiment” cohort; report results with and without overlap.- Stat adjustments: Include interaction terms between experiments in regression models; use blocking/covariate adjustment to control imbalance; use hierarchical models to estimate shared effects.- Experimental design: Coordinate experiment start/end windows; implement mutual exclusivity at assignment (exclusive buckets) or orthogonal assignment (independent hashing with ensured independence); use factorial designs when interactions are likely.- Infrastructure fixes: Ensure a single authoritative randomization service, consistent salts, and robust logging of assignment/exposure events; add monitoring alerts for assignment overlap rates exceeding threshold.

Trade-offs: exclusion reduces sample size; forced exclusivity lowers power for each experiment but prevents contamination; factorial designs require planning but let you measure interactions.

I’d present overlap rate, estimated bias on primary metric (difference-in-differences between exclusive and overlapping cohorts), and recommended remediation (short-term analysis exclusion + roadmap to enforce exclusivity or adopt factorial designs).

HardTechnical

44 practiced

You are mentoring a junior analyst who is unfamiliar with structured RCA. Draft a one-page playbook for them that includes: initial triage steps, a prioritized set of diagnostic queries to run, how to combine qualitative signals, how to document findings, and a checklist for handoff. Include concrete examples of good and bad artifacts.

Sample Answer

Objective: Give a junior analyst a one-page structured RCA (root-cause analysis) playbook to triage incidents, run diagnostics, combine qualitative signals, document outcomes, and hand off clearly.

Initial triage (first 30–60 mins)- Confirm scope & impact: metric(s) affected, timeframe, user segments, SLAs.- Reproduce the issue on dashboard/SQL for a sample timeframe.- Rule out obvious upstreams: recent deployments, ETL failures, schema changes, alerts.- Communicate status to stakeholders: “Investigating — will update in 60 min.”

Prioritized diagnostic queries (run in order)1) Verify metric integrity

sql

SELECT event_date, COUNT(*) as events
FROM events
WHERE event_date BETWEEN '2025-11-01' AND '2025-11-15'
GROUP BY event_date
ORDER BY event_date;

2) Compare cohorts / segments

sql

SELECT country, COUNT(*) FROM events WHERE event_date='2025-11-12' GROUP BY country;

3) Check upstream data freshness / duplicates

sql

SELECT max(updated_at), COUNT(*) FROM staging.table WHERE updated_at >= now() - interval '1 day';

4) Join to reference tables to find mismatches (e.g., missing user_ids)5) Run rolling-window baseline and z-score to detect anomalies

Combining qualitative signals- Correlate logs, release notes, Slack threads, incident tickets with quantitative breaks.- Assign confidence levels: Strong (direct data match), Medium (circumstantial), Weak (anecdotal).- Example: spike in error logs + drop in processed rows = Strong signal.

Documenting findings (use template)- Summary (1–2 lines), Timeline (events, queries run), Data evidence (charts & SQL snippets), Hypotheses (ranked), Tests performed, Conclusion + next steps, Confidence level.- Store as a single source (Confluence page + GitHub gist for queries).

Handoff checklist- Reproduce steps documented? Y/N- Attach raw query results & visualization links- List affected cohorts, time ranges, and P0 actions- Owner assigned & SLA for next update- Postmortem meeting scheduled if needed

Good artifact examples- Good: Confluence page titled “RCA – 2025-11-12 drop in DAU” with TL;DR, timestamps, SQL, charts, hypothesis list, owner, confidence, and links to code.- Bad: Scattershot Slack messages, screenshots without queries, “looks fine” statements, no timeline or next steps.

Tips- Be concise. Prioritize reproducible queries and evidence.- If unsure, escalate early with what you’ve ruled out.

HardBehavioral

54 practiced

Tell me about a time you had to present uncertain or partial findings to executives and recommend a course of action. How did you communicate uncertainty, quantify risk, and get alignment for a next step (e.g., rollback, monitor, investigation)? Discuss language, visuals, and escalation choices.

Sample Answer

Situation: Last year I owned weekly performance reporting for our checkout funnel when we observed a 12% drop in paid conversions the morning after a new frontend release. Traffic and revenue were at risk, but our analytics showed incomplete events because a third‑party tag failed intermittently — so findings were partial and noisy.

Task: I had to brief the VP of Product and CTO within 2 hours, communicate uncertainty and risk, and recommend an actionable next step (rollback, monitor, or deeper investigation).

Action:- I prepared a one‑page executive brief and a short slide deck. Top of page: summary sentence with my recommendation (“Monitor + quick rollback readiness”) and reason (observed 12% drop; tracking gaps; potential 3–8% true impact after adjustment).- I quantified uncertainty: presented observed drop (12%), then adjusted estimate range using sensitivity analysis — assumed 10–30% of events missing, producing a likely true impact of 4–9% with 90% CI. I showed this as a small table and a bar chart with error bars so non‑technical execs could see best/most/worst cases.- I used calibrated language: “based on current data, most likely impact ≈6% revenue loss; worst case up to 9%,” avoiding absolute claims and describing assumptions explicitly (which event types missing, when the tag failed).- Visuals: one slide with a timeline (deploy → anomaly → partial failures), a stacked waterfall estimating revenue delta under three scenarios (conservative/most likely/worst), and a simple risk matrix (impact vs likelihood) color‑coded.- Recommendation & playbook: Because rollback cost was low and user impact moderate, I recommended “Monitor for 1 hour with rollback readiness” — i.e., don’t rollback immediately but be prepared. I provided clear escalation triggers: if adjusted impact >7% or trending continues for >30 minutes, trigger rollback. I included exact SQL queries and dashboard filters the on‑call engineers could use, plus a rollback runbook and owner names.- Communication tone: concise, decisive, non‑alarmist. I said what we knew, what we assumed, the confidence level, and the contingency plan.

Result: Execs agreed to the monitor + readiness approach. We caught the issue: metrics trended to a 7.5% adjusted loss after 25 minutes, triggered the rollback, and conversions recovered within 10 minutes. The quick, quantified presentation kept leadership aligned and avoided an unnecessary immediate rollback while enabling fast action when thresholds were met.

Learning: For executives, prioritize a clear recommendation, quantify uncertainty with simple visuals and scenario ranges, and provide concrete escalation criteria and owners — that combination builds trust and speeds decision-making.

Unlock Full Question Bank

Get access to hundreds of Data Investigation and Root Cause Analysis interview questions and detailed answers.

Join thousands of developers preparing for their dream job.