Why I Always Ask About Failed Reports, Not Just Wins

STRONG ANSWER — FIRST 90 SECONDS:

Interviewer: "Tell me about a report that
failed."

Candidate: "Sure. The monthly revenue report
double-counted refunds for two months before
we caught it.

The impact: finance made a pricing call on
a number that was 8% too high. The call was
reversible, but it cost a week of rework.

The root cause: I joined the orders table to
the refunds table on order ID. I assumed
order ID was unique across the fact table —
it wasn't, because a partial refund creates
a second row with the same order ID. So the
join doubled the revenue for any refunded
order.

The fix: I added a dedup step — group by
order ID and take the net revenue — and
re-ran the two months.

The prevention: I added a reconciliation
check that compares the report total to the
finance total daily. If they diverge more
than 0.5%, the report fails loudly with the
difference in the alert. It's caught two
smaller issues since.

The lesson: I now test joins against
uniqueness before trusting them — a
unique-constraint check or a group-by count
takes 30 seconds and would have caught this."

What the interviewer thinks in 90 seconds:
- "They named the report and the impact." ✅
- "They named the root cause, not the
  blame." ✅
- "They named the fix and the prevention." ✅
- "They named the lesson in a reusable
  rule." ✅
- "They understand their own work." ✅

Signal: strong. The rest of the interview
confirms it.

WEAK ANSWER — FIRST 90 SECONDS:

Interviewer: "Tell me about a report that
failed."

Candidate: "Yeah, there was one time a
report was wrong. I think it was a data
issue or something. We fixed it and moved
on. It was a while ago so I don't remember
the details."

What the interviewer thinks in 90 seconds:
- "No report named." ❌
- "No impact — did anyone use the wrong
  number?" ❌
- "No root cause — 'a data issue' is not
  a cause." ❌
- "No fix, no prevention." ❌
- "They didn't understand the report well
  enough to remember it." ❌
- "I'll ask a follow-up, but I already
  doubt the wins." ⚠️

What happens next:
- The interviewer asks "What was the
  report measuring?"
- The candidate gives a vague answer.
- The interviewer asks "How did you know
  it was wrong?"
- The candidate says "Someone on the
  finance team flagged it."

Signal: weak. The candidate's wins now
look less impressive, because they can't
explain a failure.

Why it failed:
❌ No specific report
❌ No impact named
❌ No root cause
❌ No fix or prevention
❌ No lesson
❌ The first 90 seconds signaled "I ran
  reports," not "I own reports"

ROOT CAUSE ANSWER — WHAT DECIDES:

Interviewer: "How did you find the root
cause?"

WHAT THE CANDIDATE SAYS:
"I found it by reconciling the report to a
known number, then working backwards.

Step one: I compared the report total to
the finance total for the same month. The
report was 8% higher. That narrowed the
scope from 'the whole report is wrong' to
'a subset of rows is duplicated.'

Step two: I checked row counts. The fact
table had 12,400 rows; the report showed
13,100. A 700-row gap — and 700 was exactly
the number of partial refunds that month.
That's when I suspected the refund join.

Step three: I tested the assumption. I
grouped by order ID and found 700 order IDs
with two rows — the order and the refund.
The join on order ID was duplicating the
revenue for every refunded order.

The root cause was my assumption that order
ID was unique. It wasn't, because a partial
refund creates a second row. The data was
correct; my model of it was wrong.

The fix: group by order ID and sum the net
revenue, so the refund nets against the
order. I re-ran the two months.

The trade-off: the dedup step adds a
group-by to a 12M-row query — about 4
seconds. I accepted that because correctness
matters more than the 4 seconds."

What the interviewer thinks:
- "They reconciled to a known number." ✅
- "They used row counts to narrow scope." ✅
- "They tested the assumption." ✅
- "The root cause is a model error, not
  a data error." ✅
- "The cost of the fix is named." ✅

Signal: confirmed judgment.

