diagnosing-failed-warehouse-syncs

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

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.
当数据仓库中的 source 或 table 同步失败、卡住或产生错误数据时,应按 source → schema → 恢复操作的顺序自顶向下排查。不要直接执行“从头 resync”——这会丢弃已同步的数据并从零重启,很少是正确的第一步。

When to use this skill

何时使用此 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.
  • 用户报告某个具体同步失败(例如“我的 Stripe source 标红了”)
  • 某张 table 处于
    Running
    状态的时间远超预期
  • 数据仓库 table 中的数据过期、缺少行或看起来已损坏
  • schema 已标记为
    Completed
    ,但最新行仍未出现
  • 用户正在 cancel / reload / resync / delete-data 之间选择,但不确定应该采用哪一种
  • 另一个 skill(通常是
    auditing-warehouse-source-health
    )发现某个 source 或 schema 存在故障,用户希望进一步排查
两种入口(用户直接报告和审计移交)使用相同的工作流;审计移交只是意味着你已经知道需要诊断的项目,因此可以跳过步骤 1 中的发现搜索。

Available tools

可用工具

ToolPurpose
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
工具用途
external-data-sources-list
列出所有 source,并显示连接状态和最新错误
external-data-sources-retrieve
获取一个 source 的完整详情,包括其所有 schema
external-data-schemas-list
列出所有 source 下的 table schema,并显示每个 table 的状态和
latest_error
external-data-schemas-retrieve
获取一个 schema 的完整详情,包括
sync_type_config
external-data-schemas-cancel
取消当前处于
Running
状态的同步
external-data-schemas-reload
使用已配置的同步方式触发同步(遵循增量策略)
external-data-schemas-resync
完整重新同步——清除已同步的数据并重新开始。破坏性操作
external-data-schemas-delete-data
删除已同步的 table,但保留 schema 条目
external-data-schemas-partial-update
修改
sync_type
/
incremental_field
/
cdc_table_mode
external-data-sources-partial-update
轮换凭证后,更新 source 的凭证(
job_inputs
)
external-data-sources-reload
为某个 source 上所有已启用的 schema 重新触发同步
external-data-sources-refresh-schemas
重新获取 source 的 table 列表,以识别新增的 table
external-data-sources-check-cdc-prerequisites-create
验证某个 source 的 Postgres CDC 设置
external-data-schemas-incremental-fields-create
当 source schema 发生变化时,刷新候选增量字段
external-data-sources-webhook-info-retrieve
检查 webhook 注册状态和外部服务状态
external-data-sources-create-webhook-create
重新注册已丢失或从未注册的 webhook
external-data-sources-update-webhook-inputs-create
在 source 端轮换 signing secret 后进行更新
external-data-sources-delete-webhook-create
在重新注册前删除损坏的 webhook

Workflow

工作流

Step 1 — Locate the failing item

步骤 1——定位故障项

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.
如果用户指定了 source,直接调用
external-data-sources-retrieve
。否则先调用
external-data-sources-list
和
external-data-schemas-list
,找出标红的项目。
故障分为两种:
  • Source 级(
    ExternalDataSource.status = "Error"
    ):连接本身已断开——凭证过期、主机不可达或账户被禁用。这会影响该 source 下的所有 table。
  • Schema 级——source 连接正常,但一个或多个 table 同步失败。在
    external-data-schemas-list
    返回的序列化 API 响应中,查找
    Failed
    、
    Billing limits
    或
    Billing limits too low
    等
    status
    值。(底层模型枚举值为
    BillingLimitReached
    和
    BillingLimitTooLow
    ,但序列化器会重写这些值——为稳妥起见,应同时匹配人类可读形式和枚举形式。)
一个 source 在顶层可能显示为
Completed
,但其某个 schema 仍为
Failed
——必须同时检查两者。

Step 2 — Classify the schema status

步骤 2——对 schema 状态进行分类

