Serverless BigQuery MCP Agent with Gemini" published: true tags: googlecloud, gemini, ai, python

Demystifying Data Decisions: Building a Serverless BigQuery MCP Agent with Gemini & Google ADK

---

title: "Demystifying Data Decisions: Building a Serverless BigQuery MCP Agent with Gemini & Google ADK"
published: true
tags: googlecloud, gemini, ai, python

description: "Learn how to build and deploy a serverless BigQuery MCP data agent using Google ADK, Gemini, BigQuery, and Cloud Run."

In today's data-driven world, the gap between having raw business data and extracting strategic value is often wider than it should be. Traditionally, analyzing business data required deep SQL expertise, context switching between tools, and manual dashboard creation.

But what if you could interact with your database using natural language, allowing a specialized AI agent to formulate plans, write SQL, dry-run queries, and extract insights automatically?

In this post, I will walk you through how I built and deployed a BigQuery Model Context Protocol (MCP) Agent using Google's Agent Development Kit (ADK) and Gemini, running entirely serverless on Google Cloud Run.

---

The Architecture: Bringing Gemini to BigQuery via MCP

The Model Context Protocol (MCP) is an open standard that allows large language models (LLMs) like Gemini to interface securely with external data sources.

Instead of writing custom API wrappers for every database operation, we configure the agent with an MCP toolset pointing to the Google BigQuery MCP server.

Here's how the flow works:

graph TD
    User([User Question]) --> ChatUI[Cloud Run Web UI]
    ChatUI --> Agent[Gemini Agent]
    Agent --> MCP[BigQuery MCP Toolset]
    MCP --> BQ[BigQuery API]
    BQ --> Insights[Structured Insights / Table]
    Insights --> User

1. Natural Language Query: The user asks a business question, such as "Where should we place coffee trucks based on ride-sharing patterns?"
2. Dynamic Schema Investigation: The agent calls list_table_ids and get_table_info to inspect the available datasets and understand their schemas.
3. Plan & Query Generation: The agent formulates a plan, performs a dry run to verify SQL correctness, and executes the query using execute_sql_readonly.
4. Insight Synthesis: The results are interpreted and returned to the user as a clean Markdown response with tables, recommendations, and reasoning.

---

Step 1: Writing the Agent Logic (agent.py)

Using Google ADK, we can bundle authentication, the MCP connection, and model configuration into a relatively small Python application.

We use Application Default Credentials (ADC) to establish an IAM identity context, ensuring the agent can only access Google Cloud resources that its identity is authorized to use.

Here is the core agent.py:

import os

import google.auth
from google.auth.transport.requests import Request

from google.adk.agents import LlmAgent
from google.adk.tools.mcp_tool.mcp_toolset import McpToolset
from google.adk.tools.mcp_tool.mcp_session_manager import (
    StreamableHTTPConnectionParams,
)


# 1. Authenticate using ADC (Application Default Credentials)
_application_default_credentials, project_id = google.auth.default()
_request = Request()

_application_default_credentials.refresh(_request)

project_id = os.getenv("GOOGLE_CLOUD_PROJECT", project_id)


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,
    }


# 2. Connect the BigQuery MCP Toolset
bigquery_toolset = McpToolset(
    connection_params=StreamableHTTPConnectionParams(
        url="https://bigquery.googleapis.com/mcp",
        tool_filter=[
            "get_dataset_info",
            "list_table_ids",
            "get_table_info",
            "execute_sql_readonly",
        ],
    ),
    header_provider=_adc_auth_header_provider,
)


# 3. Define the agent's reasoning flow
system_instruction = """
You are a helpful assistant that can answer questions about data in BigQuery.

Your data is in the `bigquery-public-data.new_york_citibike` dataset
(NYC Citi Bike trips).

Plan of action:
0. Analyze the dataset schema.
1. Formulate a plan and run a Dry Run to verify SQL correctness.
2. Query the data using execute_sql_readonly.
3. Retrieve the data and display a clean Markdown summary.
"""


root_agent = LlmAgent(
    model="gemini-3.6-flash",
    name="data_agent",
    instruction=system_instruction,
    description="A helpful assistant that can answer questions using BigQuery data.",
    tools=[bigquery_toolset],
)

And our requirements.txt:

google-adk==2.4.*
mcp==1.29.*

The important part here is the McpToolset. Instead of giving the model unrestricted access to BigQuery, we explicitly expose the tools it needs:

• get_dataset_info — inspect dataset metadata
• list_table_ids — discover available tables
• get_table_info — inspect table schemas
• execute_sql_readonly — execute read-only SQL queries

This gives the agent enough capability to investigate the dataset and answer questions without needing a custom BigQuery API wrapper.

---

Step 2: Deploying the Agent to Google Cloud Run

Now that the agent works, we need a way for users to interact with it.

Instead of maintaining our own servers, we can deploy the application to Google Cloud Run, keeping the architecture serverless.

The Google ADK CLI simplifies the deployment process. With a single command, ADK can package and deploy the application.

During deployment, the workflow:

• Generates the container configuration
• Builds the container image using Cloud Build
• Stores the image in Artifact Registry
• Deploys the container to Cloud Run
• Provides a chat UI using the --with_ui option

Here's the deployment command:

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 track2-data-agent \
  --app_name data_agent \
  data_agent \
  -- \
  --allow-unauthenticated \
  --max-instances 1

