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

1. 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

2. 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:

1. Check docs: mcp_snowflake-docs_CKESnowflakeDocs("SPCS image repository")
2. Query Snowhouse: Check stage_etl_v with stage_type = 'IMAGE_REPOSITORY' (not a dedicated table!)
3. Search source: mcp_github_search_code("imageRepositoryDPO repo:snowflakedb/snowflake")
4. 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:

1. Map requirements: Failed queries → job_etl_v
2. Get examples: mcp_context7_get-library-docs("/snowflakedb/snowpark-python", "stored procedures")
3. Build query: Start with job_etl_v, filter by account_id and error_code
4. Write procedure: Use type hints, error handling, logging
5. 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:

1. Check docs: mcp_snowflake-docs_CKESnowflakeDocs("authentication methods")
2. Find source: mcp_github_search_code("auth repo:snowflakedb/snowflake-connector-python")
3. Get examples: mcp_context7_get-library-docs("/snowflakedb/snowflake-connector-python", "authentication")
4. Build minimal repro: Self-contained, runnable code
5. 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:

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

For Code Projects (SP & Repro):

6. ALWAYS use Context7 MCP for code examples
7. ALWAYS include type hints and error handling
8. 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.