AI Readiness Agent - Fivetran

Fivetran lands the data. We make sure it stays AI-ready — detecting schema drift, classifying blast radius, and gating every fix behind human approval.

Comment

Inspiration

"The dirty secret of AI is that the hardest part is the data." — Ali Ghodsi, CEO Databricks

AI agents that query warehouses loaded by Fivetran break silently when the data underneath them changes. A column gets renamed upstream, a type is promoted, a deprecated field vanishes — and the downstream agent's SQL starts hallucinating or erroring against stale assumptions. There is no native Fivetran alert for this. The data engineer only finds out when a dashboard breaks or an AI pipeline fails at 2 AM.

But schema drift is just one face of a broader problem. Fivetran's own research finds that only 7% of enterprises are AI-ready. The gaps are structural: ungoverned sensitive columns, semi-structured JSON blobs that LLMs can't query, data silos where the same real-world entity lives in three different source systems under three different names, stale data served to latency-sensitive AI consumers, and pipelines that fail with no automated diagnosis.

We built the Fivetran AI-Readiness Agent to close that loop — an AI agent that watches over everything Fivetran lands and makes sure it stays fit for the downstream AI consumers depending on it.

What It Does

The agent delivers three tracks of capability, all live:

Track 1 — Schema-Drift Detection & HITL(Human In The Loop) Remediation

A Cloud Run webhook receiver listens for Fivetran sync_end events. On every sync it snapshots the landed BigQuery schema, diffs it against the prior baseline, and hash-gates on no-change (cheap exit — 99% of syncs produce nothing). When drift is detected:

  1. Classify — each change (RENAME / TYPE_PROMOTION / REORDER / NEW_FIELD / DEPRECATION) goes to Gemini Flash, which assigns a change type, confidence score, blast-radius rationale, and a BigQuery VIEW-shim SQL that preserves the downstream contract.
  2. Propose — the finding is written to drift_events as a PROPOSED row with the full audit trail.
  3. Review — the ADK agent surfaces PROPOSED rows in the Agent Runtime playground. The reviewer approves or rejects in natural language.
  4. Remediate — on approval, the agent calls the Fivetran MCP to register the VIEW shim as a Fivetran-managed transformation. Every write tool is gated by require_confirmation=True — the agent cannot touch production without an explicit human signal.
  5. Verify — the reviewer marks the event VERIFIED. drift_events becomes a complete audit trail from detection to resolution.

Track 2 — Multi-Connection Support & Freshness SLA Monitor

Each Fivetran connection resolves to its own BigQuery dataset via the Fivetran REST API (GET /v1/connectors/{id}), cached in-process. No redeployment needed when new connections are added.

Every successful sync_end is recorded in sync_log before the hash gate, so freshness data is captured even when the schema is unchanged. check_freshness_sla and list_freshness_status let you ask "is my data fresh enough?" for a single connection or the entire fleet, with a per-call SLA threshold override for downstream consumers with tighter latency requirements.

Track 3 — Gemini-Powered AI-Readiness Analysis

Eight new FunctionTools, each addressing a specific AI-readiness gap from Fivetran's research:

Tool What it answers
score_ai_readiness What is this connection's AI-readiness grade (A–F)?
list_readiness_scores Which connections are the least AI-ready, and why?
analyze_drift_volatility Which pipelines are STABLE vs VOLATILE vs CRITICAL?
generate_schema_docs What does each column mean, in plain English?
classify_column_sensitivity Which columns are PII / FINANCIAL / HEALTH, and how should they be masked?
list_sensitive_columns What sensitive data is exposed across my entire fleet?
audit_use_case_coverage Do I have the data I need to build this AI use case? What's missing?
detect_json_columns Which columns hold semi-structured data that should be flattened?
generate_json_flattener Generate a BigQuery VIEW to flatten this JSON column into typed columns.
detect_entity_overlaps Are the same real-world entities siloed across multiple connections?
diagnose_sync_failures Why is this pipeline failing, and how do I fix it?

How I Built It

