support-rules-mcp by sfc-gh-cconner - MCP Server

Snowflake Rules Engine - MCP Server

A Snowflake-hosted MCP server that provides comprehensive rules for troubleshooting Snowflake issues, building stored procedures, and creating reproductions. Uses Cortex Search for semantic search and Cortex Analyst for natural language queries.

Note: This is an internal Snowflake project designed for support engineering workflows. It requires access to Snowflake's internal systems and data.

🎯 Purpose

This Rules Engine serves as a single source of truth for Snowflake troubleshooting knowledge across multiple projects. It provides:

DPO → Table Mappings: How Snowflake objects map to Snowhouse tables
Source Code Access: GitHub MCP patterns for exploring Snowflake repositories
Documentation Access: Snowflake Docs MCP patterns for official guidance
Code Quality: Context7 MCP patterns for examples and best practices
Investigation Workflows: Systematic troubleshooting procedures
SQL Patterns: Efficient Snowhouse querying techniques

🏗️ Architecture

Snowflake Components

┌─────────────────────────────────────────────────────┐
│                    Cursor AI                        │
│                                                     │
│  ┌───────────────────────────────────────────────┐ │
│  │         MCP Client (snow mcp connect)         │ │
│  └───────────────┬───────────────────────────────┘ │
└──────────────────┼─────────────────────────────────┘
                   │
                   ▼
┌─────────────────────────────────────────────────────┐
│         Snowflake MCP Server                        │
│         (temp.support_sp_dev.support_rules_mcp)     │
│                                                     │
│  ┌─────────────────────┐  ┌─────────────────────┐  │
│  │  get-snowflake-rule │  │ list-snowflake-rules│  │
│  │  (Cortex Search)    │  │  (Cortex Analyst)   │  │
│  └──────────┬──────────┘  └──────────┬──────────┘  │
└─────────────┼────────────────────────┼─────────────┘
              │                        │
              ▼                        ▼
   ┌─────────────────────┐  ┌─────────────────────┐
   │   rules_search      │  │   rules_metadata    │
   │ (Cortex Search)     │  │  (Semantic View)    │
   └──────────┬──────────┘  └──────────┬──────────┘
              │                        │
              └────────────┬───────────┘
                           ▼
                  ┌─────────────────┐
                  │   rules table   │
                  │  (42 rules)     │
                  └─────────────────┘

Rules Hierarchy

rules/
├── _meta/                     # Meta-rules (composite workflows)
│   ├── troubleshooting.mdc   # Complete troubleshooting workflow
│   ├── stored-procedures.mdc # Complete SP generation workflow
│   └── reproductions.mdc     # Complete reproduction workflow
│
├── core/                      # Core knowledge (reusable)
│   ├── 01-github-mcp.mdc     # Source code access patterns
│   ├── 02-docs-mcp.mdc       # Documentation access patterns
│   ├── 03-code-quality-mcp.mdc # Code examples and best practices
│   ├── 04-dpo-mappings.mdc   # DPO→table mappings (critical!)
│   └── 05-snowhouse-querying.mdc # Query patterns
│
├── workflows/                 # Workflow-specific guidance
│   ├── troubleshooting.mdc   # Investigation workflows
│   ├── stored-procedures.mdc # SP generation patterns
│   └── reproductions.mdc     # Reproduction building
│
├── connectors/                # Connector-specific rules (11 files)
│   ├── python.mdc
│   ├── jdbc.mdc
│   └── ...
│
└── spcs/                      # SPCS-specific rules (10 files)
    ├── architecture.mdc
    └── ...

🚀 Quick Start

1. Configure Cursor MCP

Add to your Cursor MCP configuration (~/.cursor/mcp.json or Cursor Settings → MCP):

{
  "mcpServers": {
    "snowflake-rules": {
      "command": "snow",
      "args": [
        "mcp",
        "connect",
        "--connection",
        "snowhouse",
        "--mcp-server",
        "temp.support_sp_dev.support_rules_mcp"
      ],
      "env": {}
    }
  }
}

2. Restart Cursor

Restart Cursor to load the MCP server.

3. Use the Rules

In Cursor chat, ask questions about Snowflake troubleshooting:

"How do I troubleshoot Python connector authentication issues?"
"Show me the DPO mappings for image repositories"
"What are the best practices for writing stored procedures?"

Cursor AI will automatically call the MCP tools to retrieve relevant rules.

📊 What's Deployed

Objects Created

Database: temp
Schema: support_sp_dev
Table: rules (42 rules: 14 core, 11 connector, 10 spcs, 7 workflow)
Cortex Search: rules_search (semantic search over all rules)
Semantic View: rules_metadata (queryable metadata)
MCP Server: support_rules_mcp (2 tools)

