Snowflake Data Warehouse Analytics & Query Optimizer FastMCP TypeScript Server

By Deepak Bagada, CEO at SaaSNext & Principal AI Architect

Welcome to another deep dive on Daily AI World Model Context Protocol Directory. In 2026, data engineering teams are shifting away from manual SQL tuning and dashboarding towards AI-driven, autonomous data operations. A critical part of this evolution is securely connecting Large Language Models (LLMs) to enterprise data warehouses like Snowflake.

In this comprehensive guide, we will build a Snowflake Data Warehouse Analytics & Query Optimizer FastMCP TypeScript Server. By the end of this tutorial, your local Claude Desktop and Cursor IDE will be able to securely query Snowflake, analyze warehouse utilization, and autonomously optimize expensive SQL queries.

Why Build a Snowflake MCP Server?

Snowflake is a powerhouse for enterprise data, but analyzing its meta-data and query history can be time-consuming. By exposing Snowflake through the Model Context Protocol (MCP), you empower an AI agent to:

1. Autonomous Query Optimization: The agent can fetch the QUERY_HISTORY, identify slow-running or highly-priced queries, and suggest rewritten SQL using its LLM reasoning capabilities.
2. Warehouse Utilization Monitoring: Agents can track credit usage and proactively suggest warehouse scaling (up or down) based on workload patterns.
3. Data Discovery: Agents can seamlessly explore INFORMATION_SCHEMA to find relevant tables and columns for analytics tasks without requiring the user to leave their IDE.

Prerequisites

Before we begin, ensure you have the following:

• Node.js (v20+) installed.
• A Snowflake Account with ACCOUNTADMIN or equivalent privileges to create users and roles.
• Claude Desktop or Cursor IDE installed.
• Basic knowledge of TypeScript and SQL.

Step 1: Setting up the Snowflake Service Account

We must never use personal credentials for an MCP server. Instead, we'll configure a dedicated service account and an OAuth security layer (or key-pair authentication) for secure, programmatic access.

-- Execute in Snowflake Snowsight
USE ROLE ACCOUNTADMIN;

-- Create a dedicated role for the MCP Server
CREATE ROLE MCP_ANALYTICS_ROLE;

-- Grant access to necessary databases (e.g., SNOWFLAKE database for account usage)
GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE MCP_ANALYTICS_ROLE;

-- Create the service user
CREATE USER MCP_SERVICE_USER
    PASSWORD = 'SuperSecretPassword123!' -- In production, use Key-Pair Auth
    DEFAULT_ROLE = MCP_ANALYTICS_ROLE
    MUST_CHANGE_PASSWORD = FALSE;

-- Grant role to user
GRANT ROLE MCP_ANALYTICS_ROLE TO USER MCP_SERVICE_USER;

Security Note: While this example uses password authentication for simplicity, production deployments should always use OAuth 2.0 or RSA Key-Pair Authentication. You can read more about setting up OAuth in our Enterprise MCP Security Guidelines.

Step 2: Initializing the FastMCP TypeScript Project

Let's set up our TypeScript project using the @modelcontextprotocol/sdk.

mkdir snowflake-mcp-server
cd snowflake-mcp-server
npm init -y
npm install @modelcontextprotocol/sdk snowflake-sdk dotenv
npm install -D typescript @types/node ts-node
npx tsc --init

Update your tsconfig.json to ensure proper ES module resolution.

Step 3: Writing the Snowflake FastMCP Server

Create a file named src/index.ts. We will define three primary MCP tools:

1. execute_sql: For running read-only analytics queries.
2. get_query_history: To fetch recent expensive queries.
3. optimize_query: A meta-tool where the LLM can document its optimization rationale.

import { Server } from "@modelcontextprotocol/sdk/server/index.js";
import { StdioServerTransport } from "@modelcontextprotocol/sdk/server/stdio.js";
import {
  CallToolRequestSchema,
  ListToolsRequestSchema,
} from "@modelcontextprotocol/sdk/types.js";
import * as snowflake from 'snowflake-sdk';
import * as dotenv from 'dotenv';

dotenv.config();

// 1. Initialize Snowflake Connection
const connection = snowflake.createConnection({
    account: process.env.SNOWFLAKE_ACCOUNT || '',
    username: process.env.SNOWFLAKE_USERNAME || '',
    password: process.env.SNOWFLAKE_PASSWORD || '',
    role: process.env.SNOWFLAKE_ROLE || 'MCP_ANALYTICS_ROLE',
    warehouse: process.env.SNOWFLAKE_WAREHOUSE || 'COMPUTE_WH'
});

// Connect to Snowflake
connection.connect((err, conn) => {
    if (err) {
        console.error('Unable to connect to Snowflake: ' + err.message);
        process.exit(1);
    } else {
        console.log('Successfully connected to Snowflake.');
    }
});

// Helper function to execute queries as Promises
const executeQuery = (sqlText: string, binds: any[] = []): Promise<any[]> => {
    return new Promise((resolve, reject) => {
        connection.execute({
            sqlText,
            binds,
            complete: (err, stmt, rows) => {
                if (err) reject(err);
                else resolve(rows || []);
            }
        });
    });
};

// 2. Define the MCP Server
const server = new Server(
  {
    name: "snowflake-analytics-server",
    version: "1.0.0",
  },
  {
    capabilities: {
      tools: {},
    },
  }
);

