Skip to main content
Glama
DataPilot MCP Server

DataPilot MCP Server

by rickyb30

DataPilot MCP Server

CI/CD Pipeline Coverage Status Python Version License: MIT Code style: black Security: bandit Pre-commit

Navigate your data with AI guidance. A comprehensive Model Context Protocol (MCP) server for interacting with Snowflake using natural language and AI. Built with FastMCP 2.0 and OpenAI integration.

Features

πŸ—„οΈ Core Database Operations

  • execute_sql - Execute SQL queries with results

  • list_databases - List all accessible databases

  • list_schemas - List schemas in a database

  • list_tables - List tables in a database/schema

  • describe_table - Get detailed table column information

  • get_table_sample - Retrieve sample data from tables

🏭 Warehouse Management

  • list_warehouses - List all available warehouses

  • get_warehouse_status - Get current warehouse, database, and schema status

πŸ€– AI-Powered Features

  • natural_language_to_sql - Convert natural language questions to SQL queries

  • analyze_query_results - AI-powered analysis of query results

  • suggest_query_optimizations - Get optimization suggestions for SQL queries

  • explain_query - Plain English explanations of SQL queries

  • generate_table_insights - AI-generated insights about table data

πŸ“Š Resources (Data Access)

  • snowflake://databases - Access database list

  • snowflake://schemas/{database} - Access schema list

  • snowflake://tables/{database}/{schema} - Access table list

  • snowflake://table/{database}/{schema}/{table} - Access table details

πŸ“ Prompts (Templates)

  • sql_analysis_prompt - Templates for SQL analysis

  • data_exploration_prompt - Templates for data exploration

  • sql_optimization_prompt - Templates for query optimization

Related MCP server: Snowflake MCP Service

Installation

  1. Clone and setup the project:

    git clone <repository-url>
    cd datapilot
    python -m venv venv
    source venv/bin/activate  # On Windows: venv\Scripts\activate
  2. Install dependencies:

    pip install -r requirements.txt
  3. Configure environment variables:

    cp env.template .env
    # Edit .env with your credentials

Configuration

Environment Variables

Create a .env file with the following configuration:

# Required: Snowflake Connection
# Account examples:
# - ACCOUNT-LOCATOR.snowflakecomputing.com (recommended)
# - ACCOUNT-LOCATOR.region.cloud
# - organization-account_name
SNOWFLAKE_ACCOUNT=ACCOUNT-LOCATOR.snowflakecomputing.com
SNOWFLAKE_USER=your_username
SNOWFLAKE_PASSWORD=your_password

# Optional: Default Snowflake Context
SNOWFLAKE_WAREHOUSE=your_warehouse_name
SNOWFLAKE_DATABASE=your_database_name
SNOWFLAKE_SCHEMA=your_schema_name
SNOWFLAKE_ROLE=your_role_name

# Required: OpenAI API
OPENAI_API_KEY=your_openai_api_key
OPENAI_MODEL=gpt-4  # Optional, defaults to gpt-4

Snowflake Account Setup

  1. Get your Snowflake account identifier - Multiple formats supported:

    • Recommended: ACCOUNT-LOCATOR.snowflakecomputing.com (e.g., SCGEENJ-UR66679.snowflakecomputing.com)

    • Regional: ACCOUNT-LOCATOR.region.cloud (e.g., xy12345.us-east-1.aws)

    • Legacy: organization-account_name

  2. Ensure your user has appropriate permissions:

    • USAGE on warehouses, databases, and schemas

    • SELECT on tables for querying

    • SHOW privileges for listing objects

Usage

Running the Server

Method 1: Direct execution

python -m src.main

Method 2: Using FastMCP CLI

fastmcp run src/main.py

Method 3: Development mode with auto-reload

fastmcp dev src/main.py

Connecting to MCP Clients

Claude Desktop

Add to your Claude Desktop configuration:

