# Common Root Cause Catalog

After gathering evidence, match your findings against these known root cause patterns. Each pattern has a signature (what the evidence looks like) and a typical fix.

## ETL & Pipeline Causes

### Pipeline scheduling failure
**Signature:** Table freshness delayed. No write queries in the expected window. ETL platform shows task failure or no task execution.
**Fix:** Check pipeline scheduler (cron, Airflow scheduler, dbt Cloud). Restart the job. Check for permission changes on the service account.

### Upstream cascade
**Signature:** Table is stale AND at least one upstream table is also stale. The upstream staleness started first.
**Fix:** Fix the upstream table first — this table will refresh automatically once its input is fresh.

### Resource contention / timeout
**Signature:** Queries are running but taking much longer than usual. Pipeline timeouts. Warehouse queue depth is high.
**Fix:** Scale the warehouse, optimize the query, or schedule during off-peak hours.

### Permission / credential change
**Signature:** Queries fail with "access denied" or "permission denied" errors. Worked fine before a specific date.
**Fix:** Check service account permissions. Re-grant access to the source data.

---

## Query & Code Causes

### Query regression
**Signature:** Query text changed around the time of the incident. New SQL produces different output (more/fewer rows, different values, nulls).
**Fix:** Review the query change. Revert or fix the SQL. Compare old vs new output.

### JOIN cardinality change
**Signature:** Row count changed dramatically. Query change shows JOIN modification (INNER ↔ LEFT, new JOIN added, JOIN key changed).
**Fix:** Review the JOIN logic. Check for fanout (1-to-many producing duplicates) or dropped rows (INNER JOIN filtering more than expected).

### Filter/WHERE clause change
**Signature:** Row count dropped or spiked. Query change shows WHERE clause modification.
**Fix:** Review the filter logic. Check if the filter is too restrictive or too permissive.

---

## Data Quality Causes

### Source data quality issue
**Signature:** Upstream table has unexpected values. Field lineage traces the bad data to a specific upstream column. The upstream column has new NULL values, outliers, or unexpected categories.
**Fix:** Fix the upstream data. Add data quality checks (validation monitors) at the source.

### Late-arriving data / backfill
**Signature:** Volume spike. New rows have old timestamps (data arrived late). No query change.
**Fix:** This may be intentional (backfill). Verify with the team. Adjust monitoring windows if needed.

### Schema drift
**Signature:** Source system changed its schema (added/removed columns, changed types). Downstream ETL failed or produced wrong results.
**Fix:** Update the ETL to handle the new schema. Add schema change monitors on the source.

---

## Infrastructure Causes

### Warehouse suspension / auto-suspend
**Signature:** Queries queued for a long time, then ran. Freshness delay matches the warehouse suspension period.
**Fix:** Adjust warehouse auto-suspend settings, or schedule a warm-up query before the critical pipeline.

### Cluster/compute failure
**Signature:** Databricks cluster failed to start, Airflow worker crashed, dbt Cloud runner timed out.
**Fix:** Check infrastructure logs. Scale the compute. Retry the job.

### Network / connectivity issue
**Signature:** Intermittent failures across multiple tables. Error messages mention timeouts, connection refused, or DNS resolution.
**Fix:** Check network connectivity. Review cloud provider status page.

---

## How to use this catalog

1. After gathering evidence in Steps 1-6 of the main workflow, review the signatures above.
2. Match your evidence to the closest pattern.
3. Present the root cause with the specific evidence that matched.
4. Suggest the fix from the catalog, adapted to the user's specific situation.
5. If no pattern matches, say so — novel root causes do exist. Present the evidence and let the user draw conclusions.

# Data Exploration Patterns

Use this reference when a database MCP server is available (Snowflake, BigQuery, Redshift, Databricks) for direct SQL investigation. These patterns are modeled after Monte Carlo's internal data exploration agent.

**Important:** Always use fully qualified table names (`database.schema.table`). The session may not have a default database or schema.

## Dialect awareness

