agent failed error when attempting to reconcile duplicate keys across warehouses

TL;DR

Reconcile from key listings, not full tables: have the agent pull the key sets and hashes per warehouse, diff the keys in set operations, and only fetch full rows for the mismatched keys. Cross-warehouse reconciliation that ships whole tables to the agent will fail on size and time long before it finds the duplicates that matter.

agent failed error when attempting to reconcile duplicate keys across warehouses

Use this when

• The check or run fails with this symptom: the agent errors during a cross-warehouse duplicate-key reconciliation.
• You are working on an agent failing while reconciling duplicate keys across warehouses and the failure repeats on the same data or configuration.
• You need the failing rows, rule, or stage isolated before changing thresholds or code.

Not for this skill when

• Reconciling within one warehouse, where set operations run locally and cheaply instead.
• The error text names a different tool, stage, or root cause. Match the verbatim error first and use that skill.

Steps

1. Define the reconciliation precisely: the business key, the warehouses, and what counts as a conflict. Verify: the key and conflict definition are written down
2. Pull per-warehouse key lists with a row hash and a load timestamp, in paged queries. Verify: key lists with hashes are collected per warehouse
3. Diff the key sets to find keys missing on one side and keys whose hashes differ. Verify: mismatched keys are enumerated
4. Fetch full rows only for mismatched keys and decide the winning record by the agreed source-of-truth rule. Verify: conflicts are resolved per the stated rule
5. Apply the reconciliation as targeted merges and rerun the key diff to confirm both sides agree. Verify: the rerun diff shows no unexplained mismatches

Variant phrasings

agent failed reconcile duplicate keys across warehouses

The verbatim cross-warehouse phrasing.

cross warehouse key diff hash reconciliation agent

The key-diff phrasing for set-based reconciliation.

duplicate keys two warehouses source of truth merge

The source-of-truth phrasing for conflict resolution.

Compatibility: Agent reconciliation of duplicate keys across Snowflake, BigQuery, or Postgres warehouses

Why it happens

Reconciliation is a set problem, but agents often implement it as row shipping because that is the straightforward pattern. At warehouse scale the straightforward pattern fails on transfer and context long before the logic gets a chance to be right, while key-and-hash diffs stay small at any table size.

Edge cases / pitfalls

• Hash the same column set on both sides with the same function semantics, or every row looks like a conflict.
• Keep the mismatched-key list as the run's artefact; it is the audit trail for every applied merge.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_6kKR7ZlkvzUqeUFyDJ51Gw