Snowflake Agent for MCP Server

March 21, 2025 · View on GitHub

This is a custom AI agent that connects to Snowflake using SQLAlchemy and integrates with an MCP (Model Context Protocol) Server to allow seamless SQL query execution on Snowflake databases. The agent is designed to be used within agentic AI ecosystems where tools are registered via MCP servers.

🧩 Features

  • Connects to Snowflake using external browser authentication.
  • Supports executing SQL queries on specified databases and schemas.
  • Works as a tool registered to an MCP server.
  • Built-in connection pooling using SQLAlchemy QueuePool.
  • Returns query results as structured JSON.

🚀 Architecture

This agent is built on top of:

  • FastMCP from mcp.server.fastmcp for tool registration and serving.
  • SQLAlchemy with Snowflake dialect (snowflake-sqlalchemy).
  • dotenv for managing environment variables securely.

⚙️ Environment Variables

Create a .env file in your project directory with the following content:

SNOWFLAKE_ACCOUNT=your_account_name
SNOWFLAKE_USER=your_username
SNOWFLAKE_WAREHOUSE=your_warehouse
SNOWFLAKE_DATABASE=SANDBOX
SNOWFLAKE_SCHEMA=PERSONAL_TESTING_SPACE_PRASANNA
SNOWFLAKE_ROLE=your_role

🛠️ Usage

  1. Install the required dependencies:
pip install -r requirements.txt

Typical dependencies:

mcp
sqlalchemy
snowflake-sqlalchemy
python-dotenv
  1. Run the MCP agent:
python your_script.py

The agent will start and register itself with the MCP server using stdio transport.

🧑‍💻 How It Works

  • When invoked via MCP, the agent will accept a query parameter and optionally database and schema values.
  • It will then connect to Snowflake and execute the provided query.
  • The results will be returned as a JSON object, either as rows (for SELECT queries) or as an execution confirmation (for INSERT/UPDATE/DELETE queries).

Add the following to Claude_desktop_config.json file

{
  "mcpServers": {
    "snowflake": {
      "command": "C:\\Users\\Lenovo\\Desktop\\Python\\mysql-mcp\\run_snowflake.bat"
    }
  }
}

🔄 Example Request via MCP

{
  "tool": "execute",
  "params": {
    "query": "SELECT * FROM MY_TABLE LIMIT 10",
    "database": "MY_DATABASE",
    "schema": "MY_SCHEMA"
  }
}

📦 Output Example

{
  "results": [
    {"COLUMN1": "value1", "COLUMN2": "value2"},
    {"COLUMN1": "value3", "COLUMN2": "value4"}
  ]
}

Or for non-SELECT queries:

{
  "results": {
    "rowcount": 1,
    "message": "Query executed successfully"
  }
}

📚 Resources