Adjust SQL syntax based on the warehouse type. Common differences:

| Pattern | Snowflake | BigQuery | Redshift |
|---------|-----------|----------|----------|
| Date subtraction | `DATEADD('day', -7, CURRENT_TIMESTAMP())` | `DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)` | `DATEADD(day, -7, GETDATE())` |
| Timestamp truncation | `DATE_TRUNC('hour', ts)` | `TIMESTAMP_TRUNC(ts, HOUR)` | `DATE_TRUNC('hour', ts)` |
| String concatenation | `col1 || col2` | `CONCAT(col1, col2)` | `col1 || col2` |
| Approximate count | `APPROX_COUNT_DISTINCT(col)` | `APPROX_COUNT_DISTINCT(col)` | `SELECT COUNT(DISTINCT col)` |

If you're unsure of the dialect, try Snowflake syntax first — error messages will indicate the correct dialect.

## Investigation queries

### Sample recent rows

```sql
SELECT * FROM database.schema.table
ORDER BY timestamp_col DESC
LIMIT 20
```

Gives a quick feel for what the data looks like right now.

### Row count over time

```sql
SELECT DATE_TRUNC('hour', timestamp_col) AS period,
       COUNT(*) AS row_count
FROM database.schema.table
WHERE timestamp_col >= DATEADD('day', -7, CURRENT_TIMESTAMP())
GROUP BY 1 ORDER BY 1
```

Reveals when volume changed — look for sudden spikes or drops.

### Null rate analysis

```sql
SELECT DATE_TRUNC('day', timestamp_col) AS day,
       COUNT(*) AS total_rows,
       COUNT(suspect_column) AS non_null,
       ROUND(1.0 - COUNT(suspect_column)::FLOAT / NULLIF(COUNT(*), 0), 4) AS null_rate
FROM database.schema.table
WHERE timestamp_col >= DATEADD('day', -14, CURRENT_TIMESTAMP())
GROUP BY 1 ORDER BY 1
```

Shows whether null rate changed at a specific point in time.

### Value distribution

```sql
SELECT suspect_column, COUNT(*) AS cnt
FROM database.schema.table
WHERE timestamp_col >= DATEADD('day', -1, CURRENT_TIMESTAMP())
GROUP BY 1 ORDER BY 2 DESC
LIMIT 30
```

Reveals if unexpected values appeared or common values disappeared.

### Before vs after comparison

```sql
-- "Before" window (known good period)
SELECT 'before' AS period,
       COUNT(*) AS rows,
       COUNT(DISTINCT key_col) AS unique_keys,
       AVG(metric_col) AS avg_metric,
       COUNT(suspect_col) AS non_null_count
FROM database.schema.table
WHERE timestamp_col BETWEEN 'good_start' AND 'good_end'

UNION ALL

-- "After" window (when issue started)
SELECT 'after' AS period,
       COUNT(*) AS rows,
       COUNT(DISTINCT key_col) AS unique_keys,
       AVG(metric_col) AS avg_metric,
       COUNT(suspect_col) AS non_null_count
FROM database.schema.table
WHERE timestamp_col BETWEEN 'bad_start' AND 'bad_end'
```

Compares key metrics between a known-good period and the anomaly period.

### Upstream correlation

When field lineage points to an upstream source, check what upstream values correlate with the anomaly:

```sql
SELECT upstream.category_field,
       COUNT(*) AS affected_rows,
       AVG(downstream.anomalous_field) AS avg_value,
       SUM(CASE WHEN downstream.anomalous_field IS NULL THEN 1 ELSE 0 END) AS null_count
FROM database.schema.downstream_table downstream
JOIN database.schema.upstream_table upstream
  ON downstream.foreign_key = upstream.primary_key
WHERE downstream.timestamp_col >= 'anomaly_start_time'
GROUP BY 1 ORDER BY 2 DESC
LIMIT 20
```

This reveals which upstream segments are driving the anomaly.

### Duplicate detection

