RUNBOOK.md

RUNBOOK – Compliance Reporting – Big-4 Audit & Consulting Firm (Azure)

Scope: Day-to-day operations, incident handling, replay/backfill, and ML model rollback
Pipelines: Streaming Pipeline (ETL) via Event Hubs + Synapse Streaming; Batch Pipeline (ELT) via ADF + Synapse + Snowflake

---

1. Daily operator checklist

1. Check pipeline status

   • Verify last 24 hours of ADF pipeline runs completed successfully.
   • Verify Synapse streaming job is running and up-to-date.
   • Confirm Snowflake tasks (if any) are healthy.

2. Validate SLOs

   • p95 latency from ingestion to Snowflake risk_scores < 90 seconds.
   • Daily batch window finished before 06:00 UTC.
   • DQ pass rate ≥ 97% on critical checks, DLQ rate < 0.5%.

3. Spot-check dashboards

   • Open key Power BI reports.
   • Compare counts to yesterday and to queries in qc_examples.sql.

If any of these fail, follow the incident playbooks below.

---

2. Incident playbook – Streaming Pipeline (ETL)

2.1 Symptoms

• No new rows in risk_scores for > 5 minutes.
• Synapse streaming job not running or in failed state.
• Latency alerts firing from observability stack.

2.2 Triage

1. Check Azure Portal → Synapse → Monitoring for job health.
2. Check Event Hubs metrics:
   • Incoming messages
   • Consumer lag / checkpoint age
3. Query Snowflake:

SELECT MAX(scored_at) AS last_score_ts
FROM analytics.risk_scores;

2.3 Fix

• If Synapse streaming job is stopped:
  • Restart the job with the same configuration.
• If Event Hubs lag is high:
  • Scale out streaming job (more partitions or higher parallelism).
• If schema validation fails:
  • Inspect DLQ / error table in Synapse.
  • Fix upstream schema or update transactions.schema.json and downstream mapping with an ADR.

2.4 Replay (streaming)

Streaming replay is handled by Event Hubs checkpoints:

1. Stop the streaming job.
2. Adjust starting position to a specific offset/time (for the incident window).
3. Restart the job and monitor lag until it returns to normal.

Document the incident in the team’s incident tracker.

---

3. Incident playbook – Batch Pipeline (ELT)

3.1 Symptoms

• ADF pipeline run failed or stuck.
• Snowflake compliance marts not refreshed.
• Batch SLO (T+1 06:00 UTC) breached.

3.2 Triage

1. Open ADF → Monitor → Pipeline Runs, filter by pipeline name and date.
2. Identify failing activity:
   • Ingestion to ADLS
   • Load to Synapse
   • Load to Snowflake
3. Validate raw data landed for the execution date:

-- Example Synapse raw zone check
SELECT load_date, COUNT(*) AS rows_loaded
FROM raw.transactions
WHERE load_date = 'YYYY-MM-DD'
GROUP BY load_date;

3.3 Fix

• Ingestion failure: re-run only ingestion activities for the failing slice/date.
• Transform failure in Synapse: fix code/config, then re-run transform activities.
• Snowflake load failure: inspect COPY INTO errors; fix bad records or file paths; re-run Snowflake load step.

3.4 Replay – specific date range

To replay a specific date (or range):

1. Use ADF pipeline parameters for start_date and end_date.
2. Trigger a manual pipeline run with the desired window.
3. Confirm:
   • Raw tables in Synapse have the expected distinct counts.
   • Compliance marts reflect new data and pass qc checks.

---

4. Backfill strategy (historical)

Backfills are needed when:

• Onboarding a new source system.
• Fixing a historical bug in logic.
• Bringing up a new environment (e.g., UAT → PROD).

4.1 Backfill steps

1. Plan

   • Define date range (e.g., last 18 months).
   • Estimate volume and required compute (Synapse DWU, Snowflake warehouse size).

2. Isolate

   • Use a separate backfill pipeline in ADF with:
     • Throttled concurrency.
     • Distinct tags/labels for monitoring.

3. Execute

   • Run backfill in chronological order.
   • Monitor loads with qc queries from qc_examples.sql.

4. Validate

   • Compare record counts and key metrics vs source system extracts.
   • Ensure DQ checks pass at same or better rate than regular runs.

5. Close

   • Turn off backfill pipeline.
   • Write a short backfill report (dates, counts, issues) in the team’s wiki.

---

5. ML model deployment & rollback

5.1 Where models live

• Models are registered in Azure ML with:
  • model_name
  • model_version
  • Metadata: training dataset hash, metrics, author, approval status.
• A Snowflake config table (e.g. config.active_model) stores the active model_version.

5.2 Deploying a new model version

1. Pre-checks

   • Confirm:
     • Offline metrics meet acceptance criteria.
     • Fairness and bias checks were reviewed.
   • Create/update ADR describing the change.

2. Update config

UPDATE config.active_model
SET model_version = 'vX.Y.Z',
    changed_by    = 'your_name',
    changed_at    = CURRENT_TIMESTAMP();

3. Deploy scoring pipeline

   • Update Synapse / Azure ML pipeline to use model_version = 'vX.Y.Z'.
   • Run a smoke test on a small batch and verify:
     • Latency
     • Score distribution
     • Field mapping (no missing features).

4. Monitor

   • For the first 24 hours:
     • Watch drift dashboards.
     • Confirm no unexpected spikes in risk score bands.

5.3 Rolling back a model

If the new version misbehaves (high false positives, drift, or technical failure):

1. Switch active model

   • Set model_version in config.active_model back to prior version (e.g., vA.B.C).

2. Redeploy scoring pipeline

   • Redeploy / reconfigure Azure ML endpoint or Synapse job to use the prior version.

3. Optional re-score

   • For the affected time window:
     • Re-run scoring with the old model version.
     • Overwrite or append corrected rows in risk_scores.

4. Document

   • Log the incident and rollback details.
   • Update ADRs if the rollback is permanent.

---

6. Contact & ownership

• Code & pipeline ownership: see CODEOWNERS
• Security questions: contact the security focal listed in SECURITY.md
• Data stewardship: data stewards for each domain are documented in docs/01-context.md