Why it decides:
✅ Reconciliation before guessing
✅ Row counts narrowed the scope
✅ The assumption was tested
✅ Root cause is honest: a model error
✅ The fix cost is named

PREVENTION ANSWER — WHAT DECIDES:

Interviewer: "How did you make sure it
didn't happen again?"

WHAT THE CANDIDATE SAYS:
"Three layers, and each one catches a
different failure.

Layer one: a reconciliation check. Every
morning, the report total is compared to
the finance total for the prior day. If they
diverge more than 0.5%, the report fails
loudly with the difference in the alert. That
catches model errors like the duplicate
refund — the class of error I made.

Layer two: a row-count check. The report
row count is compared to the fact-table row
count, adjusted for known aggregations. If
the counts diverge, the report fails before
it ships. That catches join fanout and
missing rows.

Layer three: a uniqueness test. The join
key is tested for uniqueness before the join
runs — a simple group-by count that fails the
pipeline if any key has more than one row.
That catches the exact assumption I got
wrong.

The cost: the three checks add about 20
seconds to the pipeline. I chose that over
faster runs because a wrong report costs a
week of rework — 20 seconds is cheap
insurance.

The result: the reconciliation check has
caught two smaller issues since — a missing
exchange rate and a timezone bug in the
daily boundary. Neither reached finance."

What the interviewer thinks:
- "Three layers, each with a purpose." ✅
- "The checks target different failures." ✅
- "The uniqueness test targets the exact
  mistake." ✅
- "The cost is quantified: 20 seconds." ✅
- "The checks have caught real issues." ✅

Signal: confirmed judgment.

Why it decides:
✅ Three layers, not one
✅ Each layer targets a failure class
✅ The exact mistake is covered
✅ The cost is quantified
✅ The checks have evidence

DEBUGGING STORY — STRONG VS. WEAK SIGNAL:

Scenario: "Tell me about a time a report was
wrong and you found out late."

WEAK SIGNAL:
"One time a report was wrong and the
stakeholder told me. I fixed the query and
re-ran it. It was fine after that."
What the interviewer thinks:
- "No root cause — what was wrong in the
  query?"
- "No prevention — would it happen again?"
- "They waited for the stakeholder to find
  it."
- Signal: weak.

STRONG SIGNAL:
"The retention dashboard showed a churn
spike in March that didn't exist. The
stakeholder noticed before I did — which
is the part I'm least proud of.

The root cause: the churn definition counted
a customer as churned if they had no order
in 30 days. But in March, we had a payment
outage that lasted 4 days — customers who
would have ordered didn't, so the definition
counted them as churned. The report was
measuring the outage, not churn.

The fix: I added a 'service interruption'
flag — days with a known outage are excluded
from the 30-day window, and the report
notes the exclusion so the stakeholder knows
what changed.

The prevention: I added a data-quality check
that compares the daily order count to a
7-day moving average. If it drops more than
30%, the report flags the day as anomalous
and warns the stakeholder before they read a
spike that's actually an outage.

The lesson: a report that doesn't account
for known outages will measure the outage.
I now ask 'what else could cause this
number to move?' before I trust a spike."

What the interviewer thinks:
- "They admitted the stakeholder found it
  first." ✅
- "The root cause is a definition problem,
  not a data problem." ✅
- "The fix accounts for outages." ✅
- "The prevention is a data-quality
  check." ✅
- "The lesson is a reusable question." ✅
- Signal: confirmed judgment.

Why it decides:
✅ Honest about who found it
✅ Root cause is a definition problem
✅ The fix is specific
✅ The prevention targets the cause
✅ The lesson is reusable

INTERVIEW FOLLOW-UP — STRONG VS. WEAK SIGNAL:

Scenario: "How did you know the fix
worked?"

WEAK SIGNAL:
"I re-ran the report and the number looked
right."
What the interviewer thinks:
- "Looked right compared to what?"
- "No validation."
- Signal: weak.