```sql
SELECT key_col1, key_col2, COUNT(*) AS cnt
FROM database.schema.table
WHERE timestamp_col >= DATEADD('day', -1, CURRENT_TIMESTAMP())
GROUP BY 1, 2
HAVING COUNT(*) > 1
ORDER BY cnt DESC
LIMIT 20
```

Checks if deduplication logic broke, introducing duplicates.

### Missing expected rows

```sql
-- Find keys present yesterday but missing today
SELECT yesterday.key_col
FROM (SELECT DISTINCT key_col FROM database.schema.table
      WHERE DATE(timestamp_col) = CURRENT_DATE - 1) yesterday
LEFT JOIN (SELECT DISTINCT key_col FROM database.schema.table
           WHERE DATE(timestamp_col) = CURRENT_DATE) today
  ON yesterday.key_col = today.key_col
WHERE today.key_col IS NULL
LIMIT 20
```

## Rules for data exploration

- **Always LIMIT queries** — never run unbounded SELECTs. Start with LIMIT 20, increase only if needed.
- **Use time filters** — always scope to the relevant time window around the incident.
- **Start broad, then narrow** — sample rows first, then targeted aggregations.
- **Compare before vs after** — the most powerful investigation pattern.
- **Follow the data upstream** — if this table looks wrong, check the source it reads from.

# ETL Failure Investigation Playbook

Use this when an Airflow DAG, dbt model, or Databricks job failed.

## Investigation steps

### 1. Identify the failure

Based on the alert or user description, determine which platform:

**Airflow:**
- Call `get_etl_jobs` with `platform="airflow"` and the affected table MCONs to find which DAGs/tasks write to these tables
- Call `get_etl_issues` with `platform="airflow"` and a time range — look for:
  - Task failure error messages
  - Retry counts (high retries = flaky task)
  - SLA misses
  - Upstream task failures that blocked downstream tasks

**dbt:**
- Call `get_etl_jobs` with `platform="dbt"` and the affected table MCONs to find which dbt jobs write to these tables
- Call `get_etl_issues` with `platform="dbt"` — look for:
  - Compilation errors (bad SQL syntax, missing refs)
  - Test failures (data quality assertions)
  - Timeout errors
  - Dependency failures (upstream model failed)

**Databricks:**
- Call `get_etl_jobs` with `platform="databricks"` and the affected table MCONs to find which Databricks jobs write to these tables
- Call `get_etl_issues` with `platform="databricks"` — look for:
  - Notebook execution errors
  - Cluster startup failures
  - Out of memory errors
  - Permission denied errors

### 2. Check what tables are affected

Call `get_asset_lineage(mcons=[table_mcon], direction="DOWNSTREAM")`:
- Which downstream tables couldn't refresh because this pipeline failed?
- How many consumers are impacted?

### 3. Check for recent changes

Call `get_change_timeline` — was there a code change around the failure time?
- Query text modifications right before the failure → code regression
- Volume spike right before the failure → data volume overwhelmed the pipeline

### 4. Check for query-level issues

Call `get_query_rca` with the affected table MCONs:
- **Failed** patterns: what errors are the queries hitting?
- **Futile** patterns: are queries running but producing nothing?
- Look at error messages for clues (timeout, permission, missing object)

### 5. Check job runtime trends and current status

Call `get_jobs_performance` to see runtime stats, failure rates, and current status:
- Gradual slowdown → growing data volume or inefficient query
- Sudden spike → query regression or resource contention

## Common root causes

- **Code deployment** — new dbt model or query has a bug
- **Data volume spike** — source data grew faster than the pipeline can process
- **Permission change** — service account lost access
- **Infrastructure** — cluster sizing, warehouse suspension, network issues
- **Dependency failure** — upstream pipeline failed, cascading downstream
- **Schema mismatch** — upstream schema changed, breaking the ETL query

# Field Anomaly Investigation Playbook

Use this when a field-level metric drifted (null rate spike, mean shift, distribution change).

## Investigation steps

### 1. Understand the anomaly