MCP Tools Available

get-snowflake-rule - Search and retrieve rule content
- Type: CORTEX_SEARCH_SERVICE_QUERY
- Query examples: "troubleshooting", "dpo mappings", "python connector"
- Filter by rule_type: meta, core, connector, spcs, workflow
list-snowflake-rules - List and discover available rules
- Type: CORTEX_ANALYST_MESSAGE
- Natural language queries: "list all rules", "show me connector rules", "how many core rules?"

Access

Roles with access: ENGINEER, ENGINEER_BASIC
Owner: SUPPORT_ENGINEER

🔧 Setup & Deployment

Initial Setup (Run Once)

# 1. Create rules table
snow sql -c snowhouse -f sql/01_create_table.sql

# 2. Upload rules from local files
python upload_rules.py

# 3. Create Cortex Search service
snow sql -c snowhouse -f sql/02_create_single_service.sql

# 4. Force immediate indexing (or wait ~1 hour)
snow sql -c snowhouse -f sql/04_force_refresh.sql

# 5. Create semantic view for Cortex Analyst
snow sql -c snowhouse -f sql/05_create_semantic_view.sql

# 6. Create MCP server
snow sql -c snowhouse -f sql/06_create_mcp_server.sql

# 7. Test the setup
snow sql -c snowhouse -f sql/test_queries.sql

Update Workflow

When rules need updating:

# 1. Edit rules locally in rules/ directory
vim rules/core/04-dpo-mappings.mdc

# 2. Upload changes to Snowflake
python upload_rules.py

# 3. Force immediate refresh
snow sql -c snowhouse -f sql/04_force_refresh.sql

🎯 Use Cases

1. Troubleshooting Project

Goal: Investigate why a customer's SPCS image repository creation is failing.

In Cursor:

"Load troubleshooting rules for SPCS image repository issues"

Workflow:

Check docs: mcp_snowflake-docs_CKESnowflakeDocs("SPCS image repository")
Query Snowhouse: Check stage_etl_v with stage_type = 'IMAGE_REPOSITORY' (not a dedicated table!)
Search source: mcp_github_search_code("imageRepositoryDPO repo:snowflakedb/snowflake")
Analyze logs: Get timestamps from job_etl_v, query gs_logs_v with bounds

2. Stored Procedure Project

Goal: Create a procedure that retrieves failed queries for a ticket.

In Cursor:

"Help me write a stored procedure to query failed jobs in Snowhouse"

Workflow:

Map requirements: Failed queries → job_etl_v
Get examples: mcp_context7_get-library-docs("/snowflakedb/snowpark-python", "stored procedures")
Build query: Start with job_etl_v, filter by account_id and error_code
Write procedure: Use type hints, error handling, logging
Test and document

3. Reproduction Project

Goal: Reproduce a Python connector authentication issue.

In Cursor:

"Show me how to create a minimal reproduction for a Python connector auth bug"

Workflow:

Check docs: mcp_snowflake-docs_CKESnowflakeDocs("authentication methods")
Find source: mcp_github_search_code("auth repo:snowflakedb/snowflake-connector-python")
Get examples: mcp_context7_get-library-docs("/snowflakedb/snowflake-connector-python", "authentication")
Build minimal repro: Self-contained, runnable code
Verify and document

🔍 Maintenance

View Recent Updates

SELECT rule_name, rule_type, version, updated_at
FROM temp.support_sp_dev.rules
ORDER BY updated_at DESC
LIMIT 10;

Find Rules by Keyword

SELECT rule_name, rule_type, rule_description
FROM temp.support_sp_dev.rules
WHERE rule_content ILIKE '%keyword%';

Check Rule Statistics

SELECT 
    rule_type,
    COUNT(*) AS count,
    AVG(LENGTH(rule_content)) AS avg_size
FROM temp.support_sp_dev.rules
GROUP BY rule_type;

Refresh Search Index

ALTER CORTEX SEARCH SERVICE temp.support_sp_dev.rules_search REFRESH;

🚨 Critical Knowledge

The #1 Mistake: Stage-Backed Objects

NOT ALL OBJECTS HAVE DEDICATED TABLES!

These objects use stage_etl_v:

