MCP Tools for Data Warehouses: Auth Spectrum, Async Execution Models, and Warehouse Lifecycle Health Probes

Pattern 1: The Auth Spectrum — From IAM Split-Plane to Zero-Auth

Data warehouse authentication spans a wider range of complexity than any other API category. The five platforms in this arc represent distinct authentication philosophies — not variations on the same approach.

BigQuery: IAM Split Across Job Plane and Data Plane

BigQuery's IAM model is the most nuanced of the five because permissions are split across two independent control axes. roles/bigquery.jobUser must be granted at the project level and controls the ability to submit query jobs. roles/bigquery.dataViewer must be granted at the dataset level for each dataset the service account needs to read.

The critical failure mode: a service account with jobUser but without dataViewer can successfully submit a query job and receive a job ID. The job transitions to DONE state. But when you check status.errorResult, you see Access Denied: Dataset {project}:{dataset}: Permission bigquery.tables.getData denied. The query appeared to succeed (job created, polled to DONE) and then failed at the data-read step. A health probe that only checks token validity or submits a job without a real query misses this failure.

Confusing the situation further: dry-run queries succeed even without dataViewer. Dry-run only touches the job plane to compile and plan the query — it never scans table data. So a cost estimation dry-run will return statistics.totalBytesProcessed successfully even when a real execution of the same query would fail with 403.

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

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

// Required permissions on the service account:
// roles/bigquery.jobUser  — at PROJECT level (submit jobs)
// roles/bigquery.dataViewer — at DATASET level (read table data)

// Health probe that catches the IAM split-plane failure:
async function probeBigQuery(bq, datasetId) {
  // Step 1: verify job-plane permission (dry-run)
  const [dryRunJob] = await bq.createQueryJob({
    query: `SELECT 1 AS ping`,
    dryRun: true,
  });
  // dry-run success = jobUser permission confirmed

  // Step 2: verify data-plane permission (dataset list)
  const dataset = bq.dataset(datasetId);
  await dataset.getTables(); // fails if dataViewer is missing

  return { jobPlane: 'ok', dataPlane: 'ok' };
}

The ADC (Application Default Credentials) chain attempts credentials in this order: (1) GOOGLE_APPLICATION_CREDENTIALS environment variable pointing to a service account JSON file, (2) ~/.config/gcloud/application_default_credentials.json from the gcloud CLI, (3) the GCE/GKE metadata server. For MCP servers deployed outside GCP, use an explicit service account JSON key.

Snowflake: JWT RS256 Key-Pair With Public Key Fingerprint in Claims

Snowflake SQL API v2 does not accept username/password or standard Bearer tokens. Authentication uses JWT signed with RS256 (RSASSA-PKCS1-v1_5 with SHA-256 — not PS256, which uses PSS padding). The JWT payload requires a specific claim format where the iss claim must be {account}.{user}.SHA256:{fingerprint} and the fingerprint is the SHA256 hash of the DER-encoded public key, base64-encoded.

The fingerprint calculation is the part teams get wrong. It is not the fingerprint of the PEM file contents. It is not the fingerprint of the raw key bytes. It is the SHA256 of the DER encoding of the SubjectPublicKeyInfo structure (the spki format in Node.js crypto). Getting this wrong produces a 400 with "JWT token is invalid" — which looks identical to a clock skew error or an expired token.

import { createPrivateKey, createPublicKey, createSign, createHash } from 'crypto';

function buildSnowflakeJWT({ account, user, privateKeyPem }) {
  const ACCOUNT = account.toUpperCase();
  const USER = user.toUpperCase();
  const privateKey = createPrivateKey(privateKeyPem);

  // Fingerprint: SHA256 of DER-encoded public key (SPKI format), base64-encoded
  const publicKey = createPublicKey(privateKey);
  const der = publicKey.export({ type: 'spki', format: 'der' });
  const fingerprint = 'SHA256:' + createHash('sha256').update(der).digest('base64');

  const now = Math.floor(Date.now() / 1000);
  const header = Buffer.from(JSON.stringify({ alg: 'RS256', typ: 'JWT' })).toString('base64url');
  const payload = Buffer.from(JSON.stringify({
    iss: `${ACCOUNT}.${USER}.${fingerprint}`,  // fingerprint embedded in iss
    sub: `${ACCOUNT}.${USER}`,
    iat: now,
    exp: now + 60,  // max 60 seconds — generate fresh per request or cache for 55s
  })).toString('base64url');

  const sign = createSign('SHA256');
  sign.update(`${header}.${payload}`);
  const signature = sign.sign(privateKey).toString('base64url');
  return `${header}.${payload}.${signature}`;
}

