Connect BigQuery to ChatGPT: Run Queries and Manage Datasets

If you need to connect BigQuery to ChatGPT to run SQL queries, analyze datasets, or automate table management workflows, you need a Model Context Protocol (MCP) server. This server acts as the translation layer between ChatGPT's JSON-RPC tool calls and the Google Cloud BigQuery REST APIs. You can either build and maintain this infrastructure yourself, or use a managed integration platform like Truto to dynamically generate a secure, authenticated MCP server URL.

If your team uses Claude, check out our guide on connecting BigQuery to Claude or explore our broader architectural overview on connecting BigQuery to AI Agents.

Giving a Large Language Model (LLM) read and write access to an enterprise data warehouse like BigQuery is a significant engineering challenge. You have to handle complex asynchronous job polling, map dynamic dataset locations to API endpoints, and navigate Google Cloud's strict Identity and Access Management (IAM) permissions. Every time Google updates their API schemas or authentication requirements, your custom server code must be updated, redeployed, and tested.

This guide breaks down exactly how to use Truto to generate a secure, managed MCP server for BigQuery, connect it natively to ChatGPT, and execute complex data warehouse workflows using natural language.

The Engineering Reality of the BigQuery API

A custom MCP server is a self-hosted integration layer. While the open MCP standard provides a predictable way for models to discover tools, implementing it against Google Cloud's highly specific API semantics is exceptionally painful.

If you decide to build a custom MCP server for BigQuery, you own the entire API lifecycle. Here are the specific integration challenges that break standard CRUD assumptions when working with BigQuery:

Asynchronous Query Execution and Polling

Unlike standard SaaS platforms where a GET request immediately returns data, BigQuery is designed for massive data processing. When you submit a SQL query via the /queries endpoint, the API attempts to run it synchronously within a provided timeoutMs. If the query exceeds this timeout, BigQuery returns a jobComplete: false flag alongside a jobReference.jobId. Your MCP server must be intelligent enough to instruct the LLM to take that jobId and poll a separate endpoint (queries.get) until the results are ready. If you hardcode a naive blocking request, your MCP server will drop connections and crash the agent session.

Multipart MIME Uploads vs URIs

BigQuery supports two entirely different methods for loading data. The metadata URI accepts configuration directly, while the upload URI requires sending both a load job configuration and a data stream together as two distinct multipart MIME parts. Standard MCP JSON-RPC schemas struggle to natively represent binary multipart boundaries. You have to build custom translation logic to intercept the LLM's arguments and format them correctly for Google's ingestion servers, or force the LLM to rely strictly on Cloud Storage URIs.

Project Billing and Sandbox Constraints

BigQuery API behavior changes drastically depending on the billing state of the underlying Google Cloud project. In a free tier or sandbox project, streaming inserts via the insertAll endpoint will outright fail with an accessDenied error stating "Streaming insert is not allowed in the free tier". Additionally, table creation in sandbox projects requires explicitly passing an expirationTime (maximum 60 days). Your MCP tool definitions must expose these conditional constraints to the LLM, otherwise ChatGPT will repeatedly hallucinate invalid payloads and get locked in error loops.

Creating the BigQuery MCP Server

Truto dynamically generates MCP tools from BigQuery's resource definitions and API documentation. A tool only appears in the MCP server if it has a corresponding documentation entry - acting as a quality gate. Each server is scoped to a single authenticated Google Cloud account and protected by a cryptographically hashed token.

You can create a BigQuery MCP server using either the Truto dashboard or the management API.

Method 1: Via the Truto UI

1. Navigate to the Integrated Accounts page in your Truto dashboard.
2. Select your connected BigQuery account.
3. Click the MCP Servers tab.
4. Click Create MCP Server.
5. Select your desired configuration (name, allowed methods, specific tags, and expiration).
6. Copy the generated MCP server URL. Treat this URL like a production secret.

Method 2: Via the Truto API

If you are provisioning AI infrastructure programmatically, you can generate the server via a single API call. The API validates that the integration is AI-ready, hashes the token securely, and schedules any requested expiration alarms.

curl -X POST https://api.truto.one/integrated-account/$INTEGRATED_ACCOUNT_ID/mcp \
  -H "Authorization: Bearer $TRUTO_API_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "BigQuery Analytics Agent",
    "config": {
      "methods": ["read", "write"],
      "tags": ["datasets", "tables", "queries"]
    },
    "expires_at": "2026-12-31T23:59:59Z"
  }'

