Guide · Data Warehouse

MCP Tools for Snowflake — SQL API v2, JWT RS256 auth, warehouse auto-suspend latency, async statement polling

Snowflake SQL API v2 is the recommended interface for MCP tools: it accepts SQL over HTTPS without requiring a JDBC driver or native connector. Three issues are consistently surprising for teams building on it: authentication uses JWT RS256 key pairs — not username/password, not OAuth client credentials directly — and the JWT token format requires a specific sub claim format including the public key fingerprint; virtual warehouses auto-suspend after a configurable idle period, and the first query after suspension triggers a resume that adds 5–60 seconds of latency before rows return, which looks like a timeout failure in health probes; and Snowflake folds all unquoted identifiers to uppercase, so a table created as my_events is stored as MY_EVENTS — queries using lowercase names fail unless the identifier was double-quoted at creation time.

TL;DR

Auth: key-pair JWT — generate RSA-2048 key pair, register public key with ALTER USER ... SET RSA_PUBLIC_KEY='...', sign JWT with RS256 (sub: {account}.{user}, iss: {account}.{user}.SHA256:{fingerprint}, exp: now+60s). Submit: POST https://{account}.snowflakecomputing.com/api/v2/statements with header X-Snowflake-Authorization-Token-Type: KEYPAIR_JWT. Fast queries return rows immediately (HTTP 200); slow queries return statementHandle (HTTP 202). Poll: GET /api/v2/statements/{statementHandle} until status: 'success'. Pagination: include partition=N query parameter starting at 0. Health probe: query SELECT CURRENT_WAREHOUSE(), CURRENT_ROLE() on a dedicated HEALTH_WH warehouse sized XS to avoid waking the main warehouse.

Authentication — JWT RS256 key-pair setup

Snowflake SQL API authenticates with a signed JWT. The token must be generated fresh for each session (or at most cached for 55 seconds, since the token expires in 60). The JWT payload requires a specific claim structure that includes the SHA256 fingerprint of the user's registered public key.

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

class SnowflakeJWTAuth {
  constructor({ account, user, privateKeyPem }) {
    // Snowflake account identifier must be uppercase, dots/hyphens preserved
    this.account = account.toUpperCase();
    this.user = user.toUpperCase();
    this.privateKey = createPrivateKey(privateKeyPem);

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

    this.tokenExpireAt = 0;
    this.cachedToken = null;
  }

  getToken() {
    const now = Math.floor(Date.now() / 1000);
    if (this.cachedToken && now < this.tokenExpireAt - 5) return this.cachedToken;

    // JWT header + payload — RS256 (RSASSA-PKCS1-v1_5, not PS256)
    const header = Buffer.from(JSON.stringify({ alg: 'RS256', typ: 'JWT' })).toString('base64url');
    const payload = Buffer.from(JSON.stringify({
      iss: `${this.account}.${this.user}.${this.fingerprint}`,
      sub: `${this.account}.${this.user}`,
      iat: now,
      exp: now + 60,
    })).toString('base64url');

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

    this.cachedToken = `${header}.${payload}.${signature}`;
    this.tokenExpireAt = now + 60;
    return this.cachedToken;
  }
}

// Register the public key with Snowflake (run once, as ACCOUNTADMIN):
// ALTER USER my_user SET RSA_PUBLIC_KEY='MIIBIjANBgkq...';
// Verify: DESC USER my_user; — look for RSA_PUBLIC_KEY_FP field

Snowflake supports two registered public keys per user (RSA_PUBLIC_KEY and RSA_PUBLIC_KEY_2) to enable zero-downtime key rotation — rotate by registering a new key as key 2, then updating the application to use the new private key, then removing key 1.

Submitting SQL via the SQL API v2

POST a JSON body to the statements endpoint. Fast queries that complete within Snowflake's synchronous timeout return rows immediately in the response (HTTP 200). Slower queries return a statementHandle and HTTP 202, requiring polling. Always handle both cases.

class SnowflakeSQLClient {
  constructor({ account, user, privateKeyPem, warehouse, database, schema, role }) {
    this.baseUrl = `https://${account.toLowerCase()}.snowflakecomputing.com`;
    this.auth = new SnowflakeJWTAuth({ account, user, privateKeyPem });
    this.defaults = { warehouse, database, schema, role };
  }