// Request with KEYPAIR_JWT token type header:
const response = await fetch(
  `https://${account.toLowerCase()}.snowflakecomputing.com/api/v2/statements`,
  {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${buildSnowflakeJWT({ account, user, privateKeyPem })}`,
      'X-Snowflake-Authorization-Token-Type': 'KEYPAIR_JWT',  // required
      'Content-Type': 'application/json',
    },
    body: JSON.stringify({ statement: sql, warehouse, database, schema, role }),
  }
);

Snowflake supports two registered public keys per user (RSA_PUBLIC_KEY and RSA_PUBLIC_KEY_2) for zero-downtime key rotation. Register the new key as key 2, switch the application to sign with the new private key, then drop key 1. During the transition, both keys are valid simultaneously.

Identifier case folding: Snowflake folds all unquoted identifiers to uppercase. A table created as my_events without double-quoting is stored as MY_EVENTS. Queries using my_events in lowercase fail with "Object 'MY_SCHEMA.MY_EVENTS' does not exist" unless the table was originally created with "my_events" (double-quoted, preserving case). This is a common source of confusion when migrating SQL from PostgreSQL or MySQL.

Redshift: IAM SigV4 via Data API — No VPC, No JDBC

The Redshift Data API is the correct interface for MCP tools. Unlike JDBC-based connections, it requires no VPC membership, no network routing to the cluster, and no driver installation. Authentication is standard AWS IAM SigV4 handled entirely by the SDK — you grant IAM permissions, configure credentials via environment variables or instance profile, and the SDK signs every request.

import { RedshiftDataClient, ExecuteStatementCommand } from '@aws-sdk/client-redshift-data';

// SDK credentials chain: env vars → ~/.aws/credentials → instance/task metadata
const client = new RedshiftDataClient({ region: process.env.AWS_REGION || 'us-east-1' });

// Provisioned cluster connection params:
const provisionedParams = {
  ClusterIdentifier: process.env.REDSHIFT_CLUSTER_ID,
  Database: process.env.REDSHIFT_DATABASE,
  DbUser: process.env.REDSHIFT_DB_USER,  // auto-created on first use
};

// Serverless workgroup connection params (different from provisioned):
const serverlessParams = {
  WorkgroupName: process.env.REDSHIFT_WORKGROUP,
  Database: process.env.REDSHIFT_DATABASE,
  // No ClusterIdentifier, no DbUser for Serverless
};

// Minimum IAM permissions:
// redshift-data:ExecuteStatement
// redshift-data:DescribeStatement
// redshift-data:GetStatementResult
// redshift:GetClusterCredentials  (provisioned only, for DbUser auth)

The DbUser parameter for provisioned clusters is the Redshift database username, not the IAM user. On first use, Redshift auto-creates this user if it does not exist. The IAM principal needs redshift:GetClusterCredentials permission to obtain temporary credentials for this user. Alternatively, supply a SecretArn pointing to AWS Secrets Manager credentials to avoid needing GetClusterCredentials at all.

Databricks: Bearer PAT or OAuth2 M2M

Databricks SQL Warehouse authentication is the most conventional in the group: standard Authorization: Bearer {token}. Personal Access Tokens (PATs) are workspace-scoped, never expire by default (unless the workspace enforces a policy), and are straightforward to create. For production deployments where credential rotation matters, OAuth2 M2M service principals support token rotation without code changes.

// PAT auth — simplest, fine for single-workspace MCP servers
const headers = {
  'Authorization': `Bearer ${process.env.DATABRICKS_TOKEN}`,
  'Content-Type': 'application/json',
};

// OAuth2 M2M token fetch for production:
async function getDatabricksToken(host, clientId, clientSecret) {
  const res = await fetch(`https://${host}/oidc/v1/token`, {
    method: 'POST',
    headers: { 'Content-Type': 'application/x-www-form-urlencoded' },
    body: new URLSearchParams({
      grant_type: 'client_credentials',
      client_id: clientId,
      client_secret: clientSecret,
      scope: 'all-apis',
    }).toString(),
  });
  const { access_token, expires_in } = await res.json();
  return { token: access_token, expiresIn: expires_in };
}

// Check PAT expiry (optional — PATs don't expire by default, but workspace
// policy can enforce expiry_time):
// GET /api/2.0/token/list → filter by token_id to get expiry_time

DuckDB: No Auth, No Network, No Daemon

DuckDB runs inside the MCP server process. There is no separate server to authenticate against, no network port to connect to, no credentials to manage. This makes DuckDB trivially easy to set up — and introduces failure modes that none of the other four platforms have.

The exclusive write lock: only one process can open a DuckDB file in read-write mode. A second process attempting to open the same file path will fail immediately with "database file is locked". Use { readonly: true } to open the file with multiple concurrent readers, or ensure only the MCP server process ever opens the file in write mode.

import Database from 'duckdb';
import { promisify } from 'util';

// In-memory — fastest, data lost on restart
const memDb = new Database(':memory:');

// Persistent — survives restarts, exclusive write lock
const fileDb = new Database('/data/analytics.db');

