Data Investigation and Root Cause Analysis Questions

Direct answer. Two dashboards (or a dashboard versus raw event logs) reporting different numbers for what should be the same metric is one of the most common data-investigation tasks, and it's almost always caused by a difference in DEFINITION or SCOPE, not by one side being simply wrong: different metric definitions, different timezones, different filters, different data freshness, different join logic, or different aggregation windows.

Structured elaboration. A systematic reconciliation walks through these in order of how often each one is the actual cause: (1) Metric definition - do both sides define the metric identically (does 'active user' mean the same session, event, or login threshold in both places)? This alone explains a large share of real-world discrepancies. (2) Timezone and date boundaries - are both systems truncating timestamps to the same timezone and using consistent inclusive/exclusive boundaries for 'today'? (3) Filters - does one system silently exclude bots, internal test accounts, or a specific platform that the other includes? (4) Freshness - is one number computed on a still-loading, partially-arrived dataset while the other waited for late-arriving data to settle? (5) Join and aggregation logic - does a join between two tables in one pipeline introduce duplication or drop rows the other pipeline doesn't? Deciding which source is authoritative in the short term should be based on which one is closer to the RAW, least-transformed data and which has the more defensible, documented definition, not which number is more convenient. The permanent fix is documenting one canonical definition and either making both systems compute from it or clearly labeling the difference if both are legitimately answering different questions.

Worked example. Weekly active user counts: Dashboard A shows 1.2M, Dashboard B shows 1.35M for the same week. Checking definitions first: Dashboard A counts a 'user' as a unique authenticated user ID; Dashboard B counts a unique device ID, which double-counts users who are logged in on two devices. That single definitional difference, verified by checking the fraction of users with more than one device ID in a sample, explains essentially the entire 12% gap without needing to dig into timezone or join logic at all.

Trade-offs and pitfalls. The instinct to jump straight to 'one of these must be a bug' skips the much more common and much cheaper-to-check explanation, a definitional or scope difference; always rule that out with a quick side-by-side comparison of documented definitions BEFORE debugging pipeline code. When presenting the reconciliation to teams who each trust their own number, lead with the specific mechanism (not 'your number is wrong') and propose the canonical definition going forward as the fix, since the goal is alignment, not assigning blame.

Direct answer. Presenting a root-cause conclusion under incomplete evidence, especially to a skeptical or disputing stakeholder, works best as a headline conclusion stated plainly, backed by a small number of concrete pieces of evidence, with uncertainty named explicitly rather than hidden, and a specific recommended next step with an owner attached.

Structured elaboration. Lead with the conclusion itself, not a chronological narrative of how the investigation unfolded, since a stakeholder deciding whether to act needs the bottom line first and the supporting reasoning second. Choose the two or three pieces of evidence that most directly support the conclusion (not every check that was run, just the ones that actually move the needle), and state them concretely (numbers, not adjectives). Name the specific uncertainty that remains, rather than implying more confidence than the evidence supports; a stakeholder trusts a conclusion MORE, not less, when its limits are stated honestly. Close with a specific next step and an owner, since an open-ended 'we should keep investigating' without a next action tends to stall. When a stakeholder actively disputes the conclusion, the response isn't to defend the analysis's completeness, it's to ask specifically what evidence would change their mind and go check whether that evidence exists, since a genuine disagreement is often really a disagreement about what would count as proof, not about the analysis itself.

Worked example. Presenting a conclusion that a recent pricing-algorithm change caused a revenue dip, with incomplete telemetry: 'Revenue dropped 4% starting the day the new pricing algorithm went live. The drop is concentrated specifically in the segment that received the new pricing; a comparison segment still on the old algorithm is flat over the same period. We don't yet have a full breakdown of which specific price changes drove the effect. Recommendation: pause the rollout at its current percentage while we complete that breakdown, owned by [name], with an update by [date].' When a stakeholder pushes back that 'the timing could be a coincidence,' the response is to ask what would resolve that doubt for them (a longer observation window, or a specific price-tier breakdown), and go get it, rather than re-arguing the existing evidence more forcefully.

Trade-offs and pitfalls. Overstating confidence to sound more authoritative is a common trap that backfires badly if the conclusion later turns out wrong; naming uncertainty honestly, paired with a clear recommendation anyway, is a stronger position than false certainty, because it survives scrutiny and preserves credibility for the next investigation.

Direct answer. A cohort retention table groups users by the period (typically week) they first showed up, then computes, for each subsequent period, what share of that original cohort is still active. The two edge cases that most often break a naive version of this query are users appearing under more than one identifier (multiple devices) and cohorts too recent to have reached later retention windows yet.

Structured elaboration. The query needs three pieces: (1) a canonical, deduplicated identity per real person, since a user active on two devices under two raw IDs should count once, not twice, in both the cohort-size denominator and the retained-users numerator; (2) a cohort assignment, the earliest period each canonical identity appears in, truncated to the chosen grain (week here); (3) a retention computation, for each cohort and each subsequent week offset, the count of distinct canonical identities with any activity in that offset week, divided by the cohort's total size. Partial or incomplete recent cohorts should either be excluded from the offsets they haven't reached yet, or explicitly marked, rather than silently shown as a retention curve that will still change.

Worked example (executed against DuckDB).

