Snowflake Cortex

<!-- AUTO-GENERATED by export-skills.py — DO NOT EDIT -->
---
name: snowflake-cortex
description: Snowflake Cortex AI patterns for in-warehouse LLM inference, text-to-SQL (Cortex Analyst), and RAG (Cortex Search). Use when building AI agents that operate on Snowflake data without data movement.
tags: [snowflake, cortex, analytics, llm]
---

# Snowflake Cortex AI

Build AI agents that operate directly on Snowflake data using Cortex LLM functions, Cortex Analyst (text-to-SQL), and Cortex Search (RAG) -- with zero data movement.

## When to Use

- Running LLM inference directly in SQL (summarize, classify, extract, translate)
- Building natural language to SQL interfaces over warehouse data (Cortex Analyst)
- Creating RAG search services over Snowflake-resident documents (Cortex Search)
- Choosing between Claude, Gemini, Llama, and Mistral models within Snowflake

## Supported Models

| Provider | Models |
|---|---|
| **Anthropic** | claude-3-5-sonnet, claude-3-haiku |
| **Google** | gemini-1.5-flash (limited regions) |
| **Meta** | llama3.1-70b, llama3.1-8b, llama3-70b |
| **Mistral** | mistral-large2, mixtral-8x7b |
| **Snowflake** | arctic |

## Patterns

### 1. Cortex LLM Functions (SQL-Callable AI)

```sql
-- Text generation with Claude
SELECT SNOWFLAKE.CORTEX.COMPLETE(
  'claude-3-5-sonnet',
  'Explain the key financial metrics for a SaaS company in 3 bullet points'
);

-- Sentiment analysis
SELECT
  review_id,
  review_text,
  SNOWFLAKE.CORTEX.SENTIMENT(review_text) AS sentiment_score
FROM customer_reviews
WHERE sentiment_score < -0.5;  -- Find negative reviews

-- Summarization
SELECT
  ticket_id,
  SNOWFLAKE.CORTEX.SUMMARIZE(conversation_log) AS summary
FROM support_tickets
WHERE resolved_date = CURRENT_DATE();

-- Classification
SELECT
  email_id,
  subject,
  SNOWFLAKE.CORTEX.CLASSIFY_TEXT(
    body,
    ['billing', 'technical_support', 'feature_request', 'complaint', 'praise']
  ):label::STRING AS category
FROM customer_emails;

-- Translation
SELECT
  SNOWFLAKE.CORTEX.TRANSLATE(product_description, 'en', 'vi') AS vietnamese_desc
FROM products;

-- Embeddings for vector operations
SELECT
  doc_id,
  SNOWFLAKE.CORTEX.EMBED_TEXT_1024('e5-base-v2', content) AS embedding
FROM documents;
```

### 2. Cortex Analyst (Text-to-SQL)

**Semantic Model Definition (YAML):**
```yaml
# Stage this file: PUT file://semantic_model.yaml @my_stage
name: sales_analytics
description: "Sales analytics model for revenue and order analysis"
tables:
  - name: orders
    description: "Sales order data with customer and product details"
    base_table: analytics.public.orders
    columns:
      - name: order_id
        description: "Unique order identifier"
        data_type: VARCHAR
      - name: revenue
        description: "Order revenue in USD"
        data_type: NUMBER
        aggregation: sum
      - name: quantity
        description: "Number of units ordered"
        data_type: NUMBER
        aggregation: sum
    time_dimensions:
      - name: order_date
        description: "Date the order was placed"
        data_type: DATE
    dimensions:
      - name: product_category
        description: "Product category (Electronics, Clothing, etc.)"
        data_type: VARCHAR
      - name: region
        description: "Sales region (APAC, EMEA, Americas)"
        data_type: VARCHAR
verified_queries:
  - question: "What was total revenue last quarter?"
    sql: "SELECT SUM(revenue) FROM orders WHERE order_date >= DATEADD(quarter, -1, DATE_TRUNC('quarter', CURRENT_DATE())) AND order_date < DATE_TRUNC('quarter', CURRENT_DATE())"
  - question: "Top 10 products by revenue"
    sql: "SELECT product_category, SUM(revenue) as total_revenue FROM orders GROUP BY 1 ORDER BY 2 DESC LIMIT 10"
```

