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.
HardTechnical
51 practiced
Model drift: production model accuracy drops and feature distributions shifted. Propose a statistical framework to detect drift, localize responsible features, and recommend whether to retrain, rollback, or collect more data. Include tests (PSI, KS), multivariate drift methods, and decision thresholds tied to business impact.
Sample Answer
Situation: Production model shows accuracy drop and shifted feature distributions. Here's a practical statistical framework—detection, localization, and decision policy—appropriate for an AI Engineer running large models.Detection (univariate + multivariate):- Univariate: compute Population Stability Index (PSI) for numeric features (bins by quantiles) and Kolmogorov–Smirnov (KS) test for continuous distributions. For categorical features use Chi-square or JS divergence. - Rule of thumb: PSI < 0.1 (no drift), 0.1–0.25 (moderate), >0.25 (major). - KS: p-value < 0.01 flagged (after multiple-testing correction).- Multivariate: use Maximum Mean Discrepancy (MMD) with RBF kernel, energy distance, or a classifier two-sample test (train a gradient-boosted tree to distinguish train vs. production; AUC > 0.7 indicates drift). Also monitor joint-covariance shifts via Hotelling’s T2 or monitor principal component subspace change (compare projections with Procrustes or subspace angle).Localization:- Rank features by univariate test effect sizes (PSI magnitude, KS D-statistic).- Compute feature contribution to multivariate drift using permutation importance on the two-sample classifier (drop feature -> delta AUC) and SHAP values from that classifier to localize drivers.- Correlation / covariance change: compare pairwise Pearson/Spearman matrices and flag high Δ correlations (use matrix norm or matrix distance).- Residual-conditioned analysis: segment by model residuals; for cohorts with large errors, recompute feature drift metrics to find features that correlate with degraded predictions.Decision policy (tie thresholds to business impact):- Inputs: accuracy drop ΔAcc, business loss per unit error L, sample size n_prod, drift severity S (e.g., max PSI or classifier AUC), statistical confidence.- Define expected business cost = ΔAcc * baseline_volume * L.- Actions: - Monitor-only: if S in moderate range (PSI 0.1–0.25 or classifier AUC 0.6–0.7) and expected cost < tolerance → increase sampling, alert, and start targeted data collection. - Retrain: if S high (PSI >0.25 or AUC >0.75) and expected business cost > threshold OR accuracy drop > X% (e.g., 2–5% absolute depending on SLA) → trigger retrain with recent data, run validation and canary rollout. - Rollback: if model degradation coincides with upstream data pipeline bug or transient upstream injection (detected by sudden spike in a small number of features) and historical model outperforms on recent holdout → rollback to previous model and fix pipeline. - Collect more data: if n_prod is small (low statistical power) or drift concentrated in rare cohorts, prioritize targeted labeling/collection before retraining.- Require safety checks: before full rollout, run shadow evaluation, canary with live traffic (1–5%), and monitor fairness metrics.Implementation notes:- Control false positives: use multiple testing correction (Benjamini–Hochberg) and bootstrap confidence intervals for PSI/K S.- Automate: pipeline to compute daily PSI/KS, retrain triggers in CI/CD, store windows (train, baseline, prod), logging for reproducibility.- Visualization: dashboard with per-feature PSI, KS D, two-sample classifier AUC, covariance heatmap, and recommended action.- Example thresholds (customize per product): PSI >0.25 OR classifier AUC >0.75 OR ΔAcc >3% → retrain; transient spike in one feature with rollback test passing → rollback; low n_prod (<5000 records) with moderate drift → collect data.This framework balances statistical rigor (univariate tests, multivariate detectors, localization via feature importance/SHAP) with business-driven decision rules (expected cost, thresholds, canary rollouts), and operational controls (multiple-testing correction, power checks, automated pipelines).
MediumTechnical
50 practiced
You discover support tickets spiking correlated to a recent personalization change. Describe a minimal experiment to confirm causality using feature flagging: include control/treatment allocation, sample size considerations, metrics to capture, and rollout decision criteria.
Sample Answer
Goal: Run a quick A/B (feature-flag) experiment to test whether the new personalization change caused the support-ticket spike.Experiment design- Allocation: Randomize users who saw the change into two groups via feature flag: Treatment = personalization ON, Control = personalization OFF (fallback to previous logic). Use user-level randomization (by stable user-id) to avoid cross-contamination.- Sample size: Compute minimal detectable effect (MDE) for ticket-rate increase. If baseline ticket rate is p0 (e.g., 0.5%), choose MDE you care about (e.g., 50% relative increase → p1=0.75%), set α=0.05, power=0.8, and solve standard two-proportion sample-size formula. If quick estimate needed, target ~10k users per arm for low base rates; smaller if ticket volume higher or you can run longer.- Duration: Run until reaching required sample or for at least one full product-cycle (e.g., 7–14 days) to capture usage cadence.Metrics to capture- Primary: Support-ticket incidence per user (binary) and ticket rate per 1k MAUs; time-to-first-ticket after exposure.- Secondary: Ticket severity (priority), categories (automated NLP classification of ticket text to identify root causes), engagement metrics (click-through, session length), model output diagnostics (distribution shift of personalization features), and error/exception logs.- Observability: Capture cohort identifiers, timestamps, model inputs/outputs, and breadcrumbs to reproduce the context.Analysis & rollout criteria- Causal test: Compare ticket rates with z-test or bootstrap for proportions; adjust for multiple comparisons if many segments.- Decision thresholds: - Rollback: Statistically significant increase in primary metric (p<0.05) and absolute increase exceeds business tolerance (e.g., +0.2% absolute or +40% relative) OR increase in high-severity tickets. - Proceed: No significant increase and no adverse signal in secondary metrics; or any small increase offset by positive downstream metrics (e.g., revenue) if business trade-off acceptable. - Partial rollout: If effect heterogeneous, restrict personalization to safe segments identified by analysis.Notes- If tickets are rare, augment with leading indicators (user complaints, NPS drops, classifier for complaint severity) to gain power.- Ensure feature flag allows immediate rollback and logging to trace affected users.
MediumTechnical
57 practiced
Write a SQL pattern (pseudocode acceptable) to validate pipeline integrity by comparing raw event counts vs processed event counts for the last 7 days for each producer service. Schema: raw_events(producer_id, event_date, raw_count), processed_events(producer_id, event_date, processed_count). Flag producer-date combinations with >5% discrepancy and explain how you'd handle late-arriving events.
Sample Answer
Approach: aggregate counts per producer and date for the last 7 days, join raw vs processed, compute relative discrepancy = abs(raw - processed)/NULLIF(raw,0), flag rows > 5%. Also identify days with zero raw (avoid divide-by-zero) and produce a status. For late-arriving events, keep a rolling window/backfill policy and re-run checks; mark records as "finalized" after an SLA (e.g., 48h) and track reprocessing.
sql
WITH last7 AS (
SELECT current_date - seq.day AS event_date
FROM generate_series(0,6) AS seq(day)
),
raw_agg AS (
SELECT producer_id, event_date, SUM(raw_count) AS raw_count
FROM raw_events
WHERE event_date BETWEEN current_date - 6 AND current_date
GROUP BY producer_id, event_date
),
proc_agg AS (
SELECT producer_id, event_date, SUM(processed_count) AS processed_count
FROM processed_events
WHERE event_date BETWEEN current_date - 6 AND current_date
GROUP BY producer_id, event_date
),
all_prod_dates AS (
SELECT p.producer_id, d.event_date
FROM (SELECT DISTINCT producer_id FROM raw_events
UNION SELECT DISTINCT producer_id FROM processed_events) p
CROSS JOIN last7 d
)
SELECT
ap.producer_id,
ap.event_date,
COALESCE(r.raw_count,0) AS raw_count,
COALESCE(p.processed_count,0) AS processed_count,
CASE
WHEN COALESCE(r.raw_count,0)=0 AND COALESCE(p.processed_count,0)=0 THEN 0
WHEN COALESCE(r.raw_count,0)=0 THEN 1.0 -- treat as 100% discrepancy
ELSE ABS(r.raw_count - p.processed_count)::numeric / r.raw_count
END AS relative_discrepancy,
CASE
WHEN (CASE WHEN COALESCE(r.raw_count,0)=0 THEN 1.0 ELSE ABS(r.raw_count - p.processed_count)::numeric / r.raw_count END) > 0.05
THEN 'FLAG'
ELSE 'OK'
END AS status
FROM all_prod_dates ap
LEFT JOIN raw_agg r ON r.producer_id=ap.producer_id AND r.event_date=ap.event_date
LEFT JOIN proc_agg p ON p.producer_id=ap.producer_id AND p.event_date=ap.event_date
ORDER BY ap.producer_id, ap.event_date;
Handling late-arriving events:- Implement a reconciliation job that re-runs daily and for a configurable backfill window (e.g., 30 days).- Use an "ingestion_time" or watermark to allow 48–72h SLA for late arrivals; only mark a day as final after SLA.- Persist historical discrepancy flags and alert on new/changed flags (reprocessed days).- Optionally compute tolerance bands (absolute min count) to avoid noisy flags for very small counts.
HardTechnical
44 practiced
A distributed A/B experiment was rolled out to multiple regions and treatment effect estimates differ in sign across regions. How would you reconcile these results? List checks to perform to detect heterogeneity of treatment effect vs instrumentation/assignment problems and statistical approaches to summarize global impact.
Sample Answer
Approach: separate true heterogeneous treatment effects from instrumentation/assignment artifacts by systematic checks, then use appropriate statistical synthesis (meta-analysis or hierarchical modeling) to estimate global impact and quantify heterogeneity.Checks to detect instrumentation/assignment problems (order matters: quick triage first)- Verify randomization: check covariate balance by region (standardized differences, KS tests). - Check assignment integrity: logs, feature flags, SDK versions, targeting rules, bucketing algorithm, hashing seeds per region. - Inspect telemetry for technical failures: different dropout, missing events, SDK errors, network/timeouts, client-side bugs. - Treatment compliance and exposure: actual exposure rate, dosage, clickthroughs; measure contamination/cross-region traffic. - Sample size and power: small N regions produce noisy, sign-flipping estimates. - Temporal confounding: rollout times differ across regions (seasonality, campaigns). Run time-series or pre-period checks. - Metric validity: event definitions, localization, currency conversions, denominators differ by region. - DQM: reproduce aggregates from raw logs; replay a subset to validate counting.Checks suggesting real heterogeneity- Persistent, reproducible regional effects after above fixes. - Dose–response or interaction with region-level covariates (GDP, language, device mix). - Consistent patterns across similar regions or cohorts.Statistical approaches to summarize global impact- Meta-analysis: compute region-level effect + SE, then use fixed-effect (if no heterogeneity) or random-effects (DerSimonian–Laird or REML) meta-analysis; report pooled estimate with heterogeneity stats (Cochran’s Q, I²). - Meta-regression: include region-level covariates to explain heterogeneity. - Bayesian hierarchical model: partial pooling to shrink noisy regional estimates toward global mean while allowing true differences; yields posterior distributions and probability treatment >0. - Multilevel regression with individual-level covariates (MLM / mixed-effects) to estimate overall ATE and region-treatment interactions. - Subgroup/interaction tests with multiplicity control (pre-specify, use FDR/Bonferroni prudently). - Robustness: leave-one-region-out, funnel plots, influence diagnostics, permutation tests stratified by region.Actionable reporting- Present region estimates (forest plot), heterogeneity metrics, meta-regression results, and diagnostics that ruled out instrumentation problems. - If heterogeneity remains and business impact differs regionally, consider region-specific rollout decisions or adaptive policy (regional model tuning). - Document decisions, monitoring plan, and A/B test reruns where instrumentation issues were found.This workflow helps distinguish data problems from true effect heterogeneity and provides principled global estimates with quantified uncertainty.
HardSystem Design
47 practiced
Design an end-to-end reproducibility playbook for RCA investigations: include dataset versioning approach (e.g., data hashes, Delta Lake / S3 snapshots), seed and env control for models, artifact storage, and audit logging. Provide concrete steps an analyst must follow to produce a reproducible RCA deliverable.
Sample Answer
Requirements & constraints:- Reproducible RCA package that replays data, model, env, metrics and logs to re-run experiments exactly; tamper-evident audit trail; lightweight for analysts; scale to large datasets (S3/Delta), GPU workloads, multi-user.High-level architecture:- Data layer: Delta Lake on S3 (time-travel) + immutable daily snapshots + cryptographic content hashes (Blake3/SHA256) stored in metadata DB.- Orchestration: Airflow/Prefect to run pipelines with pinned DAG versions.- Compute: Containerized jobs (Docker) with exact image tags; use Nix/Conda-lock for env reproducibility.- Artifact store: MLflow or Artifact Registry for models, schemas, metrics, and evaluation artifacts; store model weights in object storage with checksum.- Audit log: Append-only event store (CloudTrail + Kafka + Elasticsearch) capturing user actions, pipeline runs, inputs, and git commit hashes.Concrete steps an analyst must follow:1. Capture hypothesis & scope: create RCA ticket with a unique RCA_ID and link to git issue.2. Pin code: commit analysis code/config to repo; record commit SHA and CI-built Docker image tag.3. Pin data: identify Delta table version or S3 snapshot. Record Delta version number and compute & store dataset hash (partition-level + aggregate hash) in RCA metadata.4. Lock env: export and attach environment spec (conda-lock.yml or Nix derivation) and random seeds for all libs (numpy, torch, tf, random). Store seed(s) in metadata.5. Execute via orchestration: run pipeline with RCA_ID, using pinned DAG, image tag, and data version. All runs produce run_id.6. Record artifacts: log models, metrics, confusion matrices, plots to MLflow under RCA_ID; store model binary + checksum in object store and register model URI.7. Collect runtime logs & provenance: stream container stdout/stderr, GPU metrics, dependency checksums, and system metadata to audit log; include env fingerprint (OS, kernel, libs).8. Produce deliverable: bundle README, code SHA, docker image tag, data hashes, Delta version, seeds, MLflow run_id, and audit log link into a reproducible archive (RCA_ID.tar.gz or signed manifest) stored in artifact registry.9. Verification step: run "replay" job (automated) that re-runs pipeline from manifest and performs byte-for-byte check of key artifacts and metric deltas; surface drift if mismatch.10. Governance: require signed approval and immutable retention policy; periodic audits to ensure replay succeeds.Key practices & trade-offs:- Use Delta time-travel for efficient snapshotting; for very large immutable backups, create S3 object snapshots + hashes.- Prefer cryptographic hashes + signed manifests to detect tampering.- Locking everything increases overhead; adopt heuristics to snapshot only upstream data necessary for RCA to balance cost.- Automate replay verification to ensure long-term reproducibility.
Unlock Full Question Bank
Get access to hundreds of Data Investigation and Root Cause Analysis interview questions and detailed answers.
