auditing-warehouse-data-health

What this skill does

# Auditing data warehouse health

This skill produces a project-wide audit of the data warehouse pipeline. Use it when the user wants a **summary of
everything broken**, not a deep-dive on one sync. The deep-dive on individual failures is
`diagnosing-failed-warehouse-syncs`; this skill is the scan that tells them where to look first.

## When to use this skill

- "What's broken in my warehouse?" / "Give me a health check"
- "Audit my data pipeline"
- The user is new to a project and wants to know what they've inherited
- Weekly or monthly review of pipeline health
- Dashboards are stale and the user isn't sure which source is at fault

## Available tools

| Tool                                          | Purpose                                                             |
| --------------------------------------------- | ------------------------------------------------------------------- |
| `data-warehouse-data-health-issues-retrieve`  | One-shot: all failed/degraded items across the whole pipeline       |
| `external-data-sources-list`                  | All sources with status and latest error                            |
| `external-data-schemas-list`                  | All schemas with status, last_synced_at, latest_error               |
| `view-list`                                   | All saved queries / materialized views with status and latest_error |
| `view-run-history`                            | Run history for a specific materialized view                        |
| `external-data-sources-webhook-info-retrieve` | Check per-source webhook state (not covered by data-health-issues)  |

The `data-health-issues` endpoint already aggregates across materializations, sync schemas, sources, batch export
destinations, and transformations — it's the fastest path to a summary. Use the list endpoints when you need more
context than the summary provides (row counts, non-failing items, schema-level detail).

## What counts as an "issue"

The data-health endpoint returns items from five categories:

| `type`               | Trigger                                                                                                                                       | Typical urgency |
| -------------------- | --------------------------------------------------------------------------------------------------------------------------------------------- | --------------- |
| `source`             | `ExternalDataSource.status = Error` — whole source connection broken                                                                          | High            |
| `external_data_sync` | schema in Failed or BillingLimitReached state (the data-health endpoint returns `status: "failed"` or `status: "billing_limit"` respectively) | Medium–High     |
| `materialized_view`  | `DataWarehouseSavedQuery.is_materialized=true, status=Failed`                                                                                 | Medium          |
| `destination`        | Batch export's latest run is FAILED / FAILED_RETRYABLE / TIMEDOUT / TERMINATED                                                                | Medium          |
| `transformation`     | HogFunction transformation in DISABLED / DEGRADED / FORCEFULLY\_\* state                                                                      | Low–Medium      |

Each entry includes `id`, `name`, `type`, `status`, `error`, `failed_at`, `url`, and (for syncs/sources)
`source_type`.

Note the data-health endpoint only reports _active failures_. It doesn't flag:

- Schemas paused by the user (`should_sync = false`)
- Non-materialized views with errors (only materialized views are reported)
- Schemas that are slow or stale but technically `Completed`
- **Webhook problems on `sync_type: "webhook"` schemas.** The bulk-sync safety net can succeed while the webhook
  push channel is silently broken (deregistered, disabled on the remote side, failing signature verification).
  These don't surface in `data-health-issues` — check per-source with `webhook-info-retrieve`.

If the user asks about staleness or unused items, reach beyond this endpoint — see Step 4.

## Workflow

### Step 1 — One-shot pull

Call `data-warehouse-data-health-issues-retrieve`. This returns every actively failing item in one request.

If the response is empty, tell the user their pipeline is healthy and stop. Don't invent problems.

### Step 2 — Group and prioritize

Group the issues by `type` and sort within each group by severity:

1. **Sources in Error first.** A source failure cascades — every schema under it is effectively dead until the
   source reconnects. Fix these first.
2. **Sync schemas next**, in this order:
   - `status: "billing_limit"` entries (billing issue, non-technical — flag and route to billing)
   - `Failed` on heavily-used tables (user asks / check row counts via schemas-list if needed)
   - `Failed` on less-used tables
3. **Materialized views.** Usually independent of sources — a view failure is a HogQL or data issue in the view
   itself.
4. **Batch export destinations.** Affect data going _out_ of PostHog — important but generally not blocking reads.
5. **Transformations.** Affect ingestion. Flag separately since these are HogFunction issues, not warehouse syncs.

### Step 3 — Present the audit

Render a prioritized report. Don't dump the raw JSON — human-readable table per category:

```text
## Data warehouse health — 7 issues

### 🔴 Sources (1)
- Stripe — authentication failed (failed 2h ago)
  → `diagnosing-failed-warehouse-syncs` on this source

### 🟠 Sync schemas (3)
- postgres_prod.orders (Failed 6h ago) — column "updated_at" does not exist
- postgres_prod.invoices (Failed 6h ago) — column "updated_at" does not exist
- hubspot.contacts (BillingLimitReached) — team quota exceeded

### 🟠 Materialized views (2)
- monthly_revenue — view failed (syntax error in HogQL)
- active_users_30d — view failed (missing table reference)

### 🟡 Destinations (1)
- S3 export "daily-events" (FAILED_RETRYABLE 3 runs in a row)

Recommended order:
1. Stripe auth (everything under it is dead)
2. Schema-drift on postgres_prod.orders / invoices — looks like upstream renamed a column
3. Billing limit on hubspot
4. Materialized views (independent — can be tackled any time)
```

The exact format is less important than: prioritized, grouped, actionable, and hinting at the right next skill.

### Step 4 — Go beyond active failures (when asked)

If the user wants more than just "what's on fire" — e.g. "what else should I look at?" — cross-check:

**Stale but "Completed" schemas:**
Call `external-data-schemas-list` and look for schemas with old `last_synced_at` relative to their `sync_frequency`.
A schema on `1hour` frequency that last synced 3 days ago is effectively broken even if status says `Completed`.

**Unused materialized views:**
Call `view-list`. Materialized views cost storage and compute every run. If any are marked materialized but haven't
been queried lately, surface them — `cleaning-up-stale-warehouse-views` territory (not yet implemented, but the data
is available).

**Sources with zero sync activity:**
Sources where every schema has `should_sync: false` or `status = Paused`. These were set up and then abandoned —
candidates for cleanup via `external-data-sources-destroy`.

**Broken webhooks on webhook-type schemas:**
Iterate the sources that have any schema with `sync_type: "webhook"` (visible via `external-data-schemas-list`). For
each, call `external-data-sources-webhook-info-retrieve({source_id})`:

- `exists: false` while a schema is `sync_type: "webhook"` → webhook was never registered, or was deleted. Push
  channel is dead; only the bulk fallback is ingesting.
- `external_status.error` present → remote service is reporting a problem (permission revoked, endpoint
  deleted on their dashboard).
- `external_status.status` not `"enabled"` → remote has disabled the endpoint (often after repeated delivery
  failures).

Report these separately f