Stack:

  • Google ADK 1.x — single LlmAgent with McpToolset (split read/write for confirmation gating) + 17 FunctionTool wrappers across Track 1/Track 2/Track 3
  • Fivetran MCP (fivetran/fivetran-mcp) — 14 tools: 8 read (no confirmation), 6 write (confirmation-gated)
  • Gemini Flash (gemini-flash-latest) via Gemini Enterprise Agent Platform — drift classifier, remediation SQL, and all v3 analysis tools
  • BigQuery — 7 state tables: schema_snapshots, column_snapshots, drift_events, sync_log, json_flattener_log, entity_map, sync_failure_log
  • Cloud Run — webhook receiver with HMAC-SHA-256 verification, fire-and-forget daemon thread, min-instances=1 to prevent cold-start timeouts on Fivetran webhook tests
  • Gemini Enterprise Agent Platform (Agent Runtime) — deployed via agents-cli deploy; reasoningEngines/2248457298336808960 (us-east1)
  • GCP Secret Manager — Fivetran credentials accessed at runtime via _secret_or_env() helper; no .env in the container
  • Cloud Build — deploy/cloudbuild.yaml applies all DDL and redeploys the receiver in a single command

Key architectural decisions:

Single LlmAgent, not SequentialAgent. Per-webhook detection is deterministic Python — running it through LLM-driven sub-agents would burn Gemini credits on mechanical steps. The LLM handles only the conversational HITL surface.

Two McpToolsets. ADK 1.x calls require_confirmation predicates with **tool_input_args (the LLM's input args), not the tool object — so a predicate cannot inspect the tool name. Splitting into fivetran_mcp_reads (require_confirmation=False) and fivetran_mcp_writes (require_confirmation=True) is the correct ADK 1.x pattern.

Challenges

No native schema-change event in Fivetran. Fivetran supports sync_end, sync_start, and connection lifecycle webhooks — but no schema_changed. We built warehouse-side snapshot diffing: snapshot → content hash → column diff → per-change Gemini classification. The hash gate is load-bearing for cost: 99% of syncs exit in milliseconds.

ADK 1.x require_confirmation predicate limitation. The intended design used a single McpToolset with a callable predicate that inspected the tool name. ADK 1.x passes **tool_input_args to the predicate — the tool name is inaccessible at call time. Discovered by reading mcp_tool.py source. Solution: two toolsets with boolean require_confirmation.

Source DROP COLUMN is soft-dropped at destination. Fivetran marks the column reason_code: DELETED but keeps the BQ column populated with NULLs. Exercising the DEPRECATION detection path requires removing the column directly from BigQuery.

Accomplishments

  • Two real drift events captured end-to-end: NEW_FIELD (2026-05-25 20:51) and DEPRECATION (2026-05-25 21:36) on a live Fivetran connection. Both driven from PROPOSED → VERIFIED in a single HITL session.
  • Complete audit trail in drift_events with closed-loop hash proof (bootstrap → ADD → DROP → bootstrap visible in schema_snapshots).
  • Confirmation gate proven on Agent Runtime: adk_request_confirmation event with confirmed: false emitted before sync_connection executes — gate fires at the ADK protocol layer on every write-tool call.
  • More on the TEST.md on the project repo: https://github.com/qmandev/fivetran-ai-readiness-agent/blob/main/TEST.md

What I have Learned

  • Fivetran's MCP requires every tool call to pass the exact OpenAPI schema file path as an explicit parameter — a built-in acknowledgement gate in the MCP design. Listing all paths in system_instructions.md once means the LLM fills them in automatically forever.
  • ADK's require_confirmation=True fires correctly at the ADK protocol layer on all three surfaces (adk run, adk web, Agent Runtime). The visual Approve/Reject widget is a hosting-layer feature; the adk_request_confirmation event in the playground event panel is the evidence.
  • TYPE_PROMOTION in Fivetran's PostgreSQL connector causes a complete table column reorder at the destination (all ordinals shift, not just the promoted column). ordinal_position is an unreliable signal under TYPE_PROMOTION and must be treated as advisory context for Gemini, not a hard filter.

What's Next

  • Logical replication — the Hackathon demo uses Fivetran's Query-Based incremental sync (fastest setup). Logical replication is the production-grade mode; it changes detection latency characteristics and the soft-drop behavior.
  • agents-cli scaffold upgrade (0.2.1 → 0.3.0) — deferred post-submission.

Built With

  • cloud-run
  • fivetran
  • fivetran-mcp
  • gemini-flash
  • google-adk
  • google-bigquery
  • google-cloud-bigquery
  • google-cloud-secret-manager
  • google-genai
Share this project:

Updates