Lab Type

Incident Response Lab

Difficulty

Expert Hands-On

Reward

900 XP · 🚨 Data Reliability Engineer

Core Stack

Delta Lake · Time Travel · PySpark

Incident Severity

P1 - Critical (CFO Board Impact)

🚨 Hands-On Reliability Lab

DEV-014

Production Data Pipeline Incident Recovery

It is 06:30 AM. An automated PagerDuty P1 incident fires: the daily executive sales pipeline crashed mid-run due to corrupt vendor data. The CFO dashboard shows $0.00 revenue for yesterday, and the 09:00 AM board meeting is in 2.5 hours. Step in as the on-call Data Reliability Engineer, contain the blast radius, restore the Delta tables using time-travel, quarantine corrupt data, backfill the partition, and author a Post-Incident Review.

Triage P1 Alert

Contain & Freeze

Delta Time Travel

Quarantine & Backfill

Validate & Publish PIR

Mission & Operational Context

Understand the incident timeline, business impact, and reliability principles.

The Production Emergency

At 02:24 UTC, the nightly batch ingestion crashed while processing files from third-party Vendor B. Instead of a numeric decimal, line 412 of the CSV contained string value "N/A" in the unit_price column. Because the ingestion script attempted dynamic schema merging, it corrupted the Silver table schema and aborted before writing the Gold partition.

Now, the CFO executive revenue dashboard is missing yesterday's numbers. You must act methodically: do not panic, do not run destructive drops, and follow the site reliability engineering SOP to contain the failure, roll back to version 42, quarantine the corrupted row, backfill the missing partition, and restore full service before 09:00 AM.

Delta Time Travel Power

Never drop tables on failure. Use RESTORE TABLE TO VERSION AS OF to instantly revert bad commits.

Atomic Reprocessing

Use partition overwrites (replaceWhere) so that backfilling never creates duplicate transaction rows.

Zero Data Loss Quarantine

Isolate the 1 corrupt row in quarantine storage while safely loading the remaining 12,099 valid records.

Post-Incident Review (PIR)

Every critical outage must conclude with a blameless PIR detailing root cause and preventive safeguards.

Starter Materials & Incident Artifacts

Inspect the PagerDuty incident logs, failed batch metadata, and emergency runbook SOP.

📄 pagerduty_alert_INC8402.log

[2024-03-14 06:30:12 UTC] CRITICAL PAGERDUTY ALERT #INC-8402
Service: Production Sales Lakehouse Pipeline
Severity: P1 - CRITICAL
Title: Midnight Gold Aggregation Batch Failed - Incomplete Table State
-----------------------------------------------------------------------------------------
Traceback (most recent call last):
  File "/databricks/jobs/daily_gold_aggregator.py", line 84, in execute_daily_batch
    df = spark.read.format("delta").load("/lakehouse/silver/sales_transactions")
  File "/databricks/spark/python/pyspark/sql/dataframe.py", line 112, in write
    .save("/lakehouse/gold/executive_revenue_mart")
PySparkException: [DELTA_FAILED_TO_MERGE_FIELDS] Failed to merge fields 'unit_price' and 'unit_price'.
Failed to merge incompatible data types: DecimalType(10,2) and StringType
Source File: /raw_landing/sales_20240314_vendorB.csv, Line 412
Corrupt Payload: "TXN-88492,C992,P104,STORE_MUM_01,2,N/A,0.00,2024-03-14T23:15:00Z"
-----------------------------------------------------------------------------------------
BLAST RADIUS:
- Silver Sales: Partially written before abort (corrupted schema evolution attempt)
- Gold Mart: Missing entire 2024-03-14 partition
- CFO Executive Dashboard: Currently displaying $0.00 for yesterday
STATUS: Pipeline halted. On-call Data Reliability Engineer paged.

🚨 Incident Insight: At 06:30 AM, vendor file introduced 'N/A' into a DecimalType column, halting Gold generation and leaving Silver in a dirty state.

Hands-On Incident Recovery Workflow

Execute the 5 disaster recovery phases according to SRE protocols.

Phase 1: Blast Radius Assessment & Containment

Halt any downstream dependent jobs to prevent spreading un-reconciled data.

▸Inspect Delta history: DESCRIBE HISTORY delta.`/lakehouse/silver/sales_transactions`.

▸Verify corrupted commit version 43 has incomplete metadata.

▸Notify stakeholders on Slack incident channel #data-ops-incidents.

Phase 2: Delta Lake Time-Travel Restoration

Restore the Silver table to stable version 42.

▸Execute: RESTORE TABLE delta.`/lakehouse/silver/sales_transactions` TO VERSION AS OF 42;

▸Verify the table schema reverts cleanly to DecimalType(10,2) and uncommitted files are bypassed.