From
external-data-schemas-list
, each schema has a
status
:
StatusMeaningUsually means
Running
Sync currently executingNormal, unless stuck for hours
Completed
Last sync finished successfullyHealthy
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 quotaBilling issue, not a technical failure
Billing limits too low
(serializer) /
BillingLimitTooLow
(enum)
Team has insufficient creditBilling 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
.
在
external-data-schemas-list
中,每个 schema 都有一个
status
:
状态含义通常表示
Running
同步当前正在执行正常,除非已卡住数小时
Completed
上次同步已成功完成健康
Failed
上次同步发生错误——请查看
latest_error
需要诊断
Paused
用户已禁用同步(
should_sync = false
)
有意为之
Billing limits
(序列化值)/
BillingLimitReached
(枚举值)
团队已达到数据仓库行数配额账单问题,而非技术故障
Billing limits too low
(序列化值)/
BillingLimitTooLow
(枚举值)
团队信用额度不足账单问题
检查状态时,始终应同时查看
last_synced_at
。如果某个 schema 仍为
Running
,但
last_synced_at
已是 12 小时前,那么它几乎可以肯定是卡住了,即使状态不是
Failed
。

Step 3 — Interpret
latest_error

步骤 3——解读
latest_error

Map the
latest_error
string to a root cause. Common patterns:
Error substringRoot causeFix
authentication failed
,
401
,
403
,
invalid credentials
Credentials expired or rotatedUser 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 downUser 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 uniquePick different PK columns via
partial-update
Integration matching query does not exist
Source's saved integration was deletedRecreate the source
column "X" does not exist
,
does not have a column named
Schema drift — incremental field or tracked column removedUse
incremental-fields-create
to re-detect, then
partial-update
relation "..." does not exist
Source table was dropped/renamedRemove schema or rename source-side
SSL
,
connection refused
,
timeout
,
unreachable
Network / firewall / host reachabilityUser side — check host/port/allowlist
replication slot
,
publication
,
wal_level
CDC prerequisites brokenRun
check-cdc-prerequisites-create
; may need slot recreate
Schema exceeds row limit
,
billing
Billing limitUpgrade 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.
将
latest_error
字符串映射到根因。常见模式如下:
错误子串根因修复方法
authentication failed
、
401
、
403
、
invalid credentials
凭证过期或已轮换用户轮换凭证,然后调用
external-data-sources-partial-update
并传入新的
job_inputs
Could not establish session to SSH gateway
、
Could not connect to your SSH tunnel
SSH tunnel 配置错误或远程主机已宕机用户检查 SSH host、key 和 bastion
Primary key required for incremental syncs
Table 没有 PK,且
sync_type
为
incremental
/
cdc
在 source 中添加 PK,或将 schema 切换为
full_refresh
primary keys for this table are not unique
声明的 PK 列实际上并不唯一通过
partial-update
选择其他 PK 列
Integration matching query does not exist
Source 中保存的 integration 已被删除重新创建 source
column "X" does not exist
、
does not have a column named
Schema 漂移——增量字段或受跟踪的列已被删除使用
incremental-fields-create
重新检测,然后执行
partial-update
relation "..." does not exist
Source table 已被删除或重命名删除 schema,或在 source 端重命名
SSL
、
connection refused
、
timeout
、
unreachable
网络、防火墙或主机可达性问题用户侧处理——检查 host、port 和 allowlist
replication slot
、
publication
、
wal_level
CDC 前置条件已损坏运行
check-cdc-prerequisites-create
;可能需要重建 slot
Schema exceeds row limit
、
billing
账单限制升级套餐或禁用该 schema
如果
latest_error
为 null,但 schema 状态为
Failed
,请直接获取 schema 详情——错误可能只填充在详情视图中。

Step 4 — Pick the recovery action

步骤 4——选择恢复操作

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.
C2. Added / renamed tables in the source database
  • User mentions "I added a new table to Postgres but it isn't appearing", or a source table was renamed.
  • Action:
    external-data-sources-refresh-schemas
    to pick up the new table list, then configure sync on any new schemas.
D. Incremental state is wrong (duplicates, missing rows, data looks corrupt)
  • Schema status may be
    Completed
    — this isn't a "failure" per se, it's bad data.
  • Action:
    external-data-schemas-resync
    to wipe synced data and re-import from source. Destructive but often the right call for data-quality issues.