WITH cohort AS (
  SELECT canonical_user_id, MIN(DATE_TRUNC('week', signup_at)) AS cohort_week
  FROM users GROUP BY canonical_user_id
),
active_weeks AS (
  SELECT DISTINCT u.canonical_user_id,
         DATE_DIFF('week', c.cohort_week, DATE_TRUNC('week', e.event_time)) AS week_offset
  FROM events e
  JOIN users u ON u.user_id = e.user_id
  JOIN cohort c ON c.canonical_user_id = u.canonical_user_id
  WHERE DATE_DIFF('week', c.cohort_week, DATE_TRUNC('week', e.event_time)) >= 0
)
SELECT c.cohort_week, aw.week_offset, COUNT(DISTINCT aw.canonical_user_id) AS retained_users,
       (SELECT COUNT(DISTINCT canonical_user_id) FROM cohort c2 WHERE c2.cohort_week = c.cohort_week) AS cohort_size
FROM cohort c JOIN active_weeks aw ON aw.canonical_user_id = c.canonical_user_id
GROUP BY c.cohort_week, aw.week_offset ORDER BY 1,2;

Run against a small synthetic dataset (4 raw user rows, but 2 of them, user_id 1 and 2, share canonical_user_id=1, i.e. the same person on two devices, both signing up the same week), the query correctly collapses them: cohort size for the week of 2025-01-06 comes out as 3 (not 4), and week-1 retention is 3/3 = 100%, week-2 retention is 1/3 = 33% (only canonical user 1 returned in week 2). Without the canonical-ID join, the naive version would report a cohort size of 4 and silently double-count the multi-device user's activity in both the numerator and denominator, understating true per-person retention.

Trade-offs and pitfalls. The most common defect in a query like this: joining events to users on the RAW user_id for cohort assignment but then counting DISTINCT user_id (not the canonical ID) in the retention numerator, which looks correct but silently inflates numbers for any multi-device user. A second common defect is including week offsets that a recent cohort hasn't reached yet without flagging them, which makes the tail of the chart look artificially strong (only die-hard early returners have had time to show up) rather than genuinely better retention.

Direct answer. When a headline metric moves in a way that contradicts what's happening in every individual segment underneath it (an aggregate rate improves while every segment's own rate declines, or a ratio metric moves even though nothing about typical individual transactions changed), the explanation is almost always a shift in the underlying MIX or COMPOSITION, not a genuine change in behavior, and the fix is to test that directly rather than trust the aggregate.

Structured elaboration. The test: recompute the metric with the composition HELD FIXED at one period's mix (standardizing), and see whether the apparent trend survives; if a mix-adjusted version of the metric moves much less than the raw aggregate, or even reverses direction, that confirms composition, not real within-segment change, is driving most of the apparent move. For a ratio-style metric distorted by a shift in the underlying distribution (like average order value being pulled by a handful of large orders), comparing the MEAN against the MEDIAN is a fast, cheap version of the same idea: if the mean moved but the median didn't, a shift in the tail of the distribution, not typical behavior, is responsible. Catching this before reporting a misleading aggregate number means making segment-level and distributional checks a standing part of the reporting process for any metric where composition is known to shift (channel mix, geographic mix, order-size mix), not an afterthought only run when something already looks suspicious.

Worked example. Average Order Value (AOV) increases while total revenue stays flat. Comparing mean AOV against median AOV for the same period shows the median is essentially unchanged, while the mean climbed, pointing at a small number of unusually large orders pulling the average up rather than a genuine shift in typical purchase size; checking the order-size distribution directly confirms a handful of bulk enterprise orders are responsible. Reporting median AOV (or mean AOV alongside the count and size of large-order outliers) rather than mean AOV alone would have avoided the misleading headline in the first place.

Trade-offs and pitfalls. The compositional-shift explanation isn't automatically the right one every time a headline number looks surprising; it should be treated as a hypothesis to TEST (via standardization or a mean-versus-median check), not assumed, since a real behavior change and a compositional shift can also happen simultaneously and partially offset or reinforce each other, which a single standardization pass can miss if not checked carefully.

Direct answer. Once a specific instrumentation defect is confirmed (rather than still hypothesized), the work shifts from diagnosis to impact assessment and remediation: quantify how much historical data it corrupted, apply a stop-gap so the bleeding stops immediately, and design a durable fix that prevents recurrence, in that order of urgency.

Structured elaboration. Quantifying impact means identifying the exact window the defect was active (using deploy logs, or the first appearance of the anomalous pattern in the data itself if deploy timing is unclear), and estimating the magnitude on affected metrics for that window, ideally with a confidence range rather than a false-precision point estimate if the defect's effect wasn't uniform. A short-term stop-gap depends on the defect type: for null values silently excluding rows, a temporary filter or default-value patch that stops further data loss while a proper fix is built; for duplicate events, a de-duplication rule applied at query time as an immediate band-aid. The long-term fix addresses the ROOT cause in the instrumentation or pipeline code itself, not just its symptom, and should include a test or monitor that would have caught this specific failure mode automatically, so the same defect (or its close cousin) doesn't silently recur.

Worked example. A pipeline started producing null user_id values for a subset of events. Impact quantification: comparing the null rate before and after the defect's onset (identified via a deploy timestamp) shows roughly 2.5% of daily active-user events were affected over an 11-day window, understating the active-user metric by an estimated 2-3% during that period. Stop-gap: a patched version of the identity-resolution step that falls back to a secondary identifier when the primary one is null, deployed same-day to stop further loss. Long-term fix: the actual upstream cause, a software development kit (SDK) update that stopped populating a required field under a specific device-locale combination, gets fixed at the source, and a daily automated alert on the user_id null rate crossing a small threshold is added so a similar regression pages someone within hours instead of being caught 11 days later.

Trade-offs and pitfalls. Backfilling the 11 days of affected historical data is a separate decision from the stop-gap and shouldn't be rushed: if the defect's effect wasn't uniform (it may correlate with a specific device or locale, meaning a naive uniform backfill would introduce its own bias), a careful, documented backfill methodology with disclosed assumptions is worth the extra time over a fast-but-wrong correction.