MCP Tools for BigQuery — service account auth, async Jobs API, dry-run cost estimation, pagination, and health probes

Authentication — service account IAM and credential chain

BigQuery uses Google OAuth2 with service account credentials. The recommended approach for MCP servers is the @google-cloud/bigquery Node.js client, which handles token acquisition, refresh, and the Application Default Credentials (ADC) chain automatically.

The ADC chain attempts credentials in order: (1) GOOGLE_APPLICATION_CREDENTIALS environment variable pointing to a service account JSON key file, (2) the gcloud CLI's active account (~/.config/gcloud/application_default_credentials.json), (3) the GCE/GKE metadata server when running on Google infrastructure. For MCP servers deployed outside GCP, use a service account JSON key.

import { BigQuery } from '@google-cloud/bigquery';

// ADC chain — GOOGLE_APPLICATION_CREDENTIALS env var or GCE metadata
const bq = new BigQuery({ projectId: process.env.BQ_PROJECT_ID });

// Explicit key file (for non-GCP deployments)
const bqExplicit = new BigQuery({
  projectId: process.env.BQ_PROJECT_ID,
  keyFilename: process.env.GOOGLE_APPLICATION_CREDENTIALS,
  // OR: credentials: JSON.parse(process.env.BQ_SERVICE_ACCOUNT_JSON),
});

// Required IAM roles on the service account:
// - roles/bigquery.jobUser on the PROJECT (to create query jobs)
// - roles/bigquery.dataViewer on each DATASET to read (not project-level)
// A service account with jobUser but without dataViewer gets:
//   Access Denied: Dataset {project}:{dataset}: Permission bigquery.tables.getData denied
// The confusing part: dry-run queries succeed even without dataViewer
// because dry-run never touches the data plane.

BigQuery IAM is split between the job plane (who can submit queries — controlled at project level) and the data plane (who can read table data — controlled at dataset level). Grant roles/bigquery.jobUser at the project level and roles/bigquery.dataViewer at the dataset level for each dataset the MCP tool needs to access.

Submitting and polling async query jobs

Every BigQuery query executes as a job — there is no synchronous execute endpoint. The client submits a job, receives a job ID, then polls until the job reaches DONE state. The @google-cloud/bigquery client wraps this pattern in a query() call, but for MCP tools where you need timeout control and cost gating, use the lower-level jobs API directly.

// Submit a query job and poll for completion
async function runQuery(bq, sql, options = {}) {
  const { timeoutMs = 30_000, location = 'US', maximumBytesBilled } = options;

  // Submit job
  const [job] = await bq.createQueryJob({
    query: sql,
    location,
    // Hard billing cap — job fails with billingTierLimitExceeded if exceeded
    ...(maximumBytesBilled ? { maximumBytesBilled: String(maximumBytesBilled) } : {}),
  });

  const jobId = job.id;
  const deadline = Date.now() + timeoutMs;

  // Poll until DONE
  while (Date.now() < deadline) {
    const [metadata] = await job.getMetadata();
    const state = metadata.status.state; // 'RUNNING' | 'DONE' | 'PENDING'

    if (state === 'DONE') {
      if (metadata.status.errorResult) {
        const err = metadata.status.errorResult;
        throw new Error(`BigQuery job ${jobId} failed: [${err.reason}] ${err.message}`);
      }

      // Fetch rows — first page
      const [rows, nextQuery] = await job.getQueryResults({
        maxResults: 1000,
        timeoutMs: 5_000,
      });

      return { jobId, rows, nextQuery, metadata };
    }

    await new Promise(r => setTimeout(r, 1_000));
  }

  throw new Error(`BigQuery job ${jobId} did not complete within ${timeoutMs}ms`);
}

// Paginate large result sets using pageToken
async function* streamQueryResults(bq, jobId, location = 'US') {
  const job = bq.job(jobId, { location });
  let pageToken;

  do {
    const [rows, next] = await job.getQueryResults({
      maxResults: 5_000,
      pageToken,
    });
    yield rows;
    pageToken = next?.pageToken;
  } while (pageToken);
}