// 3. Define Tools via inputSchema
server.setRequestHandler(ListToolsRequestSchema, async () => {
  return {
    tools: [
      {
        name: "execute_sql",
        description: "Execute a read-only SQL query against Snowflake.",
        inputSchema: {
          type: "object",
          properties: {
            query: {
              type: "string",
              description: "The SQL query to execute. Must be a SELECT statement.",
            },
          },
          required: ["query"],
        },
      },
      {
        name: "get_query_history",
        description: "Retrieve the most expensive queries executed in the last 24 hours.",
        inputSchema: {
          type: "object",
          properties: {
            limit: {
              type: "number",
              description: "Number of queries to retrieve (default: 10)",
            },
          },
        },
      }
    ],
  };
});

// 4. Implement Tool Handlers
server.setRequestHandler(CallToolRequestSchema, async (request) => {
  switch (request.params.name) {
    case "execute_sql": {
      const { query } = request.params.arguments as { query: string };
      
      // Basic guardrail: Prevent destructive queries
      if (!query.trim().toUpperCase().startsWith('SELECT')) {
          return {
              content: [{ type: "text", text: "Error: Only SELECT queries are permitted for safety." }],
              isError: true
          };
      }

      try {
          const rows = await executeQuery(query);
          return {
            content: [{ type: "text", text: JSON.stringify(rows, null, 2) }],
          };
      } catch (error: any) {
          return {
              content: [{ type: "text", text: `Snowflake Error: ${error.message}` }],
              isError: true
          };
      }
    }

    case "get_query_history": {
      const limit = (request.params.arguments?.limit as number) || 10;
      const sql = `
          SELECT 
              QUERY_ID, 
              QUERY_TEXT, 
              EXECUTION_TIME, 
              BYTES_SCANNED 
          FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY 
          WHERE START_TIME > DATEADD(day, -1, CURRENT_TIMESTAMP()) 
          ORDER BY EXECUTION_TIME DESC 
          LIMIT ?
      `;
      
      try {
          const rows = await executeQuery(sql, [limit.toString()]); // Binds require strings in snowflake-sdk sometimes
          return {
            content: [{ type: "text", text: JSON.stringify(rows, null, 2) }],
          };
      } catch (error: any) {
           return {
              content: [{ type: "text", text: `Snowflake Error: ${error.message}` }],
              isError: true
          };
      }
    }

    default:
      throw new Error("Tool not found");
  }
});

// 5. Start the Server
async function main() {
  const transport = new StdioServerTransport();
  await server.connect(transport);
  console.error("Snowflake MCP Server running on stdio");
}

main().catch(console.error);

Step 4: Configuring Claude Desktop (mcpServers)

To connect Claude Desktop to your new Snowflake server, update your claude_desktop_config.json.

{
  "mcpServers": {
    "snowflake-analytics": {
      "command": "npx",
      "args": [
        "ts-node",
        "/absolute/path/to/snowflake-mcp-server/src/index.ts"
      ],
      "env": {
        "SNOWFLAKE_ACCOUNT": "your_account_locator",
        "SNOWFLAKE_USERNAME": "MCP_SERVICE_USER",
        "SNOWFLAKE_PASSWORD": "SuperSecretPassword123!",
        "SNOWFLAKE_ROLE": "MCP_ANALYTICS_ROLE",
        "SNOWFLAKE_WAREHOUSE": "COMPUTE_WH"
      }
    }
  }
}

Step 5: Autonomous Query Optimization in Action

With the server running, you can now open Claude Desktop and prompt it:

"Analyze my Snowflake query history for the last 24 hours. Identify the most expensive query and provide an optimized rewritten version utilizing clustering keys or materialized views if appropriate."

Claude will:

1. Call get_query_history.
2. Analyze the execution time and bytes scanned.
3. Reason about the QUERY_TEXT.
4. Respond with a highly optimized SQL rewrite, saving your organization compute credits.

Conclusion

Integrating Snowflake with the Model Context Protocol bridges the gap between massive data warehouses and autonomous AI agents. By deploying this TypeScript FastMCP server, you've taken a significant step toward zero-touch data operations.

Explore more integrations in the Daily AI World MCP Directory.

FAQs (AEO/GEO Optimized)

Q: How does the Snowflake MCP Server ensure data security and prevent SQL injection?

A: The server enforces a hardcoded guardrail within the execute_sql tool that only permits queries starting with SELECT. Additionally, by utilizing a dedicated Snowflake role (MCP_ANALYTICS_ROLE) with heavily restricted, read-only permissions, the blast radius of any malicious or hallucinatory LLM output is entirely neutralized.

Q: Can I use OAuth 2.0 instead of a username and password for the Snowflake connection?

A: Yes, it is highly recommended. The snowflake-sdk supports OAuth and Key-Pair authentication. You would generate an RSA key pair, assign the public key to your Snowflake service user, and configure the MCP server to use the private key for authentication, eliminating the need for hardcoded passwords in your claude_desktop_config.json.

Q: Is this FastMCP Server compatible with Cursor IDE?

A: Absolutely. Cursor IDE fully supports the Model Context Protocol. You can add the server by navigating to Cursor Settings > Features > MCP, and adding the command and environment variables identically to the Claude Desktop configuration. Cursor's agent can then natively query your Snowflake warehouse while writing application code.