From the alert details, identify:
- Which field/column is affected?
- What metric changed? (null rate, mean, max, min, uniqueness, etc.)
- When did the change occur?
- What was the expected vs actual value?

### 2. Trace field lineage

Call `get_field_lineage` to find where this field's data comes from:
- Which upstream table and column feeds this field?
- Is the upstream field also anomalous?
- Walk the field lineage chain upstream until you find the source of the bad data

### 3. Check for correlated anomalies

Call `get_alerts` with a time range around the incident:
- Are there other alerts on the same table at the same time? (volume, freshness)
- Are there alerts on upstream tables?
- Multiple correlated anomalies often point to a single root cause

### 4. Check for query changes

Call `get_query_changes` — did the ETL query modify how this field is computed?
- Changed CASE WHEN logic → different values
- Changed COALESCE or NULL handling → null rate changes
- Changed aggregation → mean/sum shifts
- Changed type casting → precision changes

### 5. Profile the data (if DB connector available)

Run targeted queries to understand the field's behavior:

```sql
-- Null rate over time
SELECT DATE_TRUNC('day', timestamp_col) AS day,
       COUNT(*) AS total,
       COUNT(field_name) AS non_null,
       1.0 - COUNT(field_name) / COUNT(*) AS null_rate
FROM table
WHERE timestamp_col >= DATEADD('day', -14, CURRENT_TIMESTAMP())
GROUP BY 1 ORDER BY 1

-- Value distribution shift
SELECT field_name, COUNT(*) AS cnt
FROM table
WHERE timestamp_col >= DATEADD('day', -1, CURRENT_TIMESTAMP())
GROUP BY 1 ORDER BY 2 DESC LIMIT 20

-- Check what upstream values correlate with the anomaly
SELECT upstream_table.key_field,
       COUNT(*) AS affected_rows,
       AVG(this_table.anomalous_field) AS avg_value
FROM this_table
JOIN upstream_table ON this_table.fk = upstream_table.pk
WHERE this_table.timestamp_col >= 'anomaly_start_time'
GROUP BY 1 ORDER BY 2 DESC
```

See `references/data-exploration.md` for more patterns.

### 6. Check upstream data quality

For the upstream table/field identified in Step 2:
- Call `get_table_freshness` — is the upstream data fresh?
- Call `get_table_size_history` — did upstream volume change?
- If DB connector available, profile the upstream field directly

### 7. Check column correlations (if DB connector available)

Identify what other columns are associated with the "bad" rows vs normal rows. This is one of the most powerful investigation techniques:

```sql
-- Compare dimension values between anomalous and normal rows
-- Replace anomalous_field condition with the actual anomaly (e.g., IS NULL, > threshold)
SELECT other_column,
       COUNT(*) AS total_rows,
       SUM(CASE WHEN anomalous_field IS NULL THEN 1 ELSE 0 END) AS bad_rows,
       ROUND(SUM(CASE WHEN anomalous_field IS NULL THEN 1 ELSE 0 END)::FLOAT
             / NULLIF(COUNT(*), 0), 3) AS bad_rate
FROM database.schema.table
WHERE timestamp_col >= 'anomaly_start_time'
GROUP BY 1
HAVING COUNT(*) > 10
ORDER BY bad_rate DESC
LIMIT 20
```

If one dimension value has a much higher "bad rate" than others, it's likely the root cause — e.g., "all rows from source_system='legacy_api' have NULL revenue, but rows from other sources are fine."

Try this across multiple columns (category fields, source identifiers, date partitions) to narrow down the pattern.

## Common root causes

- **Upstream data quality issue** — bad data in source propagated downstream
- **ETL logic change** — CASE/COALESCE/type handling modified
- **New data source** — a new upstream source introduced unexpected values
- **Schema change** — column type changed, causing implicit conversions
- **Backfill** — historical data reprocessed with different logic
- **Null propagation** — upstream NULL values cascading through JOINs

# Freshness Investigation Playbook

Use this when a table hasn't updated on its expected schedule.

## Investigation steps

