Data Contract Guardian

An AI agent that grounds data-reliability incidents in live Fivetran connector telemetry and BigQuery state, proposes ranked fixes, and runs human-approved remediation with re-validation.

Comment

Inspiration

Modern analytics and AI features sit on top of warehouse marts fed by ELT — typically Fivetran into BigQuery. When a schema drifts, a sync slips, or row volumes diverge, dashboards and downstream models break quietly. The breakage is usually discovered first by a business user looking at a wrong number, not by the data team. Engineers then lose hours stitching together connector telemetry, ad-hoc SQL checks, and tickets — and they repeat the same investigation the next time, because nothing durable was captured.

We wanted to see whether an agent could move past "chat about your data" into operational tool use: actually call the Fivetran connector tools, actually read the warehouse schema, ground its conclusions in that evidence, and then stop at a human-approval gate before doing anything with a side effect. Reliability is exactly the kind of high-stakes, audit-sensitive workflow where a purely autonomous agent is the wrong answer and a purely manual process is too slow — so it's a great fit for a human-in-the-loop agent.

What it does

Data Contract Guardian is a single reliability control plane for Fivetran → BigQuery pipelines:

  • Contract registry — you declare expectations in versioned YAML: freshness windows, required columns and types, row-volume floors, and semantic SQL assertions. The demo ships with a telecom network domain: cdr, data_session, cell_tower, network_alarm, and signal_sample across six contracts.
  • Validation engine — runs every check against mock or live BigQuery INFORMATION_SCHEMA and data, persisting each run with a structured checks[] breakdown. It normalizes BigQuery type aliases (INTEGER ≡ INT64, FLOAT ≡ FLOAT64) so it never raises false drift on equivalent types.
  • Multi-step agent — on a failure it runs PLAN → Fivetran MCP tools → validate → SYNTH (root cause) → PROPOSE (ranked remediations) → AWAIT. PLAN and the root-cause synthesis are authored by Gemini; the investigation is grounded in the three read-only Fivetran MCP tools (get_connection_details, get_connection_state, get_connection_schema_config).
  • Evidence-grounded incidents — every incident carries the MCP evidence bundles, a confidence score, a stakeholder summary, and remediations ranked and tailored to the specific failing checks (e.g. a SAFE_CAST MR for the exact drifted column, vs. resuming a stalled sync).
  • Human-in-the-loop remediation — nothing with a side effect runs until a human approves. The proposed action carries a SHA-256 fingerprint for idempotency. On approval the backend executes the top-ranked fix (mock warehouse refresh, Fivetran sync via REST, or advisory coordination), then re-validates with polling to confirm the incident closed.

How we built it

The agent runs on Google Cloud Agent Builder (ADK) with an McpToolset wired to the official Fivetran MCP server over stdio, so the model calls the real connector tools rather than a simulation. Gemini (gemini-2.5-flash on Vertex us-central1, with fallback model support) runs through the google-genai SDK and produces the plan, root-cause analysis, and stakeholder summary in strict JSON mode. The backend is FastAPI (Python 3.12) with SQLite for incident persistence (single Cloud Run instance); the frontend is Next.js 14 with client-side MCP discovery for fast initial load. Deployed to Cloud Run via Terraform; Fivetran credentials in Secret Manager; MCP read-only (FIVETRAN_ALLOW_WRITES=false); sync on approval uses Fivetran REST. The whole thing runs fully offline in mock mode for tests and CI, and flips to live Fivetran MCP + live BigQuery with environment flags.

Challenges we ran into

  • Gemini 3 + SDK mismatch. The legacy vertexai.generative_models SDK silently returned empty text for Gemini 3, so the agent fell back to template root-cause text on every incident. We rewrote the entire Gemini client on the google-genai SDK with JSON mode, a fallback model, and a robust parser that handles raw, fenced, and prose-wrapped JSON.
  • False schema drift. Contracts declared INTEGER/FLOAT, but BigQuery reports INT64/FLOAT64 — producing spurious "critical" incidents and a nonsensical "cast to INTEGER (currently INT64)" fix. We added type-alias normalization and numeric-family widening to the validation engine.
  • Grounding, not hallucinating. It would have been easy to let the model narrate a plausible root cause. Instead we constrained the agent to conclude only from MCP tool output, and we made evidence failure-aware: a freshness-only failure produces stale-sync evidence with no fabricated type mismatch, and vice-versa. A degraded MCP tool (one connector returns 405 on get_connection_state) is reported honestly as "not available — continuing with other tools" rather than faked.

Accomplishments that we're proud of

A genuinely closed-loop agent: it plans, calls live third-party tools, grounds its reasoning in their output, proposes ranked and column-specific fixes, waits for a human, and verifies the result — with idempotency and an audit trail throughout. And it's not a toy: live-tested end-to-end against real Vertex Gemini, the real Fivetran MCP server, a real Airtable-sourced connector, and live BigQuery.

What we learned

The hard and interesting part of an "agent" isn't the prompt — it's the discipline around it: grounding conclusions in tool evidence, refusing to fabricate when a tool degrades, normalizing the boundary between your contract language and the warehouse's type system, and putting a human gate in front of every side effect. Those constraints are what make the output trustworthy enough to act on.

What's next for Data Contract Guardian

  • Native Slack and GitHub/GitLab MR side effects behind the existing approval gate.
  • A contract authoring UI and auto-suggested contracts inferred from warehouse history.
  • More Fivetran MCP tools as they become available, and broader warehouse support beyond BigQuery.

Built With

  • artifact-registry
  • bigquery
  • cloud-run
  • docker
  • fastapi
  • fivetran
  • fivetran-mcp
  • gcp
  • gcp-agent-builder
  • gemini
  • google-genai
  • mcp
  • next.js
  • python
  • secret-manager
  • stdio
  • terraform
  • typescript
Share this project:

Updates