  async executeStatement(sql, params = [], timeout = '30s') {
    const body = {
      statement: sql,
      timeout: parseInt(timeout, 10),
      // Named bindings: use ?-style in SQL, indexed in bindings array
      ...(params.length ? {
        bindings: Object.fromEntries(params.map((v, i) => [
          String(i + 1),
          { type: typeof v === 'number' ? 'FIXED' : 'TEXT', value: String(v) },
        ])),
      } : {}),
      ...this.defaults,
    };

    const res = await fetch(`${this.baseUrl}/api/v2/statements`, {
      method: 'POST',
      headers: {
        'Content-Type': 'application/json',
        'Accept': 'application/json',
        'Authorization': `Bearer ${this.auth.getToken()}`,
        'X-Snowflake-Authorization-Token-Type': 'KEYPAIR_JWT',
        // Required: tells Snowflake which SQL API version to use
        'User-Agent': 'mcp-server/1.0',
      },
      body: JSON.stringify(body),
    });

    if (res.status === 200) {
      return await res.json(); // Completed synchronously
    }

    if (res.status === 202) {
      const data = await res.json();
      return await this.pollStatement(data.statementHandle);
    }

    const text = await res.text();
    throw new Error(`Snowflake API ${res.status}: ${text.slice(0, 400)}`);
  }

