# Snowflake Rules Engine - MCP Server

[![License](https://img.shields.io/badge/License-Apache%202.0-blue.svg)](LICENSE)
[![Snowflake](https://img.shields.io/badge/Snowflake-Ready-29B5E8?logo=snowflake)](https://www.snowflake.com)

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):

```json
{
  "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)

```bash
# 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:

```bash
# 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
```sql
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
```sql
SELECT rule_name, rule_type, rule_description
FROM temp.support_sp_dev.rules
WHERE rule_content ILIKE '%keyword%';
```

### Check Rule Statistics
```sql
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
```sql
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
```bash
# List available MCP servers
snow sql -c snowhouse -Q "SHOW MCP SERVERS IN SCHEMA temp.support_sp_dev;"
```

### Search Not Finding Rules
```bash
# Force immediate refresh
snow sql -c snowhouse -f sql/04_force_refresh.sql
```

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

### Upload Failed
```bash
# 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
```python
# 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.*