Build a Scheduled Cross-System Reconciliation Agent

A reconciliation agent flips that: a scheduled job compares source and warehouse aggregates daily, and when they drift, an LLM explains the drift in context — which segments, since when, correlated with which pipeline events — and files a ticket with evidence attached. The critical design decision is the division of labor: deterministic SQL detects, the LLM only explains, and every number in the ticket comes from the queries, not the model.

When to reach for this

Use this for source-to-warehouse pairs where silent drift has real cost — billing and finance tables, regulatory reporting feeds, CDC-replicated operational tables — and where today's detection method is "someone eventually notices."

Triggers:

• •
  A drift incident was discovered late, and the postmortem says "we should check this continuously."
• •
  CDC or batch replication whose failure mode is partial, not loud.
• •
  Month-end reconciliation is a manual ritual you want continuous and evidenced.

The workflow

1. 1.

   Define the comparison contract per table pair. Key metrics (row counts, sums of critical measures, max timestamps), the comparison grain (daily by business date, plus 2–3 segment dimensions), the lag allowance (replication delay that is normal), and exact-match vs explained-tolerance per metric. Write this as config, not code — the agent should take on new tables by config entry.

2. 2.

   Build the deterministic detection layer first — no LLM yet. A scheduled task (Airflow, Dagster, or plain cron) runs paired aggregate queries and lands results in a reconciliation table:

   Copy

   -- executed against each system, results landed side by side
   SELECT business_date, region,
          COUNT(*)     AS row_count,
          SUM(amount)  AS total_amount,
          MAX(updated_at) AS max_updated
   FROM orders
   WHERE business_date >= CURRENT_DATE - INTERVAL 7 DAY
   GROUP BY business_date, region;
   

   A pure-SQL check then flags any cell where the systems disagree beyond the contract. Run this alone for a few days: if detection is noisy or wrong, an LLM on top will be confidently noisy and wrong.

3. 3.

   Add the LLM explanation stage with structured output. When drift is flagged, the agent assembles context — the drifted cells, 30 days of drift history for the pair, and recent pipeline events (DAG runs, deploys) — and requests a structured explanation:

   Copy

   from anthropic import Anthropic
   from pydantic import BaseModel
   
   class Hypothesis(BaseModel):
       cause: str
       mechanism: str
       confirming_query: str
       confidence: str  # "high" | "medium" | "low"
   
   class DriftExplanation(BaseModel):
       summary: str
       affected_scope: str          # segments/dates, from the evidence
       started_at: str              # first drifted business_date
       hypotheses: list[Hypothesis] # ranked
       severity: str                # "page" | "ticket" | "log"
   
   client = Anthropic()
   response = client.messages.parse(
       model="claude-opus-4-8",
       max_tokens=2000,
       thinking={"type": "adaptive"},
       messages=[{
           "role": "user",
           "content": (
               "You are explaining a data reconciliation drift. Use ONLY "
               "the evidence provided; every number you cite must appear "
               "in it. Rank hypotheses; each needs one confirming SQL "
               "query.\n\nDrifted cells:\n" + drift_json +
               "\n\nDrift history (30d):\n" + history_json +
               "\n\nPipeline events (72h):\n" + events_json
           ),
       }],
       output_format=DriftExplanation,
   )
   explanation = response.parsed_output
   

4. 4.

   Verify the explanation before it ships — inside the agent. Two automated gates, run inline: every numeric claim in summary and affected_scope is checked against the drift table (a regex-extracted-numbers-must-exist-in-evidence check is crude and effective), and each hypothesis's confirming_query is validated as read-only and syntactically runnable. Optionally the agent executes the top hypothesis's confirming query (read-only role, row limit) and attaches the result. Explanations failing the gates ship as raw drift data with a "no verified explanation" note — degraded output beats fabricated output.

5. 5.

   File the ticket with evidence attached. The ticket body: contract violated, drifted cells table, drift-since date, the verified explanation, and the confirming queries ready to run. Severity routes per the contract (finance pair drifting = page; low-stakes pair = weekly digest). The agent never fixes anything — reconciliation agents that auto-repair are how small drifts become large ones with no audit trail.

6. 6.

   Tune on a burn-in period. Two weeks of shadow operation: every flag reviewed by a human, verdicts recorded (real/noise), lag allowances and tolerances adjusted from the verdict log. Only then does it page anyone. Revisit quarterly with the same verdict-replay method.

Verify before you trust it

• •
  Seeded-drift test: manually corrupt a staging copy (delete 50 rows in one region-date), run the full agent path, confirm detection, correct scope in the explanation, and a ticket with accurate numbers.
• •
  Numeric-claims gate demonstrated: feed the explainer a doctored evidence pack and confirm invented numbers are caught and the fallback path engages.
• •
  Confirming queries verified read-only against a locked-down role; the agent's warehouse credentials cannot write anywhere except its own reconciliation schema.
• •
  Lag allowance validated against real replication-delay history — the agent must not flag normal lag as drift (check the burn-in verdict log: zero "normal lag" false alarms in the final week).
• •
  Idempotency: a rerun for the same window updates the same ticket rather than filing duplicates.

Pitfalls

• LLM in the detection path. Asking a model "do these numbers match?" is strictly worse than SQL in accuracy, cost, and auditability. Detection is arithmetic; explanation is where language models belong.
• Timezone and cutoff mismatches masquerading as drift. Source aggregates by local-time day, warehouse by UTC — permanent phantom drift at day boundaries. Normalize business-date definitions in the contract before the first run.
• Explanations citing numbers not in evidence. Fluent summaries invent totals when context is thin. The numeric-claims gate plus structured output keeps every cited figure traceable.
• Alert fatigue from tolerance zero. Exact-match on a pair with known replication lag pages someone nightly until the agent is muted. Lag allowances and burn-in tuning are what make it live past month one.
• Scope creep into auto-remediation. "It found the drift, why not fix it?" Because a wrong auto-fix on a finance table is a far worse incident than a late one. Ticket with evidence; humans change data.

Tools

• Airflow Common AI provider / airflow-ai-sdkLLM calls as first-class DAG tasks with retries and backoff; the natural home for the scheduled agent in an Airflow shop.
• Claude API (structured outputs) + MCPthe explanation stage above; MCP warehouse tools let the agent run its confirming queries under a governed, read-only role.
• Great Expectations / Sodaan alternative packaging for the detection layer's contracts when you want reconciliation results in your existing quality dashboards.

Client-safe variant

Reconciliation evidence is aggregate by construction — counts and sums by date and segment — which makes this agent unusually easy to run client-safe: keep detection entirely in-warehouse, and route the explanation stage through Bedrock or Azure OpenAI provisioned in the client's tenancy so evidence packs never cross the boundary; a local Ollama model can serve the explainer for lower-stakes pairs. Ensure segment dimensions in the contract are non-identifying (region, product line — never customer-level), scrub free-text pipeline-event descriptions before they enter the evidence pack, and file tickets in the client's own tracker under their access controls.