Diagnosing Failed Warehouse Syncs

1---
2name: diagnosing-failed-warehouse-syncs
3description: >
4  Diagnose why a data warehouse sync is failing and recommend the right recovery action. Use when the user asks "why
5  isn't my Stripe/Postgres/Hubspot sync working?", "this table has been stuck for hours", "the data in the warehouse
6  looks wrong", or wants to troubleshoot a specific source or schema. Covers source-level vs schema-level failures,
7  stuck Running states, credential and schema-drift errors, incremental-field misconfig, CDC prerequisite failures,
8  and the cancel / reload / resync / delete-data recovery actions.
9---
10 
11# Diagnosing failed data warehouse syncs
12 
13Work top-down when a data warehouse source or table is failing, stuck, or producing bad data: source → schema →
14recovery action. Do **not** jump straight to "resync from scratch" — that discards synced data and restarts from
15zero, which is rarely the right first step.
16 
17## When to use this skill
18 
19- The user reports a specific sync is failing (e.g. "my Stripe source is red")
20- A table has been in `Running` state far longer than expected
21- Data in a warehouse table is stale, missing rows, or looks corrupt
22- Latest rows aren't appearing despite the schema being marked `Completed`
23- The user is choosing between cancel / reload / resync / delete-data and isn't sure which
24- Another skill — typically `auditing-warehouse-data-health` — has surfaced a failing source or schema and the user
25  wants to dig into it
26 
27Both entry points (user-reported and audit-handoff) use the same workflow; the audit just means you already know
28which item to diagnose and can skip Step 1's discovery search.
29 
30## Available tools
31 
32| Tool                                                   | Purpose                                                                    |
33| ------------------------------------------------------ | -------------------------------------------------------------------------- |
34| `external-data-sources-list`                           | List all sources with connection status and latest error                   |
35| `external-data-sources-retrieve`                       | Full details for one source including all its schemas                      |
36| `external-data-schemas-list`                           | All table schemas across all sources, with per-table status + latest_error |
37| `external-data-schemas-retrieve`                       | Full details for one schema including sync_type_config                     |
38| `external-data-schemas-cancel`                         | Cancel a sync currently in `Running` state                                 |
39| `external-data-schemas-reload`                         | Trigger a sync using the configured sync method (respects incremental)     |
40| `external-data-schemas-resync`                         | Full resync — wipes synced data and restarts. Destructive                  |
41| `external-data-schemas-delete-data`                    | Delete the synced table but keep the schema entry                          |
42| `external-data-schemas-partial-update`                 | Change sync_type / incremental_field / cdc_table_mode                      |
43| `external-data-sources-partial-update`                 | Update a source's credentials (`job_inputs`) after rotation                |
44| `external-data-sources-reload`                         | Retrigger syncs for every enabled schema on a source                       |
45| `external-data-sources-refresh-schemas`                | Re-fetch the source's table list to pick up new tables                     |
46| `external-data-sources-check-cdc-prerequisites-create` | Verify Postgres CDC setup for a source                                     |
47| `external-data-schemas-incremental-fields-create`      | Refresh candidate incremental fields when the source schema has changed    |
48| `external-data-sources-webhook-info-retrieve`          | Check webhook registration state and external service status               |
49| `external-data-sources-create-webhook-create`          | Re-register a webhook that was lost or never registered                    |
50| `external-data-sources-update-webhook-inputs-create`   | Update the signing secret after rotation on the source side                |
51| `external-data-sources-delete-webhook-create`          | Remove a broken webhook before re-registering                              |
52 
53## Workflow
54 
55### Step 1 — Locate the failing item
56 
57If the user named a source, go straight to `external-data-sources-retrieve`. Otherwise start with
58`external-data-sources-list` and `external-data-schemas-list` to find what's red.
59 
60Two kinds of failure:
61 
62- **Source-level** (`ExternalDataSource.status = "Error"`): the connection itself is broken — credentials expired,
63  host unreachable, account disabled. Affects every table.
64- **Schema-level** — the source connects fine but one or more tables are failing. In the serialized API response
65  from `external-data-schemas-list`, look for `status` values `"Failed"`, `"Billing limits"`, or `"Billing limits
66too low"`. (The underlying model enum values are `BillingLimitReached` and `BillingLimitTooLow`, but the
67  serializer rewrites them — match on both the human-readable and enum forms to be safe.)
68 
69A source can look `Completed` at the top level while one of its schemas is `Failed` — always check both.
70 
71### Step 2 — Classify the schema status
72 
73From `external-data-schemas-list`, each schema has a `status`:
74 
75| Status                                                              | Meaning                                    | Usually means                          |
76| ------------------------------------------------------------------- | ------------------------------------------ | -------------------------------------- |
77| `Running`                                                           | Sync currently executing                   | Normal, unless stuck for hours         |
78| `Completed`                                                         | Last sync finished successfully            | Healthy                                |
79| `Failed`                                                            | Last sync errored — see `latest_error`     | Needs diagnosis                        |
80| `Paused`                                                            | User disabled sync (`should_sync = false`) | Intentional                            |
81| `Billing limits` (serializer) / `BillingLimitReached` (enum)        | Team hit its warehouse row quota           | Billing issue, not a technical failure |
82| `Billing limits too low` (serializer) / `BillingLimitTooLow` (enum) | Team has insufficient credit               | Billing issue                          |
83 
84Always check `last_synced_at` alongside status. A schema in `Running` with `last_synced_at` from 12 hours ago is
85almost certainly stuck, even though the status isn't `Failed`.
86 
87### Step 3 — Interpret `latest_error`
88 
89Map the `latest_error` string to a root cause. Common patterns:
90 
91| Error substring                                              | Root cause                                                 | Fix                                                                                   |
92| ------------------------------------------------------------ | ---------------------------------------------------------- | ------------------------------------------------------------------------------------- |
93| `authentication failed`, `401`, `403`, `invalid credentials` | Credentials expired or rotated                             | User rotates creds, then `external-data-sources-partial-update` with new `job_inputs` |
94| `Could not establish session to SSH gateway`                 | SSH tunnel misconfigured or remote host down               | User checks SSH host/key/bastion                                                      |
95| `Primary key required for incremental syncs`                 | Table has no PK and sync_type is `incremental`/`cdc`       | Either add PK in source, or switch schema to `full_refresh`                           |
96| `primary keys for this table are not unique`                 | Declared PK columns aren't actually unique                 | Pick different PK columns via `partial-update`                                        |
97| `Integration matching query does not exist`                  | Source's saved integration was deleted                     | Recreate the source                                                                   |
98| `column "X" does not exist`, `does not have a column named`  | Schema drift — incremental field or tracked column removed | Use `incremental-fields-create` to re-detect, then `partial-update`                   |
99| `relation "..." does not exist`                              | Source table was dropped/renamed                           | Remove schema or rename source-side                                                   |
100| `SSL`, `connection refused`, `timeout`, `unreachable`        | Network / firewall / host reachability                     | User side — check host/port/allowlist                                                 |
101| `replication slot`, `publication`, `wal_level`               | CDC prerequisites broken                                   | Run `check-cdc-prerequisites-create`; may need slot recreate                          |
102| `Schema exceeds row limit`, `billing`                        | Billing limit                                              | Upgrade plan or disable the schema                                                    |
103 
104If `latest_error` is null but the schema is `Failed`, retrieve the schema directly — the error may only be populated
105on the detail view.
106 
107### Step 4 — Pick the recovery action
108 
109The recovery action depends on root cause, not just status. Match the user's situation to one of these:
110 
111**A. Transient failure (network blip, temporary API outage)**
112 
113- Data synced so far is still valid.
114- Action: `external-data-schemas-reload` to retry using the configured sync method.
115- Incremental/append syncs pick up where they left off.
116 
117**B. Credentials expired or rotated**
118 
119- Every schema under the source is failing with an auth error.
120- Action: user rotates creds → `external-data-sources-partial-update` with the new `job_inputs` → the reload happens
121  automatically when the source status flips back to running, or trigger manually with `external-data-sources-reload`.
122 
123**C. Schema drift — column renamed, dropped, or type changed**
124 
125- Error mentions a specific column that no longer matches the source.
126- Action: `external-data-schemas-incremental-fields-create` to get the current fields, then
127  `external-data-schemas-partial-update` with the corrected `incremental_field` / `incremental_field_type` /
128  `primary_key_columns`. Usually no need to wipe data.
129 
130**C2. Added / renamed tables in the source database**
131 
132- User mentions "I added a new table to Postgres but it isn't appearing", or a source table was renamed.
133- Action: `external-data-sources-refresh-schemas` to pick up the new table list, then configure sync on any new
134  schemas.
135 
136**D. Incremental state is wrong (duplicates, missing rows, data looks corrupt)**
137 
138- Schema status may be `Completed` — this isn't a "failure" per se, it's bad data.
139- Action: `external-data-schemas-resync` to wipe synced data and re-import from source. Destructive but often the
140  right call for data-quality issues.
141 
142**E. CDC pipeline broken on Postgres**
143 
144- Error mentions replication slot, publication, WAL.
145- Action: `external-data-sources-check-cdc-prerequisites-create` to enumerate what's broken, fix on the Postgres
146  side, then `external-data-schemas-reload`. If the WAL position was lost, a `resync` is sometimes unavoidable.
147 
148**F. Sync is stuck in `Running` for hours**
149 
150- Check `last_synced_at`. If it's hours old and still `Running`, the job is orphaned.
151- Action: `external-data-schemas-cancel` to stop it, then `external-data-schemas-reload`.
152 
153**G. Table data is corrupt but you want to keep the schema config**
154 
155- Action: `external-data-schemas-delete-data` to drop the synced table but preserve the schema entry. Next reload
156  re-imports from scratch without losing the configured sync_type/incremental_field.
157 
158**H. Billing limit**
159 
160- Action isn't technical. Explain the limit, recommend upgrading the plan or disabling lower-priority schemas so the
161  important ones fit under quota.
162 
163**I. Webhook-backed schema isn't receiving events**
164 
165- Symptoms: schema has `sync_type: "webhook"`, initial bulk sync finished, but no new rows arrive despite activity on
166  the source side. Status may still read `Completed` because the bulk sync (the safety-net cadence) is succeeding —
167  the problem is the push path.
168- Action:
169  1. `external-data-sources-webhook-info-retrieve({source_id})`.
170  2. If `exists: false` → the webhook was never registered, or was deleted. Call `create-webhook-create` to
171     register it.
172  3. If `exists: true` but `external_status.error` is set → typically "API key doesn't have permission to read
173     webhooks" or similar. The webhook may have been deleted on the source's dashboard. Re-create it.
174  4. If `external_status.status` isn't `"enabled"` → the source disabled the webhook (usually after repeated
175     delivery failures). Re-enable or re-register.
176  5. If payloads are arriving but failing signature verification → the signing secret was rotated. Get the new
177     one from the source's dashboard and call `update-webhook-inputs-create({source_id}, {inputs:
178{signing_secret: "..."}})`.
179- After any fix, check the source's webhook logs (on their side) to confirm PostHog is now responding 2xx.
180 
181### Step 5 — Confirm before destructive actions
182 
183Three recovery actions discard data and cannot be undone:
184 
185- `external-data-schemas-resync` — wipes synced rows, re-imports from scratch
186- `external-data-schemas-delete-data` — drops the synced table
187- `external-data-sources-destroy` — deletes the source and all its schemas
188 
189Always present the fix you're proposing and wait for explicit approval before calling any of these. "Just try
190resync" is rarely the right default.
191 
192## Example interaction
193 
194```text
195User: "Our Stripe sync is broken, can you check?"
196 
197Agent:
198- external-data-sources-list → find Stripe source, status = Error
199- external-data-sources-retrieve({id}) → latest_error: "authentication failed: 401 Unauthorized"
200- Report: "Your Stripe source's API key is no longer authenticating.
201   All 8 tables under it are failing with 401s. This usually means the key was rotated on the Stripe side.
202 
203   To fix:
204   1. Grab a fresh restricted API key from the Stripe dashboard.
205   2. I'll update the source with the new key.
206   3. Syncs will resume automatically — no data loss.
207 
208   Paste the new key here when ready."
209 
210User: "sk_live_..."
211 
212Agent:
213- external-data-sources-partial-update({id}, {job_inputs: {stripe_secret_key: "sk_live_..."}})
214- external-data-sources-reload({id}) to trigger retry
215- Report: "Updated and re-triggered. Check back in a few minutes — latest_error should clear."
216```
217 
218## Important notes
219 
220- **Source status overrides schema status for diagnosis.** If the source is `Error`, nothing under it will work;
221  fixing the source usually fixes all its schemas at once.
222- **`Running` isn't always healthy.** Cross-check `last_synced_at`. A sync stuck in `Running` needs `cancel` then
223  `reload`, not `resync`.
224- **Resync is destructive.** It discards synced data. Only recommend it when the data itself is bad (duplicates,
225  missing rows, corrupt) or when recovery genuinely requires a clean slate (lost WAL position on CDC). Never use it
226  as a first-try for transient errors.
227- **Delete-data preserves config.** When a user says "I just want to start this table over from scratch", prefer
228  `delete-data` + `reload` over `resync` + new schema entry — it keeps the configured sync_type / incremental_field
229  / PK setup.
230- **Billing limits aren't technical failures.** Don't try to retry or reconfigure your way out. Route to billing.
231- **Webhook failures can hide behind a green status.** A webhook-type schema whose bulk fallback sync succeeded looks
232  `Completed` even when the push channel is broken. When users say "my data is hours behind" on a webhook schema,
233  call `webhook-info-retrieve` before looking at schema status. Webhook issues don't surface on
234  `external-data-schemas-list`.
235