MCP Tools for Databricks — SQL Warehouse REST API, PAT auth, statement execution, warehouse lifecycle, Unity Catalog

Authentication — Personal Access Token and OAuth M2M

Databricks supports two auth methods for MCP servers: Personal Access Token (PAT) for simple deployments and OAuth2 M2M (machine-to-machine) with a service principal for production. PATs are workspace-scoped, never expire by default (unless workspace policy enforces expiry), and are simpler to configure. OAuth2 M2M service principals support scoped permissions and token rotation, preferred when multiple MCP servers access the same workspace.

class DatabricksClient {
  constructor({ workspaceHost, token, warehouseId }) {
    // workspaceHost: 'adb-1234567890.12.azuredatabricks.net' (no trailing slash, no https://)
    this.baseUrl = `https://${workspaceHost}`;
    this.headers = {
      'Authorization': `Bearer ${token}`,
      'Content-Type': 'application/json',
    };
    this.warehouseId = warehouseId;
  }

  async request(method, path, body = null) {
    const res = await fetch(`${this.baseUrl}${path}`, {
      method,
      headers: this.headers,
      body: body ? JSON.stringify(body) : null,
      signal: AbortSignal.timeout(30_000),
    });

    if (!res.ok) {
      const text = await res.text().catch(() => '');
      throw new Error(`Databricks ${method} ${path} → ${res.status}: ${text.slice(0, 400)}`);
    }

    return res.json();
  }
}