The status.errorResult field is the authoritative failure signal — a job in DONE state with errorResult present has failed, not succeeded. The status.errors array may contain additional details including child errors from parallel sub-tasks.

Dry-run for cost estimation before execution

BigQuery charges $5 per TB scanned (on-demand pricing). For MCP tools that accept user-supplied SQL, a dry-run before execution is the correct safety gate — it returns statistics.totalBytesProcessed (the estimated bytes that would be scanned) without executing the query or incurring cost. Dry-run never interacts with the data plane, so it succeeds even without bigquery.dataViewer on the relevant datasets.

// Dry-run to estimate cost before execution
async function estimateQueryCost(bq, sql, location = 'US') {
  const [job] = await bq.createQueryJob({
    query: sql,
    location,
    dryRun: true,
  });

  const bytesProcessed = parseInt(job.metadata.statistics.totalBytesProcessed, 10);
  const gbProcessed = bytesProcessed / 1_073_741_824;
  // BigQuery on-demand: $5 per TB = $0.005 per GB
  const estimatedCostUsd = (bytesProcessed / 1_099_511_627_776) * 5;

  return {
    bytesProcessed,
    gbProcessed: gbProcessed.toFixed(2),
    estimatedCostUsd: estimatedCostUsd.toFixed(4),
    // Flag if scan exceeds a reasonable threshold for interactive MCP queries
    exceedsThreshold: bytesProcessed > 10 * 1_073_741_824, // 10 GB
  };
}

// MCP tool: query_bigquery with cost gate
server.tool('query_bigquery', {
  sql: z.string().describe('SQL query to execute'),
  confirm_if_over_10gb: z.boolean().optional()
    .describe('Set true to confirm execution when estimated scan exceeds 10 GB'),
}, async ({ sql, confirm_if_over_10gb }) => {
  const estimate = await estimateQueryCost(bq, sql);

  if (estimate.exceedsThreshold && !confirm_if_over_10gb) {
    return {
      content: [{
        type: 'text',
        text: `Query would scan ${estimate.gbProcessed} GB (~$${estimate.estimatedCostUsd}). ` +
              `Set confirm_if_over_10gb: true to proceed.`,
      }],
    };
  }

  const result = await runQuery(bq, sql);
  return { content: [{ type: 'text', text: JSON.stringify(result.rows, null, 2) }] };
});

Note: dry-run estimates can be wrong for queries that use wildcard table names, scripting, or table functions — the actual bytes scanned may differ at execution time. The maximumBytesBilled job parameter provides a hard billing cap as a secondary guard.

Schema introspection and dataset listing

MCP tools that expose BigQuery to LLM agents need to surface schema information for the model to write correct SQL. BigQuery exposes this through both the REST API and INFORMATION_SCHEMA tables. Use the REST API for listing datasets and tables; use INFORMATION_SCHEMA for column details with descriptions and partition specs.

// List datasets in a project
async function listDatasets(bq) {
  const [datasets] = await bq.getDatasets();
  return datasets.map(d => ({
    id: d.id,
    location: d.metadata.location,
    labels: d.metadata.labels || {},
  }));
}

// List tables in a dataset
async function listTables(bq, datasetId) {
  const dataset = bq.dataset(datasetId);
  const [tables] = await dataset.getTables();
  return tables.map(t => ({
    id: t.id,
    type: t.metadata.type, // 'TABLE' | 'VIEW' | 'EXTERNAL' | 'MATERIALIZED_VIEW'
    numRows: t.metadata.numRows,
    numBytes: t.metadata.numBytes,
    // Partition info present for partitioned tables
    partitioning: t.metadata.timePartitioning || t.metadata.rangePartitioning || null,
  }));
}

// Get column schema for a table
async function getTableSchema(bq, datasetId, tableId) {
  const [metadata] = await bq.dataset(datasetId).table(tableId).getMetadata();
  return metadata.schema.fields.map(f => ({
    name: f.name,
    type: f.type,         // STRING | INTEGER | FLOAT | BOOLEAN | RECORD | DATE | TIMESTAMP | BYTES | NUMERIC | BIGNUMERIC | JSON | GEOGRAPHY
    mode: f.mode,         // NULLABLE | REQUIRED | REPEATED
    description: f.description || '',
    fields: f.fields || [], // nested RECORD fields
  }));
}