E. CDC pipeline broken on Postgres
  • Error mentions replication slot, publication, WAL.
  • Action:
    external-data-sources-check-cdc-prerequisites-create
    to enumerate what's broken, fix on the Postgres side, then
    external-data-schemas-reload
    . If the WAL position was lost, a
    resync
    is sometimes unavoidable.
F. Sync is stuck in
Running
for hours
  • Check
    last_synced_at
    . If it's hours old and still
    Running
    , the job is orphaned.
  • Action:
    external-data-schemas-cancel
    to stop it, then
    external-data-schemas-reload
    .
G. Table data is corrupt but you want to keep the schema config
  • Action:
    external-data-schemas-delete-data
    to drop the synced table but preserve the schema entry. Next reload re-imports from scratch without losing the configured sync_type/incremental_field.
H. Billing limit
  • Action isn't technical. Explain the limit, recommend upgrading the plan or disabling lower-priority schemas so the important ones fit under quota.
I. Webhook-backed schema isn't receiving events
  • Symptoms: schema has
    sync_type: "webhook"
    , initial bulk sync finished, but no new rows arrive despite activity on the source side. Status may still read
    Completed
    because the bulk sync (the safety-net cadence) is succeeding — the problem is the push path.
  • Action:
    1. external-data-sources-webhook-info-retrieve({source_id})
      .
    2. If
      exists: false
      → the webhook was never registered, or was deleted. Call
      create-webhook-create
      to register it.
    3. If
      exists: true
      but
      external_status.error
      is set → typically "API key doesn't have permission to read webhooks" or similar. The webhook may have been deleted on the source's dashboard. Re-create it.
    4. If
      external_status.status
      isn't
      "enabled"
      → the source disabled the webhook (usually after repeated delivery failures). Re-enable or re-register.
    5. If payloads are arriving but failing signature verification → the signing secret was rotated. Get the new one from the source's dashboard and call
      update-webhook-inputs-create({source_id}, {inputs: {signing_secret: "..."}})
      .
  • After any fix, check the source's webhook logs (on their side) to confirm PostHog is now responding 2xx.
恢复操作取决于根因,而不只是状态。请将用户的情况与以下选项之一匹配:
A. 暂时性故障(网络波动、临时 API 中断)
  • 目前已同步的数据仍然有效。
  • 操作:使用
    external-data-schemas-reload
    ,按已配置的同步方式重试。
  • 增量/追加同步会从上次中断的位置继续。
B. 凭证过期或已轮换
  • Source 下的每个 schema 都在出现认证错误。
  • 操作:用户轮换凭证 → 调用
    external-data-sources-partial-update
    并传入新的
    job_inputs
    → 当 source 状态恢复为 running 时会自动执行 reload,也可以手动调用
    external-data-sources-reload
    。
C. Schema 漂移——列被重命名、删除或更改了类型
  • 错误提到某个具体列,该列已与 source 不匹配。
  • 操作:使用
    external-data-schemas-incremental-fields-create
    获取当前字段,然后调用
    external-data-schemas-partial-update
    ,更新正确的
    incremental_field
    /
    incremental_field_type
    /
    primary_key_columns
    。通常不需要清除数据。
C2. Source 数据库中新增或重命名了 table
  • 用户提到“我在 Postgres 中新增了一张表,但它没有出现”,或者 source 中的 table 被重命名。
  • 操作:使用
    external-data-sources-refresh-schemas
    获取新的 table 列表,然后为任何新增 schema 配置同步。
D. 增量状态错误(重复数据、缺少行、数据看似损坏)
  • Schema 状态可能为
    Completed
    ——严格来说,这不算“故障”,而是数据质量不佳。
  • 操作:使用
    external-data-schemas-resync
    清除已同步的数据,并从 source 重新导入。该操作具有破坏性,但通常是数据质量问题的正确选择。
E. Postgres 上的 CDC pipeline 已损坏
  • 错误提到 replication slot、publication 或 WAL。
  • 操作:使用
    external-data-sources-check-cdc-prerequisites-create
    找出损坏的项目,在 Postgres 端进行修复,然后调用
    external-data-schemas-reload
    。如果 WAL position 已丢失,有时不可避免地需要执行
    resync
    。