// OAuth2 M2M token fetch — service principal client_id + client_secret
async function getDatabricksOAuthToken(workspaceHost, clientId, clientSecret) {
  const res = await fetch(`https://${workspaceHost}/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(),
  });

  if (!res.ok) throw new Error(`OAuth token request failed: ${res.status}`);
  const data = await res.json();
  return { token: data.access_token, expiresIn: data.expires_in };
}

Statement execution and result retrieval

Submit SQL to the statement execution endpoint with a wait_timeout. Queries that complete within the timeout return results inline. Queries that exceed the timeout return a statement_id for async polling. Always set on_wait_timeout: "CONTINUE" — the default "CANCEL" aborts the query if it doesn't finish within the timeout.

async function executeStatement(client, sql, options = {}) {
  const {
    catalog,
    schema,
    timeoutSec = 30,
    rowLimit = 10_000,
  } = options;

  const body = {
    statement: sql,
    warehouse_id: client.warehouseId,
    wait_timeout: `${timeoutSec}s`,
    on_wait_timeout: 'CONTINUE', // Don't cancel — continue async
    ...(catalog ? { catalog } : {}),
    ...(schema ? { schema } : {}),
    // Row limit applied in query result — does not add SQL LIMIT
    disposition: 'INLINE',  // vs 'EXTERNAL_LINKS' for large results
  };

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

  if (status.state === 'SUCCEEDED') {
    return parseStatementResult(response);
  }

  if (status.state === 'FAILED') {
    throw new Error(
      `Databricks statement failed: ${status.error?.message} (code: ${status.error?.error_code})`
    );
  }

  // PENDING or RUNNING — poll for completion
  return await pollStatement(client, statement_id);
}

async function pollStatement(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 state = response.status.state;
    // States: PENDING | RUNNING | SUCCEEDED | FAILED | CLOSED | CANCELED

    if (state === 'SUCCEEDED') return parseStatementResult(response);

    if (state === 'FAILED') {
      throw new Error(
        `Statement ${statementId} failed: ${response.status.error?.message}`
      );
    }

    if (state === 'CANCELED' || state === 'CLOSED') {
      throw new Error(`Statement ${statementId} is ${state}`);
    }

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

  // Cancel orphaned statement
  await client.request('DELETE', `/api/2.0/sql/statements/${statementId}`).catch(() => {});
  throw new Error(`Statement ${statementId} did not complete in ${maxWaitMs}ms`);
}

// Parse both inline (data_array) and external_links result formats
async function parseStatementResult(response) {
  const schema = response.manifest?.schema?.columns || [];
  const colNames = schema.map(c => c.name);

  // Small results — inline data_array
  if (response.result?.data_array) {
    const rows = response.result.data_array.map(row =>
      Object.fromEntries(colNames.map((name, i) => [name, row[i]]))
    );
    return { rows, schema, totalRows: response.manifest?.total_row_count };
  }

  // Large results — external_links (presigned S3 URLs, expire in 15 min)
  const allRows = [];
  if (response.result?.external_links) {
    for (const chunk of response.result.external_links) {
      const chunkRes = await fetch(chunk.external_link); // presigned URL — no auth header
      const data = await chunkRes.json();
      const rows = (data.data_array || []).map(row =>
        Object.fromEntries(colNames.map((name, i) => [name, row[i]]))
      );
      allRows.push(...rows);
    }
  }

  return { rows: allRows, schema, totalRows: response.manifest?.total_row_count };
}

SQL Warehouse lifecycle — detecting and starting stopped warehouses

Databricks SQL Warehouses auto-stop after a configurable idle period (default: 45 minutes). Sending a statement to a stopped warehouse starts it automatically — but the statement queue doesn't execute until the warehouse is RUNNING, which adds 2–5 minutes of cold-start latency to the first query. For MCP tools where response time matters, check warehouse state and explicitly start it if needed rather than waiting for the auto-start to complete.

async function getWarehouseState(client, warehouseId) {
  const wh = await client.request('GET', `/api/2.0/sql/warehouses/${warehouseId}`);
  return {
    id: wh.id,
    name: wh.name,
    state: wh.state,
    // States: STARTING | RUNNING | STOPPING | STOPPED | DELETING | DELETED
    size: wh.cluster_size, // '2X-Small' | 'X-Small' | 'Small' | ... | '4X-Large'
    autoStopMins: wh.auto_stop_mins,
    numClusters: wh.num_clusters,
    creatorName: wh.creator_name,
  };
}

async function ensureWarehouseRunning(client, warehouseId, maxWaitMs = 300_000) {
  const state = await getWarehouseState(client, warehouseId);

  if (state.state === 'RUNNING') return state;

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

  // Wait for RUNNING
  const deadline = Date.now() + maxWaitMs;
  while (Date.now() < deadline) {
    await new Promise(r => setTimeout(r, 5_000));
    const current = await getWarehouseState(client, warehouseId);
    if (current.state === 'RUNNING') return current;
    if (current.state === 'DELETED' || current.state === 'DELETING') {
      throw new Error(`Warehouse ${warehouseId} is being deleted`);
    }
  }

  throw new Error(`Warehouse ${warehouseId} did not reach RUNNING within ${maxWaitMs}ms`);
}

// List all accessible warehouses
async function listWarehouses(client) {
  const result = await client.request('GET', '/api/2.0/sql/warehouses');
  return (result.warehouses || []).map(wh => ({
    id: wh.id,
    name: wh.name,
    state: wh.state,
    size: wh.cluster_size,
  }));
}

Unity Catalog schema introspection

Unity Catalog organizes data in a three-level namespace: catalog.schema.table. SQL queries must either explicitly qualify table names (SELECT * FROM mycatalog.myschema.mytable) or set the default catalog and schema at the session level. The Statement Execution API accepts catalog and schema parameters in the request body to set session defaults without requiring USE CATALOG and USE SCHEMA SQL statements.

// List catalogs
async function listCatalogs(client) {
  const result = await client.request('GET', '/api/2.1/unity-catalog/catalogs');
  return (result.catalogs || []).map(c => ({
    name: c.name,
    comment: c.comment,
    owner: c.owner,
    createdAt: c.created_at,
    metastoreId: c.metastore_id,
  }));
}

// List schemas in a catalog
async function listSchemas(client, catalogName) {
  const result = await client.request(
    'GET', `/api/2.1/unity-catalog/schemas?catalog_name=${encodeURIComponent(catalogName)}`
  );
  return (result.schemas || []).map(s => ({
    name: s.name,
    catalogName: s.catalog_name,
    comment: s.comment,
    owner: s.owner,
  }));
}

// List tables in a schema
async function listTables(client, catalogName, schemaName) {
  const result = await client.request(
    'GET',
    `/api/2.1/unity-catalog/tables?catalog_name=${encodeURIComponent(catalogName)}&schema_name=${encodeURIComponent(schemaName)}`
  );
  return (result.tables || []).map(t => ({
    name: t.name,
    fullName: t.full_name, // catalog.schema.table
    tableType: t.table_type, // 'MANAGED' | 'EXTERNAL' | 'VIEW' | 'MATERIALIZED_VIEW'
    dataSourceFormat: t.data_source_format, // 'DELTA' | 'CSV' | 'PARQUET' | ...
    comment: t.comment,
    owner: t.owner,
    columns: (t.columns || []).map(c => ({
      name: c.name,
      typeText: c.type_text,
      nullable: c.nullable,
      comment: c.comment,
    })),
  }));
}

Health monitoring for Databricks MCP tools

A Databricks health probe needs to check: (1) authentication and workspace reachability, (2) SQL warehouse state — whether the warehouse is RUNNING or STOPPED, and (3) query execution capability. The warehouse state check is the most important — a stopped warehouse looks healthy from an API perspective but adds minutes of latency to the first real query.

async function probeDatabricks(client, warehouseId) {
  const results = await Promise.allSettled([

    // 1. Workspace auth and reachability
    (async () => {
      const wh = await client.request('GET', `/api/2.0/sql/warehouses/${warehouseId}`);
      return {
        kind: 'auth',
        workspaceReachable: true,
        warehouseName: wh.name,
      };
    })(),

    // 2. Warehouse state — critical for latency prediction
    (async () => {
      const state = await getWarehouseState(client, warehouseId);
      if (state.state === 'DELETED' || state.state === 'DELETING') {
        throw new Error(`Warehouse ${warehouseId} is ${state.state}`);
      }
      return {
        kind: 'warehouse',
        state: state.state,
        size: state.size,
        warning: state.state !== 'RUNNING' ? `warehouse is ${state.state} — first query will be slow` : null,
      };
    })(),

    // 3. Query execution — only if warehouse is likely running
    (async () => {
      const result = await executeStatement(client, 'SELECT 1 AS alive', { timeoutSec: 15 });
      return { kind: 'query', rowCount: result.rows.length };
    })(),
  ]);

  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 at a 5-minute interval. Set the expected response time to 10 seconds — if the warehouse is STOPPED, the state check itself will return quickly, but AliveMCP can flag it as a warning-level condition for you to act on before the next real query arrives.

Common integration pitfalls

Using EXTERNAL_LINKS disposition but not refreshing presigned URLs

External result links (presigned S3/ADLS/GCS URLs) expire in 15 minutes after creation. If result chunks are fetched more than 15 minutes after the statement succeeds, the presigned URL returns a 403. For large result sets that take time to process, re-fetch the statement to get fresh links: GET /api/2.0/sql/statements/{id} returns new external_links with a fresh expiry.

Missing catalog context in Unity Catalog environments

In Unity Catalog workspaces, the default catalog is hive_metastore unless explicitly set. SQL that references tables without a catalog qualifier (SELECT * FROM myschema.mytable) resolves against hive_metastore.myschema.mytable. If the table was created in a Unity Catalog catalog, add the catalog parameter to the Statement Execution API request, or use fully qualified names in SQL.

Statement size limit and LIMIT in SQL

The Statement Execution API returns at most 1 MB inline (INLINE disposition). For larger results, either set disposition: 'EXTERNAL_LINKS' explicitly, or add a LIMIT clause to the SQL. The API's row limit parameter limits how many rows are returned in the response, but does not add LIMIT to the SQL — the full query executes and scans all data regardless.

Checking warehouse ID vs warehouse name

Warehouse IDs are alphanumeric strings (e.g., abc123def456). Warehouse names are human-readable and can change. Always store the warehouse ID in configuration, not the name — a renamed warehouse still has the same ID.

PAT expiry in workspace with token lifecycle policy

Databricks workspace admins can enforce PAT expiry policies (e.g., 90-day max lifetime). A PAT that worked 3 months ago may suddenly return 403 PERMISSION_DENIED: This token has expired. Monitor this by checking the expiry_time field when listing tokens via GET /api/2.0/token/list, and alert at least 14 days before expiry.

Related guides

• MCP tools for BigQuery — service account auth, async Jobs API, dry-run cost estimation
• MCP tools for Snowflake — SQL API v2, JWT auth, warehouse auto-suspend
• MCP tools for Redshift Data API — SigV4 auth, async statement execution, cluster health
• MCP tools for DuckDB — in-process analytics, no-network health probes, MotherDuck
• MCP server health check patterns — composite endpoints and failure classification
• MCP server uptime monitoring — probes, intervals, and alert routing