**Python Client:**
```python
import requests
import json

def ask_cortex_analyst(question: str, model_file: str = "@my_stage/semantic_model.yaml") -> dict:
    """Send natural language question to Cortex Analyst."""
    from snowflake.connector import connect

    conn = connect(account="...", user="...", password="...", warehouse="MY_WH")

    response = requests.post(
        f"https://{conn.account}.snowflakecomputing.com/api/v2/cortex/analyst/message",
        headers={
            "Authorization": f"Snowflake Token=\"{conn.rest.token}\"",
            "Content-Type": "application/json",
        },
        json={
            "messages": [{"role": "user", "content": [{"type": "text", "text": question}]}],
            "semantic_model_file": model_file,
        },
    )
    return response.json()
```

### 3. Cortex Search (RAG)

```sql
-- Create a search service over knowledge base articles
CREATE OR REPLACE CORTEX SEARCH SERVICE knowledge_search
  ON article_text
  ATTRIBUTES category, author, department
  WAREHOUSE = my_warehouse
  TARGET_LAG = '1 hour'
  AS (
    SELECT
      article_id,
      article_text,
      category,
      author,
      department
    FROM knowledge_base
    WHERE status = 'published'
  );
```

**Python Search Client:**
```python
from snowflake.core import Root

def search_knowledge_base(query: str, filters: dict | None = None, limit: int = 5) -> list[dict]:
    """Search the knowledge base using Cortex Search."""
    root = Root(session)
    search_service = root.databases["MY_DB"].schemas["PUBLIC"].cortex_search_services["knowledge_search"]

    search_params = {
        "query": query,
        "columns": ["article_id", "article_text", "category", "author"],
        "limit": limit,
    }
    if filters:
        search_params["filter"] = filters  # e.g., {"@eq": {"category": "billing"}}

    results = search_service.search(**search_params)
    return results.results
```

### 4. Cortex Agents (Combined Structured + Unstructured)

```python
# Cortex Agent combines Analyst (SQL) + Search (RAG) in one agent
import requests

def ask_cortex_agent(question: str) -> dict:
    """Query the Cortex Agent which routes between SQL and RAG automatically."""
    response = requests.post(
        f"https://{account}.snowflakecomputing.com/api/v2/cortex/agent/message",
        headers={"Authorization": f"Snowflake Token=\"{token}\"", "Content-Type": "application/json"},
        json={
            "messages": [{"role": "user", "content": [{"type": "text", "text": question}]}],
            "tools": [
                {"type": "cortex_analyst_text_to_sql", "semantic_model_file": "@stage/model.yaml"},
                {"type": "cortex_search", "name": "knowledge_search"},
            ],
            "model": "claude-3-5-sonnet",
        },
    )
    return response.json()
```

## Anti-Patterns

- Moving data out of Snowflake for LLM processing -- use Cortex LLM functions in-place
- Running unbounded COMPLETE() on large tables -- always LIMIT and estimate token costs
- Skipping semantic model verified queries -- they dramatically improve Analyst accuracy
- Using one model for everything -- match model to task (haiku for classification, sonnet for reasoning)

## References

- [Snowflake Cortex LLM Functions](https://docs.snowflake.com/en/user-guide/snowflake-cortex/llm-functions)
- [Cortex Analyst](https://docs.snowflake.com/en/user-guide/snowflake-cortex/cortex-analyst)
- [Cortex Search](https://docs.snowflake.com/en/user-guide/snowflake-cortex/cortex-search/cortex-search-overview)
- [Cortex Agents](https://docs.snowflake.com/en/user-guide/snowflake-cortex/cortex-agents)

<!-- Source: .faos/custom/skills/integrations/snowflake-cortex/SKILL.md -->