Data Engineering Copilot

Build and monitor data pipelines with plain English; an AI-powered copilot powered by Gemini and Fivetran.

Comment

Inspiration

Modern data teams spend a surprising amount of time not analyzing data. Before a single dashboard exists, someone has to pick connectors, design schemas, decide between full and incremental syncs, configure schedules, wire credentials, and then babysit the whole thing for failures and schema drift. Tools like Fivetran brilliantly automate the execution of pipelines — but humans still drive almost every decision through dashboards, tickets, and tribal knowledge.

We kept asking ourselves one question:

What if you could just say what data you need and where it should land — and an agent handled the design, the build, and the monitoring?

That question became the Data Engineering Copilot: a natural-language front door to production data pipelines, with Gemini as the brain and Fivetran as the execution layer.

What it does

You type something like "Sync Salesforce and Stripe to BigQuery daily," and the copilot:

  1. Understands the intent — sources, destination, frequency, joins.
  2. Designs a pipeline pattern — schema shape, incremental vs. full sync, validation.
  3. Reviews the plan for risk and security before anything is created.
  4. Asks for human approval through a Connect Card checklist — humans stay in the loop.
  5. Executes via the Fivetran MCP server — creating connections, running setup tests, triggering the initial sync.
  6. Reports back in a live dashboard with connector health, pipeline state, and sync status.

The guiding principle: the agent proposes, the human approves, Fivetran executes.

How we built it

The system is four cooperating layers, all deployable to Google Cloud:

User (natural language)
        │
        ▼
  Next.js UI (Cloud Run)  ──►  FastAPI backend (Cloud Run)
                                        │
                                        ▼
                          Vertex Agent Runtime (ADK + Gemini)
                                        │
                              MCP (Streamable HTTP)
                                        ▼
                          Fivetran MCP server (Cloud Run)
                                        │
                                        ▼
                          Fivetran API → BigQuery
  • Frontend — a Next.js copilot dashboard acting as a BFF, holding the session in an httpOnly cookie so the browser never touches Fivetran keys or raw JWTs.
  • Backend — a FastAPI service that proxies runs, streams ADK events, mints per-run tokens, and maps tool results into a structured dashboard snapshot.
  • Agent — an ADK root agent on Vertex Agent Runtime, orchestrating sub-agents for architecture planning, data-quality and risk review, a human approval gate, and the final build report.
  • Fivetran MCP — an MCP server exposing connector lifecycle and sync operations, runnable as local stdio, local HTTP, or production Streamable HTTP on Cloud Run.

For security, per-user Fivetran credentials live in Secret Manager (fivetran-{user_sub}-api_key). The agent never sees raw keys — instead, each copilot message carries a short-lived run token (X-Decopilot-Run-Token) that the MCP server resolves into the right user's credentials at call time:

$$ \text{run token} = \text{HMAC}_{\text{INTERNAL_JWT_SECRET}}(\text{user_sub},\ \text{run_id},\ \text{exp}) $$

so a leaked token is useless after expiry and is scoped to a single user and run. Everything ships through path-filtered GitHub Actions (PRs run tests; merges deploy to Cloud Run and Agent Runtime), with infrastructure provisioned by Terraform (APIs, IAM, Workload Identity Federation, Artifact Registry, Cloud Run shells).

Challenges we ran into

  • Region and resource wiring. Agent Runtime engines are region-pinned, and the backend's AGENT_RUNTIME_RESOURCE / AGENT_ENGINE_LOCATION had to match exactly where the reasoning engine was deployed — a subtle source of "works locally, 404s in prod" bugs.
  • Streaming + state mapping. Turning a stream of ADK tool-call events into a coherent, pollable dashboard snapshot (metadata.pipeline_state) took several iterations to get right.
  • Approval flow plumbing. Coordinating the Connect Card authorization between the backend broker and the MCP write path — while keeping secrets out of the browser — was the trickiest integration.
  • Credential isolation. Designing around "the agent must never hold a long-lived secret" forced us into run tokens and Secret Manager early, which reshaped the architecture but made it far safer.
  • Ephemeral run history. Runs are currently held in memory on the backend and lost on Cloud Run restart.

Accomplishments that we're proud of

  • A working end-to-end demo path: hosted web UI → backend → Vertex Agent Runtime → Fivetran MCP → BigQuery.
  • Security we'd actually trust in front of real connectors — Google OAuth, httpOnly session cookies, per-user secrets in Secret Manager, and short-lived run tokens, with no Fivetran keys ever reaching the browser.
  • A genuine human-in-the-loop approval gate (bulk secure authorization via Connect Cards) before any Fivetran write.
  • MCP as a clean seam that let us swap transports (stdio → HTTP → Streamable HTTP on Cloud Run) without touching agent logic.
  • A real CI/CD + IaC setup — Terraform-provisioned infra and path-filtered GitHub Actions deploying four independent services.

What we learned

  • MCP is the right seam. Putting Fivetran behind an MCP server — rather than baking the API into the agent — kept the agent as the brain and MCP as the nervous system.
  • Credential isolation changes the architecture. Treating "no long-lived secrets in the agent" as a hard constraint led us to run tokens and Secret Manager, making the system safe to demo with real accounts.
  • Structured handoffs beat free text. Forcing sub-agents to exchange structured JSON (plans, approval payloads, pipeline state) made dashboard mapping and approval modals far more reliable than parsing prose.
  • Humans in the loop is a feature, not a limitation. An approval gate before any write turned the agent into something trustworthy in front of production connectors.

What's next for Data Engineering Copilot

  • Secure headless credential forms for API-key connectors.
  • Persistent run history (today it's in-memory and lost on restart).
  • SDK and CLI interfaces sharing the exact same agent brain — chat today, automation tomorrow.
  • Live BigQuery validation and schema-drift advisors beyond the current sub-agent stubs.

Built With

Share this project:

Updates