Auditing Warehouse Data Health

1---
2name: auditing-warehouse-data-health
3description: >
4  Audit the health of a PostHog project's data warehouse — find every broken or degraded pipeline item across
5  sources, sync schemas, materialized views, batch exports, and transformations. Use when the user asks "what's
6  broken in my warehouse?", "give me a health check", "audit my data pipeline", "why are some dashboards stale?",
7  or wants a one-shot triage summary before deciding where to spend time. Produces a prioritized report of issues
8  grouped by severity and type, with recommended next steps.
9---
10 
11# Auditing data warehouse health
12 
13This skill produces a project-wide audit of the data warehouse pipeline. Use it when the user wants a **summary of
14everything broken**, not a deep-dive on one sync. The deep-dive on individual failures is
15`diagnosing-failed-warehouse-syncs`; this skill is the scan that tells them where to look first.
16 
17## When to use this skill
18 
19- "What's broken in my warehouse?" / "Give me a health check"
20- "Audit my data pipeline"
21- The user is new to a project and wants to know what they've inherited
22- Weekly or monthly review of pipeline health
23- Dashboards are stale and the user isn't sure which source is at fault
24 
25## Available tools
26 
27| Tool                                          | Purpose                                                             |
28| --------------------------------------------- | ------------------------------------------------------------------- |
29| `data-warehouse-data-health-issues-retrieve`  | One-shot: all failed/degraded items across the whole pipeline       |
30| `external-data-sources-list`                  | All sources with status and latest error                            |
31| `external-data-schemas-list`                  | All schemas with status, last_synced_at, latest_error               |
32| `view-list`                                   | All saved queries / materialized views with status and latest_error |
33| `view-run-history`                            | Run history for a specific materialized view                        |
34| `external-data-sources-webhook-info-retrieve` | Check per-source webhook state (not covered by data-health-issues)  |
35 
36The `data-health-issues` endpoint already aggregates across materializations, sync schemas, sources, batch export
37destinations, and transformations — it's the fastest path to a summary. Use the list endpoints when you need more
38context than the summary provides (row counts, non-failing items, schema-level detail).
39 
40## What counts as an "issue"
41 
42The data-health endpoint returns items from five categories:
43 
44| `type`               | Trigger                                                                                                                                       | Typical urgency |
45| -------------------- | --------------------------------------------------------------------------------------------------------------------------------------------- | --------------- |
46| `source`             | `ExternalDataSource.status = Error` — whole source connection broken                                                                          | High            |
47| `external_data_sync` | schema in Failed or BillingLimitReached state (the data-health endpoint returns `status: "failed"` or `status: "billing_limit"` respectively) | Medium–High     |
48| `materialized_view`  | `DataWarehouseSavedQuery.is_materialized=true, status=Failed`                                                                                 | Medium          |
49| `destination`        | Batch export's latest run is FAILED / FAILED_RETRYABLE / TIMEDOUT / TERMINATED                                                                | Medium          |
50| `transformation`     | HogFunction transformation in DISABLED / DEGRADED / FORCEFULLY\_\* state                                                                      | Low–Medium      |
51 
52Each entry includes `id`, `name`, `type`, `status`, `error`, `failed_at`, `url`, and (for syncs/sources)
53`source_type`.
54 
55Note the data-health endpoint only reports _active failures_. It doesn't flag:
56 
57- Schemas paused by the user (`should_sync = false`)
58- Non-materialized views with errors (only materialized views are reported)
59- Schemas that are slow or stale but technically `Completed`
60- **Webhook problems on `sync_type: "webhook"` schemas.** The bulk-sync safety net can succeed while the webhook
61  push channel is silently broken (deregistered, disabled on the remote side, failing signature verification).
62  These don't surface in `data-health-issues` — check per-source with `webhook-info-retrieve`.
63 
64If the user asks about staleness or unused items, reach beyond this endpoint — see Step 4.
65 
66## Workflow
67 
68### Step 1 — One-shot pull
69 
70Call `data-warehouse-data-health-issues-retrieve`. This returns every actively failing item in one request.
71 
72If the response is empty, tell the user their pipeline is healthy and stop. Don't invent problems.
73 
74### Step 2 — Group and prioritize
75 
76Group the issues by `type` and sort within each group by severity:
77 
781. **Sources in Error first.** A source failure cascades — every schema under it is effectively dead until the
79   source reconnects. Fix these first.
802. **Sync schemas next**, in this order:
81   - `status: "billing_limit"` entries (billing issue, non-technical — flag and route to billing)
82   - `Failed` on heavily-used tables (user asks / check row counts via schemas-list if needed)
83   - `Failed` on less-used tables
843. **Materialized views.** Usually independent of sources — a view failure is a HogQL or data issue in the view
85   itself.
864. **Batch export destinations.** Affect data going _out_ of PostHog — important but generally not blocking reads.
875. **Transformations.** Affect ingestion. Flag separately since these are HogFunction issues, not warehouse syncs.
88 
89### Step 3 — Present the audit
90 
91Render a prioritized report. Don't dump the raw JSON — human-readable table per category:
92 
93```text
94## Data warehouse health — 7 issues
95 
96### 🔴 Sources (1)
97- Stripe — authentication failed (failed 2h ago)
98  → `diagnosing-failed-warehouse-syncs` on this source
99 
100### 🟠 Sync schemas (3)
101- postgres_prod.orders (Failed 6h ago) — column "updated_at" does not exist
102- postgres_prod.invoices (Failed 6h ago) — column "updated_at" does not exist
103- hubspot.contacts (BillingLimitReached) — team quota exceeded
104 
105### 🟠 Materialized views (2)
106- monthly_revenue — view failed (syntax error in HogQL)
107- active_users_30d — view failed (missing table reference)
108 
109### 🟡 Destinations (1)
110- S3 export "daily-events" (FAILED_RETRYABLE 3 runs in a row)
111 
112Recommended order:
1131. Stripe auth (everything under it is dead)
1142. Schema-drift on postgres_prod.orders / invoices — looks like upstream renamed a column
1153. Billing limit on hubspot
1164. Materialized views (independent — can be tackled any time)
117```
118 
119The exact format is less important than: prioritized, grouped, actionable, and hinting at the right next skill.
120 
121### Step 4 — Go beyond active failures (when asked)
122 
123If the user wants more than just "what's on fire" — e.g. "what else should I look at?" — cross-check:
124 
125**Stale but "Completed" schemas:**
126Call `external-data-schemas-list` and look for schemas with old `last_synced_at` relative to their `sync_frequency`.
127A schema on `1hour` frequency that last synced 3 days ago is effectively broken even if status says `Completed`.
128 
129**Unused materialized views:**
130Call `view-list`. Materialized views cost storage and compute every run. If any are marked materialized but haven't
131been queried lately, surface them — `cleaning-up-stale-warehouse-views` territory (not yet implemented, but the data
132is available).
133 
134**Sources with zero sync activity:**
135Sources where every schema has `should_sync: false` or `status = Paused`. These were set up and then abandoned —
136candidates for cleanup via `external-data-sources-destroy`.
137 
138**Broken webhooks on webhook-type schemas:**
139Iterate the sources that have any schema with `sync_type: "webhook"` (visible via `external-data-schemas-list`). For
140each, call `external-data-sources-webhook-info-retrieve({source_id})`:
141 
142- `exists: false` while a schema is `sync_type: "webhook"` → webhook was never registered, or was deleted. Push
143  channel is dead; only the bulk fallback is ingesting.
144- `external_status.error` present → remote service is reporting a problem (permission revoked, endpoint
145  deleted on their dashboard).
146- `external_status.status` not `"enabled"` → remote has disabled the endpoint (often after repeated delivery
147  failures).
148 
149Report these separately from the primary audit — they're a different shape of problem than the other categories,
150and the fix is a different skill (`diagnosing-failed-warehouse-syncs` scenario I, or
151`setting-up-a-data-warehouse-source` step 5.5).
152 
153Only run these extra checks if the user explicitly asks for a broader audit — they involve more tool calls and
154heuristics.
155 
156### Step 5 — Offer the next step
157 
158End the audit with a clear hand-off:
159 
160- "Want me to dig into the Stripe failure?" → hands off to `diagnosing-failed-warehouse-syncs`
161- "Want me to fix the schema drift on orders?" → hands off to `tuning-incremental-sync-config`
162- "Want to disable the billing-capped schemas?" → one-click via `external-data-schemas-partial-update`
163 
164Never start applying fixes autonomously from an audit — the audit's job is to report and recommend, not remediate.
165Any fix should be confirmed explicitly before executing.
166 
167## Example interaction
168 
169```text
170User: "Check the health of our data warehouse"
171 
172Agent:
173- data-warehouse-data-health-issues-retrieve → 4 items
174   [{type: "source", name: "Stripe", error: "401 Unauthorized", ...},
175    {type: "external_data_sync", name: "postgres_prod.orders",
176     error: "column \"updated_at\" does not exist", ...},
177    {type: "materialized_view", name: "monthly_revenue",
178     error: "syntax error at or near \"FORM\"", ...},
179    {type: "destination", name: "S3 daily-events", error: "AccessDenied", ...}]
180 
181- Report:
182  "Found 4 issues in your data warehouse:
183 
184   🔴 Sources (1)
185   - Stripe — auth failure 2h ago (401). All 8 tables under it are currently dead.
186 
187   🟠 Sync schemas (1)
188   - postgres_prod.orders — column `updated_at` was dropped on the source side,
189     incremental sync can't find it.
190 
191   🟠 Materialized views (1)
192   - monthly_revenue — HogQL syntax error ('FORM' instead of 'FROM').
193 
194   🟡 Destinations (1)
195   - S3 export 'daily-events' — access denied on write.
196 
197   Suggest fixing in this order:
198   1. Stripe credentials (unblocks everything under it)
199   2. orders incremental-field drift
200   3. monthly_revenue typo
201   4. S3 permissions
202 
203   Want me to start on Stripe?"
204```
205 
206## Important notes
207 
208- **The audit is read-only.** Never call destructive tools from the audit flow. Hand off to the diagnosis/tuning
209  skills — which in turn confirm before acting.
210- **Empty = healthy.** Don't pad an empty audit with hypothetical issues. "No issues found" is a good answer.
211- **Source failures cascade.** When reporting a source in Error, also mention which schemas under it are affected
212  (or will be, once they try to sync again). The user needs to understand the blast radius.
213- **Billing limits aren't technical problems.** Flag them but route to billing / quota discussion, not to a
214  recovery action.
215- **Transformation issues are separate.** HogFunctions aren't warehouse syncs — they show up in the audit because
216  they're part of the broader pipeline, but they live in the `posthog` ingestion side. Route those to pipeline
217  skills rather than trying to fix in-place here.
218- **`data-health-issues` only surfaces active failures.** For staleness, unused views, or abandoned sources, you
219  need to cross-check the list endpoints. Only do this when the user explicitly asks for a deeper audit.
220- **Webhook health is separate from schema health.** The data-health endpoint doesn't know about webhook state.
221  When a user's request mentions "real-time", "Stripe webhook", or "why is data hours behind on a webhook
222  source", go straight to `webhook-info-retrieve` rather than inferring from schema status.
223