The response will contain a url field (e.g., https://api.truto.one/mcp/<token>). This URL contains everything needed for routing and authentication.

Connecting the MCP Server to ChatGPT

Once you have your Truto MCP URL, connecting it to ChatGPT takes less than a minute. You can configure this through the ChatGPT interface or via a manual configuration file.

Method 1: Via the ChatGPT UI

1. Open ChatGPT and navigate to Settings -> Apps -> Advanced settings.
2. Ensure Developer mode is enabled (this flag controls MCP support).
3. Under MCP servers / Custom connectors, click Add new server.
4. Name: Enter a recognizable label (e.g., "BigQuery via Truto").
5. Server URL: Paste the URL generated in the previous step.
6. Click Save. ChatGPT will immediately execute an MCP initialize handshake and call tools/list to discover the available BigQuery operations.

Method 2: Via Manual Config File

If you are running a local testing environment, a custom agent orchestrator, or a desktop client that relies on standard MCP configuration files, you can connect using the official Server-Sent Events (SSE) transport.

Create or update your MCP configuration file (mcp_config.json) with the following:

{
  "mcpServers": {
    "truto-bigquery": {
      "command": "npx",
      "args": [
        "-y",
        "@modelcontextprotocol/server-sse",
        "--url",
        "https://api.truto.one/mcp/<token>"
      ]
    }
  }
}

Security and Access Control

Giving an LLM unconstrained access to your production data warehouse is a massive security risk. Truto's MCP servers provide four native mechanisms to lock down what ChatGPT can do.

• Method Filtering (config.methods): Restrict the MCP server to specific HTTP operation types. Passing ["read"] limits ChatGPT strictly to get and list operations, preventing the model from accidentally dropping tables or running expensive INSERT mutations.
• Tag Filtering (config.tags): Scope the server to specific functional areas. By passing ["queries", "datasets"], ChatGPT will only see tools related to running SQL and reading dataset metadata, completely hiding IAM role modifications or row access policies.
• Extra Authentication (require_api_token_auth): By default, the cryptographically secure MCP URL is the only auth needed. Setting this flag to true injects a secondary authentication middleware. The client must now pass a valid Truto API token in the Authorization header, meaning possession of the URL alone is useless without valid backend credentials.
• Automatic Expiration (expires_at): Schedule a definitive time-to-live for the server. Truto uses distributed edge storage and durable alarms to guarantee the token is purged from all systems the millisecond it expires. Ideal for temporary incident response agents.

BigQuery Hero Tools

Truto translates BigQuery's REST API into an optimized flat input namespace for LLMs. Here are the highest-leverage tools available for your ChatGPT agents.

Create a BigQuery Query

Executes a BigQuery SQL query synchronously. If the query completes within the specified timeout, it returns the results immediately. If it times out, it returns a job reference ID that the LLM must use to poll for completion. Note that Data Manipulation Language (DML) requires an active billing project.

"Run a SQL query against the production_analytics project to find the top 10 most active users in the events_2026 table over the last 30 days. Use a timeout of 10000ms."

Get Single BigQuery Query Result By ID

Retrieves the results of a query job. This is the critical second-half tool required when create_a_big_query_query times out. The LLM passes the jobReference.jobId to fetch the final dataset.

"The previous query timed out and returned job ID 'job_xyz_789'. Fetch the final query results for that job now."

BigQuery Datasets Restore

Undeletes a dataset that is currently within the BigQuery time-travel window. If a specific deletionTime is provided, the exact version deleted at that timestamp is restored. Restored datasets are subject to a 7-day undelete cool-down.

"Restore the staging_reports dataset in project data-warehouse-prod. It was accidentally deleted earlier today. Do not specify a deletion time so it restores the last live version."

BigQuery Table Rows Bulk Create

Streams data into BigQuery one record at a time without needing to run a heavy load job. Rows must be formatted as [{json: {column: value}}]. This requires standard billing enabled and will fail on BigQuery sandbox accounts.

"Stream a new event record into the telemetry_logs table. The event name is 'agent_execution', the user_id is 'usr_999', and the timestamp is right now."

Create a BigQuery Dataset

Provisions a new, empty dataset. The LLM can specify geographic locations, default table expirations, and access controls during creation.

"Create a new dataset named marketing_experiments_q4 in the analytics-project. Set the location to US and ensure the default table expiration is set to 30 days."

To view the complete inventory of BigQuery tools, JSON schema definitions, and required IAM permissions, visit the BigQuery integration page.

Workflows in Action

Here is how ChatGPT orchestrates multi-step BigQuery workflows using the tools provided by the Truto MCP server.

Scenario 1: Executing a Long-Running Analytics Query

A FinOps engineer asks ChatGPT to calculate compute costs for the previous quarter. The query takes 45 seconds to run, forcing the LLM to handle BigQuery's async polling architecture.

"Calculate our total BigQuery compute spend for Q3 grouped by project. The data is in billing.export_data. Run the query and get me the final numbers."

sequenceDiagram
    participant User as FinOps Engineer
    participant ChatGPT as ChatGPT
    participant Truto as Truto MCP
    participant BQ as BigQuery API

    User->>ChatGPT: Calculate Q3 compute spend
    ChatGPT->>Truto: call create_a_big_query_query (timeout: 10000ms)
    Truto->>BQ: POST /projects/p/queries
    BQ-->>Truto: jobComplete: false, jobId: 'bqux_123'
    Truto-->>ChatGPT: Job incomplete. Polling required.
    ChatGPT->>Truto: call get_single_big_query_query_result_by_id (id: 'bqux_123')
    Truto->>BQ: GET /projects/p/queries/bqux_123
    BQ-->>Truto: Results Array (Cost Data)
    Truto-->>ChatGPT: Formatted Text Result
    ChatGPT-->>User: Total spend is $14,200. Breakdown provided below.

1. ChatGPT calls create_a_big_query_query with the generated SQL.
2. BigQuery hits the timeout limit and returns a jobId instead of data.
3. ChatGPT reads the jobComplete: false flag from the schema description.
4. ChatGPT autonomously calls get_single_big_query_query_result_by_id using the provided ID to retrieve the final financial data.

Scenario 2: Disaster Recovery via Time-Travel

A Data Engineer realizes a critical reporting dataset was dropped. They ask ChatGPT to recover it using BigQuery's native time-travel features.

"Someone dropped the annual_revenue_reports dataset this morning. Undelete it immediately, then list all datasets to confirm it is back online."

sequenceDiagram
    participant User as Data Engineer
    participant ChatGPT as ChatGPT
    participant Truto as Truto MCP
    participant BQ as BigQuery API

    User->>ChatGPT: Undelete dataset and verify
    ChatGPT->>Truto: call big_query_datasets_restore (id: 'annual_revenue_reports')
    Truto->>BQ: POST /datasets/annual_revenue_reports:undelete
    BQ-->>Truto: 200 OK (Dataset Restored)
    Truto-->>ChatGPT: Success response
    ChatGPT->>Truto: call list_all_big_query_datasets
    Truto->>BQ: GET /datasets
    BQ-->>Truto: Array of Datasets
    Truto-->>ChatGPT: Confirmed in list
    ChatGPT-->>User: Dataset successfully restored and verified.

1. ChatGPT invokes big_query_datasets_restore targeting the deleted dataset ID.
2. BigQuery pulls the dataset from the time-travel window and restores it.
3. ChatGPT immediately calls list_all_big_query_datasets to verify the state of the project.
4. ChatGPT reports back to the engineer that the incident is resolved.

Handling Rate Limits and Retries

When deploying AI agents in production, understanding rate limit architecture is critical. Google Cloud enforces strict quota limits on concurrent API requests, query execution bytes, and streaming inserts.

Factual note on rate limits: Truto does not retry, throttle, or apply backoff on rate limit errors. When the upstream BigQuery API returns an HTTP 429 (Too Many Requests), Truto passes that error directly to the caller.

Truto normalizes the upstream rate limit information into standardized HTTP headers (ratelimit-limit, ratelimit-remaining, ratelimit-reset) per the IETF specification. Your LLM framework or orchestrator is entirely responsible for reading these headers, pausing execution, and implementing exponential backoff. Do not design workflows under the assumption that the MCP server will absorb rate limit errors on your behalf.

Summary

Connecting BigQuery to ChatGPT transforms a complex, highly technical data warehouse into an accessible, conversation-driven engine. By leveraging a managed MCP server, you abstract away the complexities of Google's OAuth requirements, multipart payloads, and asynchronous polling logic.

Instead of wasting engineering cycles writing point-to-point API wrappers, your team can focus on writing better SQL prompts, defining stricter IAM boundaries, and actually utilizing your organization's data.