diagnosing-failed-warehouse-syncs

Files (1)

• SKILL.md

SKILL.md (first lines)

# Diagnosing failed data warehouse syncs

Work top-down when a data warehouse source or table is failing, stuck, or producing bad data: source → schema →
recovery action. Do **not** jump straight to "resync from scratch" — that discards synced data and restarts from
zero, which is rarely the right first step.

## When to use this skill

- The user reports a specific sync is failing (e.g. "my Stripe source is red")
- A table has been in `Running` state far longer than expected
- Data in a warehouse table is stale, missing rows, or looks corrupt
- Latest rows aren't appearing despite the schema being marked `Completed`
- The user is choosing between cancel / reload / resync / delete-data and isn't sure which
- Another skill — typically `auditing-warehouse-source-health` — has surfaced a failing source or schema and the user
  wants to dig into it

Both entry points (user-reported and audit-handoff) use the same workflow; the audit just means you already know
which item to diagnose and can skip Step 1's discovery search.

## Available tools

| Tool                                                   | Purpose                                                                    |
| ------------------------------------------------------ | -------------------------------------------------------------------------- |
| `external-data-sources-list`                           | List all sources with connection status and latest error                   |
| `external-data-sources-retrieve`                       | Full details for one source including all its schemas                      |
| `external-data-schemas-list`                           | All table schemas across all sources, with per-table status + latest_error |
| `external-data-schemas-retrieve`                       | Full details for one schema including sync_type_config                     |
| `external-data-schemas-cancel`                         | Cancel a sync currently in `Running` state                                 |
| `external-data-schemas-reload`                         | Trigger a sync using the configured sync method (respects incremental)     |
| `external-data-schemas-resync`                         | Full resync — wipes synced data and restarts. Destructive                  |
| `external-data-schemas-delete-data`                    | Delete the synced table but keep the schema entry                          |
| `external-data-schemas-partial-update`                 | Change sync_type / incremental_field / cdc_table_mode                      |
| `external-data-sources-partial-update`                 | Update a source's credentials (`job_inputs`) after rotation                |
| `external-data-sources-reload`                         | Retrigger syncs for every enabled schema on a source                       |
| `external-data-sources-refresh-schemas`                | Re-fetch the source's table list to pick up new tables                     |
| `external-data-sources-check-cdc-prerequisites-create` | Verify Postgres CDC setup for a source                                     |
| `external-data-schemas-incremental-fields-create`      | Refresh candidate incremental fields when the source schema has changed    |
| `external-data-sources-webhook-info-retrieve`          | Check webhook registration state and external service status               |
| `external-data-sources-create-webhook-create`          | Re-register a webhook that was lost or never registered                    |
| `external-data-sources-update-webhook-inputs-create`   | Update the signing secret after rotation on the source side                |
| `external-data-sources-delete-webhook-create`          | Remove a broken webhook before re-registering                              |

## Workflow

### Step 1 — Locate the failing item

If the user named a source, go straight to `external-data-sources-retrieve`. Otherwise start with
`external-data-sources-list` and `external-data-schemas-list` to find what's red.

Two kinds of failure:

- **Source-level** (`ExternalDataSource.status = "Error"`): the connection itself is broken — credentials expired,
  host unreachable, account disabled. Affects every table.
- **Schema-level** — the source connects fine but one or more tables are failing. In the serialized API response
  from `external-data-schemas-list`, look for `status` values `"Failed"`, `"Billing limits"`, or `"Billing limits
too low"`. (The underlying model enum values are `BillingLimitReached` and `BillingLimitTooLow`, but the
  serializer rewrites them — match on both the human-readable and enum forms to be safe.)

A source can look `Completed` at the top level while one of its schemas is `Failed` — always check both.

### Step 2 — Classify the schema status

From `external-data-schemas-list`, each schema has a `status`:

| Status                                                              | Meaning                                    | Usually means                          |
| ------------------------------------------------------------------- | ------------------------------------------ | -------------------------------------- |
| `Running`                                                           | Sync currently executing                   | Normal, unless stuck for hours         |
| `Completed`                                                         | Last sync finished successfully            | Healthy                                |
| `Failed`                                                            | Last sync errored — see `latest_error`     | Needs diagnosis                        |
| `Paused`                                                            | User disabled sync (`should_sync = false`) | Intentional                            |
| `Billing limits` (serializer) / `BillingLimitReached` (enum)        | Team hit its warehouse row quota           | Billing issue, not a technical failure |
| `Billing limits too low` (serializer) / `BillingLimitTooLow` (enum) | Team has insufficient credit               | Billing issue                          |

Always check `last_synced_at` alongside status. A schema in `Running` with `last_synced_at` from 12 hours ago is
almost certainly stuck, even though the status isn't `Failed`.

### Step 3 — Interpret `latest_error`

Map the `latest_error` string to a root cause. Common patterns:

| Error substring                                                                      | Root cause                                                 | Fix                                                                                   |
| ------------------------------------------------------------------------------------ | ---------------------------------------------------------- | ------------------------------------------------------------------------------------- |
| `authentication failed`, `401`, `403`, `invalid credentials`                         | Credentials expired or rotated                             | User rotates creds, then `external-data-sources-partial-update` with new `job_inputs` |
| `Could not establish session to SSH gateway`, `Could not connect to your SSH tunnel` | SSH tunnel misconfigured or remote host down               | User checks SSH host/key/bastion                                                      |
| `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`                           |
| `primary keys for this table are not unique`                                         | Declared PK columns aren't actually unique                 | Pick different PK columns via `partial-update`                                        |
| `Integration matching query does not exist`                                          | Source's saved integration was deleted                     | Recreate the source                                                                   |
| `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`                   |
| `relation "..." does not exist`                                                      | Source table was dropped/renamed                           | Remove schema or rename source-side                                                   |
| `SSL`, `connection refused`, `timeout`, `unreachable`                                | Network / firewall / host reachability                     | User side — check host/port/allowlist                                                 |
| `replication slot`, `publication`, `wal_level`                                       | CDC prerequisites broken                                   | Run `check-cdc-prerequisites-create`; may need slot recreate                          |
| `Schema exceeds row limit`, `billing`                                                | Billing limit                                              | Upgrade plan or disable the schema                                                    |

If `latest_error` is null but the schema is `Failed`, retrieve the schema directly — the error may only be populated
on the detail view.

### Step 4 — Pick the recovery action

The recovery action depends on root cause, not just status. Match the user's situation to one of these:

**A. Transient failure (network blip, temporary API outage)**

- Data synced so far is still valid.
- Action: `external-data-schemas-reload` to retry using the configured sync method.
- Incremental/append syncs pick up where they left off.

**B. Credentials expired or rotated**

- Every schema under the source is failing with an auth error.
- Action: user rotates creds → `external-data-sources-partial-update` with the new `job_inputs` → the reload happens
  automatically when the source status flips back to running, or trigger manually with `external-data-sources-reload`.

**C. Schema drift — column renamed, dropped, or type changed**

- Error mentions a specific column that no longer matches the source.
- Action: `external-data-schemas-incremental-fields-create` to get the current fields, then
  `external-data-schemas-partial-update` with the corrected `incremental_field` / `incremental_field_type` /
  `primary_key_columns`. Usually no need to wipe data.

From PostHog/skills, MIT.