{
  "mcpServers": {
    "datapilot": {
      "command": "python",
      "args": ["-m", "src.main"],
      "cwd": "/path/to/datapilot",
      "env": {
        "SNOWFLAKE_ACCOUNT": "your_account",
        "SNOWFLAKE_USER": "your_user",
        "SNOWFLAKE_PASSWORD": "your_password",
        "OPENAI_API_KEY": "your_openai_key"
      }
    }
  }
}

Using FastMCP Client

from fastmcp import Client

async def main():
    async with Client("python -m src.main") as client:
        # List databases
        databases = await client.call_tool("list_databases")
        print("Databases:", databases)
        
        # Natural language to SQL
        result = await client.call_tool("natural_language_to_sql", {
            "question": "Show me the top 10 customers by revenue",
            "database": "SALES_DB",
            "schema": "PUBLIC"
        })
        print("Generated SQL:", result)

Example Usage

1. Natural Language Query

# Ask a question in natural language
question = "What are the top 5 products by sales volume last month?"
sql = await client.call_tool("natural_language_to_sql", {
    "question": question,
    "database": "SALES_DB",
    "schema": "PUBLIC"
})
print(f"Generated SQL: {sql}")

2. Execute and Analyze

# Execute a query and get AI analysis
analysis = await client.call_tool("analyze_query_results", {
    "query": "SELECT product_name, SUM(quantity) as total_sales FROM sales GROUP BY product_name ORDER BY total_sales DESC LIMIT 10",
    "results_limit": 100,
    "analysis_type": "summary"
})
print(f"Analysis: {analysis}")

3. Table Insights

# Get AI-powered insights about a table
insights = await client.call_tool("generate_table_insights", {
    "table_name": "SALES_DB.PUBLIC.CUSTOMERS",
    "sample_limit": 50
})
print(f"Table insights: {insights}")

4. Query Optimization

# Get optimization suggestions
optimizations = await client.call_tool("suggest_query_optimizations", {
    "query": "SELECT * FROM large_table WHERE date_column > '2023-01-01'"
})
print(f"Optimization suggestions: {optimizations}")

Architecture

β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”    β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”    β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
β”‚   MCP Client    β”‚    β”‚   FastMCP       β”‚    β”‚   Snowflake     β”‚
β”‚   (Claude/etc)  │◄──►│   Server        │◄──►│   Database      β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜    β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜    β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜
                                β”‚
                                β–Ό
                       β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
                       β”‚   OpenAI API    β”‚
                       β”‚   (GPT-4)       β”‚
                       β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜

Project Structure

datapilot/
β”œβ”€β”€ src/
β”‚   β”œβ”€β”€ __init__.py
β”‚   β”œβ”€β”€ main.py              # Main FastMCP server
β”‚   β”œβ”€β”€ models.py            # Pydantic data models
β”‚   β”œβ”€β”€ snowflake_client.py  # Snowflake connection & operations
β”‚   └── openai_client.py     # OpenAI integration
β”œβ”€β”€ requirements.txt         # Python dependencies
β”œβ”€β”€ env.template            # Environment variables template
└── README.md              # This file

Development

Adding New Tools

  1. Define your tool function in src/main.py:

@mcp.tool()
async def my_new_tool(param: str, ctx: Context) -> str:
    """Description of what the tool does"""
    await ctx.info(f"Processing: {param}")
    # Your logic here
    return "result"
  1. Add appropriate error handling and logging

  2. Test with FastMCP dev mode: fastmcp dev src/main.py

Adding New Resources

@mcp.resource("snowflake://my-resource/{param}")
async def my_resource(param: str) -> Dict[str, Any]:
    """Resource description"""
    # Your logic here
    return {"data": "value"}

Troubleshooting

Common Issues

  1. Connection Errors

    • Verify Snowflake credentials in .env

    • Check network connectivity

    • Ensure user has required permissions

  2. OpenAI Errors

    • Verify OPENAI_API_KEY is set correctly

    • Check API quota and billing

    • Ensure model name is correct

  3. Import Errors

    • Activate virtual environment

    • Install all requirements: pip install -r requirements.txt

    • Run from project root directory

