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

86 practiced

Design a reproducible investigation platform for RCA that includes templated SQL notebooks, automated data-quality checks, a hypothesis-tracking registry, dashboards, and runbooks. As Product Manager, specify the core components, team responsibilities, data lineage needs, access patterns, and KPIs you would use to measure adoption and effectiveness across product teams.

Sample Answer

Requirements & constraints:- Primary: reproducible, auditable RCA workflows for product teams across scale (10s–100s teams). Secondary: low-friction adoption, security, multi-cloud data sources, <5 min end-to-end query latency for interactive notebooks.

High-level architecture:- Orchestration/API layer: notebook service + workflow engine (prefers managed Airflow-like). Hosts templated SQL notebooks, triggers DQ checks, writes CI artifacts to registry.- Notebook templating & execution: parameterized SQL/SQLMagic notebooks (versioned), execution sandbox with query planner + cached results.- Data-quality service: configurable checks (schema, nulls, cardinality, distributional drift) run on schedule and on-demand.- Hypothesis registry: tracked hypotheses with metadata (owner, dataset snapshot, notebook version, results, status, links to runbook).- Lineage & metadata store: column-level lineage, dataset versions, query provenance, schema snapshots (integrate with open lineage / Marquez).- Runbooks & dashboards: templated runbooks auto-populated from hypothesis runs; dashboards in BI tool surfaced by lineage tags.- Auth & access control: RBAC + dataset-level ABAC, SSO, audit logs.- Storage: immutable artifact store for query results, datasets snapshots, notebook versions.

Team responsibilities:- Product: vision, prioritization, UX, KPI tracking.- Data Platform Eng: lineage, execution infra, DQ primitives.- Analytics/ML Eng: notebook templates, hypothesis patterns.- Security/Privacy: access controls, PII handling.- Support/Docs: runbooks, onboarding, training.

Data lineage needs:- Column-level provenance, query->dataset edges, timestamped dataset snapshots, dataset quality history, mapping to hypothesis runs and notebooks.

Access patterns:- Read-heavy: interactive notebook queries, dashboard reads.- Write: hypothesis creation, DQ rule edits, notebook commits.- Bulk snapshot exports for offline replay; caching for frequent queries.

KPIs (adoption & effectiveness):- Adoption: number of active teams/month; % of RCAs using platform; notebook template reuse rate.- Efficiency: median time-to-RCA (first hypothesis → resolution); % reduction in duplicated investigations.- Reproducibility & trust: % of RCA runs that reproduce results (binary); DQ alert MTTR.- Quality: average hypothesis lifecycle (open→validated/rejected) time; number of production incidents traced & resolved using platform.- Platform health: successful run rate, average execution latency, lineage coverage (% datasets instrumented).Measurement cadence: weekly product dashboards + quarterly business reviews; NPS from users and case studies.

MediumTechnical

72 practiced

Explain funnel gap analysis. Provide a systematic approach to identify which funnel step is responsible for an increase in overall drop-off. Include the SQL-level approach to compute unique users at each ordered step, how to calculate absolute and relative drop per step, and how to handle users who skip steps or have out-of-order events.

Sample Answer

Funnel gap analysis identifies which step(s) cause most users to drop between ordered stages (e.g., Visit → Sign-up → Activate → Purchase). Systematic approach:1. Define steps and time window (session vs 7-day).2. For each user, find the first timestamp they performed each step.3. Count unique users who completed each step in the required order.4. Compute absolute and relative drops and prioritize largest impact gaps.5. Investigate cohorts, device, traffic source for root causes.

SQL-level approach (example using event table events(user_id, event_name, ts)):

sql

-- first timestamp per user per step
WITH first_step AS (
  SELECT user_id, event_name,
         MIN(ts) AS first_ts
  FROM events
  WHERE event_name IN ('visit','signup','activate','purchase')
  GROUP BY user_id, event_name
),
-- pivot to columns
user_steps AS (
  SELECT
    user_id,
    MIN(CASE WHEN event_name='visit' THEN first_ts END) AS t_visit,
    MIN(CASE WHEN event_name='signup' THEN first_ts END) AS t_signup,
    MIN(CASE WHEN event_name='activate' THEN first_ts END) AS t_activate,
    MIN(CASE WHEN event_name='purchase' THEN first_ts END) AS t_purchase
  FROM first_step
  GROUP BY user_id
),
-- enforce ordering: only count a step if its timestamp is after prior step
ordered AS (
  SELECT user_id,
    CASE WHEN t_visit IS NOT NULL THEN 1 ELSE 0 END AS did_visit,
    CASE WHEN t_signup IS NOT NULL AND t_signup >= t_visit THEN 1 ELSE 0 END AS did_signup,
    CASE WHEN t_activate IS NOT NULL AND t_activate >= t_signup THEN 1 ELSE 0 END AS did_activate,
    CASE WHEN t_purchase IS NOT NULL AND t_purchase >= t_activate THEN 1 ELSE 0 END AS did_purchase
  FROM user_steps
)
SELECT
  SUM(did_visit) AS users_visit,
  SUM(did_signup) AS users_signup,
  SUM(did_activate) AS users_activate,
  SUM(did_purchase) AS users_purchase
FROM ordered;

Calculations:- Absolute drop between step i and i+1 = users_i - users_{i+1}- Relative drop (%) = (users_i - users_{i+1}) / users_i * 100Prioritize steps with high absolute loss (business impact) and high relative loss (efficiency problem).

Handling skips & out-of-order events:- Choose funnel strictness: strict (require ordering) vs. lenient (count step if performed anytime). Use ordering by first_ts to avoid counting out-of-order noise.- For skips: report both “direct converters” (skip intermediate) and “sequential converters” to understand patterns.- Use session_id or max allowed delta between steps (e.g., 24h) to avoid linking unrelated events.- Run sensitivity checks: vary time window and strictness, and surface anomalies (bots, QA users).