  async pollStatement(statementHandle, maxWaitMs = 120_000) {
    const deadline = Date.now() + maxWaitMs;

    while (Date.now() < deadline) {
      const res = await fetch(`${this.baseUrl}/api/v2/statements/${statementHandle}`, {
        headers: {
          'Authorization': `Bearer ${this.auth.getToken()}`,
          'X-Snowflake-Authorization-Token-Type': 'KEYPAIR_JWT',
        },
      });

      const data = await res.json();
      const status = data.status; // 'running' | 'success' | 'failed'

      if (status === 'success') return data;

      if (status === 'failed') {
        const msg = data.message || JSON.stringify(data.statementStatusUrl);
        throw new Error(`Snowflake statement ${statementHandle} failed: ${msg}`);
      }

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

    throw new Error(`Snowflake statement ${statementHandle} did not complete in ${maxWaitMs}ms`);
  }
}

// Result format — data is an array of arrays (row-major)
// resultSetMetaData.rowType gives column names and types
function parseResults(response) {
  const cols = response.resultSetMetaData.rowType;
  return (response.data || []).map(row =>
    Object.fromEntries(cols.map((col, i) => [col.name, row[i]]))
  );
}

Warehouse auto-suspend and resume latency

Snowflake virtual warehouses automatically suspend after a configurable idle period (default: 10 minutes for XS warehouses, configurable down to 1 minute). When a query arrives for a suspended warehouse, Snowflake auto-resumes it — but resume takes 5–60 seconds depending on warehouse size and cluster count. This resume latency appears as a timeout in health probes that expect responses within a few seconds.

// Warehouse state management
async function getWarehouseState(client) {
  const result = await client.executeStatement('SHOW WAREHOUSES LIKE \'%\'');
  const rows = parseResults(result);
  return rows.map(r => ({
    name: r.name,
    state: r.state,          // 'STARTED' | 'SUSPENDED' | 'RESIZING' | 'STARTING'
    size: r.size,
    autoSuspend: r.auto_suspend, // seconds of inactivity before suspend
    running: r.running,      // currently running queries
    queued: r.queued,
    isDefault: r.is_default === 'Y',
  }));
}

// Explicitly resume a warehouse before a health probe
// to avoid mistaking resume latency for a failure
async function ensureWarehouseStarted(client, warehouseName, timeoutMs = 90_000) {
  // Resume command is idempotent — safe if already running
  await client.executeStatement(`ALTER WAREHOUSE ${warehouseName} RESUME IF SUSPENDED`);

  const deadline = Date.now() + timeoutMs;
  while (Date.now() < deadline) {
    const states = await getWarehouseState(client);
    const wh = states.find(w => w.name === warehouseName.toUpperCase());
    if (!wh) throw new Error(`Warehouse ${warehouseName} not found`);
    if (wh.state === 'STARTED') return wh;
    await new Promise(r => setTimeout(r, 3_000));
  }
  throw new Error(`Warehouse ${warehouseName} did not start within ${timeoutMs}ms`);
}

// Best practice: use a dedicated XS warehouse for health probes
// so the probe doesn't trigger resume of the main COMPUTE_WH
// SET AUTO_SUSPEND = 60 on the health probe warehouse to keep it warm cheaply

A dedicated HEALTH_PROBE_WH warehouse set to X-Small with auto-suspend of 60 seconds costs roughly $0.0005/hour when idle and resumes in under 5 seconds — cheap insurance against health probes that falsely time out because they woke the main warehouse.

Identifier case folding and schema introspection

Snowflake folds all unquoted identifiers to uppercase at creation and query time. A table created as CREATE TABLE my_events (...) is stored as MY_EVENTS. SQL querying my_events works because Snowflake folds the query identifier too. But code that reads the table name from the API (SHOW TABLES returns MY_EVENTS) and then queries it as my_events also works. The problem arises when identifiers were created with double-quotes: CREATE TABLE "my_events" (...) stores the table as my_events (mixed-case preserved) and requires double-quotes in every subsequent query.

// Schema introspection using INFORMATION_SCHEMA
async function listSchemaObjects(client, database, schema) {
  const tables = await client.executeStatement(`
    SELECT
      TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,
      TABLE_TYPE, ROW_COUNT, BYTES, CREATED, LAST_ALTERED
    FROM ${database}.INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = '${schema.toUpperCase()}'
    ORDER BY TABLE_NAME
  `);

  return parseResults(tables);
}

async function getColumnMetadata(client, database, schema, table) {
  const result = await client.executeStatement(`
    SELECT
      COLUMN_NAME, DATA_TYPE, IS_NULLABLE,
      CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION,
      COLUMN_DEFAULT, COMMENT
    FROM ${database}.INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = '${schema.toUpperCase()}'
      AND TABLE_NAME = '${table.toUpperCase()}'
    ORDER BY ORDINAL_POSITION
  `);
  return parseResults(result);
}

// For VARIANT columns (JSON), cast before use in filters:
// SELECT v:field::STRING FROM table WHERE v:id::NUMBER = 123

Health monitoring for Snowflake MCP tools

A Snowflake health probe must check: (1) JWT token generation is working (private key loaded correctly), (2) the warehouse assigned to the MCP tool is reachable and will start within an acceptable latency, and (3) the user and role combination has the required database and schema access. Use a dedicated XS probe warehouse rather than the main compute warehouse.

async function probeSnowflake(client, probeWarehouse = 'HEALTH_PROBE_WH') {
  const results = await Promise.allSettled([

    // 1. JWT auth + account reachability
    (async () => {
      const r = await client.executeStatement('SELECT CURRENT_ACCOUNT(), CURRENT_REGION()');
      const rows = parseResults(r);
      return { kind: 'auth', account: rows[0].CURRENT_ACCOUNT(), region: rows[0].CURRENT_REGION() };
    })(),

    // 2. Warehouse state — detect suspended before timing out
    (async () => {
      const states = await getWarehouseState(client);
      const wh = states.find(w => w.name === probeWarehouse.toUpperCase());
      if (!wh) throw new Error(`Probe warehouse ${probeWarehouse} not found`);
      return { kind: 'warehouse', state: wh.state, size: wh.size };
    })(),

    // 3. Data access — verify role + database grants
    (async () => {
      const r = await client.executeStatement(
        'SELECT CURRENT_ROLE(), CURRENT_DATABASE(), CURRENT_SCHEMA()'
      );
      const rows = parseResults(r);
      return {
        kind: 'access',
        role: rows[0].CURRENT_ROLE(),
        database: rows[0].CURRENT_DATABASE(),
        schema: rows[0].CURRENT_SCHEMA(),
      };
    })(),
  ]);

  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 warehouse state check with AliveMCP as its own monitor with a longer timeout (90 seconds) to accommodate warehouse resume latency. Set the alert threshold at 2 consecutive failures — a single timeout during warehouse resume is not a real outage.

Common integration pitfalls

JWT token expiry and caching: Snowflake JWT tokens expire in 60 seconds. Reusing a token cached more than 55 seconds ago returns JWT token is expired. Always regenerate the token per-request or cache it with a 55-second TTL. The fingerprint (SHA256: + base64 of public key DER) never changes, so only the iat/exp claims change between token refreshes.
Account identifier format: The account identifier in JWT claims and the API URL must use the full locator form: {org}-{account} (e.g., MYORG-MYACCOUNT) for accounts created after 2021, or {account}.{region}.{cloud} (e.g., xy12345.us-east-1.aws) for legacy accounts. Mixing the two formats causes 401 errors. Check with SELECT CURRENT_ACCOUNT().
Warehouse not explicitly set in API request: If no warehouse is specified in the API request body and the user has no default warehouse set, the query returns No active warehouse selected in the current session. Always include warehouse in the request body, or set a default for the user with ALTER USER ... SET DEFAULT_WAREHOUSE = ....
VARIANT column serialization in result rows: VARIANT columns (JSON objects/arrays) are returned as JSON-encoded strings in the SQL API response rows array. Code that expects a parsed object gets a string — call JSON.parse(row[i]) for VARIANT column values. Check resultSetMetaData.rowType[i].type === 'variant' to identify which columns need parsing.
Case-sensitive identifiers from double-quoted creation: If a table or column was created with double-quotes (CREATE TABLE "MyTable"), every subsequent reference must also use double-quotes. Unquoted references fold to uppercase and fail with Object 'MYTABLE' does not exist. Use SHOW TABLES to see the exact stored identifier before querying.