Logging

Enable debug logging:

LOG_LEVEL=DEBUG

Contributing

  1. Fork the repository

  2. Create a feature branch

  3. Make your changes

  4. Add tests if applicable

  5. Submit a pull request

License

This project is licensed under the MIT License.

Support

For issues and questions:

  • Check the troubleshooting section

  • Review FastMCP documentation: https://gofastmcp.com/

  • Open an issue in the repository

Available Tools

13 tools
analyze_query_resultsC

Execute a query and analyze its results using AI

Inputs: query (required), results_limit, analysis_type. Returns: result

describe_tableC

Get detailed information about a table's columns

Inputs: table_name (required), database, schema. Returns: result

execute_sqlB

Execute a SQL query on Snowflake and return results

Inputs: request (required). Returns: Result of a SQL query execution

explain_queryA

Explain what a SQL query does in plain English

Inputs: query (required). Returns: result

generate_table_insightsC

Generate AI-powered insights about a table's data

Inputs: table_name (required), sample_limit. Returns: result

get_table_sampleC

Get a sample of data from a table

Inputs: table_name (required), limit. Returns: Result of a SQL query execution

get_warehouse_statusB

Get current warehouse, database, and schema status

Inputs: no named parameters. Returns: object

list_databasesB

List all databases available to the user

Inputs: no named parameters. Returns: result

list_schemasC

List all schemas in a database

Inputs: database (required). Returns: result

list_tablesB

List all tables in a database/schema

Inputs: database, schema. Returns: result

list_warehousesB

List all warehouses available to the user

Inputs: no named parameters. Returns: result

natural_language_to_sqlC

Convert natural language question to SQL query using AI

Inputs: request (required). Returns: result

suggest_query_optimizationsB

Get AI-powered suggestions for optimizing a SQL query

Inputs: query (required). Returns: result

Tool Schema Changelog

Recent tool additions, removals, and schema changes observed during successful MCP inspections.

Loading tool schema changelog…

    TDQS

    A3.5/5.0

    Scored across 13 tools

    Disambiguation5/5

    Every tool has a clearly distinct purpose with no ambiguity. For example, 'execute_sql' runs queries while 'explain_query' describes them, and 'list_databases' enumerates databases whereas 'describe_table' provides column details. The tools cover different aspects of the data workflow without overlap.

    Naming Consistency5/5

    All tool names follow a consistent verb_noun pattern using snake_case, such as 'list_databases', 'execute_sql', and 'generate_table_insights'. This uniformity makes the toolset predictable and easy to navigate, with no deviations in naming conventions.

    Tool Count5/5

    With 13 tools, the count is well-scoped for a Snowflake data management server. Each tool serves a specific function in querying, listing, analyzing, or optimizing data, and none appear redundant, fitting the domain appropriately.

    Completeness5/5

    The toolset provides complete coverage for data exploration and SQL workflows, including listing resources (databases, schemas, tables), executing and explaining queries, generating insights, and optimizing performance. There are no obvious gaps, enabling agents to handle end-to-end tasks without dead ends.

    Maintenance

    ActivityInactive
    ResponsivenessNo recent community issues

    Related MCP Connectors

    Related MCP Servers

    • A
      license
      Not graded
      quality
      D
      maintenance
      A Model Context Protocol server that enables Claude to execute SQL queries on Snowflake databases with automatic connection lifecycle management.
      45
      MIT
    • A
      license
      Not graded
      quality
      D
      maintenance
      A Model Context Protocol server that provides access to Snowflake databases for any MCP-compatible client, allowing execution of SQL queries with automatic connection management.
      5
      MIT
    • A
      license
      Not graded
      quality
      D
      maintenance
      A Model Context Protocol server that provides AI assistants with comprehensive access to SQL databases, enabling schema inspection, query execution, and database operations with enterprise-grade security.
      62 npm
      9
      MIT