Case 3 — Data Quality Failure Investigation

# Case 3 — Data Quality Failure Investigation ## The brief The CEO sees today's revenue as 30% lower than expected. They ping you. You have one hour to: identify what's wrong, fix it, and make sure it can't happen again. ## The setup We provide a broken pipeline with realistic bugs. Source data is fine; somewhere in the transformation, value is lost. Pipeline (provided): ``` raw.stripe_payments (correct) → stg_payments (transformation #1) → fct_revenue_daily (transformation #2) → Looker dashboard ``` Today's number on the dashboard: $42,000. Expected: ~$60,000. ## Possible bugs (one or more present) - A filter dropped some payments incorrectly. - A join lost rows (inner where outer needed). - A type cast silently failed (NULL where number expected). - An aggregation has wrong grouping (double-counted or under-counted). - Timezone issue: cutoff wrong. - Currency conversion missing. - Duplicate detection over-aggressive. ## The task ### Step 1: Investigate the layers (20 min) Query raw, staging, mart in sequence: - raw.stripe_payments: sum total for today. Expected? - stg_payments: sum total for today. Matches raw? - fct_revenue_daily: sum total for today. Matches staging? Find where the number drops. ### Step 2: Diagnose the specific bug (15 min) In the layer where the loss happens, identify what's being dropped/wrong: - Compare row counts. - Sample rows from raw that don't appear in next layer. - Compare aggregate breakdowns (by status, country, etc.). ### Step 3: Fix the bug (15 min) Patch the SQL/dbt model. Re-run; verify the dashboard shows ~$60K. ### Step 4: Add tests to prevent recurrence (20 min) Add dbt tests that would catch this: - Row count assertion (count in stg_payments == count in raw.stripe_payments for the day). - Sum invariant test (sum in fct_revenue_daily == sum in stg_payments * conversion_rate). - Specific not_null, accepted_values for the fields involved. Document in the PR: the bug, how you found it, what tests prevent it. ## Deliverables - Written investigation log: each query you ran, what it showed, what you concluded. - Fixed SQL/dbt code. - New dbt tests in the PR. - A `RUNBOOK.md`: "if dashboard shows unexpected drop, here's how to investigate". ## What you'll learn This is the most common DE task in production: data is wrong, find why, fix, prevent. The skill: methodical layer-by-layer narrowing instead of guessing. - Investigate from raw forward; don't start from the dashboard. - Compare counts AND sums (a bug that loses 1 row vs 100 rows looks different). - Always add a regression test. - Document the runbook so the next investigation is faster.