F. 同步卡在
Running
状态数小时
  • 检查
    last_synced_at
    。如果它已是数小时前且状态仍为
    Running
    ,则该 job 已成为孤儿任务。
  • 操作:使用
    external-data-schemas-cancel
    停止任务,然后调用
    external-data-schemas-reload
    。
G. Table 数据已损坏,但希望保留 schema 配置
  • 操作:使用
    external-data-schemas-delete-data
    删除已同步的 table,但保留 schema 条目。下次 reload 会从头重新导入数据,同时不会丢失已配置的
    sync_type
    /
    incremental_field
    。
H. 账单限制
  • 这不属于技术问题。解释具体限制,并建议升级套餐,或禁用优先级较低的 schema,使重要 schema 的数据量控制在配额以内。
I. 由 Webhook 驱动的 schema 未收到事件
  • 症状:schema 的
    sync_type
    为
    webhook
    ,初始批量同步已经完成,但即使 source 端有活动,仍没有新行到达。状态可能仍显示为
    Completed
    ,因为批量同步(兜底定时同步)成功了——真正的问题是推送链路。
  • 操作:
    1. external-data-sources-webhook-info-retrieve({source_id})
      。
    2. 如果
      exists: false
      → webhook 从未注册或已被删除。调用
      create-webhook-create
      注册 webhook。
    3. 如果
      exists: true
      ,但
      external_status.error
      已被设置 → 通常是
      API key doesn't have permission to read webhooks
      或类似错误。Webhook 可能已从 source 的 dashboard 中删除,需要重新创建。
    4. 如果
      external_status.status
      不是
      enabled
      → source 已禁用 webhook(通常发生在重复投递失败后)。重新启用或重新注册 webhook。
    5. 如果 payload 正在到达但签名验证失败 → signing secret 已被轮换。从 source 的 dashboard 获取新值,然后调用
      update-webhook-inputs-create({source_id}, {inputs: {signing_secret: "..."}})
      。
  • 完成任何修复后,检查 source 端的 webhook 日志,确认 PostHog 现在能够返回 2xx。

Step 5 — Confirm before destructive actions

步骤 5——执行破坏性操作前确认

Three recovery actions discard data and cannot be undone:
  • external-data-schemas-resync
    — wipes synced rows, re-imports from scratch
  • external-data-schemas-delete-data
    — drops the synced table
  • external-data-sources-destroy
    — deletes the source and all its schemas
Always present the fix you're proposing and wait for explicit approval before calling any of these. "Just try resync" is rarely the right default.
以下三种恢复操作会丢弃数据且无法撤销:
  • external-data-schemas-resync
    ——清除已同步的行并从头重新导入
  • external-data-schemas-delete-data
    ——删除已同步的 table
  • external-data-sources-destroy
    ——删除 source 及其所有 schema
始终应先说明准备执行的修复,并等待用户明确批准,然后再调用上述操作。“直接试试 resync”很少是正确的默认方案。

Example interaction

示例交互

text
User: "Our Stripe sync is broken, can you check?"

Agent:
- external-data-sources-list → find Stripe source, status = Error
- external-data-sources-retrieve({id}) → latest_error: "authentication failed: 401 Unauthorized"
- Report: "Your Stripe source's API key is no longer authenticating.
   All 8 tables under it are failing with 401s. This usually means the key was rotated on the Stripe side.

   To fix:
   1. Grab a fresh restricted API key from the Stripe dashboard.
   2. I'll update the source with the new key.
   3. Syncs will resume automatically — no data loss.

   Paste the new key here when ready."

User: "sk_live_..."

Agent:
- external-data-sources-partial-update({id}, {job_inputs: {stripe_secret_key: "sk_live_..."}})
- external-data-sources-reload({id}) to trigger retry
- Report: "Updated and re-triggered. Check back in a few minutes — latest_error should clear."
text
User: "Our Stripe sync is broken, can you check?"