### 1. Confirm the freshness delay

Call `get_table_freshness` with the table's `full_table_id` and `resource_id`. Also call `get_table` with the same `full_table_id` — its response includes the table's `mcon`, which the lineage and query tools below need. Check:
- When was the last successful update?
- What's the normal update cadence? (hourly, daily, etc.)
- How long has the delay been?

### 2. Check the ETL pipeline

The table is populated by an ETL pipeline. Check if the pipeline failed:

**Airflow:**
- Call `get_etl_jobs` with `platform="airflow"` to find which DAGs/tasks write to this table
- Call `get_etl_issues` with `platform="airflow"` and a time range — look for task failures, retries, or SLA misses

**dbt:**
- Call `get_etl_jobs` with `platform="dbt"` to find which dbt jobs/models produce this table
- Call `get_etl_issues` with `platform="dbt"` — look for compilation errors, test failures, or timeouts

**Databricks:**
- Call `get_etl_jobs` with `platform="databricks"` to find relevant jobs
- Call `get_etl_issues` with `platform="databricks"` — look for notebook failures, cluster issues

### 3. Check the write queries

Call `get_queries_for_table(mcon=table_mcon, query_type="destination")` to see recent write queries:
- Did write queries stop running entirely? → pipeline scheduling issue
- Did write queries run but with errors? → data or permission issue
- Did write queries run successfully but produce no rows? → upstream data issue

### 4. Check upstream freshness

Call `get_asset_lineage(mcons=[table_mcon], direction="UPSTREAM")` to find upstream tables, then:
- Call `get_table_freshness` on each upstream table
- If an upstream table is also stale, the issue is propagating from there
- Recurse upstream until you find the root source of the delay

### 5. Check ETL job performance

Call `get_jobs_performance` to check if the ETL job's runtime has degraded:
- Is the job taking longer than usual? (compare `avgDuration` to 7-day trend)
- Is the job failing more often? (check `failureRate`)
- Is the job currently running or stuck? (check last run status)
- A job that's running but taking 3x longer than normal may explain the freshness delay without an outright failure

Also call `get_etl_jobs` with the relevant `platform` and the table MCONs to find which specific jobs write to this table, then check their issues with `get_etl_issues`.

### 6. Check for query changes

Call `get_query_changes` — did someone modify the ETL query recently?
- New JOINs that produce empty results
- Changed WHERE clauses that filter out all data
- Modified schedule or dependency

## Common root causes

- **Pipeline scheduling failure** — cron job stopped, DAG was paused, permissions revoked
- **Upstream freshness cascade** — an upstream table is stale, blocking this table's refresh
- **Query timeout** — the refresh query is taking too long and timing out
- **Resource contention** — warehouse is overloaded, queries are queued
- **Permission change** — service account lost access to source data

# Intake Flow: No Incident ID

Use this when the user describes a data problem but doesn't have a specific Monte Carlo alert or incident ID.

## Goal

Narrow down: **which table**, **what type of issue**, **when it started**, and whether Monte Carlo already detected it.

## Steps

### 1. Ask clarifying questions

Get the essentials from the user:
- **What table or data asset** is affected? (table name, dashboard, report)
- **What looks wrong?** (stale data, wrong numbers, missing rows, new columns, etc.)
- **When did you notice it?** (approximate timestamp helps scope the search)
- **Which warehouse?** (if they have multiple)

### 2. Find the table

Call `search(query="table_name")` to find the table's MCON and metadata.

If the user mentions a dashboard or report, search for it and then trace lineage upstream to find the source table:
- `search(query="dashboard_name")`
- `get_asset_lineage(mcons=[dashboard_mcon], direction="UPSTREAM")`

### 3. Search for existing alerts

Call `get_alerts` with a recent time range (last 7-14 days):
- Filter by the affected table if possible
- Look for alerts that match the user's description (freshness, volume, schema, field metric)
- If a matching alert exists, use its details to drive the investigation — proceed as if the user provided an incident ID

