Can the model write arbitrary SQL?

It can write SELECT statements, but the tool should reject any DDL/DML keywords before sending the query to BigQuery, since a model producing occasionally-wrong SQL is a normal failure mode, not an edge case.

Does query caching help with repeated model queries?

Yes - BigQuery caches identical query results for a period by default, so a model re-running the same exploratory query typically isn't billed twice.

What region should queries run in?

Run the query in the same region as the dataset. Cross-region queries either fail or require an explicit cross-region configuration, and add latency either way.

developmentTechArticle

Building a BigQuery MCP Server

Let AI agents run parameterized BigQuery queries safely, with cost controls and partition-aware guidance.

Quick Answer / TL;DR

A BigQuery MCP server exposes a tool that runs parameterized SQL through the official @google-cloud/bigquery client, with query cost and row limits enforced before execution rather than trusted to the model.

Key Takeaways

Use parameterized queries (query_params), never string-interpolated SQL, to avoid injection through model-generated input.
Set maximumBytesBilled to cap the cost of any single query the model can trigger.
Favor partitioned and clustered tables - BigQuery bills by bytes scanned, not rows returned, so an unfiltered query on a large table is expensive even if the result set is small.

Why not just copy a reference implementation?

The official MCP servers repository is explicit about its own scope: "The servers in this repository are intended as reference implementations to demonstrate MCP features and SDK usage... not as production-ready solutions." That's a reasonable design choice for an educational repo, but it means a naive tool built by following the same pattern typically has no cost cap, no read-only enforcement, and no row limit - exactly the three things a BigQuery tool needs given its per-byte billing model.

A cost-bounded query tool

The official @google-cloud/bigquery client supports parameterized queries and a maximumBytesBilled cap, which turns an open-ended 'let the model query the warehouse' tool into one with a hard cost ceiling per call.

typescript

import { BigQuery } from "@google-cloud/bigquery";
import { z } from "zod";

const bigquery = new BigQuery({
  projectId: process.env.GCP_PROJECT_ID,
});

const QuerySchema = z.object({
  sql: z.string(),
  params: z.record(z.any()).optional(),
});

async function runBigQueryTool(args: unknown) {
  const { sql, params } = QuerySchema.parse(args);

  if (/\b(INSERT|UPDATE|DELETE|DROP|MERGE)\b/i.test(sql)) {
    throw new Error("Only SELECT queries are permitted through this tool.");
  }

  const [job] = await bigquery.createQueryJob({
    query: sql,
    params,
    maximumBytesBilled: "1000000000", // 1 GB cap per query
    location: "asia-south1",
  });

  const [rows] = await job.getQueryResults({ maxResults: 200 });
  return rows;
}

Partitioning and cost awareness

BigQuery's on-demand pricing bills per byte scanned. A time-partitioned table lets a WHERE clause on the partition column skip scanning irrelevant partitions entirely, which is usually the single biggest cost lever available - larger than query tuning elsewhere in the SQL.

Practice	Effect
Filter on a partitioned date column	Scans only matching partitions instead of the full table
Cluster by frequently-filtered columns	Prunes blocks within a partition, reducing bytes read further
Cap maximumBytesBilled per call	Query fails before running if it would exceed the cap, instead of billing silently