Agent:
- external-data-sources-list → find Stripe source, status = Error
- external-data-sources-retrieve({id}) → latest_error: "authentication failed: 401 Unauthorized"
- Report: "Your Stripe source's API key is no longer authenticating.
   All 8 tables under it are failing with 401s. This usually means the key was rotated on the Stripe side.

   To fix:
   1. Grab a fresh restricted API key from the Stripe dashboard.
   2. I'll update the source with the new key.
   3. Syncs will resume automatically — no data loss.

   Paste the new key here when ready."

User: "sk_live_..."

Agent:
- external-data-sources-partial-update({id}, {job_inputs: {stripe_secret_key: "sk_live_..."}})
- external-data-sources-reload({id}) to trigger retry
- Report: "Updated and re-triggered. Check back in a few minutes — latest_error should clear."

Important notes

重要说明

  • Source status overrides schema status for diagnosis. If the source is
    Error
    , nothing under it will work; fixing the source usually fixes all its schemas at once.
  • Running
    isn't always healthy.
    Cross-check
    last_synced_at
    . A sync stuck in
    Running
    needs
    cancel
    then
    reload
    , not
    resync
    .
  • Resync is destructive. It discards synced data. Only recommend it when the data itself is bad (duplicates, missing rows, corrupt) or when recovery genuinely requires a clean slate (lost WAL position on CDC). Never use it as a first-try for transient errors.
  • Delete-data preserves config. When a user says "I just want to start this table over from scratch", prefer
    delete-data
    +
    reload
    over
    resync
    + new schema entry — it keeps the configured sync_type / incremental_field / PK setup.
  • A failure can belong to a destination, not the source. Some projects sync a source to their own database as well as to PostHog. When they do,
    latest_error
    is prefixed with the destination's name, e.g.
    customer postgres: connection refused
    . That is the customer's database refusing the write, not the source failing to extract — check their database and its credentials rather than the source's. Every destination shares one lifecycle, so one unreachable destination holds up the whole sync, PostHog included.
  • Billing limits aren't technical failures. Don't try to retry or reconfigure your way out. Route to billing.
  • Webhook failures can hide behind a green status. A webhook-type schema whose bulk fallback sync succeeded looks
    Completed
    even when the push channel is broken. When users say "my data is hours behind" on a webhook schema, call
    webhook-info-retrieve
    before looking at schema status. Webhook issues don't surface on
    external-data-schemas-list
    .
  • 诊断时,source 状态优先于 schema 状态。 如果 source 为
    Error
    ,其下的任何内容都无法正常工作;修复 source 通常可以一次解决所有 schema 的问题。
  • Running
    不一定代表健康。
    应交叉检查
    last_synced_at
    。卡在
    Running
    状态的同步需要先
    cancel
    再
    reload
    ,而不是
    resync
    。
  • Resync 具有破坏性。 仅当数据本身存在问题(重复、缺行、损坏),或恢复过程确实需要从干净状态开始(CDC 的 WAL position 丢失)时,才建议使用。绝不能将 transient error 的首选方案设为 resync。
  • Delete-data 会保留配置。 当用户表示“只想让这张 table 从头开始”时,应优先使用
    delete-data
    +
    reload
    ,而不是
    resync
    + 新建 schema 条目——这样可以保留已配置的
    sync_type
    /
    incremental_field
    / PK 设置。
  • 故障可能属于 destination,而不是 source。 某些项目除了同步到 PostHog,还会将 source 同步到自己的数据库。在这种情况下,
    latest_error
    会带 destination 名称前缀,例如
    customer postgres: connection refused
    。这表示客户的数据库拒绝写入,而不是 source 无法提取数据;应检查其数据库和凭证,而不是 source。每个 destination 都共享同一个 lifecycle,因此只要有一个 destination 不可达,整个同步(包括 PostHog)就会停滞。
  • 账单限制不属于技术故障。 不要尝试通过重试或重新配置来绕过限制,应转交账单团队处理。
  • Webhook 故障可能隐藏在绿色状态之下。 当 webhook 类型的 schema 批量兜底同步成功时,它会显示为
    Completed
    ,即使推送链路已经损坏。当用户表示 webhook schema 的“数据已经落后数小时”时,应先调用
    webhook-info-retrieve
    ,再查看 schema 状态。Webhook 问题不会出现在
    external-data-schemas-list
    中。