### 4. Check table health

Even without an alert, check the table directly:

- **Freshness:** `get_table_freshness` — when was it last updated? Is it overdue?
- **Volume:** `get_table_size_history` — has the row count changed unexpectedly?
- **Schema:** `get_table(mcon=..., include_fields=true)` — check current schema
- **Query activity:** `get_queries_for_table` — are write queries still running?

### 5. Classify the issue type

Based on the evidence gathered, determine the issue type:

| Symptom | Issue Type | Next Step |
|---------|-----------|-----------|
| Table hasn't updated recently | Freshness | `references/freshness-investigation.md` |
| Row count spiked or dropped | Volume | `references/volume-investigation.md` |
| Columns added/removed/changed | Schema | `references/schema-investigation.md` |
| Data values look wrong (nulls, weird averages) | Field anomaly | `references/field-anomaly-investigation.md` |
| Pipeline failed or errored | ETL failure | `references/etl-failure-investigation.md` |
| Query was modified | Query change | `references/query-change-investigation.md` |
| Problem is about an AI agent (quality, latency, traces, conversations) | Agent issue | Hand off — read and follow `../../troubleshoot-agent-traces/SKILL.md` |

### 6. Proceed to investigation

Once you've identified the table, issue type, and approximate timeline, continue with Step 2 (Map the blast radius) from the main SKILL.md workflow.

> **TSA note.** This intake path intentionally does **not** invoke the Troubleshooting Agent (TSA), because `run_troubleshooting_agent` requires a Monte Carlo incident UUID and this path starts without one. If Step 3 above identifies a matching alert, treat the user as having provided that alert's incident ID and re-enter the main `SKILL.md` flow at Step 1 — Step 1.5 there will auto-invoke TSA. If no matching alert is found, run the manual investigation only.

## Tips

- **Users often know the symptom but not the cause.** "The dashboard shows yesterday's numbers" = freshness issue. "Revenue is way too high" = volume or field anomaly.
- **Check downstream first if the user reports a dashboard issue.** The bad data might originate several tables upstream.
- **Multiple alerts on the same table at the same time** usually have a single root cause.

# Query Change Investigation Playbook

Use this when SQL modifications are suspected of causing a data issue.

## Investigation steps

### 1. Detect query changes

Call `get_query_changes(mcons=[table_mcon], start_time=..., end_time=...)`:
- Look at the time range around when the issue started
- Compare old vs new SQL text — what changed?
- Focus on: WHERE clauses, JOINs, GROUP BY, column selections, CTEs

### 2. Correlate with the incident timeline

Call `get_change_timeline` for a unified view:
- Did the query change happen right before the anomaly?
- Was there also a volume shift at the same time?
- Were there ETL failures immediately after the query change?

### 3. Understand the impact

For each detected query change:
- **Added/removed JOINs**: Can change cardinality (row count) dramatically
- **Changed WHERE clause**: Can include/exclude different data subsets
- **Modified GROUP BY**: Can change aggregation granularity
- **New columns or removed columns**: Schema change
- **Changed UNION**: Can add or remove entire data sources

### 4. Trace to code changes

**If GitHub MCP is available:**
Search for PRs merged around the time of the query change. Look for:
- dbt model modifications (`.sql` files in `models/`)
- Stored procedure changes
- ETL script updates
- Configuration changes (e.g., different source tables)

**If no GitHub MCP:**
The `get_query_changes` output should include enough SQL diff information to understand what changed. Ask the user if they know who made the change.

### 5. Verify the fix

If the root cause is a bad query change:
- Show the user the before/after SQL
- Suggest reverting the change or fixing the query
- If DB connector is available, run the old and new queries on a sample to compare outputs

## Common patterns

- **Accidental filter removal** — WHERE clause removed, producing more rows than expected
- **JOIN type change** — INNER → LEFT JOIN introduces NULLs; LEFT → INNER drops rows
- **Dedup logic change** — DISTINCT or ROW_NUMBER window changed, altering unique row count
- **Source table swap** — query now reads from a different source table
- **Aggregation change** — GROUP BY granularity changed, producing different row counts