Finally, segment drops by cohort, channel, device and run qualitative checks (UX, technical errors, instrumentation gaps) to pinpoint root causes.

MediumTechnical

87 practiced

Describe how to perform variance decomposition for a metric that changed by X% between two periods. Explain step-by-step how you would compute the contribution of each segment (for example: country, platform, traffic-source) to the overall change and how you would present the results to stakeholders so they can understand which segments drove the change.

HardTechnical

45 practiced

How would you detect and prevent 'peeking', p-hacking, and other experiment malpractice across hundreds of experiments run by multiple teams? Propose governance (policy), automated checks (software), and cultural practices (training, incentives) that maintain experiment validity while not blocking experimentation.

Sample Answer

Situation: As a Product Manager responsible for many experiments across teams, you need governance that preserves statistical validity without killing velocity.

Policy (governance)- Mandatory experiment registry: every experiment must be pre-registered with hypothesis, primary metric, sample size, stopping rule, and owner before data collection.- Define acceptable analysis families (A/B, multi-armed bandit, sequential) and required error-control methods (alpha spending, Bonferroni, or FDR) depending on multiplicity.- Enforce primary/secondary metric distinction; only primary drives decisions.- Audit cadence: random audits + post-mortem for every ship from experimental evidence.

Automated checks (software)- CI-style experiment linter that blocks deployment of experiments not pre-registered.- Automated power/sample-size calculator and guardrail that warns if underpowered.- Runtime monitoring: randomization balance checks, early peek detector (tracks sequential looks vs. registered stopping rules), and automated alpha-spending adjustment when interim looks occur.- Standardized analysis library (shared SDK) that applies pre-specified estimators, multiple-testing corrections, and logs all analyses to immutable storage for reproducibility.- Dashboards with experiment metadata, p-values, confidence intervals, and replication flags; alerts for suspicious patterns (multiple near-threshold p-values across variants).

Cultural practices (training & incentives)- Train teams on hypothesis-driven design, basic stats (Type I/II, p-hacking risks), and interpretation.- Reward quality of experiments (replicable, pre-registered, used in decisions) not just "positive" outcomes.- Create lightweight review board: quick 30-minute pre-registration review for high-impact experiments.- Encourage replication by allocating small % of roadmap to replication/validation.- Promote blameless postmortems and public experiment library so learnings scale.

Why this balances validity and speed- Pre-registration + automated linting prevents most peeking/p-hacking without manual slowdowns.- Shared SDK and dashboards make correct analysis the path of least resistance.- Cultural incentives align teams to value rigor over cherry-picked wins while lightweight reviews and automation preserve experimentation velocity.

HardTechnical

55 practiced

An A/B test increased sign-ups (+10%) but shows lower 90-day retention (-5%) for users in the treatment. As PM, design an integrated analysis and decision framework that balances short-term acquisition gains against long-term retention costs. Define the metrics to track (LTV, retention curves), required sample sizes and holdout periods, possible strategies (targeted rollout, follow-up experiments, personalization), and a pre-specified decision rule for rollout or rollback.

Sample Answer

Objective: decide whether the treatment’s +10% sign-ups is worth a -5% 90-day retention loss by measuring downstream value (LTV) and business impact, then choose rollout strategy.

Metrics to track- Acquisition: sign-up rate, CAC (cost per acquired user)- Engagement/retention: daily/weekly retention curves (D1, D7, D30, D90), cohort survival function- Revenue & value: short-term ARPU and projected LTV using cohort-based discounting: LTV = Σ_{t=0..T} ARPU_t * retention_t / (1+discount)^t- Secondary metrics: activation rate, NPS, support load, churn reasons- Risk metrics: variance of retention by segment (device, geography, traffic source)

Experiment design & sample size- Power test on key metric: if LTV is primary, compute minimum detectable effect (MDE) in LTV (e.g., detect 3% drop with 80% power, α=0.05). Use historical variance of weekly ARPU × retention to compute N. As rule-of-thumb, hold at least several thousand users per arm for retention sensitivity; for small revenue per user, scale to tens of thousands.- Holdout period: run until cohorts reach stable retention signal—minimum 90 days if measuring D90. Use interim checks (D7/D30) with pre-specified stopping rules but do not make final decision before D90 for retention.

Analysis plan- Causal intent-to-treat; bootstrap confidence intervals on retention curves and LTV.- Compute net incremental value per 1000 users: ΔSignups*Conversion*LTV_new - lost_existing_users*LTV_loss - incremental costs.- Segment analysis: identify segments where retention drop is concentrated (e.g., organic vs paid).

Strategies- Targeted rollout: enable treatment only for segments with high acquisition ROI and neutral retention.- Follow-up experiments: A/B test mitigations (onboarding tweaks, nudges, feature flags) to recover retention while keeping acquisition lift.- Personalization: route users to treatment condition only when predicted lifetime value (via propensity model) is above threshold.- Rollback if net LTV negative across priority segments.

Pre-specified decision rule (example)1. Calculate incremental net LTV per new user after 90 days with 95% CI.2. If lower bound of net incremental LTV > 0 and acquisition volume scales business KPIs → full rollout.3. If point estimate >0 but lower bound ≤0 → targeted rollout to segments with positive net LTV + run remediation experiments for 1–2 cohorts (additional 90 days).4. If point estimate ≤0 → rollback immediately.5. Re-evaluate quarterly using long-run cohorts and monitor for persistent effects.

Governance: document plan, required sample sizes, holdout windows, and decision thresholds before unblinding; include finance/legal for revenue modeling and product/eng to implement targeted flags.

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.