Case 4 — Incident Response: Data Quality SLA Failure Investigation

Executive Context & Business Prompt

At 8:15 AM on Monday morning, you receive an urgent Slack escalation in #data-incidents:

CFO: "Why does the Executive Financial Dashboard show weekend revenue as $42,000 when our average weekend is $60,000? We have an investor board meeting in 2 hours. Is this real business churn or broken data?"

You check the Airflow DAG: all tasks completed with exit code 0 (SUCCESS). No infrastructure errors occurred. However, querying the underlying marts.fct_daily_revenue confirms a 30% drop in recorded sales.

Head of Analytics Prompt:

"You have 45 minutes to diagnose what broke, trace the lineage back to source systems, deploy a code fix, and establish automated data quality testing so silent revenue drops never reach executives again."

Explicit Portfolio Deliverables

1. Incident Post-Mortem & Root-Cause Analysis (RCA_INCIDENT_20260916.md):
   • Document the failure mechanism: A payment provider API change introduced duplicate webhook authorizations with null settlement IDs, causing downstream inner joins to drop valid settled records.
2. Tiered dbt Test Suite (schema.yml):
   • Implement tiered severity: severity: error on primary keys and revenue non-null constraints (halts pipeline).
   • Implement severity: warn on day-over-day volume deviation thresholds.
3. Source Freshness & SLA Alerting Configuration (dbt_project.yml):
   • Configures warn_after: {count: 12, period: hour} and error_after: {count: 24, period: hour} on Stripe raw ingestion sources.

Starter Code Scaffold

version: 2

sources:
  - name: stripe
    database: raw_ingestion
    schema: stripe_webhooks
    tables:
      - name: charges
        # TODO: Add source freshness rules
        # TODO: Add schema assertions
models:
  - name: fct_daily_revenue
    description: "Daily settled revenue mart for financial reporting"
    columns:
      - name: revenue_date
        tests:
          - not_null
          - unique
      - name: net_settled_amount
        tests:
          - not_null
          # TODO: Add custom dbt test asserting no negative revenue

100-Point Grading Rubric