ServicesAboutNotesContact Get in touch →
EN FR
Note

AI Query Cost Control for BigQuery MCP

Managing the cost and safety risks of AI assistants running BigQuery queries through MCP — cost mitigation, write protection, and practical guardrails.

Planted
mcpbigquerygcpaicost optimization

AI assistant access to BigQuery via MCP introduces cost and safety risks that do not exist with human-only access. An AI assistant optimizes for answering questions, not for cost — if answering a question requires scanning a 10 TB table, it will. Both MCP options (the Remote Server and the Toolbox) are in preview/beta as of early 2026, adding a layer of unpredictability. Every execute_sql call runs a real BigQuery query at real cost ($6.25 per TiB scanned on on-demand pricing per the BigQuery Cost Model).

The Cost Risk

A curious AI asking broad questions about large tables can rack up charges quickly. Consider the interaction pattern:

  1. User asks: “What are the trends in our order data?”
  2. AI calls list_table_ids to find relevant tables
  3. AI calls get_table_info on several tables to understand schemas
  4. AI writes and runs a broad query — possibly SELECT * on a large fact table — to explore the data
  5. AI writes follow-up queries to refine the analysis

Steps 3 and 4 are where costs accumulate. Schema inspection is cheap, but the exploratory queries can be expensive. An AI doesn’t know (or care) that orders is a 5 TB table. It writes the query it thinks will answer your question.

BigQuery’s pricing model makes this particularly sharp: you pay for bytes scanned, not bytes returned. A query that scans 5 TB and returns 10 rows costs the same as one that returns 10 million rows. LIMIT clauses don’t reduce the bill — BigQuery scans first, limits second.

Cost Mitigation Strategies

Use custom parameterized queries. This is the most effective control. The Toolbox’s tools.yaml lets you define queries with built-in LIMIT clauses, specific column selections, and partition filters. The AI fills in parameters but can’t change the query shape. A query defined with SELECT order_id, amount FROM orders WHERE date BETWEEN @start AND @end LIMIT 100 will never become a full table scan.

Restrict to specific datasets. Grant bigquery.dataViewer only on datasets the AI should access, not at the project level. If the AI can only see your mart-layer datasets (which are smaller and pre-aggregated), the maximum cost per query is bounded by those table sizes. Raw/staging tables, which tend to be much larger, stay off-limits.

Set BigQuery cost controls. Use project-level and user-level quotas to cap daily query costs:

  • Custom quotas per project limit total daily bytes scanned
  • Per-user quotas limit what a single service account can scan

These are safety nets, not optimization strategies. They prevent runaway costs but don’t make queries cheaper.

Monitor query patterns. Query INFORMATION_SCHEMA.JOBS_BY_PROJECT filtered by the MCP service account to see what the AI is actually running. Look for:

  • Queries without partition filters on partitioned tables
  • SELECT * patterns on wide tables
  • Repeated queries scanning the same large tables

This monitoring tells you when to add custom tools or tighten dataset access.

The Write Protection Problem

Here’s something that surprises people: execute_sql can run INSERT, UPDATE, DELETE, and DDL statements. If the AI has bigquery.dataEditor permissions (which allows table modifications), a query like DROP TABLE or DELETE FROM is technically possible.

For read-only MCP access, the IAM strategy is clear:

  • Grant only roles/bigquery.user (for running queries and listing datasets)
  • Grant only roles/bigquery.dataViewer (for reading table data)
  • Never grant roles/bigquery.dataEditor

The BigQuery IAM Patterns note covers this in depth, but the key takeaway for MCP: the AI’s service account should have the minimum permissions needed for read operations. If you need the AI to write data (rare), create a separate service account with write access to specific datasets only.

Alternatively, use custom parameterized queries with read-only SQL statements exclusively. Even if the service account has broader permissions, the AI can only execute the queries you’ve defined.

Other Gotchas

Long-running queries may timeout. MCP has its own timeout separate from BigQuery’s. A complex query that BigQuery would eventually finish might time out at the MCP protocol level, leaving you with a failed request and a still-running (still-billing) BigQuery job.

Large result sets get truncated. MCP responses have size limits. If a query returns millions of rows, the response will be cut off. The AI might not realize the results are incomplete and draw conclusions from partial data.

Concurrent query limits. BigQuery has per-project concurrent query limits. If the AI runs multiple queries in rapid succession (which it sometimes does when exploring data), it can hit these limits and receive errors.

Beta instability. Both MCP options are in preview/beta as of early 2026. Pin to a specific Toolbox version in production. Expect breaking changes between releases.

The Practical Approach

Start conservative and loosen as needed:

  1. Use the Toolbox with custom parameterized queries only — no execute_sql
  2. Point tools at mart-layer tables, not raw data
  3. Include LIMIT clauses in every query definition
  4. Monitor the service account’s query activity for a few weeks
  5. Add execute_sql as a tool only if parameterized queries prove insufficient

This inverts the usual approach (grant everything, restrict later) into the safer pattern of granting nothing and adding access where demonstrated need exists. The Security Posture for AI Agents note covers the broader philosophy behind this least-privilege approach for AI tools.