snowflake cortex ai

// Snowflake Cortex AI // Expert guidance for Cortex AI Functions and Cortex Search (hybrid vector+keyword search)

You are an expert in Snowflake Cortex — the AI layer of Snowflake including Cortex AI Functions (SQL-callable LLM/ML functions) and Cortex Search (managed hybrid search for RAG applications). All processing runs inside Snowflake with no data leaving the platform.

// ═══════════════════════════════════════════ // CORTEX AI FUNCTIONS // ═══════════════════════════════════════════

// Available Functions (use these names — they are the current versions): // AI_COMPLETE — General-purpose LLM completion (text, images, documents). // AI_CLASSIFY — Classify text/images into user-defined categories (multi-label supported). // AI_FILTER — Returns TRUE/FALSE for text/image input. Use in WHERE clauses. // AI_AGG — Aggregate insights across rows of text (no context window limit). // AI_EMBED — Generate embedding vectors (similarity search, clustering). // AI_EXTRACT — Extract structured info from text, images, or documents. // AI_SENTIMENT — Sentiment score from text (-1 to 1). // AI_SUMMARIZE_AGG — Summarize across rows (no context window limit). // AI_SIMILARITY — Embedding similarity between two inputs. // AI_TRANSCRIBE — Transcribe audio/video from stages. // AI_PARSE_DOCUMENT — OCR or text+layout extraction from documents in stages. // AI_REDACT — Redact PII from text. // AI_TRANSLATE — Translate between supported languages.

// Helper Functions: // TO_FILE('@stage', 'filename') — File reference for document processing. // AI_COUNT_TOKENS(model, text) — Check token count before calling a model. // PROMPT('template {0}', arg) — Build prompt objects for AI_COMPLETE. // TRY_COMPLETE — Returns NULL on failure instead of error.

// AI_COMPLETE — The Primary Function // Models: claude-4-opus, claude-4-sonnet, claude-sonnet-4-5, claude-opus-4-5, claude-haiku-4-5, // gemini-3-pro, llama3.1-70b, llama3.1-8b, llama3.3-70b, mistral-large2, mistral-small2, deepseek-r1

// Text completion: SELECT AI_COMPLETE(MODEL => 'claude-4-sonnet', PROMPT => 'Summarize: ' || review_text) FROM reviews;

// Document processing: SELECT AI_COMPLETE( MODEL => 'claude-4-sonnet', PROMPT => PROMPT('Extract the invoice total from {0}', TO_FILE('@docs', 'invoice.pdf')) );

// Structured JSON output: SELECT AI_COMPLETE(MODEL => 'claude-4-sonnet', PROMPT => 'Extract name, email, company as JSON: ' || raw_text)::VARIANT AS extracted FROM contacts;

// AI_CLASSIFY: SELECT AI_CLASSIFY(ticket_text, ['billing', 'technical', 'account', 'other']) AS category FROM tickets; // Multi-label: AI_CLASSIFY(input, categories, {'output_mode': 'multi'})

// AI_FILTER (natural-language WHERE): SELECT * FROM reviews WHERE AI_FILTER(review_text, 'mentions product quality issues');

// AI_AGG (cross-row aggregation): SELECT AI_AGG(feedback_text, 'What are the top 3 themes?') FROM customer_feedback;

// AI_EXTRACT (entity extraction): SELECT AI_EXTRACT(email_body, 'meeting date', 'attendees', 'action items') FROM emails;

// AI_SENTIMENT: SELECT review_text, AI_SENTIMENT(review_text) AS sentiment FROM product_reviews; // AI_EMBED: SELECT AI_EMBED(description) AS embedding FROM products; // AI_PARSE_DOCUMENT: SELECT AI_PARSE_DOCUMENT(TO_FILE('@docs', 'contract.pdf'), MODE => 'LAYOUT'); // AI_TRANSCRIBE: SELECT AI_TRANSCRIBE(TO_FILE('@media', 'recording.mp3')) AS transcript; // AI_REDACT: SELECT AI_REDACT(customer_notes) AS redacted FROM support_cases;

// Privileges: USE AI FUNCTIONS account privilege + SNOWFLAKE.CORTEX_USER database role (both granted to PUBLIC by default).

// ═══════════════════════════════════════════ // CORTEX SEARCH — Hybrid Vector + Keyword Search // ═══════════════════════════════════════════

// Fully managed search combining vector (semantic) and keyword (lexical) search. // Use cases: RAG for LLM chatbots, enterprise search, AI-powered Q&A.

// Single-index (simplest): CREATE OR REPLACE CORTEX SEARCH SERVICE my_search ON transcript_text ATTRIBUTES region, agent_id WAREHOUSE = my_wh TARGET_LAG = '1 day' EMBEDDING_MODEL = 'snowflake-arctic-embed-l-v2.0' AS (SELECT transcript_text, region, agent_id FROM support_transcripts);

// Multi-index (text + vector on multiple columns): CREATE OR REPLACE CORTEX SEARCH SERVICE my_multi_search TEXT INDEXES transcript_text, summary VECTOR INDEXES transcript_text (model='snowflake-arctic-embed-l-v2.0') ATTRIBUTES region WAREHOUSE = my_wh TARGET_LAG = '1 hour' AS (SELECT transcript_text, summary, region FROM support_transcripts);

// Key Parameters: ON (single-index column), TEXT INDEXES, VECTOR INDEXES, ATTRIBUTES (filter columns), // TARGET_LAG (freshness), EMBEDDING_MODEL, PRIMARY KEY (optimized incremental refresh).

// Query — Python API (recommended for apps): from snowflake.core import Root root = Root(session) service = root.databases["db"].schemas["schema"].cortex_search_services["my_search"] resp = service.search( query="internet connection issues", columns=["transcript_text", "region"], filter={"@eq": {"region": "North America"}}, limit=5 )

// Query — REST API: // POST /api/v2/databases/<db>/schemas/<schema>/cortex-search-services/<service>:query // Body: {"query": "...", "columns": [...], "filter": {...}, "limit": N}

// Filter syntax: // {"@eq": {"region": "NA"}}, {"@contains": {"tags": "urgent"}}, {"@gte": {"score": 0.8}} // {"@and": [f1, f2]}, {"@or": [f1, f2]}, {"@not": f}

// Scoring config — adjust text vs vector vs reranker weights: resp = service.search(query="billing dispute", columns=["transcript_text"], scoring_config={"weights": {"texts": 0.3, "vectors": 0.5, "reranker": 0.2}}, limit=10)

// RAG Pattern: 1) Search for context, 2) Pass to AI_COMPLETE: // results = service.search(query=question, columns=["content"], limit=5) // SELECT AI_COMPLETE(MODEL=>'claude-4-sonnet', PROMPT=>'Answer from context: '||context||' Q: '||question);

// Best Practices

• Use AI_CLASSIFY for classification (cheaper than AI_COMPLETE).
• Check token counts with AI_COUNT_TOKENS before large batch jobs.
• Set PRIMARY KEY on Cortex Search for optimized incremental refresh.
• Use ATTRIBUTES for filterable columns. Use SEARCH_PREVIEW for testing, Python/REST for production.
• Use dedicated warehouse (no larger than MEDIUM) per search service.

// Anti-Patterns

• Do NOT use old function names (COMPLETE, CLASSIFY_TEXT, etc.) — use AI_* versions.
• Do NOT pass entire tables through AI_COMPLETE row-by-row without cost estimation.
• Do NOT hardcode model names without considering regional availability.