Phase 3: Quarantine Corrupted Record

Implement safe parsing to intercept 'N/A' strings before they hit Silver.

▸Use try_cast(col('unit_price') as 'decimal(10,2)') to detect unparseable values.

▸Route TXN-88492 to /lakehouse/quarantine/sales_bad_records with reject_reason='CORRUPT_DECIMAL_NA'.

▸Ingest the remaining 12,099 clean records from Vendor B into Silver.

Phase 4: Atomic Gold Partition Backfill

Reprocess and write partition 2024-03-14 into Gold without duplicating data.

▸Use Delta Lake replaceWhere: df.write.format('delta').mode('overwrite').option('replaceWhere', "txn_date = '2024-03-14'").save(...).

▸Assert total clean transaction count for 2024-03-14 equals exactly 30,499 (18,400 from A + 12,099 from B).

Phase 5: Snowflake Re-Sync & Post-Incident Review

Sync restored Gold partition to Snowflake and write the formal PIR.

▸Execute MERGE into Snowflake executive sales table.

▸Confirm CFO Tableau dashboard displays verified revenue.

▸Author blameless Post-Incident Review document detailing root cause, resolution timeline, and preventive actions.

Target Recovery Metrics & Validation

Prove that the backfill successfully recovered all valid records.

Restored Partition Targets (2024-03-14)

Restored Silver Rows

30,499 Rows

18,400 (A) + 12,099 (B)

Quarantined Rows

1 Row

TXN-88492 with 'N/A' price

Gold Table Version

Version 20

Partition 2024-03-14 active

CFO Dashboard Status

ONLINE

Revenue verified at 07:45 AM

Validation Checks & Incident Sign-Off Queries

Run these verification queries to certify that the recovery was clean and duplicate-free.

SQL Test 1 — Confirm Zero Duplicate Transactions in Restored Partition

SELECT txn_id, COUNT(*) AS cnt 
FROM delta.`/lakehouse/silver/sales_transactions`
WHERE txn_date = '2024-03-14'
GROUP BY txn_id 
HAVING COUNT(*) > 1;

SQL Test 2 — Confirm Corrupt Record is Safely Quarantined

SELECT txn_id, raw_unit_price, reject_reason, quarantined_at 
FROM delta.`/lakehouse/quarantine/sales_bad_records`
WHERE txn_id = 'TXN-88492';

Evidence To Submit & Post-Incident Review (PIR)

Compile your recovery evidence and submit the formal incident report.

Your submission must include the recovery script and a complete Post-Incident Review document:

☑

1. Recovery Execution Script (disaster_recovery.py)

Script executing Delta RESTORE TABLE, safe quarantine try_cast, and atomic replaceWhere backfill.

☑

2. Delta History Proof

Output of DESCRIBE HISTORY showing stable rollback to version 42 and subsequent clean commit.

☑

3. Quarantine Verification Output

Query result proving TXN-88492 was routed to quarantine with reject_reason='CORRUPT_DECIMAL_NA'.

☑

4. Restored Gold Partition Row Count

Screenshot or log showing 30,499 clean transactions for 2024-03-14 in Gold.

☑

5. Formal Post-Incident Review (PIR) Report

Structured document covering: Summary, Root Cause, Timeline, Recovery Actions, and 3 Preventive Safeguards.

Common Mistakes in Incident Response

Critical errors to avoid during production emergencies.

⚠️ Dropping and Rebuilding the Table from Scratch

Panicking and running DROP TABLE deletes transaction history, breaks concurrent readers, and forces hours of unnecessary full historical backfills. Always use Delta time travel.

⚠️ Overwriting the Entire Table Instead of the Affected Partition

Running a full table overwrite mode('overwrite') wipes other historical partitions if dynamic partition overwrite is not configured properly. Use explicit replaceWhere.

⚠️ Discarding Vendor Files Without Logging Rejections

Skipping bad vendor files without creating a quarantine record prevents finance from issuing vendor clawbacks or requesting corrected files.

Stretch / Bonus Objectives

Build permanent resilience mechanisms.

⭐ Automated Pre-Ingestion File Gatekeeper

Write a lightweight pre-flight schema validator that checks CSV files for invalid headers or data types before initiating Spark cluster jobs.

⭐ PagerDuty Webhook Integration

Build an automated webhook handler that posts recovery status updates to Slack and closes the PagerDuty incident automatically upon successful reconciliation.

Reliability Certification

Complete DEV-014 to Earn 900 XP & Data Reliability Engineer Badge

Contain the incident, execute Delta time-travel recovery, quarantine corrupt vendor records, backfill the partition, and author your Post-Incident Review.

🚨

900 XP

Data Reliability Engineer