// Concurrent read access — no exclusive lock
const readOnlyDb = new Database('/data/analytics.db', { readonly: true });

function createConn(db) {
  const conn = db.connect();
  return {
    all: promisify(conn.all.bind(conn)),
    run: promisify(conn.run.bind(conn)),
  };
}

// Critical: set memory_limit before any queries
// Without this, a large Parquet scan can OOM the MCP server process
async function initSession(conn) {
  await conn.run("SET memory_limit='2GB'");
  await conn.run("SET threads=4");
  await conn.run("SET temp_directory='/tmp/duckdb-spill'");
}

Auth Model Comparison Table

Pattern 2: Async Execution Models — Five Different Status Machines

Every data warehouse in this group executes SQL asynchronously. You cannot submit a query and receive rows in a single synchronous response (with one exception: DuckDB, which is synchronous by default). For the other four platforms, the flow is: submit SQL → receive a statement or job identifier → poll until terminal state → fetch results. But each platform uses completely different field names, different terminal state values, and different ways to represent failure.

BigQuery: Job State + errorResult Field

BigQuery queries execute as jobs. The poll target is status.state on the job metadata. The only terminal state is 'DONE' — there is no separate 'FAILED' state. A failed BigQuery job reaches DONE with status.errorResult populated. An succeeded job reaches DONE with status.errorResult === null. Never check status.state === 'DONE' and assume success — always check status.errorResult immediately after.

async function pollBigQueryJob(job, timeoutMs = 30_000) {
  const deadline = Date.now() + timeoutMs;
  while (Date.now() < deadline) {
    const [metadata] = await job.getMetadata();
    if (metadata.status.state === 'DONE') {
      // DONE does NOT mean success — check errorResult
      if (metadata.status.errorResult) {
        const { reason, message } = metadata.status.errorResult;
        throw new Error(`BigQuery job failed [${reason}]: ${message}`);
      }
      return metadata; // success
    }
    // state: 'PENDING' | 'RUNNING' — keep polling
    await new Promise(r => setTimeout(r, 1_000));
  }
  throw new Error(`BigQuery job timed out after ${timeoutMs}ms`);
}

// Pagination: use pageToken from getQueryResults
async function* streamBigQueryResults(job) {
  let pageToken;
  do {
    const [rows, next] = await job.getQueryResults({ maxResults: 5_000, pageToken });
    yield rows;
    pageToken = next?.pageToken;
  } while (pageToken);
}

For cost control, implement a dry-run gate before executing user-supplied SQL. Dry-run returns statistics.totalBytesProcessed (an estimate) at zero cost and without data-plane access:

async function estimateCost(bq, sql) {
  const [job] = await bq.createQueryJob({ query: sql, dryRun: true });
  const bytes = parseInt(job.metadata.statistics.totalBytesProcessed, 10);
  const estimatedUsd = (bytes / 1_099_511_627_776) * 5; // $5/TB
  return { bytes, estimatedUsd: estimatedUsd.toFixed(4) };
}

Snowflake: HTTP 200 vs 202 Then status Field

Snowflake SQL API v2 uses HTTP status codes to distinguish synchronous completion from async pending: a 200 OK response contains the result rows directly; a 202 Accepted response contains a statementHandle for polling. Both cases must be handled — which statement returns 200 vs 202 depends on query complexity and server load, not on any parameter you control.

