From RAG to Live Data: Building a BigQuery-Connected Agent with Google ADK and MCP

Why This One Was Different

A week earlier, I'd shipped a RAG-powered coffee barista agent — an agent that answered questions by retrieving from a static, curated menu file. That project taught me the RAG pattern end to end: grounding a model's answers in a known, trusted source rather than letting it improvise.

This codelab asked a different question entirely: what happens when the "knowledge base" isn't a file you control, but a live, queryable data warehouse? That's the problem BigQuery MCP is built to solve, and building this agent is what finally made the distinction between retrieval and action click for me — not as a slide-deck concept, but as two genuinely different architectures I'd now built with my own hands.

What I Built

An ADK LlmAgent, powered by Gemini, connected to Google's fully managed BigQuery MCP server — giving it the ability to explore a real dataset's schema, form its own query plan, write SQL, execute it read-only, and reason over the results in natural language. No hand-rolled query logic, no pre-built API wrapper around BigQuery. Just an agent with tools and a clear system instruction.

The target dataset: bigquery-public-data.new_york_citibike — years of real Citi Bike trip and station records for the NYC area.

The Architecture, Piece by Piece

Model Context Protocol — the connective layer

MCP (Model Context Protocol) standardizes how an LLM-powered agent reaches external tools and data sources. Instead of writing a custom integration for every service an agent might need, MCP gives you one consistent interface. The BigQuery MCP server is Google's fully managed implementation of this for BigQuery specifically — it exposes a defined set of tools (list tables, describe schema, run read-only SQL) that any MCP-compatible agent can call, without Google or the agent developer having to manage that server's infrastructure.

This is the detail that made the "USB-C for AI" analogy stop being a marketing line and start being obviously true: my agent code never talks to BigQuery's native API directly. It talks to an MCP toolset, and that toolset happens to be backed by BigQuery today — but the agent's reasoning logic doesn't know or care about that implementation detail.

Setting up the agent — agent.py

The core of the build lives in a single LlmAgent definition. A few pieces worth calling out:

Authenticating with the agent's own identity, not a static key. Rather than embedding an API key, the agent fetches Application Default Credentials (ADC) and refreshes them as needed:

application_default_credentials, project_id = google.auth.default()
application_default_credentials.refresh(Request())

It then builds its own auth header provider function for MCP requests:

def _adc_auth_header_provider(context=None) -> dict[str, str]:
    if not _application_default_credentials.valid:
        _application_default_credentials.refresh(_request)
    return {
        "Authorization": f"Bearer {_application_default_credentials.token}",
        "x-goog-user-project": project_id,
    }

This was, honestly, the most instructive part of the entire codelab. It's a small piece of plumbing, but it's exactly the kind of detail that separates a toy demo from something you'd trust with real data access — the agent authenticates as itself, using credentials that get validated and refreshed on every request rather than a token that's valid until someone remembers to rotate it.

Scoping the toolset deliberately, not broadly. The MCPToolset is initialized with an explicit tool_filter:

bigquery_toolset = MCPToolset(
    connection_params=StreamableHTTPConnectionParams(
        url="https://bigquery.googleapis.com/mcp",
        headers=_adc_auth_header_provider,
        tool_filter=[
            'get_dataset_info',
            'list_table_ids',
            'get_table_info',
            'execute_sql_readonly',
        ]
    )
)

Note what's not in that list: anything that could write, modify, or delete data. execute_sql_readonly is a deliberate guardrail — the agent can investigate and analyze, but it structurally cannot mutate the dataset it's exploring. That's a security decision encoded directly into the tool surface, not a policy hoped-for after the fact.

A system instruction that enforces investigation before assumption. The prompt explicitly tells the agent to verify data structure before reasoning about it:

"DO NOT MAKE ASSUMPTIONS ABOUT DATA (structure, type, values, relationships) BASED ON YOUR PRIOR KNOWLEDGE. ALWAYS VERIFY YOUR ASSUMPTIONS."

This single line does a lot of work. LLMs are very good at confidently guessing what a "citibike" table probably looks like based on training data — and just as capable of guessing wrong about column names, types, or the specific slice of data available. Forcing the agent to call list_table_ids and get_table_info before writing any SQL turns "plausible-sounding" into "actually verified against the live schema."

The folder structure ADK expects

data_agent/
    __init__.py
    agent.py
    requirements.txt

