Case 4 — Incident Response: Data Quality SLA Failure Investigation

# 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 ```yaml 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 | Criteria | Points | Passing Standard | |---|:---:|---| | **1. Root-Cause Diagnosis & Lineage Trace** | 25 pts | Traces data lineage from raw Stripe tables through staging to marts; identifies join drop-off caused by schema drift. | | **2. Tiered Test Architecture (`warn` vs `error`)** | 25 pts | Categorizes tests appropriately: critical primary key / integrity assertions block pipelines; volume drift triggers warnings. | | **3. Source Freshness Configuration** | 25 pts | Defines declarative source freshness thresholds in dbt matching business SLAs; prevents stale data from compiling downstream. | | **4. Preventative Post-Mortem & Data Contract** | 25 pts | Produces an executive RCA document detailing incident timeline, technical root cause, and upstream webhook validation contract. |