# Schema Investigation Playbook

Use this when columns were added, removed, or had their types changed.

## Investigation steps

### 1. Identify what changed

Call `get_table(mcon=table_mcon, include_fields=true)` to see the current schema.
Compare against the alert details — what columns were added/removed/modified?

### 2. Check for query changes

Call `get_query_changes` — schema changes almost always come from ETL modifications:
- New SELECT columns → column additions
- Removed SELECT columns → column removals
- CAST or type conversion changes → type modifications
- CREATE TABLE AS SELECT with different schema

### 3. Check downstream impact

Call `get_asset_lineage(mcons=[table_mcon], direction="DOWNSTREAM")`:
- Which downstream tables depend on the changed columns?
- Call `get_field_lineage` to trace exactly which downstream fields are affected

### 4. Check ETL pipeline

Schema changes often happen during deployments:
- Call `get_etl_issues` with `platform="dbt"` — dbt model changes are the most common source
- Call `get_etl_issues` with `platform="airflow"` — pipeline deployment may have changed the schema
- Check `get_change_timeline` for a correlated view

### 5. Check for code changes

If GitHub MCP is available, search for recent PRs that modified:
- dbt models (`.sql` files in `models/`)
- SQL migration scripts
- Schema definition files

If no GitHub MCP, `get_query_changes` will show the SQL modifications.

## Common root causes

- **dbt model change** — column added/removed in a model definition
- **Migration script** — ALTER TABLE or CREATE TABLE AS SELECT with new schema
- **Source schema change** — upstream system changed its schema, propagating downstream
- **Type promotion** — implicit type coercion changed (e.g., INT → FLOAT)
- **Column rename** — a column was renamed, breaking downstream references

# Volume Investigation Playbook

Use this when a table's row count changed unexpectedly (spike or drop).

## Investigation steps

### 1. Quantify the change

Call `get_table_size_history` with the table's `full_table_id` and `resource_id`:
- What was the row count before and after?
- When exactly did the change occur?
- Is this a sudden jump or a gradual trend?
- Compare to the normal pattern (seasonality field may help)

### 2. Check for query changes

Call `get_query_changes` — did the ETL query change around the time of the volume shift?
- New or removed WHERE clauses can dramatically change row counts
- Changed JOINs (INNER → LEFT, or vice versa) affect output volume
- Modified deduplication logic

Call `get_change_timeline` for a unified view of all changes correlated with the volume shift.

### 3. Check upstream volume

Call `get_asset_lineage(mcons=[table_mcon], direction="UPSTREAM")` to find source tables.
For each upstream table:
- Call `get_table_size_history` — did the source data volume also change?
- If upstream volume changed proportionally, the issue is in the source data, not this table's ETL

### 4. Check for failed/futile queries

Call `get_query_rca` with the table MCONs and a time range:
- **Failed queries** with new error types may indicate broken inserts
- **Futile queries** (ran but produced nothing) may explain missing rows
- **QDR (query didn't run)** may explain why expected data wasn't loaded

### 5. Profile the data (if DB connector available)

If a database MCP server is connected:
- Compare row counts by date partition: `SELECT date_col, COUNT(*) FROM table GROUP BY 1 ORDER BY 1`
- Check for duplicate rows that appeared: `SELECT *, COUNT(*) FROM table GROUP BY ALL HAVING COUNT(*) > 1`
- Check if specific segments grew/shrank: group by key dimensions
- See `references/data-exploration.md` for more SQL patterns

## Common root causes

- **Source data volume change** — upstream system sent more/fewer records than usual
- **Filter change** — ETL WHERE clause was modified, including/excluding different rows
- **Dedup logic change** — deduplication rules changed, producing more or fewer unique rows
- **Late-arriving data** — backfill or reprocessing loaded historical data
- **Partition swap** — a full partition was replaced with different data
- **Schema migration** — table was truncated and reloaded as part of a migration