__init__.py just needs an import statement so the package resolves correctly:

from . import agent

requirements.txt pins the essentials — google-adk for the framework itself, and mcp for the Model Context Protocol client library.

Testing locally before deploying

ADK ships with adk web, a local interactive interface for developing and debugging agents before they ever touch production infrastructure:

uv tool run --with "mcp==1.29.*" --from "google-adk[mcp]==2.4.*" adk web --allow_origins="*" --port 8080 .

I opened the local UI and asked the simplest possible question — "What data do you have?" — and watched the agent call list_table_ids and get_table_info against the live Citibike dataset before answering. Seeing that tool-call loop happen in real time, rather than trusting it blindly, is a habit I'm carrying over from two decades of program delivery: verify the thing works before you ship it, not after.

Deploying to Cloud Run

The deploy step used the ADK CLI directly, wrapping the agent in a web UI:

uv tool run --from google-adk==2.4.0 \
  adk deploy cloud_run \
  --with_ui \
  --project $GOOGLE_CLOUD_PROJECT \
  --region $GOOGLE_CLOUD_REGION \
  --service_name bq-data-agent \
  --app_name data_agent \
  data_agent \
  -- \
  --allow-unauthenticated \
  --max-instances 1 \
  --labels dev-tutorial=codelab-cloud-run-adk-gemini-bq-mcp \
  --set-env-vars GOOGLE_GENAI_USE_ENTERPRISE=True,GOOGLE_CLOUD_PROJECT=${GOOGLE_CLOUD_PROJECT},GOOGLE_CLOUD_LOCATION=${GOOGLE_CLOUD_LOCATION}

A few minutes later, a live, publicly reachable agent:

🔗 bq-data-agent-330208986773.asia-south1.run.app

Putting It to the Test

The codelab's suggested prompt is deceptively simple, and a good stress test of everything above:

"We have budget for 3 coffee trucks. We want to find the best city bike stations to place our coffee trucks."

There's no SQL here — just a business question. Watching the agent work through it end to end was the payoff for the whole build: it investigated the dataset's schema and dimensions first (per its own system instruction), formed a plan, wrote SQL to identify high-traffic stations, ran it read-only, and came back with a reasoned shortlist — all without me writing a single query myself.

What This Taught Me, Building on the RAG Agent

Doing this one week after the coffee-barista RAG build turned out to be the more valuable sequencing than I expected — the contrast is what made both patterns click:

• RAG grounds an agent in a fixed, curated source. My barista agent's "knowledge" was a JSON menu I wrote and controlled. Comprehensive within its scope, but static — the agent could never know more than what I'd put in that file.
• MCP connects an agent to a live, authoritative system. This agent's "knowledge" is whatever's actually in BigQuery right now — no file to maintain, no retrieval index to keep in sync, but a correspondingly larger responsibility around access scope and authentication.
• The reasoning layer stays constant; the grounding mechanism is what changes. Both agents use an ADK LlmAgent with tools and a system instruction. The real engineering decision isn't "RAG or MCP" as competing techniques — it's matching the grounding mechanism to whether your source of truth is static and curated, or live and queryable.
• Guardrails belong in the tool surface, not just the prompt. A system instruction can ask an agent to behave safely; scoping the toolset to execute_sql_readonly guarantees it structurally can't do otherwise. That distinction matters a lot once you're imagining this pattern applied to real enterprise data rather than a public Citibike dataset.

Where I'd Take It Next

A few extensions worth exploring now that this is live:

• Combining both patterns in a single agent — RAG over a curated policy/knowledge document, MCP for live transactional or analytical data, orchestrated together
• Adding basic observability around which queries the agent generates and how often it needs to self-correct after schema exploration
• Testing the same architecture against a private, enterprise-scoped BigQuery dataset rather than a public one, with tighter IAM boundaries around the service identity

Closing Thought

The coffee-barista agent taught me how to ground a model in what I already know. This one taught me how to let a model responsibly investigate what I don't — and know the difference between the two well enough to pick the right tool for the job. That's a more useful skill than either pattern on its own, and it's exactly the kind of thing that only becomes obvious once you've actually built both.

Built as part of Gen AI Academy [Track 2] Turn Business Data into Strategic Decisions, Hack2Skill × Google Cloud. Deployed agent: bq-data-agent-330208986773.asia-south1.run.app