// INFORMATION_SCHEMA query for full schema with partition details
async function getSchemaWithPartitions(bq, project, dataset, table) {
  const [rows] = await bq.query(`
    SELECT
      column_name, data_type, is_nullable, column_default
    FROM \`${project}.${dataset}.INFORMATION_SCHEMA.COLUMNS\`
    WHERE table_name = '${table}'
    ORDER BY ordinal_position
  `);
  return rows;
}

Health monitoring for BigQuery MCP tools

A BigQuery health probe needs to verify: (1) credential validity and token freshness, (2) that the service account has the correct IAM role combination to both submit jobs and read data, and (3) that the target project is accessible. Use a dry-run of a trivially cheap query against a known table to validate the full role chain without incurring execution cost.

async function probeBigQuery(bq, datasetId, tableId) {
  const results = await Promise.allSettled([

    // 1. Can we submit jobs? (tests bigquery.jobUser)
    (async () => {
      const [job] = await bq.createQueryJob({
        query: 'SELECT 1 AS alive',
        dryRun: true,
      });
      return {
        kind: 'job_submission',
        bytesEstimate: job.metadata.statistics.totalBytesProcessed,
      };
    })(),

    // 2. Can we list datasets? (tests bigquery.metadataViewer at project level)
    (async () => {
      const [datasets] = await bq.getDatasets({ maxResults: 1 });
      return { kind: 'dataset_list', count: datasets.length };
    })(),

    // 3. Can we read a known table schema? (tests bigquery.dataViewer on dataset)
    (async () => {
      const [metadata] = await bq.dataset(datasetId).table(tableId).getMetadata();
      return {
        kind: 'table_access',
        numRows: metadata.numRows,
        lastModified: metadata.lastModifiedTime,
      };
    })(),
  ]);

  return {
    healthy: results.every(r => r.status === 'fulfilled'),
    components: results.map(r =>
      r.status === 'fulfilled'
        ? { ...r.value, ok: true }
        : { ok: false, error: r.reason?.message }
    ),
  };
}

Register the table access check separately with AliveMCP as it tests the data-plane IAM path that job submission alone does not cover. A service account whose dataset IAM bindings are removed will fail all real queries while the credentials-only health probe continues returning healthy.

Common integration pitfalls

Treating dry-run success as full access confirmation

Dry-run queries never access table data, so they succeed even if the service account lacks bigquery.dataViewer on the relevant datasets. Always include a real read (e.g., GET table metadata) in the health probe to verify the full role chain.

Not handling DONE + errorResult as a failure

When a BigQuery job fails, it transitions to DONE state with status.errorResult set. Code that only checks state === 'DONE' will silently treat failed jobs as successful. Always check status.errorResult before reading results.

Missing the job location mismatch

Jobs must be created in the same location as the datasets they query. Querying a dataset in EU from a job created in US returns Not found: Dataset {project}:{dataset} — the same error as a missing dataset. Always make location an explicit configuration parameter.

Scanning full tables due to missing partition filters

Partitioned tables should always include a partition filter in queries (e.g., WHERE _PARTITIONDATE >= '2026-01-01'). Without it, BigQuery scans all partitions. The timePartitioning.requirePartitionFilter table setting enforces this at the API level and returns an error instead of running an unfiltered scan.

Pagination via job re-polling instead of pageToken

For large result sets, retrieve subsequent pages using pageToken from getQueryResults, not by re-submitting the query. Re-submitting creates a new job, incurs cost again, and may return different results if the underlying data changed.

Related guides

• MCP tools for Snowflake — SQL API v2, JWT auth, warehouse auto-suspend, async statement polling
• MCP tools for Redshift Data API — SigV4 auth, async statement execution, cluster health vs API health
• MCP tools for Databricks — SQL Warehouse REST API, PAT auth, statement execution, cluster lifecycle
• MCP tools for AWS S3 — IAM auth, S3-compatible API patterns
• MCP server health check patterns — composite endpoints and failure classification
• MCP server uptime monitoring — probes, intervals, and alert routing