❌ WRONG: image_repository_etl_v (doesn't exist!)
✅ RIGHT: stage_etl_v with stage_type = 'IMAGE_REPOSITORY'

Stage-backed objects:

Image repositories → stage_etl_v with stage_type = 'IMAGE_REPOSITORY'
Git repositories → stage_etl_v with stage_type = 'GIT_REPOSITORY'
Named stages → stage_etl_v with stage_type = 'INTERNAL'
External stages → stage_etl_v with stage_type IN ('S3', 'AZURE', 'GCS')

💡 Key Principles

For All Projects:

ALWAYS use GitHub MCP for source code (never local paths)
ALWAYS use Snowflake Docs MCP for official documentation
ALWAYS filter Snowhouse queries by account_id
ALWAYS start with job_etl_v for timestamps
ALWAYS check if objects are stage-backed

For Code Projects (SP & Repro):

ALWAYS use Context7 MCP for code examples
ALWAYS include type hints and error handling
ALWAYS validate inputs and add logging

🎯 Benefits

vs Local Python MCP Server

✅ No Python environment setup needed
✅ Works for all users with ENGINEER role
✅ Centralized rule management
✅ Automatic scaling and availability
✅ Version tracking in the database
✅ Semantic search built-in

🛠️ Troubleshooting

MCP Server Not Found

# List available MCP servers
snow sql -c snowhouse -Q "SHOW MCP SERVERS IN SCHEMA temp.support_sp_dev;"

Search Not Finding Rules

# Force immediate refresh
snow sql -c snowhouse -f sql/04_force_refresh.sql

Permission Denied

# Check grants
snow sql -c snowhouse -Q "SHOW GRANTS ON MCP SERVER temp.support_sp_dev.support_rules_mcp;"

Upload Failed

# Check connection
snow connection test --connection snowhouse

# Verify table exists
snow sql -c snowhouse -Q "SELECT COUNT(*) FROM temp.support_sp_dev.rules;"

🔀 Alternative Implementations

The main approach uses a single unified Cortex Search service for all rules, which is recommended for most use cases.

An alternative multi-service approach is available in sql/alternatives/ that creates separate Cortex Search services for each rule type (meta, core, connector, spcs, workflow). This provides more granular control but increases complexity.

When to consider alternatives:

Need different refresh schedules per rule type
Want to grant access to specific rule categories only
Require strict separation between rule types

See sql/alternatives/README.md for details and trade-offs.

📁 Project Structure

.
├── sql/                          # Setup SQL scripts
│   ├── 01_create_table.sql       # Create rules table
│   ├── 02_create_single_service.sql # Create Cortex Search
│   ├── 04_force_refresh.sql      # Force indexing
│   ├── 05_create_semantic_view.sql # Create semantic view
│   ├── 06_create_mcp_server.sql  # Create MCP server
│   ├── test_queries.sql          # Validation queries
│   └── alternatives/             # Alternative implementations
│       └── README.md             # Multi-service approach
│
├── rules/                        # Rule content (42 .mdc files)
│   ├── _meta/                    # Meta-rules
│   ├── core/                     # Core knowledge
│   ├── workflows/                # Workflow guidance
│   ├── connectors/               # Connector-specific
│   └── spcs/                     # SPCS-specific
│
├── upload_rules.py               # Upload script
├── rules_semantic_model.yaml     # Cortex Analyst model
├── cursor-mcp-config.json        # Cursor MCP configuration
├── README.md                     # This file
├── QUICK_START.md                # Fast setup guide
│
└── archive/                      # Archived implementations
    └── python-mcp-server/        # Original Python MCP server

⚡ Quick Reference

Most Common Rules

Rule	Purpose
`_meta/troubleshooting.mdc`	Complete troubleshooting setup
`_meta/stored-procedures.mdc`	Complete SP development setup
`core/04-dpo-mappings.mdc`	Object→table mappings
`core/05-snowhouse-querying.mdc`	Query patterns

Most Common Mappings

Object	Table	Filter
Image Repository	`stage_etl_v`	`stage_type = 'IMAGE_REPOSITORY'`
Git Repository	`stage_etl_v`	`stage_type = 'GIT_REPOSITORY'`
Query	`job_etl_v`	`start_time` range
Warehouse	`warehouse_etl_v`	`warehouse_name`

MCP Server Quick Reference

# From Cursor - these tools are called automatically
mcp_snowflake-rules_get-snowflake-rule(query="troubleshooting", filter={...})
mcp_snowflake-rules_list-snowflake-rules(message="list all core rules")

📞 Support

Issues: File in your internal issue tracker
Updates: Rules are updated centrally and automatically available
Questions: Check existing rules first, then ask for help
Alternative: Python MCP server available in archive/python-mcp-server/

Status: ✅ Production Ready
Last Updated: 2025-10-10
Rules Count: 42 (14 core, 11 connector, 10 spcs, 7 workflow)
Maintained by: Snowflake Support Engineering

Maintaining Snowflake's troubleshooting knowledge base for consistent, efficient investigations across all projects.