Once the deployment finishes, Cloud Run provides a URL where we can interact with the agent through the browser.

This means users don't need to know Python, SQL, BigQuery, or even how the agent works internally. They simply ask questions in natural language.

---

Step 3: Extracting Insights from Public Data

Once deployed, it was time to put the agent to the test.

For this example, we're working with the NYC Citi Bike public dataset, which contains millions of trip records with information about start stations, end stations, timestamps, and user types.

I gave the agent the following business-oriented prompt:

"We want to find the best city bike stations to place our 3 coffee trucks based on trip data. Which stations do you recommend?"

Instead of manually inspecting the schema and writing SQL, the agent handled the analysis workflow.

How the Agent Solved It

The agent first inspected the dataset to understand what information was available.

1. Schema Check

It examined the citibike_trips table and identified fields related to:

• Start and end stations
• Trip timestamps
• Trip volume
• User type
• Subscriber vs. customer activity

2. SQL Formulation

Based on the available fields, the agent constructed a read-only query to analyze station activity.

The analysis considered factors such as:

• Overall trip volume
• Morning activity
• Commuter-oriented usage
• Subscriber activity

For this example, the morning commute window was defined as 6:00 AM to 10:00 AM.

3. Insight Generation

Based on the resulting trip activity, the analysis identified three strong candidates for coffee-truck placement:

1. Midtown East / Grand Central

Station: E 42 St & Vanderbilt Ave

The station showed extremely high overall activity along with substantial morning usage, making the Grand Central area a strong candidate for reaching commuters.

2. Midtown South / Union Square & Flatiron

Station: E 17 St & Broadway

Its combination of high trip volume and central location makes it another promising candidate, particularly for reaching riders moving through the Union Square and Flatiron area.

3. Midtown West / Penn Station

Station: 8 Ave & W 33 St

The station showed strong morning activity and overall usage around one of Manhattan's major transportation hubs.

The resulting analysis looked like this:

Rank	Station Name	Major Corridor	Total Trip Volume	Morning Rush Trips (6–10 AM)	Subscriber %
1	E 42 St & Vanderbilt Ave	Grand Central Terminal	1,062,097	301,192	93.9%
2	E 17 St & Broadway	Union Square / Flatiron	867,794	146,548	91.1%
3	8 Ave & W 33 St	Penn Station / Midtown West	639,039	157,272	92.6%

Based on total trip volume and morning peak activity, these stations emerged as strong candidates from the available trip data.

Of course, trip volume alone doesn't guarantee coffee sales. A production analysis could incorporate additional signals such as pedestrian traffic, nearby competitors, office density, weather, permits, and historical sales.

That's also where an agent-based approach becomes interesting: additional datasets can be connected and incorporated into the analysis without changing how the end user asks questions.

---

Why Use MCP Here?

One of the most interesting parts of this architecture is that the agent isn't limited to generating text.

Through MCP, Gemini can interact with tools that expose actual capabilities.

In our case, the flow becomes:

User question → Gemini reasoning → MCP tool call → BigQuery → Gemini interpretation → User

The model can inspect the database before attempting to answer the question.

This is particularly useful because database schemas change. Instead of hard-coding every table and column into the application, the agent can use tools such as list_table_ids and get_table_info to understand what data is available.

---

Security Considerations

Giving an AI agent access to a production data warehouse requires careful permission management.

This implementation uses Application Default Credentials (ADC), which means access can be controlled through Google Cloud IAM rather than embedding credentials directly in the application.

The agent also uses:

execute_sql_readonly

for querying data.

That distinction is important: an analytics agent should generally not need permission to modify or delete warehouse data.

In a production environment, I would also recommend:

• Following the principle of least privilege
• Restricting the service account to required datasets
• Avoiding hard-coded credentials
• Keeping query execution read-only
• Monitoring BigQuery usage and query costs
• Requiring authentication for applications that expose sensitive company data

For this demonstration, --allow-unauthenticated makes the UI easy to access. For an application connected to private company data, authentication and authorization should be configured before exposing the service.

---

Why Serverless?

Cloud Run is a good fit for this architecture because the application doesn't need a permanently running VM.

The overall stack stays relatively simple:

User
  ↓
Cloud Run
  ↓
Google ADK + Gemini
  ↓
BigQuery MCP Server
  ↓
BigQuery

Cloud Run handles the application runtime, while BigQuery handles the analytical workload.

That leaves the application focused primarily on the agent logic.

---

Conclusion & Next Steps

Using Google's ADK and the BigQuery MCP server, we can build a natural-language interface over analytical data without creating a large custom API layer.

The agent can:

• Inspect BigQuery schemas
• Understand available tables and fields
• Formulate an analysis plan
• Generate SQL
• Validate queries
• Execute read-only queries
• Interpret the results
• Present the findings in readable Markdown

Application Default Credentials provide the IAM identity used to access Google Cloud resources, while Cloud Run gives us a serverless deployment target and ADK provides the agent framework.

Most importantly, the end user doesn't need to think about any of this infrastructure.

They can simply ask:

"Where should we place our coffee trucks?"

And the agent can turn that business question into a structured data-analysis workflow.

The same architecture could be extended to use internal sales data, customer analytics, inventory information, operational metrics, or other datasets stored in BigQuery.

That's where MCP-powered data agents become especially useful: turning natural-language business questions into controlled, tool-driven analytical workflows.

---

Built as part of the Google Cloud Cohort 3 Challenge.