async function executeSnowflakeSQL(client, sql) {
  const res = await fetch(`${client.baseUrl}/api/v2/statements`, {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${client.auth.getToken()}`,
      'X-Snowflake-Authorization-Token-Type': 'KEYPAIR_JWT',
      'Content-Type': 'application/json',
    },
    body: JSON.stringify({ statement: sql, ...client.defaults }),
  });

  if (res.status === 200) {
    return await res.json(); // completed synchronously, rows inline
  }
  if (res.status === 202) {
    const { statementHandle } = await res.json();
    return await pollSnowflakeStatement(client, statementHandle);
  }
  throw new Error(`Snowflake API error ${res.status}: ${await res.text()}`);
}

async function pollSnowflakeStatement(client, handle, maxWaitMs = 120_000) {
  const deadline = Date.now() + maxWaitMs;
  while (Date.now() < deadline) {
    const res = await fetch(`${client.baseUrl}/api/v2/statements/${handle}`, {
      headers: {
        'Authorization': `Bearer ${client.auth.getToken()}`,
        'X-Snowflake-Authorization-Token-Type': 'KEYPAIR_JWT',
      },
    });
    const data = await res.json();
    // Terminal states: 'success' | 'failed'
    // Intermediate states: 'running'
    if (data.status === 'success') return data;
    if (data.status === 'failed') {
      throw new Error(`Snowflake statement failed: ${data.message}`);
    }
    await new Promise(r => setTimeout(r, 2_000));
  }
  // Cancel orphaned statements on timeout
  await fetch(`${client.baseUrl}/api/v2/statements/${handle}/cancel`, {
    method: 'POST',
    headers: { 'Authorization': `Bearer ${client.auth.getToken()}`, 'X-Snowflake-Authorization-Token-Type': 'KEYPAIR_JWT' },
  }).catch(() => {});
  throw new Error(`Snowflake statement ${handle} timed out`);
}

Snowflake result sets larger than a single partition require fetching additional partitions via GET /api/v2/statements/{handle}?partition=N. The first response includes resultSetMetaData.partitionInfo describing how many partitions exist. Fetch partition 0 inline, then iterate through additional partition URLs. VARIANT columns come back as JSON-encoded strings — always JSON.parse() them before passing to the LLM agent.

Redshift: Five-State Machine With Three Terminal States

Redshift Data API statements go through a five-state machine: SUBMITTED → PICKED → STARTED → FINISHED | FAILED | ABORTED. Three terminal states exist — not one (FINISHED) and not two (FINISHED/FAILED). ABORTED is separate: it occurs when a statement is explicitly cancelled via CancelStatement, or when the cluster pauses mid-execution.

The cluster-pause failure trap: when a provisioned Redshift cluster is paused, the Data API continues to accept ExecuteStatement calls and returns an HTTP 200 with a statement ID. DescribeStatement then returns Status: 'FAILED' with Error: 'QueryException' — not a network timeout, not a cluster-unavailable error. This means a health probe that only tests "does ExecuteStatement return HTTP 200?" will report healthy even when the cluster is paused and all queries are failing.

import {
  RedshiftDataClient,
  ExecuteStatementCommand,
  DescribeStatementCommand,
  GetStatementResultCommand,
  CancelStatementCommand,
} from '@aws-sdk/client-redshift-data';

async function executeRedshiftQuery(client, conn, sql, timeoutMs = 60_000) {
  const { Id: statementId } = await client.send(new ExecuteStatementCommand({
    ...conn,
    Sql: sql,
  }));

  const deadline = Date.now() + timeoutMs;
  while (Date.now() < deadline) {
    const describe = await client.send(new DescribeStatementCommand({ Id: statementId }));
    const { Status, Error: errMsg } = describe;

    if (Status === 'FINISHED') return statementId;
    if (Status === 'FAILED') throw new Error(`Redshift query failed: ${errMsg}`);
    if (Status === 'ABORTED') throw new Error(`Redshift query aborted (cluster paused?): ${errMsg}`);
    // SUBMITTED | PICKED | STARTED — poll
    await new Promise(r => setTimeout(r, 2_000));
  }

  // Cancel orphan on timeout
  await client.send(new CancelStatementCommand({ Id: statementId })).catch(() => {});
  throw new Error(`Redshift query ${statementId} timed out after ${timeoutMs}ms`);
}

// Fetch rows — values as typed union: longValue | stringValue | doubleValue | booleanValue | isNull
async function fetchRedshiftRows(client, statementId) {
  const rows = [];
  let nextToken;
  do {
    const result = await client.send(new GetStatementResultCommand({
      Id: statementId,
      NextToken: nextToken,
    }));
    for (const record of result.Records) {
      const row = {};
      result.ColumnMetadata.forEach((col, i) => {
        const cell = record[i];
        row[col.name] = cell.isNull ? null
          : cell.longValue ?? cell.stringValue ?? cell.doubleValue ?? cell.booleanValue;
      });
      rows.push(row);
    }
    nextToken = result.NextToken;
  } while (nextToken);
  return rows;
}

Databricks: wait_timeout Determines Sync vs Async

Databricks SQL Warehouse statement execution uses a wait_timeout parameter to control synchronous vs async behavior. Queries that complete within the timeout return results immediately with status.state === 'SUCCEEDED'. Queries that exceed the timeout return a statement_id for polling. Critically, the on_wait_timeout parameter defaults to 'CANCEL' — which aborts the query if it doesn't complete within the timeout. Always set on_wait_timeout: 'CONTINUE' to convert the response to async instead of cancelling.

async function executeDatabricksSQL(client, sql, options = {}) {
  const { catalog, schema, timeoutSec = 30 } = options;

  const body = {
    statement: sql,
    warehouse_id: client.warehouseId,
    wait_timeout: `${timeoutSec}s`,
    on_wait_timeout: 'CONTINUE',  // CRITICAL: default is 'CANCEL' which aborts the query
    ...(catalog ? { catalog } : {}),
    ...(schema ? { schema } : {}),
  };

  const response = await client.request('POST', '/api/2.0/sql/statements', body);
  const { statement_id, status } = response;

  if (status.state === 'SUCCEEDED') return parseResult(response);
  if (status.state === 'FAILED') {
    throw new Error(`Databricks statement failed: ${status.error?.message}`);
  }
  // PENDING or RUNNING — poll
  return pollDatabricksStatement(client, statement_id);
}

async function pollDatabricksStatement(client, statementId, maxWaitMs = 120_000) {
  const deadline = Date.now() + maxWaitMs;
  while (Date.now() < deadline) {
    const response = await client.request('GET', `/api/2.0/sql/statements/${statementId}`);
    const { status } = response;
    if (status.state === 'SUCCEEDED') return parseResult(response);
    if (status.state === 'FAILED') {
      throw new Error(`Databricks statement failed: ${status.error?.message} [${status.error?.error_code}]`);
    }
    // PENDING | RUNNING — continue polling
    await new Promise(r => setTimeout(r, 2_000));
  }
  // Cancel orphan
  await client.request('DELETE', `/api/2.0/sql/statements/${statementId}`).catch(() => {});
  throw new Error(`Databricks statement ${statementId} timed out after ${maxWaitMs}ms`);
}

function parseResult(response) {
  // Small results: result.data_array — array of arrays (row-major, column schema in manifest)
  // Large results: result.external_links — presigned S3 URLs, expire in 15 minutes
  const { result, manifest } = response;
  const columns = manifest?.schema?.columns || [];
  if (result?.data_array) {
    return result.data_array.map(row =>
      Object.fromEntries(columns.map((col, i) => [col.name, row[i]]))
    );
  }
  // External links require separate fetch per chunk URL
  return { externalLinks: result.external_links, columns };
}

DuckDB: Synchronous — No Poll Required, But Timeout Needed

DuckDB executes SQL synchronously in-process. There is no poll, no statement ID, no status machine. The query blocks the calling code until rows are returned. For MCP tools, this means implementing a timeout via Promise.race — DuckDB's Node.js binding does not provide a built-in query timeout.

async function safeQuery(conn, sql, params = [], timeoutMs = 30_000) {
  const queryPromise = params.length ? conn.all(sql, ...params) : conn.all(sql);
  const timeoutPromise = new Promise((_, reject) =>
    setTimeout(() => reject(new Error(`DuckDB query timed out after ${timeoutMs}ms`)), timeoutMs)
  );
  return Promise.race([queryPromise, timeoutPromise]);
}

DuckDB is especially powerful for MCP tools that give LLM agents access to data files without loading them first. The httpfs extension enables direct S3 querying:

// Query Parquet files directly from S3 — no ETL pipeline required
async function queryParquet(conn, s3Uri, sql, s3Config = {}) {
  await conn.run('INSTALL httpfs');
  await conn.run('LOAD httpfs');
  if (s3Config.region) await conn.run(`SET s3_region='${s3Config.region}'`);
  if (s3Config.accessKeyId) {
    await conn.run(`SET s3_access_key_id='${s3Config.accessKeyId}'`);
    await conn.run(`SET s3_secret_access_key='${s3Config.secretAccessKey}'`);
  }
  // Glob pattern reads multiple Parquet files in one query
  return conn.all(`SELECT * FROM read_parquet('${s3Uri}') WHERE ${sql} LIMIT 1000`);
}

Async Execution Status Machine Comparison

Pattern 3: Warehouse Lifecycle — "Reachable" Is Not "Ready to Execute Queries"

The most consistently misunderstood health probe failure mode in data warehouse integrations is that the API endpoint can respond successfully — returning HTTP 200, accepting statement submissions, returning statement IDs — while the underlying compute is unavailable to execute any query. This happens because of warehouse auto-suspend, cluster pausing, and provisioning delays. A naive health probe that checks "does the API accept a request?" will report healthy during these states.

Snowflake: Warehouse Auto-Suspend and Resume Latency

Snowflake virtual warehouses auto-suspend after a configurable idle period (minimum 60 seconds for most warehouse sizes). When a query arrives for a suspended warehouse, Snowflake resumes it automatically — but this resume adds 5–60 seconds of latency before the first row appears. For an MCP tool with a 30-second timeout, a warehouse that was idle for 5 minutes will appear to time out on the first query after suspension.

The correct health probe strategy for Snowflake is a dedicated monitoring warehouse, separate from the production query warehouse. Size it XS (smallest available) and set AUTO_SUSPEND = 60 so it comes back quickly. Run the health probe against the monitoring warehouse — never wake the production warehouse just for health checking.

-- Create a dedicated monitoring warehouse (run once):
CREATE WAREHOUSE IF NOT EXISTS HEALTH_WH
  WAREHOUSE_SIZE = 'X-SMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  COMMENT = 'Dedicated health probe warehouse — do not use for queries';

-- Health probe checks warehouse state before querying:
async function probeSnowflake(client) {
  // Check if monitoring warehouse is suspended
  const whResult = await client.executeStatement(
    `SHOW WAREHOUSES LIKE 'HEALTH_WH'`
  );
  const whRow = whResult.data?.[0];
  const state = whRow?.[3]; // 'STARTED' | 'SUSPENDED' | 'STARTING'

  // If SUSPENDED, pre-emptively resume before the health query
  if (state === 'SUSPENDED') {
    await client.executeStatement('ALTER WAREHOUSE HEALTH_WH RESUME');
    await new Promise(r => setTimeout(r, 5_000)); // brief wait for resume
  }

  // Run health query against the dedicated warehouse
  const start = Date.now();
  await client.executeStatement(
    'SELECT CURRENT_WAREHOUSE(), CURRENT_ROLE()',
    { warehouse: 'HEALTH_WH' }
  );
  return { latencyMs: Date.now() - start, warehouseState: state };
}

Databricks: SQL Warehouse Auto-Stop and Startup Delay

Databricks SQL warehouses auto-stop after a configurable idle period (default 45 minutes in most workspace configurations). Restarting a stopped warehouse takes 2–5 minutes. Unlike Snowflake where the API automatically resumes the warehouse on query, Databricks returns an error if you submit a query to a warehouse in STOPPED state — HTTP 400 with error RESOURCE_DOES_NOT_EXIST or a warehouse state error. You must explicitly start the warehouse and wait for it to reach RUNNING state before submitting queries.

async function ensureWarehouseRunning(client, warehouseId, startTimeoutMs = 300_000) {
  const state = await client.request('GET', `/api/2.0/sql/warehouses/${warehouseId}`);

  if (state.state === 'RUNNING') return; // Already ready

  if (state.state === 'STOPPED' || state.state === 'STOPPING') {
    await client.request('POST', `/api/2.0/sql/warehouses/${warehouseId}/start`);
  }

  // Poll until RUNNING
  const deadline = Date.now() + startTimeoutMs;
  while (Date.now() < deadline) {
    const current = await client.request('GET', `/api/2.0/sql/warehouses/${warehouseId}`);
    if (current.state === 'RUNNING') return;
    if (current.state === 'STOPPED') throw new Error('Warehouse failed to start');
    // STARTING — keep waiting
    await new Promise(r => setTimeout(r, 10_000));
  }
  throw new Error(`Warehouse ${warehouseId} did not reach RUNNING within ${startTimeoutMs}ms`);
}

// Health probe: check warehouse state first, then execute
async function probeDatabricks(client) {
  const wh = await client.request('GET', `/api/2.0/sql/warehouses/${client.warehouseId}`);
  const warehouseState = wh.state; // 'RUNNING' | 'STOPPED' | 'STARTING' | 'STOPPING'

  const start = Date.now();
  if (warehouseState === 'RUNNING') {
    // Quick query to verify actual query execution
    await client.request('POST', '/api/2.0/sql/statements', {
      statement: 'SELECT 1 AS health_check',
      warehouse_id: client.warehouseId,
      wait_timeout: '10s',
      on_wait_timeout: 'CONTINUE',
    });
  }
  return {
    warehouseState,
    queryReady: warehouseState === 'RUNNING',
    latencyMs: Date.now() - start,
  };
}

Redshift: Data API Responds Even When Cluster Is Paused

Redshift's Data API is a fully managed service layer separate from the Redshift compute cluster. When a provisioned Redshift cluster is paused, the Data API endpoint continues responding normally: ExecuteStatement returns HTTP 200 with a statement ID. Only when you poll DescribeStatement does the failure become visible — Status: 'FAILED' with Error containing "QueryException".

For Redshift Serverless, there is no explicit suspend mechanism — Serverless capacity is managed automatically. The primary cluster state check is via GetWorkgroup → workgroup.status (should be 'AVAILABLE') or DescribeClusters for provisioned clusters.

import {
  RedshiftClient,
  DescribeClustersCommand,
} from '@aws-sdk/client-redshift';
import {
  RedshiftServerlessClient,
  GetWorkgroupCommand,
} from '@aws-sdk/client-redshift-serverless';

// Provisioned cluster health — separate from Data API health
async function probeRedshiftProvisioned(clusterId, region) {
  const redshiftClient = new RedshiftClient({ region });
  const { Clusters } = await redshiftClient.send(
    new DescribeClustersCommand({ ClusterIdentifier: clusterId })
  );
  const cluster = Clusters[0];
  const clusterStatus = cluster.ClusterStatus; // 'available' | 'paused' | 'maintenance' | ...
  const available = clusterStatus === 'available';

  if (!available) {
    return { available: false, clusterStatus, message: `Cluster is ${clusterStatus}` };
  }

  // Only run query execution test if cluster is available
  // (Data API ExecuteStatement always returns 200, even when paused)
  const dataClient = new RedshiftDataClient({ region });
  const { Id: stmtId } = await dataClient.send(new ExecuteStatementCommand({
    ClusterIdentifier: clusterId,
    Database: process.env.REDSHIFT_DATABASE,
    DbUser: process.env.REDSHIFT_DB_USER,
    Sql: 'SELECT 1 AS health_check',
  }));
  const result = await pollRedshiftStatement(dataClient, stmtId, 15_000);
  return { available: true, clusterStatus, queryExecuted: true };
}

BigQuery: Serverless — No Warehouse to Suspend

BigQuery is fully serverless — there is no virtual warehouse or compute cluster to suspend. Query capacity is allocated automatically per-job. The absence of a warehouse lifecycle problem is one of BigQuery's operational advantages for MCP tools. The primary health failure mode for BigQuery is IAM drift (losing dataViewer on a dataset) rather than compute availability.

DuckDB: Latency-Based Health Signal

DuckDB has no network endpoint to probe, no HTTP API, no daemon process. The health signal for DuckDB in an MCP server is query execution latency. A DuckDB instance under memory pressure, with a fragmented WAL, or spilling to a slow disk will show elevated latency on a simple SELECT 1 query — well before it would fail outright.

// DuckDB health probe: latency-based, not reachability-based
async function probeDuckDB(conn) {
  const start = Date.now();
  await conn.all('SELECT count(*) FROM (SELECT 1) t');
  const latencyMs = Date.now() - start;

  // Thresholds: healthy < 100ms, degraded 100-500ms, unhealthy > 500ms
  const status = latencyMs < 100 ? 'healthy'
    : latencyMs < 500 ? 'degraded'
    : 'unhealthy';

  return { status, latencyMs };
}

// Expose via HTTP endpoint in the MCP server:
app.get('/health/duckdb', async (req, res) => {
  const result = await probeDuckDB(conn);
  res.status(result.status === 'unhealthy' ? 503 : 200).json(result);
});

Warehouse Lifecycle Comparison

Composite Health Probe Design

A production-quality MCP health probe for data warehouse integrations must verify three things: credentials are valid, the warehouse/cluster is in a state that can execute queries, and an actual query executes successfully. Testing only the first leaves the second and third failure modes invisible to monitoring.

// Composite health probe — verify all three layers
const probes = {
  async bigquery(bq, datasetId) {
    // Layer 1: credential validity (dry-run, zero cost, no data-plane access)
    await bq.createQueryJob({ query: 'SELECT 1', dryRun: true });
    // Layer 2: data-plane permission (required for real queries)
    await bq.dataset(datasetId).getTables();
    // No warehouse state to check — BigQuery is serverless
    return { auth: 'ok', dataPlane: 'ok' };
  },

  async snowflake(client) {
    // Layer 1: JWT validity (implicit — any request fails if token bad)
    // Layer 2: warehouse state
    const [{ state }] = await client.executeStatement('SHOW WAREHOUSES LIKE ?', ['HEALTH_WH']);
    // Layer 3: actual query execution
    const start = Date.now();
    await client.executeStatement('SELECT 1', { warehouse: 'HEALTH_WH' });
    return { auth: 'ok', warehouseState: state, latencyMs: Date.now() - start };
  },

  async redshift(dataClient, redshiftClient, clusterId, conn) {
    // Layer 1: IAM (implicit — any call fails if credentials invalid)
    // Layer 2: cluster availability
    const { Clusters } = await redshiftClient.send(new DescribeClustersCommand({ ClusterIdentifier: clusterId }));
    const clusterStatus = Clusters[0].ClusterStatus;
    if (clusterStatus !== 'available') return { auth: 'ok', clusterStatus, queryReady: false };
    // Layer 3: actual query (Data API says HTTP 200 even when paused — must check query result)
    const stmtId = (await dataClient.send(new ExecuteStatementCommand({ ...conn, Sql: 'SELECT 1' }))).Id;
    await pollRedshiftStatement(dataClient, stmtId, 15_000);
    return { auth: 'ok', clusterStatus, queryReady: true };
  },

  async databricks(client) {
    // Layer 1: Bearer token validity (any request returns 401 if bad)
    // Layer 2: warehouse state
    const wh = await client.request('GET', `/api/2.0/sql/warehouses/${client.warehouseId}`);
    if (wh.state !== 'RUNNING') return { auth: 'ok', warehouseState: wh.state, queryReady: false };
    // Layer 3: actual query execution
    const start = Date.now();
    await client.request('POST', '/api/2.0/sql/statements', {
      statement: 'SELECT 1', warehouse_id: client.warehouseId,
      wait_timeout: '10s', on_wait_timeout: 'CONTINUE',
    });
    return { auth: 'ok', warehouseState: 'RUNNING', latencyMs: Date.now() - start };
  },

  async duckdb(conn) {
    // Layer 1: no auth
    // Layer 2: no warehouse
    // Layer 3: query latency as the only signal
    const start = Date.now();
    await conn.all('SELECT count(*) FROM (SELECT 1) t');
    const latencyMs = Date.now() - start;
    return {
      status: latencyMs < 100 ? 'healthy' : latencyMs < 500 ? 'degraded' : 'unhealthy',
      latencyMs,
    };
  },
};

Unity Catalog and Three-Level Namespace (Databricks)

Databricks workspaces migrated to Unity Catalog use a three-level namespace: catalog.schema.table. Workspaces not on Unity Catalog use the legacy two-level Hive metastore: schema.table. SQL queries written for the Hive metastore may fail on Unity Catalog workspaces with "Table or view not found" even when the table exists — because the catalog level is implicit and defaults to the workspace's default catalog, not the catalog containing the table.

Always qualify table references with three levels in SQL intended for Unity Catalog workspaces, or pass the catalog parameter in the statement execution request to set the default catalog for the session:

// Explicit catalog in request body — sets default for unqualified names
const body = {
  statement: 'SELECT * FROM my_schema.my_table LIMIT 10',
  warehouse_id: client.warehouseId,
  catalog: 'main',  // Unity Catalog catalog name
  schema: 'my_schema',
  // ...
};

// OR: fully-qualified three-level reference in SQL
const sql = 'SELECT * FROM main.my_schema.my_table LIMIT 10';

The Hive metastore is still accessible via the hive_metastore catalog name for backwards compatibility: hive_metastore.my_schema.my_table. Use this when migrating SQL that predates Unity Catalog adoption.

BigQuery Dry-Run as a Cost Gate

MCP tools that accept user-supplied SQL for BigQuery must implement cost estimation as a first-class gate. BigQuery's on-demand pricing charges $5 per TB scanned. A query submitted by an LLM agent could scan a multi-TB table and incur immediate billing, with no configurable spending cap at the API call level unless you use maximumBytesBilled.

The dry-run endpoint returns statistics.totalBytesProcessed — an accurate estimate of bytes that would be scanned — before any execution occurs. Implement a mandatory dry-run + confirmation gate for all user-SQL tools:

server.tool('query_bigquery', {
  sql: z.string().describe('SQL query to execute against BigQuery'),
  confirm_large_scan: z.boolean().optional()
    .describe('Set true to confirm execution when estimated scan > 10 GB'),
}, async ({ sql, confirm_large_scan }) => {
  // Always dry-run first
  const [dryRun] = await bq.createQueryJob({ query: sql, dryRun: true });
  const bytesStr = dryRun.metadata.statistics.totalBytesProcessed;
  const bytes = parseInt(bytesStr, 10);
  const gbScanned = (bytes / 1_073_741_824).toFixed(2);
  const estimatedCostUsd = ((bytes / 1_099_511_627_776) * 5).toFixed(4);

  if (bytes > 10 * 1_073_741_824 && !confirm_large_scan) {
    return {
      content: [{
        type: 'text',
        text: `Query would scan ${gbScanned} GB (~$${estimatedCostUsd}). ` +
              `Set confirm_large_scan: true to proceed.`,
      }],
    };
  }

  // Hard cap: reject queries estimated over 100 GB regardless of confirmation
  if (bytes > 100 * 1_073_741_824) {
    return {
      content: [{ type: 'text', text: `Query rejected: estimated scan ${gbScanned} GB exceeds 100 GB hard cap.` }],
    };
  }

  const [job] = await bq.createQueryJob({
    query: sql,
    maximumBytesBilled: String(100 * 1_073_741_824), // hard billing cap at API level
  });
  await pollBigQueryJob(job);
  const rows = [];
  for await (const batch of streamBigQueryResults(job)) rows.push(...batch);
  return { content: [{ type: 'text', text: JSON.stringify(rows.slice(0, 1000), null, 2) }] };
});

Monitoring Data Warehouse MCP Servers With AliveMCP

The three failure modes documented in this post — IAM permission drift across job and data planes, warehouse auto-suspend making reachability an incomplete health signal, and paused clusters that accept API calls while rejecting actual queries — are all silent by default. The API returns HTTP 200. The statement gets created. The failure only surfaces when results are expected and never arrive.

AliveMCP probes MCP server endpoints every 60 seconds. For data warehouse MCP tools, the composite health probes from this post — credential validity + warehouse state + actual query execution — should be wired as the endpoint's health check so AliveMCP can detect IAM drift, suspended warehouses, and paused clusters before LLM agent users encounter timeout errors.

Each of the five platforms covered in this post has a dedicated per-platform guide with complete working code: BigQuery (IAM split-plane, async Jobs API, dry-run cost gating, pageToken pagination), Snowflake (JWT RS256 key-pair, SQL API v2, warehouse auto-suspend latency, identifier case folding), Redshift (Data API SigV4, async statement polling, provisioned vs Serverless params, paused cluster detection), Databricks (PAT + OAuth2 M2M, statement execution, warehouse lifecycle, Unity Catalog namespace), and DuckDB (in-process setup, file locking, memory limits, S3 Parquet querying, latency-based health probes). The AliveMCP blog covers similar integration patterns for transactional email APIs, CRM platforms, cloud storage, and the broader MCP ecosystem.