analytics-troubleshooter

name	analytics-troubleshooter
description	Diagnoses analytics pipeline issues — missing data, metric mismatches, slow queries, failed dbt runs. Traces through data lineage to identify root cause and prescribes fixes.
allowed-tools	Read, Write, WebFetch
effort	high

When to activate

When stakeholders report metric discrepancies, dashboards show stale data, dbt jobs fail, or query performance degrades. Use to quickly diagnose and fix production issues without lengthy investigation.

When NOT to use

Not for preventive monitoring — use data quality tools for continuous checks. Not for architectural redesigns — use data-modeling skill if fundamental structure needs rework. Not without access to logs and query execution plans.

Troubleshooting Checklist

1. Define the symptom — What is broken (missing data, wrong numbers, slow load, failed job)?
2. Scope the impact — Which tables, metrics, or dashboards affected; how long has it been broken?
3. Check data freshness — When was each upstream table last updated; any delays?
4. Review lineage — Trace data flow from source → staging → marts → dashboard
5. Validate SQL — Run source and transformation queries independently; compare results
6. Inspect logs — Check dbt Cloud, ETL tool, database query logs for errors
7. Compare to baseline — Verify expected row counts, volumes, and timing
8. Test the fix — Run corrected query; validate output matches expectation
9. Document and deploy — Update code; re-run pipeline; confirm resolution

Root Cause Categories

Data source issue — Raw data late, incomplete, or malformed at ingestion.

Transformation logic bug — SQL or dbt logic incorrect (wrong join, bad filter, calculation error).

Dependency failure — Upstream table failed to load; cascading downstream failures.

Refresh timing — Job didn't run; ran late; partial data loaded; cache not cleared.

Performance regression — Query or data volume grew; missing index; execution plan changed.

Schema mismatch — Column renamed or dropped; new required column introduced; data type mismatch.

Troubleshooting Runbook Template

# Analytics Troubleshooting Runbook

**Issue:** [What is broken]  
**Reported:** [Date/time]  
**Severity:** [Critical / High / Medium / Low]  
**Status:** [Investigating / In Progress / Resolved]  

---

## Symptom

**What users see:** [Dashboard shows incorrect numbers / query times out / data missing]  
**When:** [Time range; frequency — sporadic or constant]  
**Impact:** [Which dashboards/metrics affected; how many users impacted]  

**Example:**
- Dashboard: Monthly Revenue Report
- Expected value (MTD May 2026): $2.4M
- Actual value: $1.8M (missing 25%)
- Last known good: May 31, 2026 at 6:00 AM
- Detected: June 1, 2026 at 9:00 AM

---

## Investigation Steps

### 1. Check Data Freshness

\`\`\`sql
SELECT 
  table_name,
  MAX(last_updated_ts) AS last_refresh,
  DATEDIFF(hour, MAX(last_updated_ts), CURRENT_TIMESTAMP()) AS hours_since_refresh
FROM metadata_tables
WHERE table_name IN ('fct_orders', 'fact_transactions', 'dim_customers')
GROUP BY table_name
ORDER BY hours_since_refresh DESC;
\`\`\`

**Result:**
| Table | Last Refresh | Hours Since |
|-------|--------------|-------------|
| fct_orders | 2026-06-01 06:00 AM | 27h ⚠ |
| fact_transactions | 2026-06-01 06:00 AM | 27h ⚠ |
| dim_customers | 2026-06-01 06:00 AM | 27h ⚠ |

**Finding:** All tables stale by 27 hours (expected: <24h). Likely cause: failed refresh job.

### 2. Check dbt Cloud Logs

**dbt Cloud run history:**
- Last run: June 1, 2026 at 5:58 AM — Status: ✓ Success (took 45 min)
- Current run (scheduled June 2, 6 AM): Still running (now 10 AM, +4h overdue)

 See query timeout on  model

\\`
[10:15 AM] Running model 'marts/martmetrics'
[10:15 AM] SELECT ... FROM fctdate
[10:42 AM] Query timeout (>1800s). Killed.
[10:42 AM] FAILURE
\\`

 dbt job hung on martmetrics; query timeout. Missing data in mart = no downstream refresh.



\\`sql
-- marts/martmetrics
SELECT 
  DATE(orderdate,
  COUNT() AS orderamount) AS dailyorders
WHERE orderDATE - INTERVAL 2 YEARS  -- Limit to 2-year lookback
GROUP BY orderdate DESC;
\\`


 Removed unnecessary JOIN to dimdate for faster filtering
CREATE CLUSTERED INDEX idxordersorders (orderorders by orderorders; -- 180M rows scanned
-- Run time: timeout (>30 min)
\\`


\\`sql
SELECT COUNT(

Example

See Troubleshooting Runbook Template above.

---