diagnosing-failed-warehouse-syncs

What this skill does

# 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-data-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`                 | 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 re