STRONG SIGNAL:
"Three checks, and I can show the numbers.

Check one: I reconciled the fixed report to
the finance total for the two affected
months. The difference went from 8% to
0.2% — within our tolerance.

Check two: I re-ran the report for the six
months before the bug and compared them to
the old report. Five of six months were
unchanged, which is what I expected — the
bug only affected months with partial
refunds, and only two had them. The one
other month that changed was a month with a
single partial refund; the fix corrected it
by 0.1%, which was within noise.

Check three: I back-tested the reconciliation
check against the six months of history. It
would have caught the bug in the first month
it appeared — the divergence was 8%, well
above the 0.5% threshold.

The cost: the back-test took half a day. I
chose to do it because 'the number looks
right' is not evidence — I wanted to know the
check would have caught the original bug, not
just that today's number is plausible."

What the interviewer thinks:
- "Three checks, each with a purpose." ✅
- "The reconciliation has before/after
  numbers." ✅
- "The historical comparison isolates the
  affected months." ✅
- "The back-test proves the check would
  have worked." ✅
- "They refused to accept 'looks right'." ✅
- Signal: confirmed judgment.

Why it decides:
✅ Reconciliation with before/after
✅ Historical comparison
✅ Back-test of the prevention
✅ The cost of the back-test is named
✅ "Looks right" is rejected as evidence

Failed-Report Story Checklist:

FIRST 90 SECONDS:
☐ Name the report and what it measured
☐ Name the decision it misled and the
  impact
☐ Name the root cause, not the blame
☐ Avoid "a data issue" — name the actual
  cause

WHAT DECIDES:
☐ Root cause is a model error, a
  definition error, or a code error
☐ The fix addresses the root cause, not
  a symptom
☐ The prevention targets the failure
  class
☐ Name how you knew the fix worked:
  reconciliation, back-test, comparison
☐ Name the cost of the fix or the
  prevention

WHAT CONFIRMS:
☐ A reusable lesson or rule
☐ Honest about who found it
☐ The follow-up "how did you know?" is
  answered with evidence
☐ "Looks right" is rejected as evidence
☐ The prevention has caught real issues
  since

RED FLAGS:
❌ "I don't remember the details"
❌ No report named
❌ No impact
❌ "A data issue" as the cause
❌ No fix, no prevention
❌ "I re-ran it and it looked fine"
❌ Blame on the stakeholder or the data
❌ No lesson

Why this works:
- The first 90 seconds set the signal
- The rest of the story confirms or
  overturns it
- Impact, root cause, and prevention are
  the three things that can't be faked

30-DAY PRACTICE PLAN (FOR CANDIDATES):

Week 1 — Recall the failures
☐ List 5 reports that failed in the last
  2 years
☐ For each, write: the report, the
  decision, the impact
☐ Pick the 3 with the clearest impact
☐ Record yourself telling each in
  90 seconds
☐ Cut every "I don't remember" and
  "a data issue"

Week 2 — Root causes
☐ For each story, write the root cause
  in one sentence
☐ Classify it: model error, definition
  error, or code error
☐ Write how you found it: reconciliation,
  row count, test
☐ Write the fix and why it addresses the
  cause

Week 3 — Prevention and evidence
☐ For each story, write the prevention
  and what it targets
☐ Write how you knew the fix worked
☐ Write one reusable lesson or rule
☐ Add the cost of the fix or prevention

Week 4 — Mock interviews
☐ Run 2 mock interviews with a peer
☐ Ask them to challenge: "How did you
  know?" and "What was the root cause?"
☐ Re-run with stronger evidence
☐ Compare the two recordings

Signs it's working:
✅ You can name the impact without
  pausing
✅ You name a model error, not "a data
  issue"
✅ You name how you found it
✅ You name how you knew the fix worked
✅ You name a reusable lesson
✅ The interviewer asks "what else?"
  instead of "what was the report?"