Snowflake Cortex AI Functions: LLMs, Embeddings & Vector Search in SQL

snowflake cortex is the single biggest AI-in-the-warehouse capability shift Snowflake has shipped since external functions, and the one most data teams underestimate on the first read because they treat it as "another AI add-on" instead of as a complete in-SQL LLM platform. Cortex bundles three things into a single namespace — a library of hosted cortex llm functions (COMPLETE, EXTRACT_ANSWER, SUMMARIZE, TRANSLATE, SENTIMENT, CLASSIFY_TEXT), a family of EMBED_TEXT_* functions plus a native VECTOR column type for snowflake embeddings and snowflake vector search, and a higher-level orchestration tier (cortex search, Cortex Analyst, cortex agents, Document AI). The whole surface is callable from plain SQL, billed in credits, and runs without the data ever leaving your Snowflake account.

This guide is the senior-DE walkthrough you wished existed the first time an interviewer asked "when do you pick cortex ai over an external LLM stack?" or "how would you build a RAG pipeline on top of Snowflake with zero new services?" It covers the cortex complete model picker (Llama-3, Mistral, Claude family on the hosted side; external models via Cortex's adapter on the bring-your-own side), the embed + cosine_similarity pattern for ad-hoc semantic search, Cortex Search Service as a managed hybrid BM25 + vector + filters index, Cortex Agents as the orchestration layer that wires tool calls into a single LLM session, Document AI for parsing PDFs into structured rows, Cortex Analyst as governed text-to-SQL on top of a semantic model, and the 5-question decision tree senior engineers use to pick snowflake llm vs an external stack in 2026. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse on the ETL practice library →, and sharpen the modelling axis with the joins practice library →.

---

On this page

• Why Cortex is Snowflake's AI play in 2026
• Cortex LLM functions
• Cortex Embed + Vector Search
• Cortex Agents + Document AI
• Picking Cortex vs external LLM stack
• Cheat sheet — Cortex recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why Cortex is Snowflake's AI play in 2026

Cortex is "AI in SQL" — no model hosting, no GPU ops, no data egress, billed in credits

The one-sentence invariant: Snowflake Cortex turns LLM inference, embeddings, and vector search into ordinary SQL functions billed in Snowflake credits, with the data never leaving the customer's Snowflake account. Every other observation about Cortex — the model picker, the credit cost, the freshness window of the hosted models, the Cortex Search Service hybrid index, the Agents orchestration layer — follows from that one structural commitment. Once you internalise "AI in SQL, data stays in," the entire Cortex interview surface collapses to a sequence of consequences from that one design choice.

Four axes interviewers actually probe.

• Function flavours. Cortex is not a single API. It is three tiers stacked: low-level per-call functions (COMPLETE, EMBED_TEXT_768, SUMMARIZE, etc.), a managed search service (cortex search with hybrid BM25 + vector), and a higher-level orchestration tier (Cortex Agents, Cortex Analyst, Document AI). Knowing which tier solves which problem is the first interview signal.
• Cost. Cortex is billed by tokens consumed times a model-specific credit rate, plus warehouse compute for the SQL that wraps the call. A bad model picker turns a $200/month workload into a $20,000/month one. Senior interviewers will quiz you on the cost model directly.
• Freshness. Cortex's hosted models have fixed training cut-offs (Llama-3 family ~2024, Mistral 7B ~2024, Claude Sonnet family on a rolling cadence). For anything that needs real-time facts, you ship retrieval-augmented generation (RAG) — embed your docs, store in a VECTOR column or Cortex Search Service, retrieve at query time, inject into the prompt.
• Data-stays-put. This is the headline. Cortex calls run inside the customer's Snowflake-managed cloud account; rows never leave the Snowflake boundary. For regulated industries (healthcare, finance, public sector), this is often the deciding axis — no DPA negotiation with a third-party LLM vendor, no PII redaction pipeline before the prompt.

The 2026 reality — what changed since 2023.

• Cortex LLM functions went GA in late 2024; the model catalogue now spans Llama-3 (8B, 70B, 405B), Mistral 7B / 8x7B, Claude family (Sonnet, Haiku via partner integration), Snowflake Arctic, and Reka models. Model availability varies by region.
• Cortex Search Service went GA in 2025 — a managed hybrid index that combines BM25 lexical scoring, embedding-based semantic search, and metadata filters in a single API. Behind the scenes, Snowflake handles the embedding refresh, the index rebuild, and the rank fusion.
• Cortex Agents are in public preview as of 2025 — a tool-calling orchestrator that lets a single LLM session call Cortex Analyst, vector search, COMPLETE, and user-defined SQL functions inside one prompt-response loop.
• Document AI matured from preview into GA — a vision-language model trained on document layouts that turns PDFs and images into structured rows you can SELECT from.
• Cortex Analyst (governed text-to-SQL on top of a semantic model) reached preview, going head-to-head with stand-alone text-to-SQL vendors.

What interviewers listen for.

• Do you say "Cortex is AI in SQL — no model hosting, no GPU ops" in the first sentence? — senior signal.
• Do you describe the cost model as "tokens × model credit rate + warehouse compute" unprompted? — required answer.
• Do you mention "data stays in Snowflake" as the deciding axis for regulated workloads? — senior signal.
• Do you push back on "Cortex replaces OpenAI" with "it complements; hybrid is the production pattern"? — senior signal.

Worked example — first Cortex COMPLETE call

Detailed explanation. The classic Cortex "Hello World" — call SNOWFLAKE.CORTEX.COMPLETE from a SELECT, get an LLM completion back, see the credit cost in SNOWFLAKE.ACCOUNT_USAGE.CORTEX_FUNCTIONS_USAGE_HISTORY. The call is a single SQL function; the runtime is everything you would otherwise build (model serving infra, autoscaling GPUs, auth, network egress, billing).

Question. Write a SQL query that uses SNOWFLAKE.CORTEX.COMPLETE to generate a one-sentence summary of a support_tickets.body column. Pick a small model (Llama-3 8B) to keep the cost low and explain what each part of the call is doing.

Input.

ticket_id	body
t1	"My laptop won't boot after the last update; the screen is black but the fan is running."
t2	"I cannot download the invoice for invoice #4827 from the customer portal — the button is greyed out."
t3	"Two-factor authentication is asking for a code from an old phone number I no longer use."

Code.

SELECT
    ticket_id,
    SNOWFLAKE.CORTEX.COMPLETE(
        'llama3-8b',                                       -- model name
        CONCAT('Summarize this support ticket in one sentence: ',
               body)
    ) AS summary
FROM support_tickets
WHERE created_at >= DATEADD('day', -1, CURRENT_TIMESTAMP());

Step-by-step explanation.

1. SNOWFLAKE.CORTEX.COMPLETE(model, prompt) is a scalar SQL function in the SNOWFLAKE.CORTEX schema. Every row in the result set triggers one LLM call.
2. The first argument is the model identifier — here llama3-8b. Other options include llama3-70b, mistral-7b, mistral-large, snowflake-arctic, and Claude variants depending on region.
3. The second argument is the prompt string. You can use CONCAT to splice in row values; Snowflake handles the marshalling.
4. The function runs against the active warehouse. A bigger warehouse does not run the LLM faster — model latency is dominated by token generation on the Cortex side, not by warehouse size. Pick the smallest warehouse that satisfies your SQL.
5. Cost lands in two places: CORTEX_FUNCTIONS_USAGE_HISTORY (tokens × model rate, billed in Cortex credits) and the regular warehouse credit meter (for the SELECT itself). For a 3-row example, the cost is fractions of a cent; for a 10M-row batch over llama3-70b, the cost can run into thousands of dollars — pay attention.

Output.

ticket_id	summary
t1	"Laptop has a black screen but the fan is running after a recent update."
t2	"User cannot download invoice #4827 because the portal button is disabled."
t3	"Two-factor authentication is requesting a code from an old phone number the user no longer owns."

Rule of thumb. Start every Cortex experiment on a small model (llama3-8b or mistral-7b). If the quality is acceptable on the small model, you save 5-10x the credit cost vs the 70B-class models. Upgrade the model only when the small one fails on real data, never preemptively.

Worked example — Cortex vs external LLM cost model

Detailed explanation. A frequent interview probe: "you can call Cortex COMPLETE from SQL, or you can call OpenAI's API from a Python service. What is the cost difference, and what other axes besides raw token price matter?" The answer is more nuanced than the per-token table suggests — Cortex bundles model + infra + egress + governance into one credit rate, while external APIs decouple them.

Question. Compare the all-in cost of summarising 10M support tickets with Cortex COMPLETE(llama3-8b) vs an external OpenAI API call from a Python lambda. Include egress, governance, and ops cost — not just the per-token sticker price.

Input.

Workload	Volume
Tickets to summarise	10M
Avg prompt tokens	400
Avg completion tokens	80
Storage	Snowflake (no egress)

Code.

-- Cortex path — single SQL statement, data stays in Snowflake
INSERT INTO ticket_summaries (ticket_id, summary)
SELECT ticket_id,
       SNOWFLAKE.CORTEX.COMPLETE('llama3-8b',
         CONCAT('Summarize: ', body))
FROM support_tickets;

# External path — Python service reads from Snowflake, calls OpenAI, writes back
import snowflake.connector, openai
conn = snowflake.connector.connect(...)
rows = conn.cursor().execute("SELECT ticket_id, body FROM support_tickets")
for r in rows:
    resp = openai.ChatCompletion.create(
        model="gpt-4o-mini",
        messages=[{"role": "user", "content": f"Summarize: {r[1]}"}])
    write_back(r[0], resp.choices[0].message.content)

Step-by-step explanation.

1. The Cortex path is a single SQL statement that runs entirely inside Snowflake. The token cost is billed in Cortex credits at the published llama3-8b rate. The warehouse credit for the SELECT is the only other charge.
2. The external path requires three additional cost lines: (a) egress from Snowflake (bytes leaving the cloud region, often $0.09/GB outside-region), (b) the Python service's compute (Lambda / Kubernetes pod), (c) per-call API charges with OpenAI plus the data-protection agreement overhead.
3. On a 10M-ticket batch at ~480 tokens per call, Cortex llama3-8b runs about 4.8B tokens total — at a representative rate, that's roughly $500-1,500 in Cortex credits (numbers vary by region and pricing tier).
4. The same workload on gpt-4o-mini would be cheaper per token, but the egress + Python infra + DPA negotiation often eat the savings. For a regulated dataset, the DPA alone can cost more than the model.
5. The hidden axis: governance. Cortex calls inherit Snowflake's row-access policies, masking policies, and audit log automatically. The external Python path needs you to re-implement masking before the prompt — that is a real engineering cost and a real compliance risk.

Output (all-in 12-month cost estimate for 10M tickets/month).

Line item	Cortex llama3-8b	External gpt-4o-mini
Model tokens	$6K-18K	$4K-10K
Egress	$0	$1K-3K
Python infra	$0	$5K-15K
DPA + audit ops	$0	$10K-30K
Total	$6K-18K	$20K-58K

Rule of thumb. For Snowflake-resident data with row-level governance, Cortex usually wins on all-in cost — even when the per-token sticker rate looks higher than an external API. The "per-token" metric is a trap for the unwary.

Worked example — RAG starter pattern in three SQL statements

Detailed explanation. A common interview opener: "show me the shortest path to a working RAG (retrieval-augmented generation) pipeline in Snowflake — assume the docs are already loaded as a table." The answer is three statements: embed the docs, find the top-K nearest neighbours to the user question, inject them into a Cortex COMPLETE prompt. The whole loop is plain SQL.

Question. Build a starter RAG pipeline on a docs(id, text) table. Embed all docs once, store the embeddings, then answer the question "What is our PTO policy?" by retrieving the top 3 docs and calling COMPLETE.

Input.

id	text
d1	"Employees accrue 18 days of paid time off per year, prorated by start date."
d2	"The company observes 11 paid public holidays in the United States."
d3	"Health insurance is 100% employer-paid for the employee and 50% for dependants."
d4	"Unused PTO carries over up to 5 days into the next calendar year."

Code.

-- 1) Embed every doc once, store the vector
ALTER TABLE docs ADD COLUMN embedding VECTOR(FLOAT, 768);

UPDATE docs
SET embedding = SNOWFLAKE.CORTEX.EMBED_TEXT_768(
                  'snowflake-arctic-embed-m', text);

-- 2) Retrieve top-3 nearest neighbours to the question
WITH q AS (
    SELECT SNOWFLAKE.CORTEX.EMBED_TEXT_768(
             'snowflake-arctic-embed-m',
             'What is our PTO policy?') AS qv
),
ranked AS (
    SELECT id, text,
           VECTOR_COSINE_SIMILARITY(embedding, q.qv) AS score
    FROM docs, q
    ORDER BY score DESC
    LIMIT 3
)
-- 3) Inject retrieved context into a COMPLETE call
SELECT SNOWFLAKE.CORTEX.COMPLETE('llama3-8b',
         CONCAT(
           'Answer using only the context below. Context: ',
           LISTAGG(text, '\n---\n'),
           '\nQuestion: What is our PTO policy?'))
FROM ranked;

Step-by-step explanation.

1. The embedding column is declared with the native VECTOR(FLOAT, 768) type — a fixed-length array Snowflake stores efficiently and indexes for similarity search.
2. The UPDATE statement calls EMBED_TEXT_768 on every row. The snowflake-arctic-embed-m model is a 768-dim embedder optimised for English; use EMBED_TEXT_1024 with a multilingual model for non-English corpora.
3. The CTE q computes the question's embedding once. We then JOIN it to every doc, compute cosine similarity, and take the top 3.
4. VECTOR_COSINE_SIMILARITY(a, b) returns a value in [-1, 1]; higher is more similar. VECTOR_L2_DISTANCE and VECTOR_INNER_PRODUCT are alternatives — cosine is the default for normalised embeddings.
5. The final COMPLETE call passes the retrieved doc texts as context. LISTAGG concatenates them with a separator so the LLM sees one labelled prompt.

Output.

answer
"Employees accrue 18 days of paid time off per year, prorated by start date. Unused PTO carries over up to 5 days into the next calendar year."

Rule of thumb. For < 100K docs and < 100 QPS, plain VECTOR_COSINE_SIMILARITY + ORDER BY ... LIMIT K is fast enough — no separate vector DB. Past that scale, switch to Cortex Search Service (next sections), which Snowflake operates as a managed hybrid index.

Senior interview question on Cortex's place in 2026

A senior interviewer might open with: "You join a Snowflake-shop data team that wants to add AI capabilities. How do you choose between using Snowflake Cortex vs standing up a separate LLM stack? Walk me through the questions you'd ask in order."

Solution Using the 4-axis Cortex decision framework

Cortex decision framework
==========================

1. Where does the data live?
   - All in Snowflake → Cortex eligible
   - Multi-cloud / on-prem  → external stack or hybrid

2. What is the governance bar?
   - Regulated (HIPAA, PCI, sovereignty) → Cortex strongly preferred
   - Public data, no PII                  → either works

3. What is the freshness need?
   - Frozen training cut-off OK → hosted Cortex model
   - Needs real-time facts     → Cortex + RAG OR external + RAG

4. What is the cost shape?
   - High volume + Snowflake-resident data → Cortex usually cheapest all-in
   - Sparse calls + already-built Python   → external API may win

Step-by-step trace.

Use case	Q1 location	Q2 governance	Q3 freshness	Q4 cost	Picked
Support-ticket summary	Snowflake	low	OK with frozen	high volume	Cortex
Real-time chatbot on company docs	Snowflake	medium	RAG	medium volume	Cortex + RAG
Healthcare claim coding	Snowflake	HIPAA	RAG	high volume	Cortex
Internal LLM assistant (frontier model needed)	Snowflake	low	real-time	low volume	External + Cortex hybrid
Multi-cloud customer chatbot	mixed	medium	real-time	high volume	External + external vector DB

After the 4-axis pass, the Cortex decision is usually unambiguous. The remaining 10-15% — where both stacks work — defaults to "what does the team already operate?"

Output:

Stack	When it wins
Cortex only	Snowflake-resident data, regulated, batch or near-real-time, cost-sensitive
Cortex + RAG	Need real-time facts on Snowflake-resident docs, any governance
Cortex + external (hybrid)	Need frontier-model quality for user-facing surface, governed batch on Cortex
External only	Multi-cloud data, non-regulated, frontier-model dependency, existing Python team

Why this works — concept by concept:

• Data location is the gating axis — Cortex is only viable if the data is already in Snowflake. For multi-cloud or on-prem data, the egress cost and synchronization complexity often nullify Cortex's advantages.
• Governance is the second axis — for regulated workloads, Cortex's "data stays in" guarantee is worth real money. The cost of a DPA, masking pipeline, and audit trail for an external API often equals or exceeds the per-token cost difference.
• Freshness drives RAG, not engine choice — if you need real-time facts, you need retrieval-augmented generation no matter which engine. Cortex's EMBED_TEXT_* + VECTOR_COSINE_SIMILARITY makes RAG implementable in plain SQL, which is a real engineering win.
• Cost is all-in, not per-token — the per-token sticker price is misleading. Egress, Python infra, governance ops, and the engineering time to wire it all together often dominate. Cortex bundles all of this into the credit meter.
• Cost — both engines are O(tokens) per call; the cost difference lives in the bundling, not the throughput. A wrong engine choice at the 10M-rows-per-day scale costs $100K+/year in the wrong direction.

SQL
Topic — sql
Snowflake Cortex SQL problems

Practice →

ETL Topic — etl AI ETL pipeline problems

Practice →

---

2. Cortex LLM functions

cortex llm functions are six SELECT-able primitives — COMPLETE, EXTRACT_ANSWER, SUMMARIZE, TRANSLATE, SENTIMENT, CLASSIFY_TEXT

The mental model in one line: Cortex LLM functions are six scalar SQL functions in the SNOWFLAKE.CORTEX schema, each wrapping a different family of pretrained model and billed by tokens consumed times a model-specific credit rate. Once you say that out loud, every "how do I do task X with cortex complete?" question becomes a deduction from "which of the six primitives matches my task, and which model fits the budget?"

The six primitives.

• COMPLETE(model, prompt) — open-ended text generation. Takes a model name plus a prompt string (or a chat-format array of messages), returns the completion. Use for summarisation prompts that need control, conversational answers, structured generation, JSON-formatted output.
• EXTRACT_ANSWER(question, context) — extractive QA. Returns a span from context that answers question, with a confidence score. No hallucination — the answer is always a substring of the input.
• SUMMARIZE(text) — opinionated summarisation. Returns a short paraphrase of text. Simpler than COMPLETE with a summary prompt, but less controllable.
• TRANSLATE(text, source_lang, target_lang) — language translation. Auto-detects source if source_lang is empty; supports a wide ISO-639 set.
• SENTIMENT(text) — returns a polarity score in [-1, 1]. Optimised for short product-review-shape text; less accurate on multi-paragraph documents.
• CLASSIFY_TEXT(text, ARRAY[label1, label2, ...]) — zero-shot text classification. Pass a label set; the function returns the most likely label. No fine-tuning required.

The model picker.

• llama3-8b — small, fast, cheapest. Good default for high-volume SUMMARIZE-shape work.
• llama3-70b — mid-tier; significantly better instruction following than 8B at ~5-10x the cost per token.
• mistral-7b — alternative small model; some workloads prefer it.
• mistral-large — frontier-class open model; comparable quality to mid-tier Claude on many benchmarks.
• snowflake-arctic — Snowflake's in-house MoE model; optimised for SQL generation and enterprise tasks.
• claude-3-5-sonnet (and variants) — premium quality via partner integration; highest per-token cost in the Cortex catalogue, but often the right answer for tasks where accuracy is the deciding axis (legal analysis, contract review, multi-step reasoning).
• Regional availability matters. Not every model is in every region; always check the model availability matrix before locking your model choice.

The chat-format COMPLETE call.

• COMPLETE also accepts an array of messages and an options object: COMPLETE(model, [{role: 'system', content: '...'}, {role: 'user', content: '...'}], {temperature: 0.2, max_tokens: 200}).
• The chat format lets you set a system prompt, control temperature, cap output tokens, and pass tool definitions for function-calling models. Production prompts should always use the structured form.
• Tool-calling: when the model returns a tool_use block, the runtime can dispatch it to a registered tool and re-prompt with the tool result. This is the foundation Cortex Agents builds on (next section).

Cost model — tokens × rate.

• Every call counts input tokens + output tokens. The credit rate per million tokens differs by model: small models like llama3-8b are an order of magnitude cheaper per million tokens than frontier-class models.
• The exact rate is published in Snowflake's docs and updated periodically. The mental model: pick the smallest model that satisfies your quality bar, and re-run the cost estimate after every model swap.
• Usage is logged in SNOWFLAKE.ACCOUNT_USAGE.CORTEX_FUNCTIONS_USAGE_HISTORY — query it weekly to catch a cost regression early.

The structured-output trap.

• LLMs love returning prose. If you COMPLETE "summarise this ticket in JSON," you sometimes get prose with a JSON block, sometimes prose alone, sometimes malformed JSON.
• The robust pattern: ask for JSON, then PARSE_JSON(SNOWFLAKE.CORTEX.COMPLETE(...)) and trap parse errors with TRY_PARSE_JSON. Filter out malformed rows; alert on rate.
• For workloads where structured output is critical, prefer a model with a documented response_format=json_object support, or use EXTRACT_ANSWER for the pure extractive case.

Common interview probes on the LLM functions.

• "When would you use EXTRACT_ANSWER instead of COMPLETE?" — when you need a guaranteed-from-context answer (no hallucination), and the answer is a span of the input.
• "Why is SUMMARIZE simpler than COMPLETE with a summary prompt?" — opinionated default + fewer knobs, but you lose temperature control and chat-format options.
• "How do you cap the cost of a 100M-row COMPLETE batch?" — start with llama3-8b, sample 1K rows, manually evaluate quality, then either scale up or stay; meter CORTEX_FUNCTIONS_USAGE_HISTORY daily.
• "What is the relationship between Cortex's warehouse cost and its token cost?" — they're separate meters; the warehouse runs the SQL, Cortex bills tokens.

Worked example — extracting structured fields from messy text

Detailed explanation. A real interview opener: "you have a support_tickets table with a free-text body column. Extract product_name, error_code, and severity (1-5) into structured columns. Use Cortex." The clean answer uses COMPLETE with a chat-format call and a JSON schema, then parses the JSON.

Question. Extract structured fields (product_name, error_code, severity) from the body column of support_tickets using COMPLETE and PARSE_JSON.

Input.

ticket_id	body
t1	"Acme Widget v3 throws error E-401 on boot. Critical — production down."
t2	"Minor cosmetic glitch in Acme Dashboard v2 — alignment is off on Safari."
t3	"Acme Gateway returning E-503 intermittently; about 1 in 10 requests fail."

Code.

WITH extracted AS (
    SELECT
        ticket_id,
        TRY_PARSE_JSON(
          SNOWFLAKE.CORTEX.COMPLETE(
            'llama3-70b',
            ARRAY_CONSTRUCT(
              OBJECT_CONSTRUCT('role', 'system',
                'content', 'Return JSON with product_name, error_code, severity (1-5). If a field is missing, use null.'),
              OBJECT_CONSTRUCT('role', 'user',
                'content', body)),
            OBJECT_CONSTRUCT('temperature', 0, 'max_tokens', 200,
                              'response_format', OBJECT_CONSTRUCT('type', 'json_object'))
          )) AS payload
    FROM support_tickets
)
SELECT
    ticket_id,
    payload:product_name::STRING AS product_name,
    payload:error_code::STRING   AS error_code,
    payload:severity::NUMBER     AS severity
FROM extracted
WHERE payload IS NOT NULL;

Step-by-step explanation.

1. COMPLETE is called in chat-message format with a system prompt setting the JSON contract and a user message containing the ticket body.
2. temperature=0 makes the output deterministic given the same input — critical for an extraction pipeline you don't want to re-evaluate every run.
3. response_format=json_object instructs the model to emit a JSON object directly. Not every model honours this; for models that don't, you fall back to prompt-only JSON instructions and accept a higher parse-failure rate.
4. TRY_PARSE_JSON returns NULL for malformed payloads instead of erroring. The downstream WHERE payload IS NOT NULL filters them; pair with a daily alert on the parse-failure rate.
5. The semi-structured access syntax payload:product_name::STRING extracts each field with explicit casting. Snowflake's VARIANT type makes this idiomatic.

Output.

ticket_id	product_name	error_code	severity
t1	Acme Widget v3	E-401	5
t2	Acme Dashboard v2	NULL	2
t3	Acme Gateway	E-503	4

Rule of thumb. For structured extraction, always set temperature=0, always request a strict JSON schema, always wrap in TRY_PARSE_JSON, and always log the parse-failure rate as a SLI. The trio prevents 90% of the production bugs.

Worked example — classifying support tickets at low cost

Detailed explanation. Another common probe: "categorise 100K incoming tickets per day into one of 8 buckets — billing, auth, bug, feature_request, outage, onboarding, data_export, other. Minimise cost." Zero-shot CLASSIFY_TEXT is faster, cheaper, and simpler than a prompt-engineered COMPLETE for this exact shape.

Question. Use CLASSIFY_TEXT to bucket support tickets into 8 categories. Show the call, the output column shape, and how it compares to a COMPLETE alternative.

Input.

ticket_id	body
t1	"I cannot log in — the password reset email never arrives."
t2	"Please refund the $99 charge on invoice INV-7733; it was duplicate."
t3	"Could you add a CSV export to the analytics dashboard?"
t4	"Status page is showing red; my production calls are 500-ing."

Code.

SELECT
    ticket_id,
    SNOWFLAKE.CORTEX.CLASSIFY_TEXT(
      body,
      ARRAY_CONSTRUCT('billing', 'auth', 'bug',
                       'feature_request', 'outage',
                       'onboarding', 'data_export', 'other')
    ):label::STRING AS category,
    SNOWFLAKE.CORTEX.CLASSIFY_TEXT(
      body,
      ARRAY_CONSTRUCT('billing', 'auth', 'bug',
                       'feature_request', 'outage',
                       'onboarding', 'data_export', 'other')
    ):score::FLOAT AS confidence
FROM support_tickets;

Step-by-step explanation.

1. CLASSIFY_TEXT(text, labels) returns a VARIANT with two fields: label (the chosen class) and score (a confidence). Snowflake routes this to a model trained for zero-shot classification, so no fine-tuning data is required.
2. The label set is your enum. The model is constrained to pick from the set; it cannot invent a new label.
3. Compared to a COMPLETE call with a "classify this into X, Y, Z" prompt, CLASSIFY_TEXT is 3-10x cheaper per call and produces a structured output without JSON parsing risk.
4. For high-volume daily batches (10M+ rows), CLASSIFY_TEXT is almost always the right primitive — its cost-per-row is the lowest in the Cortex LLM catalogue for label-set workloads.
5. The trade-off: you lose the open-ended explainability of COMPLETE. If you need "why is this billing?" you have to call COMPLETE separately on the misclassified subset.

Output.

ticket_id	category	confidence
t1	auth	0.94
t2	billing	0.97
t3	feature_request	0.91
t4	outage	0.96

Rule of thumb. When the output is a fixed label set, reach for CLASSIFY_TEXT first. Only escalate to COMPLETE if you need free-text rationale or your label set is large enough (50+) to confuse the zero-shot classifier.

Worked example — sentiment + translation on multilingual reviews

Detailed explanation. A typical real-world workload: a product-review table contains reviews in many languages, and the team wants both a sentiment score (for the BI dashboard) and an English translation (for the support team that does not read every language). Cortex makes this a 2-function pipeline with no Python.

Question. Process a multilingual reviews(id, language, body) table: translate every non-English review to English using TRANSLATE, then run SENTIMENT on the English text.

Input.

id	language	body
r1	en	"Best widget I've ever used — perfect every time."
r2	es	"El servicio fue terrible y el producto llegó roto."
r3	de	"Sehr gut, schnelle Lieferung, ich bin zufrieden."
r4	fr	"Ça va, mais le prix est un peu élevé pour la qualité."

Code.

WITH translated AS (
    SELECT
        id,
        language,
        body,
        CASE
            WHEN language = 'en' THEN body
            ELSE SNOWFLAKE.CORTEX.TRANSLATE(body, language, 'en')
        END AS body_en
    FROM reviews
)
SELECT
    id,
    language,
    body,
    body_en,
    SNOWFLAKE.CORTEX.SENTIMENT(body_en) AS sentiment_score
FROM translated;

Step-by-step explanation.

1. TRANSLATE(text, source_lang, target_lang) accepts ISO-639 codes. If you don't know the source language, pass an empty string and Cortex auto-detects.
2. We use a CASE to skip the translation for already-English rows — saving the cost of a no-op call. For very mixed corpora, this can cut translation cost in half.
3. SENTIMENT(text) returns a float in [-1, 1] — positive at +1, negative at -1, neutral near 0. The model is trained on short-to-medium product-review-shape text; long multi-paragraph essays produce less reliable scores.
4. For mixed-length text, summarise first (SUMMARIZE or COMPLETE) then sentiment-score the summary; the score on the summary is usually more accurate than on the raw long text.
5. The two-step pipeline is a single SQL statement. No Python service, no per-language model selection, no translation API account.

Output.

id	language	body	body_en	sentiment_score
r1	en	"Best widget..."	"Best widget I've ever used..."	0.92
r2	es	"El servicio fue terrible..."	"The service was terrible and the product arrived broken."	-0.85
r3	de	"Sehr gut..."	"Very good, fast delivery, I'm satisfied."	0.78
r4	fr	"Ça va..."	"It's OK, but the price is a bit high for the quality."	-0.12

Rule of thumb. Always normalise to one language before scoring sentiment. The English-trained sentiment model produces dramatically more accurate scores on translated text than on the source language for most non-English locales.

Senior interview question on Cortex model picker

A senior interviewer might ask: "You have a 50M-row product-description table and you want to extract category, material, and target_audience for each row. Walk me through how you'd pick the Cortex model, validate quality, and cap the cost."

Solution Using sample-evaluate-scale ladder

-- Step 1: pick a candidate model (start small)
SELECT
    id,
    SNOWFLAKE.CORTEX.COMPLETE('llama3-8b',
        CONCAT('Extract category, material, target_audience as JSON: ', description))
        AS payload
FROM products
SAMPLE (1000 ROWS);

-- Step 2: human-evaluate the 1K-sample output for accuracy
-- (load into a review UI; mark each row 'good' / 'wrong')

-- Step 3: if accuracy < 90%, escalate to llama3-70b and re-evaluate
-- if accuracy < 95% at 70B, escalate to mistral-large or claude

-- Step 4: lock the model, run the full 50M batch
INSERT INTO product_attributes (id, payload)
SELECT
    id,
    TRY_PARSE_JSON(SNOWFLAKE.CORTEX.COMPLETE('llama3-8b',
        CONCAT('Extract category, material, target_audience as JSON: ', description)))
FROM products
WHERE id NOT IN (SELECT id FROM product_attributes);   -- idempotent

-- Step 5: monitor cost daily
SELECT DATE_TRUNC('day', start_time) AS d,
       function_name,
       SUM(token_credits) AS credits
FROM SNOWFLAKE.ACCOUNT_USAGE.CORTEX_FUNCTIONS_USAGE_HISTORY
WHERE start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
GROUP BY 1, 2
ORDER BY 1 DESC;

Step-by-step trace.

Step	Action	Decision criterion
1	Sample 1K rows, call llama3-8b	Cheapest model — establish a baseline
2	Human-evaluate 1K outputs	Accuracy ≥ 90% → lock 8B; else escalate
3	If 8B fails, repeat at llama3-70b	Accuracy ≥ 95% → lock 70B; else escalate
4	Run full 50M batch, idempotent insert	WHERE id NOT IN makes retries safe
5	Query CORTEX_FUNCTIONS_USAGE_HISTORY daily	Alert if daily credit > budget × 1.2

The ladder approach prevents two common mistakes: picking the biggest model preemptively (10x the cost for marginal quality), and shipping a small model without validating quality (rework + business impact).

Output:

Metric	8B baseline	70B escalation	Frontier model
Cost per million tokens	$X	~5-8× X	~20-30× X
Latency per call	low	medium	medium
Typical extraction accuracy	80-90%	92-97%	95-99%
When to pick	high volume, simple schemas	mid volume, nuanced schemas	low volume, high-stakes

Why this works — concept by concept:

• Sample first, scale second — the 1K sample is the only cheap way to know whether a model is good enough for your data. Without it, you either over-pay (frontier model on everything) or under-deliver (small model on hard text).
• Human eval is non-negotiable — token-level metrics like BLEU don't predict extraction accuracy on real data. Sit with 1K rows for 30 minutes; the ROI is enormous.
• Idempotent batch — WHERE id NOT IN (already_processed) makes retries free. Snowflake will surface transient failures (rare, but they happen); idempotency lets you re-run without double-billing.
• Daily cost meter — CORTEX_FUNCTIONS_USAGE_HISTORY is your only protection against a runaway cost regression. Alert on 1.2× budget; investigate every alert.
• Cost — model cost grows roughly 5-10x per tier (8B → 70B → frontier). Picking the right tier on the right workload is a 100K+/year decision at 50M-row scale.

SQL
Topic — sql
Cortex LLM SQL extraction problems

Practice →

ETL Topic — etl LLM batch ETL problems

Practice →

---

3. Cortex Embed + Vector Search

snowflake vector search is EMBED_TEXT_* + the VECTOR type + cosine_similarity — and Cortex Search Service is the managed hybrid index on top

The mental model in one line: Cortex ships two EMBED_TEXT_* functions plus a native VECTOR(FLOAT, N) column type for storing embeddings, three VECTOR_* similarity functions for ad-hoc nearest-neighbour queries, and a managed cortex search service that combines BM25 lexical scoring with vector similarity and metadata filters in a single API. Once you say "embed once, store in VECTOR, similarity-search at query time, escalate to Cortex Search Service when scale demands it," every snowflake embeddings interview question becomes a deduction from that pipeline.

The embed functions.

• EMBED_TEXT_768(model, text) — 768-dimension embedding. Use with English-optimised models (e.g. snowflake-arctic-embed-m) where 768 dims is enough resolution for the corpus.
• EMBED_TEXT_1024(model, text) — 1024-dimension embedding. Use with multilingual or higher-resolution models (e.g. nv-embed-qa-4, voyage-multilingual-2) where the extra dimensions help with cross-language or fine-grained semantic discrimination.
• Both return a VECTOR(FLOAT, dim) value that you can store, index, and compare.

The VECTOR column type.

• VECTOR(FLOAT, N) is a fixed-length array of N floats, stored compactly in Snowflake's micro-partitions.
• It's a real column type: you can CREATE TABLE docs (id INT, text VARCHAR, embedding VECTOR(FLOAT, 768)), you can INSERT and UPDATE it, you can join on similarity expressions.
• The dim is part of the type — you cannot mix 768-d and 1024-d vectors in the same column.

The similarity functions.

• VECTOR_COSINE_SIMILARITY(a, b) — returns (a · b) / (||a|| × ||b||) in [-1, 1]. Default choice for normalised embeddings.
• VECTOR_L2_DISTANCE(a, b) — Euclidean distance. Lower is closer. Useful when the embedder produces unnormalised vectors.
• VECTOR_INNER_PRODUCT(a, b) — raw dot product. Use when the embedder is trained to produce dot-product-compatible vectors (some retrieval models).
• For most modern embedding models, cosine similarity is the right default.

Cortex Search Service — managed hybrid index.

• A CORTEX SEARCH SERVICE is a Snowflake object you create with CREATE CORTEX SEARCH SERVICE on a query that selects from a source table.
• Behind the scenes, Snowflake builds three indexes: a BM25 inverted index on text, a vector index on embeddings (auto-computed if not provided), and a filter index on whatever metadata columns you attach.
• Queries go through SEARCH_PREVIEW(service_name, query, options) (Snowflake SQL) or the REST API; the service returns top-K results scored by a learned rank fusion of BM25 + vector + filter match.
• The service auto-refreshes on a configurable cadence (incrementally or full rebuild).

Refresh strategy — incremental vs full.

• Incremental refresh — Cortex Search Service tracks change tracking on the source table and re-embeds + re-indexes only the changed rows. Default for tables with stable schemas.
• Full rebuild — required when the embedding model changes or the source schema changes. Cost scales linearly with row count; budget accordingly.
• For high-update tables, set the refresh cadence aggressively (every 1-5 minutes); for low-update tables, daily is enough.

The hybrid retrieval pattern.

• Pure vector search misses exact-keyword matches ("error E-401" → vector search may return rows about similar errors but miss the exact code).
• Pure BM25 misses semantic matches ("how do I cancel?" → BM25 needs the word "cancel" but the doc says "terminate subscription").
• Hybrid (Cortex Search Service does this for you) combines both: a learned rank fusion ranks results that score well on either axis.

Common interview probes on embed + search.

• "What is the difference between EMBED_TEXT_768 and EMBED_TEXT_1024?" — embedding dim; pick by model and corpus.
• "When do you build a Cortex Search Service instead of querying VECTOR_COSINE_SIMILARITY directly?" — past ~100K docs or when you need hybrid BM25 + vector + filters.
• "How does Cortex Search Service refresh its index?" — incremental on change-tracked source tables; full rebuild on model change.
• "What is the cost model for embeddings?" — token-based, billed at the model's embedding rate; index storage billed separately.

Worked example — building a Cortex Search Service from scratch

Detailed explanation. A typical greenfield setup: a kb_articles(id, title, body, product, status) table needs a managed search endpoint that supports text queries with semantic search, BM25, and filters on product and status. Cortex Search Service is two DDL statements.

Question. Create a Cortex Search Service on kb_articles with vector + text indexing and metadata filters on product and status. Query it for "how to cancel subscription" filtered to active billing articles.

Input.

id	title	body	product	status
a1	"Cancellation flow"	"To terminate your subscription, go to settings → billing → cancel."	billing	active
a2	"Refund policy"	"We process refunds within 7 business days of cancellation."	billing	active
a3	"Legacy v1 cancellation"	"Old v1 customers can email support to cancel."	billing	deprecated
a4	"Password reset"	"Use the password reset link on the login page."	auth	active

Code.

-- 1) Create the search service (one-time DDL)
CREATE OR REPLACE CORTEX SEARCH SERVICE kb_search
ON body
ATTRIBUTES product, status
WAREHOUSE = my_wh
TARGET_LAG = '5 minutes'
AS (
    SELECT id, title, body, product, status
    FROM kb_articles
);

-- 2) Query the search service
SELECT SNOWFLAKE.CORTEX.SEARCH_PREVIEW(
    'kb_search',
    OBJECT_CONSTRUCT(
        'query', 'how to cancel subscription',
        'columns', ARRAY_CONSTRUCT('id', 'title', 'body'),
        'filter', OBJECT_CONSTRUCT(
            '@and', ARRAY_CONSTRUCT(
                OBJECT_CONSTRUCT('@eq',
                    OBJECT_CONSTRUCT('product', 'billing')),
                OBJECT_CONSTRUCT('@eq',
                    OBJECT_CONSTRUCT('status', 'active'))
            )),
        'limit', 5
    )
);

Step-by-step explanation.

1. CREATE CORTEX SEARCH SERVICE defines the search endpoint. ON body is the column that will be embedded and BM25-indexed. ATTRIBUTES product, status declares the filterable columns.
2. TARGET_LAG = '5 minutes' configures the refresh cadence — Snowflake will keep the index within 5 minutes of source freshness, using incremental refresh on change-tracked rows.
3. WAREHOUSE = my_wh is the warehouse Snowflake uses for index maintenance (embedding new rows, rebuilding sub-indexes). It's separate from the warehouse that issues queries.
4. SEARCH_PREVIEW is the SQL-callable interface (the REST endpoint is the production interface). It takes the service name and an options object with the query, columns to return, filter expression, and limit.
5. The filter syntax (@and, @eq) is a JSON-shaped DSL — Snowflake translates it into the filter index lookup. The service returns top-K results ranked by hybrid score, restricted to rows where product='billing' and status='active'.

Output.

id	title	body
a1	"Cancellation flow"	"To terminate your subscription..."
a2	"Refund policy"	"We process refunds within 7 business days of cancellation."

Rule of thumb. Always declare the filterable columns as ATTRIBUTES at create time. Adding an attribute later requires a full service rebuild. Be liberal: list every column you might ever filter on.

Worked example — ad-hoc vector search without a search service

Detailed explanation. When the corpus is small (< 100K rows) or the use case is internal-only, you don't need a Cortex Search Service. Plain SQL with EMBED_TEXT_768 + VECTOR_COSINE_SIMILARITY + ORDER BY ... LIMIT K is fast enough and avoids the service maintenance cost.

Question. Build an in-SQL semantic search over a 50K-row tickets(id, body) table. Embed once, then answer "tickets about failed payments" by retrieving the top 10 nearest neighbours.

Input.

id	body
t1	"Credit card declined when paying for the Pro plan."
t2	"Refund pending for invoice INV-1198."
t3	"Login screen is stuck loading."
t4	"Charge failed: card expired."

Code.

-- 1) Embed every ticket once
ALTER TABLE tickets ADD COLUMN embedding VECTOR(FLOAT, 768);

UPDATE tickets
SET embedding = SNOWFLAKE.CORTEX.EMBED_TEXT_768(
                  'snowflake-arctic-embed-m', body);

-- 2) Run a similarity query
SELECT
    t.id,
    t.body,
    VECTOR_COSINE_SIMILARITY(
        t.embedding,
        SNOWFLAKE.CORTEX.EMBED_TEXT_768(
            'snowflake-arctic-embed-m',
            'tickets about failed payments')
    ) AS score
FROM tickets t
ORDER BY score DESC
LIMIT 10;

Step-by-step explanation.

1. Embeddings are computed once per row (in the UPDATE step) and stored. This is the dominant cost; do it once and amortise across all future queries.
2. The similarity query embeds the query text at runtime (one call per query) and joins each row's embedding to it. The cost per query is one embedding call plus N similarity computations.
3. VECTOR_COSINE_SIMILARITY is the standard for normalised embeddings. The result is in [-1, 1]; higher means more semantically related.
4. ORDER BY score DESC LIMIT 10 is the brute-force top-K. Snowflake does not auto-create a vector index on the column at this scale — but at 50K rows, the scan is fast enough on a Medium warehouse.
5. As the row count grows past ~100K, the scan latency grows linearly. Past that point, switch to Cortex Search Service which builds an approximate-nearest-neighbour index behind the scenes.

Output.

id	body	score
t1	"Credit card declined when paying for the Pro plan."	0.88
t4	"Charge failed: card expired."	0.85
t2	"Refund pending for invoice INV-1198."	0.71
t3	"Login screen is stuck loading."	0.32

Rule of thumb. Use ad-hoc VECTOR_COSINE_SIMILARITY for corpora up to ~100K rows or for one-off analyses. Above that, the cost-per-query and latency of a managed Cortex Search Service amortises faster than the brute-force scan.

Worked example — hybrid search with metadata filters

Detailed explanation. Real-world retrieval needs three axes at once: semantic relevance (vector), exact-keyword recall (BM25), and metadata filters (status, tenant, date range). The Cortex Search Service hybrid query lets you express all three in a single call; doing it by hand requires re-implementing rank fusion.

Question. Search a customer-support knowledge base for "refund timeline" while constraining to product='billing' AND status='active' AND updated_at > 2026-01-01. Use the hybrid Cortex Search Service.

Input.

id	title	product	status	updated_at
a1	"Refund timeline"	billing	active	2026-03-12
a2	"Refund timeline (legacy)"	billing	deprecated	2024-09-04
a3	"Refund eligibility"	billing	active	2026-01-15
a4	"Cancellation flow"	billing	active	2026-02-20

Code.

SELECT SNOWFLAKE.CORTEX.SEARCH_PREVIEW(
    'kb_search',
    OBJECT_CONSTRUCT(
        'query', 'refund timeline',
        'columns', ARRAY_CONSTRUCT('id', 'title'),
        'filter', OBJECT_CONSTRUCT(
            '@and', ARRAY_CONSTRUCT(
                OBJECT_CONSTRUCT('@eq',
                    OBJECT_CONSTRUCT('product', 'billing')),
                OBJECT_CONSTRUCT('@eq',
                    OBJECT_CONSTRUCT('status', 'active')),
                OBJECT_CONSTRUCT('@gte',
                    OBJECT_CONSTRUCT('updated_at', '2026-01-01'))
            )),
        'limit', 10,
        'experimental', OBJECT_CONSTRUCT('rerank', TRUE)
    )
);

Step-by-step explanation.

1. The query string 'refund timeline' is BM25-indexed (lexical match on the words) and vector-embedded (semantic similarity on the meaning). Cortex Search Service runs both lookups in parallel.
2. The filter block constrains the candidate set BEFORE the rank fusion: only billing × active × updated_at >= 2026-01-01 rows are scored. This is essential — without the filter, deprecated articles and other-product articles would pollute the result.
3. @and / @eq / @gte are the filter operators. The full operator set covers equality, range, set membership, and boolean composition. Anything declared as ATTRIBUTES at service-create time is filterable.
4. experimental.rerank=TRUE enables an optional second-pass reranker (a cross-encoder model that scores each candidate against the query). This typically improves top-1 precision by 5-15% at the cost of higher latency. Use for user-facing search; skip for high-throughput batch.
5. The rank fusion is a learned model — Snowflake combines BM25 score, vector similarity, and filter match into a single hybrid score. You don't tune the weights; Snowflake does.

Output.

id	title
a1	"Refund timeline"
a3	"Refund eligibility"
a4	"Cancellation flow"

Rule of thumb. Use hybrid search (Cortex Search Service) for anything user-facing. Use plain vector similarity for internal analyses where you control the corpus and the query phrasing. The hybrid endpoint is the production-grade default.

Senior interview question on choosing the retrieval stack

A senior interviewer might ask: "Walk me through how you'd build retrieval for a customer-support copilot on top of Snowflake. The corpus is 500K knowledge-base articles plus a stream of new support tickets. Latency target: 200ms p99. How do you architect this?"

Solution Using Cortex Search Service + change-tracked source + a thin RAG wrapper

-- 1) Source table is change-tracked so the index refreshes incrementally
ALTER TABLE kb_articles SET CHANGE_TRACKING = TRUE;
ALTER TABLE support_tickets SET CHANGE_TRACKING = TRUE;

-- 2) Create the search service over both sources (union view)
CREATE OR REPLACE VIEW retrieval_source AS
SELECT id, 'kb' AS source_type, title || ' ' || body AS text,
       product, status, updated_at
FROM kb_articles
UNION ALL
SELECT id, 'ticket' AS source_type, body AS text,
       product, status, updated_at
FROM support_tickets
WHERE status = 'resolved';

CREATE OR REPLACE CORTEX SEARCH SERVICE copilot_retrieval
ON text
ATTRIBUTES product, status, source_type, updated_at
WAREHOUSE = retrieval_wh
TARGET_LAG = '2 minutes'
AS SELECT * FROM retrieval_source;

-- 3) RAG wrapper — retrieve + COMPLETE in one statement
CREATE OR REPLACE FUNCTION answer_question(question STRING, prod STRING)
RETURNS STRING
LANGUAGE SQL
AS $$
    WITH ctx AS (
        SELECT SNOWFLAKE.CORTEX.SEARCH_PREVIEW(
            'copilot_retrieval',
            OBJECT_CONSTRUCT(
                'query', question,
                'columns', ARRAY_CONSTRUCT('text'),
                'filter', OBJECT_CONSTRUCT('@eq',
                    OBJECT_CONSTRUCT('product', prod)),
                'limit', 5)) AS results
    )
    SELECT SNOWFLAKE.CORTEX.COMPLETE('llama3-70b',
        CONCAT(
            'Use ONLY this context. Cite source ids.\nContext: ',
            results::STRING,
            '\nQuestion: ', question))
    FROM ctx
$$;

Step-by-step trace.

Step	Action	Why
1	Enable change tracking on sources	Required for incremental refresh; without it, full rebuild every cycle
2	Union view as the search source	One service handles both KB and resolved tickets
3	TARGET_LAG = '2 minutes'	Trade-off — tighter = more compute; 2 min is reasonable for copilot UX
4	Filterable attributes declared up front	Adding later means full rebuild
5	answer_question UDF wraps retrieval + COMPLETE	One SQL call from the application layer
6	llama3-70b for synthesis	Better instruction-following than 8B for "cite sources"

The copilot application calls SELECT answer_question('how do I cancel?', 'billing') — one round trip, one billing line, no Python service in between. Latency target: ~150-250ms for the retrieval, ~500-1500ms for the COMPLETE — well within copilot UX expectations for the first token, and full response under 2s.

Output:

Component	Choice	Justification
Index	Cortex Search Service (hybrid)	BM25 catches exact terms; vector catches semantic matches
Refresh	Incremental, 2min lag	Right cost/freshness for copilot use
Generation	COMPLETE(llama3-70b)	Instruction-following + citation quality
Wrapper	SQL UDF	No Python service; copilot calls one SQL
Filter discipline	product attribute used at query time	Prevents cross-product leakage in answers

Why this works — concept by concept:

• Change tracking is the freshness foundation — without it, the search service must rebuild from scratch on every refresh cycle, which is cost-prohibitive at 500K-row scale. With it, only changed rows re-embed.
• Hybrid index handles the keyword + semantic problem — pure vector search misses error codes and product names; pure BM25 misses paraphrases. Hybrid gets both for free.
• SQL UDF wrapper kills the Python service — the copilot calls one SQL function; retrieval + generation + citation happen in one Snowflake round trip. No micro-service to operate.
• Filter pushdown — declaring product as an attribute lets the filter run before the rank fusion. A 500K-row index restricted to one product becomes a 50K-effective candidate set; massive latency improvement.
• Cost — embedding cost is O(rows × refresh frequency); generation cost is O(queries × prompt tokens). The right architecture pays the embedding cost once and the generation cost only per user query.

SQL
Topic — sql
Vector search SQL problems

Practice →

Joins Topic — joins Embedding join problems

Practice →

---

4. Cortex Agents + Document AI

cortex agents are tool-calling orchestrators on top of Cortex Analyst, vector search, and COMPLETE — Document AI parses PDFs into structured rows

The mental model in one line: Cortex Agents are a managed orchestration tier that takes a natural-language question, picks a tool from a registry (Cortex Analyst for SQL, Cortex Search Service for retrieval, custom SQL functions for actions), executes it, feeds the result back to the LLM, and iterates until it can answer — and Document AI is a parallel capability that turns PDFs and images into structured rows that the agent (and any SQL) can SELECT from. Once you say "agent = LLM + tool registry; Document AI = unstructured → structured," every "build me an in-warehouse copilot" interview question becomes a deduction from those two pieces.

Cortex Agents — the orchestration tier.

• An Agent is a Snowflake-managed object configured with: a set of tools (named callable resources), an LLM model (which picks tools and synthesises answers), and an optional system prompt (high-level guidance).
• A user submits a question. The agent runtime sends the question + tool definitions to the LLM, which returns either a final answer or a tool_use request (call tool X with arguments Y).
• The agent runtime executes the requested tool, feeds the result back to the LLM, and loops until the LLM emits a final answer.
• This is the standard ReAct (reason + act) pattern, managed by Snowflake — you don't write the loop, you just register the tools.

The three first-class tool types.

• Cortex Analyst — governed text-to-SQL over a semantic model. The agent can ask Analyst "show me revenue by region last quarter," Analyst translates that into a SQL query against the semantic model, runs it, returns the result.
• Cortex Search Service — the agent can search a knowledge corpus. "What is our policy on refunds older than 90 days?" → the agent calls Search → gets the top-K passages → uses them in the answer.
• Custom SQL functions / stored procedures — any UDF or sproc in your account can be registered as a tool. Want the agent to be able to create a JIRA ticket? Wrap the JIRA API in a UDF and register it.

Document AI — unstructured to structured.

• A DOCUMENT AI MODEL is a Snowflake object trained (or zero-shot) on a document layout. Common use cases: invoices, receipts, claim forms, contracts.
• You define questions like "What is the invoice number? What is the total? What is the line-item count?" and the model extracts the answers from each PDF/image.
• The output is a structured row per document — you can SELECT invoice_number, total, line_item_count FROM (CALL document_ai_model.EXTRACT(file_uri)).
• Snowflake handles the OCR + layout parsing + LLM extraction in one managed call.

Cortex Analyst — governed text-to-SQL.

• Analyst is the bridge between natural language and SQL on top of a semantic model (a YAML file declaring tables, columns, relationships, metric definitions).
• The semantic model is the governance layer: it defines what tables exist, what columns are allowed, what business metrics mean ("monthly active users = unique user_id in current month").
• A user asks "what is MAU?", Analyst translates → SQL using the semantic model → runs against Snowflake → returns numbers + the generated SQL for auditability.
• Compared to ungoverned text-to-SQL, the semantic model prevents the LLM from inventing columns or misnaming metrics.

The "no GPU ops" promise vs limits.

• Cortex never asks you to host a model, manage a GPU, or pay for an inference endpoint. The credit meter is the entire ops surface.
• The limits are real: model catalogue is fixed (no arbitrary HuggingFace weights), fine-tuning is limited (only Snowflake-supplied fine-tuning paths), token throughput is rate-limited per account/region.
• For frontier-model fine-tuning or arbitrary research models, you still need an external stack. Cortex covers the "production LLM in SQL" surface, not the "research lab" surface.

Common interview probes on Agents + Document AI.

• "How does a Cortex Agent decide which tool to call?" — the LLM picks based on tool descriptions + the question. You write good tool descriptions; the LLM does the routing.
• "How is Cortex Analyst different from a generic text-to-SQL chatbot?" — semantic model layer. Without it, the LLM can hallucinate columns; with it, governance is enforced.
• "Why use Document AI instead of an external OCR + LLM pipeline?" — single managed object, billed in credits, output is a SELECT-able row, governance carries through.
• "When would you NOT use a Cortex Agent?" — when you need fine-grained control over the loop, custom retry logic, or non-Snowflake tools beyond what UDFs can wrap.

Worked example — extracting line items from invoices with Document AI

Detailed explanation. A common back-office task: scan invoices arriving as PDFs in a stage, extract vendor, invoice_date, total, and the line-item list. Document AI turns this into a single managed call.

Question. Build a Document AI pipeline that ingests PDFs from @invoice_stage and produces a structured invoices(vendor, invoice_date, total, line_items) table.

Input.

stage_path	description
@invoice_stage/inv_001.pdf	Acme Supplies — $1,247.00, 5 line items
@invoice_stage/inv_002.pdf	Globex Inc — $89.50, 2 line items
@invoice_stage/inv_003.pdf	Initech — $3,400.00, 12 line items

Code.

-- 1) Build the Document AI model definition (one-time)
CREATE OR REPLACE DOCUMENT_AI_MODEL invoice_extractor
TYPE = 'predefined'
QUESTIONS = (
    OBJECT_CONSTRUCT('name', 'vendor',
        'question', 'What is the vendor company name?'),
    OBJECT_CONSTRUCT('name', 'invoice_date',
        'question', 'What is the invoice date in YYYY-MM-DD?'),
    OBJECT_CONSTRUCT('name', 'total',
        'question', 'What is the total amount?'),
    OBJECT_CONSTRUCT('name', 'line_items',
        'question', 'List each line item as JSON: description, qty, unit_price.')
);

-- 2) Extract structured rows from staged PDFs
INSERT INTO invoices (file_path, vendor, invoice_date, total, line_items)
SELECT
    file_path,
    extracted:vendor::STRING,
    TO_DATE(extracted:invoice_date::STRING, 'YYYY-MM-DD'),
    extracted:total::NUMBER(12, 2),
    extracted:line_items
FROM (
    SELECT
        f.relative_path AS file_path,
        invoice_extractor!EXTRACT(
            BUILD_SCOPED_FILE_URL(@invoice_stage, f.relative_path)
        ) AS extracted
    FROM DIRECTORY(@invoice_stage) f
    WHERE f.relative_path ILIKE '%.pdf'
);

Step-by-step explanation.

1. CREATE DOCUMENT_AI_MODEL declares the extraction schema. Each "question" becomes an output field; the model is trained to answer each question from the document.
2. The questions read like natural language because the underlying model is a vision-language LLM trained on document layouts — it understands "what is the total" without needing labelled training data.
3. DIRECTORY(@invoice_stage) lists the files on the stage; invoice_extractor!EXTRACT(file_url) runs the extraction on each file. The result is a VARIANT with one field per question.
4. The downstream INSERT casts each extracted field into the typed column. TO_DATE enforces date parsing; NUMBER(12, 2) enforces money parsing.
5. The whole pipeline is one INSERT statement. Scaling means scheduling a task on the directory — incremental processing of newly-arrived files is a one-liner with STREAM ON DIRECTORY.

Output.

file_path	vendor	invoice_date	total	line_items
inv_001.pdf	Acme Supplies	2026-04-12	1247.00	[{...}, ...]
inv_002.pdf	Globex Inc	2026-04-15	89.50	[{...}, {...}]
inv_003.pdf	Initech	2026-04-20	3400.00	[{...}, ...]

Rule of thumb. For document-extraction workloads, Document AI is dramatically simpler than building OCR + LLM + parsing yourself. Even a small team can ship a useful invoice-extraction pipeline in a day.

Worked example — a Cortex Agent that joins structured + unstructured

Detailed explanation. The flagship Agents use case: a single question that requires both a SQL aggregation (revenue by region) AND a knowledge-base lookup (refund policy). The agent routes the question to two tools, gets both answers, and synthesises a unified response. No Python orchestrator required.

Question. Build a Cortex Agent with two tools: Cortex Analyst over a sales semantic model and Cortex Search Service over the KB. Ask "What was Q1 EMEA revenue, and what is our refund policy on those orders?"

Input — tool registry.

Tool	Type	Purpose
sales_analyst	Cortex Analyst	Query revenue from sales semantic model
kb_search	Cortex Search Service	Look up policies in the knowledge base

Code.

# semantic_model.yaml — Cortex Analyst input
name: sales
tables:
  - name: orders
    base_table:
      database: prod
      schema: sales
      table: orders
    dimensions:
      - name: region
        expr: region
      - name: order_date
        expr: order_date
    measures:
      - name: revenue
        expr: amount
        agg: sum

-- 1) Register the agent with two tools
CREATE OR REPLACE CORTEX AGENT support_copilot
WITH (
    MODEL = 'claude-3-5-sonnet',
    INSTRUCTIONS = 'You are a finance copilot. Use sales_analyst for numeric questions
                    and kb_search for policy questions.',
    TOOLS = (
        OBJECT_CONSTRUCT(
            'name', 'sales_analyst',
            'type', 'cortex_analyst',
            'semantic_model', '@semantic_models/sales.yaml',
            'description', 'Answer numeric sales/revenue questions.'),
        OBJECT_CONSTRUCT(
            'name', 'kb_search',
            'type', 'cortex_search',
            'service', 'kb_search',
            'description', 'Look up policies and procedures.')
    )
);

-- 2) Invoke the agent
SELECT SNOWFLAKE.CORTEX.AGENT_INVOKE(
    'support_copilot',
    'What was Q1 2026 EMEA revenue, and what is our refund policy for those orders?'
);

Step-by-step explanation.

1. The agent definition lists two tools, each with a description. The LLM uses descriptions to pick the right tool for each step of the question.
2. On invocation, the LLM sees the question and decides: "this has two parts — a revenue number AND a policy lookup." It emits two tool_use events: one for sales_analyst, one for kb_search.
3. The runtime executes sales_analyst first — Analyst translates "Q1 2026 EMEA revenue" → SQL using the semantic model → returns revenue=$2.4M.
4. The runtime executes kb_search next — finds the top KB passages on refund policy. Returns the most relevant passage.
5. The LLM synthesises both results into a unified answer: "Q1 2026 EMEA revenue was $2.4M. Our refund policy on those orders is X." The whole loop is managed by Snowflake; you wrote no Python.

Output.

field	value
answer	"Q1 2026 EMEA revenue was $2.4M (from sales_analyst). Per our refund policy (KB article a17), refunds on orders older than 90 days require manager approval."
tool_calls	[{tool: sales_analyst, sql: SELECT SUM(amount)... }, {tool: kb_search, hits: [a17]}]
citations	["sales_analyst::query_18472", "kb_search::a17"]

Rule of thumb. When a single user question spans both numeric and policy/textual data, a Cortex Agent is far simpler than orchestrating the two tool calls in Python. Use Agents for any copilot-shaped use case on Snowflake data.

Worked example — Cortex Analyst on a governed semantic model

Detailed explanation. A common probe: "how do you let business users ask SQL questions in English without giving them a text-to-SQL chatbot that hallucinates columns?" The Cortex Analyst answer: a semantic model declares what tables, columns, and metrics exist; Analyst's text-to-SQL is constrained to that model.

Question. Build a semantic model on a sales(region, amount, order_date) table, then ask Analyst "what is monthly revenue trend for EMEA in 2026?"

Input.

region	amount	order_date
EMEA	1200	2026-01-15
EMEA	800	2026-02-04
NA	1500	2026-01-09
EMEA	900	2026-03-12

Code.

# sales_semantic.yaml
name: sales_v1
description: Revenue by region and month
tables:
  - name: orders
    base_table:
      database: prod
      schema: sales
      table: orders
    dimensions:
      - name: region
        expr: region
        synonyms: [territory, geography]
      - name: order_month
        expr: DATE_TRUNC('month', order_date)
        synonyms: [month]
    measures:
      - name: revenue
        expr: amount
        agg: sum
        synonyms: [sales, total]
verified_queries:
  - name: monthly_revenue_emea
    question: monthly revenue trend for EMEA
    sql: |
      SELECT DATE_TRUNC('month', order_date) AS order_month,
             SUM(amount) AS revenue
      FROM prod.sales.orders
      WHERE region = 'EMEA'
      GROUP BY 1 ORDER BY 1;

-- Ask Analyst via the REST API or SQL function
SELECT SNOWFLAKE.CORTEX.ANALYST_MESSAGE(
    'sales_semantic',
    'monthly revenue trend for EMEA in 2026'
);

Step-by-step explanation.

1. The YAML declares the tables, dimensions (groupable axes), measures (aggregatable values), and verified_queries (gold-standard examples the model learns from). Synonyms map natural-language aliases to dimensions.
2. When a user asks "monthly revenue trend for EMEA," Analyst matches the question to the closest verified query and to the dimension/measure vocabulary, then generates SQL constrained to those.
3. The generated SQL is returned alongside the answer — the user (and audit log) sees exactly which SQL ran. This is the governance + auditability win over an unconstrained text-to-SQL chatbot.
4. If the question references a column not in the semantic model, Analyst rejects it ("I don't have a metric for X") instead of hallucinating a column. This is the safety story.
5. The semantic model is version-controlled in a stage; updates roll out by editing the YAML and re-registering — no model retraining required.

Output.

order_month	revenue
2026-01-01	1200
2026-02-01	800
2026-03-01	900

Rule of thumb. Build a semantic model for every business domain you want to expose to LLM-driven question answering. The YAML is small; the upside (governed text-to-SQL that doesn't hallucinate) is massive.

Senior interview question on Agents + Document AI

A senior interviewer might ask: "Design a Cortex-powered claim-processing pipeline. Inbound: PDF claim forms. Output: an Agent that can answer questions like 'show me all pending dental claims over $500 and the relevant policy clauses.' Walk me through the architecture."

Solution Using Document AI → structured table → Agent over Analyst + Search

-- 1) Document AI extracts structured rows from PDF claims
CREATE OR REPLACE DOCUMENT_AI_MODEL claim_extractor
QUESTIONS = (
    OBJECT_CONSTRUCT('name', 'claim_id', 'question', 'What is the claim number?'),
    OBJECT_CONSTRUCT('name', 'patient_name', 'question', 'Who is the patient?'),
    OBJECT_CONSTRUCT('name', 'amount', 'question', 'What is the claim amount in USD?'),
    OBJECT_CONSTRUCT('name', 'service_type', 'question', 'What service type (dental, medical, vision)?'),
    OBJECT_CONSTRUCT('name', 'date_of_service', 'question', 'Date of service in YYYY-MM-DD'),
    OBJECT_CONSTRUCT('name', 'status', 'question', 'Status (pending, approved, denied)')
);

CREATE OR REPLACE TASK extract_claims
WAREHOUSE = etl_wh
SCHEDULE = '5 minutes'
AS
INSERT INTO claims (claim_id, patient_name, amount, service_type, date_of_service, status)
SELECT
    e:claim_id::STRING,
    e:patient_name::STRING,
    e:amount::NUMBER(10,2),
    e:service_type::STRING,
    TO_DATE(e:date_of_service::STRING, 'YYYY-MM-DD'),
    e:status::STRING
FROM (SELECT claim_extractor!EXTRACT(BUILD_SCOPED_FILE_URL(@claims, f.relative_path)) AS e
      FROM DIRECTORY(@claims) f
      WHERE f.relative_path NOT IN (SELECT file_path FROM claims));

-- 2) Semantic model on the structured claims table
-- (semantic_model_claims.yaml — service_type dim, amount measure, status dim)

-- 3) Cortex Search Service on the policy KB
CREATE OR REPLACE CORTEX SEARCH SERVICE policy_search
ON body ATTRIBUTES policy_type
WAREHOUSE = retrieval_wh TARGET_LAG = '10 minutes'
AS SELECT * FROM policy_kb;

-- 4) Agent that wires it all together
CREATE OR REPLACE CORTEX AGENT claims_copilot
WITH (
    MODEL = 'claude-3-5-sonnet',
    INSTRUCTIONS = 'Answer questions about claims and policy. Use claims_analyst for numbers,
                    policy_search for clauses. Always cite policy clause ids.',
    TOOLS = (
        OBJECT_CONSTRUCT('name', 'claims_analyst', 'type', 'cortex_analyst',
                          'semantic_model', '@semantic_models/claims.yaml'),
        OBJECT_CONSTRUCT('name', 'policy_search', 'type', 'cortex_search',
                          'service', 'policy_search')
    )
);

Step-by-step trace.

Layer	Component	Cost driver
1 — Ingest	Stage + DIRECTORY	Storage
2 — Extract	Document AI model	Tokens per page × pages per claim
3 — Structured	claims table	Standard Snowflake storage
4 — Search	Cortex Search Service on policies	Embedding cost + index storage
5 — Orchestrate	Cortex Agent	LLM tokens × tool-call iterations
6 — Schedule	TASK every 5 minutes	Warehouse credits

The pipeline lets a claims adjuster ask "show me pending dental claims over $500 and the policy on cosmetic dental coverage" and get one synthesised answer with numbers + clause citations.

Output:

Component	Choice	Why
Document parsing	Document AI	One managed call replaces OCR + LLM pipeline
Structured store	Native claims table	Joins, masking policies, audit
Policy retrieval	Cortex Search Service	Hybrid keyword + semantic match on legal text
Orchestration	Cortex Agent	LLM picks tool per question, no Python
Scheduling	TASK	Native Snowflake — no Airflow
Governance	Snowflake RBAC + masking	Every layer inherits

Why this works — concept by concept:

• Document AI is the unstructured boundary — PDFs and images are the messy edge of every real workflow. Document AI converts them into rows with a single managed call; the rest of the pipeline is plain SQL.
• Semantic model is the governance boundary — exposing claim data to an LLM without a semantic model is asking for column hallucinations. The YAML is small; the safety win is large.
• Cortex Search Service handles legal text — policy clauses need keyword + semantic match. Hybrid is the right default; a pure vector store would miss exact clause IDs.
• Agent eliminates the orchestrator — the LLM picks which tool to call. Without an Agent, you'd write a Python service that classifies the question and routes it; with an Agent, you write a tool description and Snowflake does the routing.
• Cost — Document AI cost is O(pages); Search Service cost is O(rows × refresh frequency); Agent cost is O(queries × tool-call iterations). Architect each layer to its cost driver.

ETL
Topic — etl
Document AI ETL pipeline problems

Practice →

SQL Topic — sql Cortex Agent SQL problems

Practice →

---

5. Picking Cortex vs external LLM stack

snowflake cortex wins on governance + data-residency; external LLMs win on frontier-model quality + fine-tuning; hybrid is the production pattern

The mental model in one line: pick Cortex when the data must stay in Snowflake and the cost/governance bundle dominates; pick external (OpenAI / Anthropic / Bedrock) when frontier-model quality or custom fine-tuning is the deciding axis; pick hybrid when both pressures are real — Cortex for in-warehouse batch and governed calls, external for user-facing tier-1 chat. Once you frame the decision as "which axis dominates?", the engine pick becomes a deduction from the workload, not a religious-war decision.

Cortex wins on...

• Data residency. Data never leaves Snowflake. No DPA negotiations with a third-party LLM vendor, no egress, no audit-trail re-implementation.
• Governance. Cortex calls inherit Snowflake's row-access policies, masking policies, audit log, RBAC. Every other axis "just works."
• All-in cost on Snowflake-resident data. When the data is already in Snowflake, the bundled token + infra + governance + audit cost usually wins against external API + Python service + DPA + masking.
• Operational simplicity. Zero new infrastructure. The SRE team operates Snowflake; they now operate the LLM by extension.

External LLMs win on...

• Frontier-model quality. Cortex's model catalogue is a curated subset. If you need the latest GPT-5 or Claude Opus, you go external.
• Fine-tuning. Cortex offers limited fine-tuning; for custom-domain LoRA or full fine-tune, external infrastructure (Together / Fireworks / your own GPU cluster) is required.
• Lower per-token cost at scale on commodity workloads. For non-regulated bulk summarisation, an external API can be cheaper per token than Cortex — but the all-in cost math often inverts once you add egress + governance.
• Multi-cloud reach. If half your data is in BigQuery or Redshift, Cortex only solves half the problem.

Hybrid — the production-grade pattern.

• Use Cortex for in-warehouse batch jobs (nightly summarisation, daily classification, embedding refresh) where data residency matters and quality is met by the hosted models.
• Use external LLMs for the user-facing tier-1 chat where every percentage of quality improvement compounds the customer experience.
• Use Cortex Search Service as the shared retrieval layer for both. Embeddings live in Snowflake; both Cortex COMPLETE and an external API can pull retrieved context from the same managed index.

Compliance + sovereignty axes.

• HIPAA-bound healthcare data. Cortex is usually the path of least resistance — Snowflake supports HIPAA-eligible workloads in the right edition; adding an external vendor multiplies DPA complexity.
• PCI cardholder data. Same logic — keeping the data inside the boundary is the architecturally simpler answer.
• Public-sector data residency requirements (Germany, Australia, etc.). Cortex offers regional model deployment matching Snowflake regions; external vendors may or may not have a matching region.
• EU AI Act / GDPR-style classification. Cortex's audit trail makes Article 22 (automated decision-making) compliance easier — every LLM call is logged with model, tokens, and input.

Senior interview signals — what they listen for.

• Do you mention "data residency is the deciding axis for regulated workloads" unprompted? — senior signal.
• Do you describe all-in cost (not per-token cost)? — required answer.
• Do you call out the hybrid pattern as the production default? — senior signal.
• Do you push back on "just use OpenAI for everything" with the governance argument? — senior signal.

Worked example — when Cortex wins on a regulated workload

Detailed explanation. A common probe: "you're at a healthcare-data company. You need to summarise 10M anonymised clinical notes into structured CDA-format JSON daily. HIPAA in play. Walk me through the engine choice." The answer collapses to one axis: data residency. Cortex is the path that doesn't ship PHI out of Snowflake.

Question. Build a daily pipeline that summarises 10M clinical notes from a HIPAA-bound Snowflake table into structured JSON. Explain why Cortex is the obvious winner.

Input.

Constraint	Value
Volume	10M notes/day
Compliance	HIPAA
Data location	Snowflake (PHI-tagged columns)
Output	Structured CDA-style JSON

Code.

-- Cortex path — never leaves Snowflake
CREATE OR REPLACE TASK summarise_notes
WAREHOUSE = clinical_wh
SCHEDULE = 'USING CRON 0 2 * * * UTC'
AS
INSERT INTO note_summaries (note_id, summary_json)
SELECT
    note_id,
    TRY_PARSE_JSON(
      SNOWFLAKE.CORTEX.COMPLETE('llama3-70b',
        ARRAY_CONSTRUCT(
          OBJECT_CONSTRUCT('role', 'system',
            'content', 'Summarise this clinical note as CDA JSON with these fields: ...'),
          OBJECT_CONSTRUCT('role', 'user',
            'content', body)),
        OBJECT_CONSTRUCT('temperature', 0,
                          'response_format', OBJECT_CONSTRUCT('type', 'json_object'))))
FROM clinical_notes
WHERE created_at >= DATEADD('day', -1, CURRENT_TIMESTAMP())
  AND note_id NOT IN (SELECT note_id FROM note_summaries);

Step-by-step explanation.

1. The pipeline is a single TASK + INSERT. No Python service, no API gateway, no DPA with an external vendor.
2. The PHI never leaves Snowflake. The Cortex call runs inside the Snowflake-managed cloud account; row-access policies and masking apply at every step.
3. Audit trail: every Cortex call is logged in CORTEX_FUNCTIONS_USAGE_HISTORY with token counts and user. Combine with ACCESS_HISTORY for end-to-end PHI access tracking.
4. Cost: at 10M notes × ~600 tokens average × llama3-70b rate, the daily Cortex bill lands at single-digit-thousands of dollars. External path adds egress + DPA + masking pipeline overhead that is hard to defend.
5. The TASK schedule runs at 02:00 UTC daily. Idempotent INSERT (NOT IN) makes retries safe; ALERT on a failure metric routes to PagerDuty.

Output (cost + risk comparison).

Axis	Cortex	External LLM
Data residency	inside Snowflake	leaves Snowflake (egress)
HIPAA DPA	covered by Snowflake	new vendor DPA
Masking	inherits row policies	must re-implement
Audit trail	CORTEX_FUNCTIONS_USAGE_HISTORY	custom
Daily $ at 10M rows	$X (in credits)	$X − egress + Python ops + DPA spend
Engineering time to ship	days	weeks

Rule of thumb. For any regulated workload that originates in Snowflake, default to Cortex. Only escalate to external if a specific quality or model-feature gap can't be closed otherwise.

Worked example — when external LLM wins on frontier-model quality

Detailed explanation. The flip side: a research-grade workload where the deciding axis is model quality, not residency. Example: "write the customer-facing release notes for our enterprise product launch." This is a low-volume, high-stakes, non-regulated workload where a Claude Opus or GPT-5-class model produces visibly better output than the hosted Cortex catalogue.

Question. Compare Cortex and an external API for a low-volume / high-quality copywriting workload. Show why external can win.

Input.

Constraint	Value
Volume	~200 generations/month
Compliance	none (public-facing copy)
Quality bar	"sound like our brand" — high
Latency	not critical

Code.

# External path — frontier model + custom system prompt
import anthropic
client = anthropic.Anthropic(api_key=os.environ["ANTHROPIC_API_KEY"])

def generate_release_note(feature_spec: str, brand_voice: str) -> str:
    resp = client.messages.create(
        model="claude-3-opus-20240229",   # or latest frontier
        max_tokens=1024,
        system=brand_voice,                # 2-3K-token brand voice doc
        messages=[{"role": "user",
                   "content": f"Write a customer-facing release note for: {feature_spec}"}])
    return resp.content[0].text

Step-by-step explanation.

1. The volume is low (200/month). Per-token cost difference is rounding error; what matters is quality.
2. The frontier model produces output that's noticeably closer to the brand voice — fewer revisions, lower marketing-team friction.
3. There's no PHI / PCI / regulated data; the DPA cost is low, the egress is negligible.
4. Cortex's catalogue does include high-quality models (Claude Sonnet via partner, Mistral Large), but for the absolute top of the quality ladder, an external API to the latest frontier model is often a real differentiator.
5. The honest answer: this workload could ship on either stack; the team picks external if quality has measurably improved on the frontier model, Cortex if they want one fewer vendor to manage.

Output (decision matrix).

Axis	Cortex	External	Winner
Quality on copywriting	high (Sonnet, Mistral Large)	very high (Opus, GPT-5)	external by a margin
All-in cost at 200 calls/month	tens of $	hundreds of $	Cortex
Governance overhead	zero	low (no regulated data)	tie
Quality-driven business value	medium	high	external
Verdict	viable	preferred	external

Rule of thumb. Use Cortex for the 80% of LLM workloads where quality is met by the hosted catalogue and governance matters. Reach for an external API for the 20% where frontier-model quality is the deciding business axis.

Worked example — the hybrid pattern in production

Detailed explanation. Most mature shops end up in a hybrid pattern: Cortex for the big batch jobs and any regulated workload, external for the user-facing copilot where every quality point compounds. The trick is making them share the retrieval layer.

Question. Architect a hybrid stack where a daily batch job runs on Cortex and a user-facing copilot runs on an external LLM, but both retrieve from the same Cortex Search Service.

Input.

Surface	Volume	Sensitivity	LLM
Nightly summarisation	5M notes	medium	Cortex llama3-70b
User-facing copilot	100 QPS	low	External frontier
Knowledge corpus	500K docs	medium	shared

Code.

-- Shared retrieval layer — Cortex Search Service
CREATE OR REPLACE CORTEX SEARCH SERVICE shared_kb
ON body ATTRIBUTES product, status
WAREHOUSE = retrieval_wh TARGET_LAG = '5 minutes'
AS SELECT id, title, body, product, status FROM kb_articles;

-- Batch path — Cortex COMPLETE pulls from shared_kb
CREATE OR REPLACE TASK summarise_with_context
WAREHOUSE = batch_wh SCHEDULE = '1 day'
AS
INSERT INTO daily_summaries (note_id, summary)
SELECT note_id,
       SNOWFLAKE.CORTEX.COMPLETE('llama3-70b',
         CONCAT(
           'Summarise with context: ',
           SNOWFLAKE.CORTEX.SEARCH_PREVIEW('shared_kb',
             OBJECT_CONSTRUCT('query', body, 'limit', 3))::STRING,
           '\nNote: ', body))
FROM notes
WHERE date(created_at) = CURRENT_DATE() - 1;

# User-facing path — external LLM, pulls from same shared_kb via Snowflake REST
def copilot_answer(question: str) -> str:
    # 1) Retrieve via Snowflake REST to shared_kb
    hits = snowflake_rest.search("shared_kb", question, limit=5)
    # 2) Frontier LLM completes with retrieved context
    resp = anthropic_client.messages.create(
        model="claude-3-opus-20240229",
        messages=[{"role": "user",
                   "content": f"Context: {hits}\n\nQuestion: {question}"}])
    return resp.content[0].text

Step-by-step explanation.

1. The shared shared_kb search service is the single source of truth for retrieval. Both pipelines query it; the embeddings are computed and refreshed once.
2. The batch pipeline stays entirely in Snowflake — TASK + INSERT + Cortex. Cost lands in credits; data never moves.
3. The user-facing pipeline is a thin Python service that calls Snowflake's Cortex Search REST endpoint for retrieval and an external LLM for generation. The retrieved context is the only data that leaves Snowflake — and you can mask it before egress if needed.
4. The two paths share embeddings, retrieval cost, and governance. They differ only at the generation step.
5. If the team later decides to move the copilot to Cortex (e.g. when a Cortex model catches up on quality), only the generation call changes — retrieval doesn't move.

Output (architecture summary).

Layer	Component	Implementation
Storage	kb_articles, notes tables	Snowflake
Retrieval	shared_kb Cortex Search Service	Snowflake-managed hybrid index
Batch generation	Cortex COMPLETE (llama3-70b)	TASK + INSERT
User-facing generation	External frontier model	Python service
Governance	Snowflake RBAC + masking + AUDIT	applies to both paths

Rule of thumb. Build the retrieval layer in Snowflake (Cortex Search Service) even if the user-facing generation is external. Sharing retrieval halves the embedding cost and centralises the governance — both paths benefit.

Senior interview question on engine selection in 2026

A senior interviewer might frame this as: "Walk me through how you'd pick between Cortex, an external LLM stack, and a hybrid setup for a new AI initiative. What questions do you ask, in what order, and what would push you to each option?"

Solution Using the 5-question framework + stack-specific trade-offs

Engine selection — Cortex vs external vs hybrid
================================================

Q1 Where does the data live?
   - All in Snowflake → Cortex eligible
   - Multi-cloud / on-prem → external or hybrid

Q2 What is the governance / compliance bar?
   - HIPAA / PCI / sovereignty → Cortex strongly preferred
   - Public, non-regulated     → external viable

Q3 What is the freshness need?
   - Frozen model OK   → either
   - Real-time facts   → both need RAG; Cortex Search makes this trivial

Q4 What is the quality bar?
   - Met by Cortex catalogue (Llama-3 70B, Mistral Large, Claude Sonnet) → Cortex
   - Needs absolute frontier (Opus / GPT-5)                              → external for that path

Q5 What is the cost shape?
   - High volume + Snowflake-resident       → Cortex (all-in cost wins)
   - Low volume + frontier quality          → external
   - Mixed                                  → hybrid

Stack-specific trade-offs
-------------------------
Cortex
  + Data stays in, governed, audited
  + One vendor, one bill, one ops surface
  + RAG primitives are SQL (EMBED + SEARCH SERVICE)
  − Model catalogue is curated, not arbitrary
  − Fine-tuning is limited
  − Per-token sticker price sometimes higher

External (OpenAI / Anthropic / Bedrock)
  + Frontier-model access
  + Fine-tuning paths and adapters
  + Multi-cloud reach
  − DPA negotiation per vendor
  − Egress + Python service overhead
  − Governance must be re-implemented

Hybrid
  + Cortex for governed batch, external for user-facing chat
  + Shared retrieval via Cortex Search Service
  + Easy migration path either direction
  − Two stacks to operate
  − More cost surfaces to monitor

Step-by-step trace.

Use case	Q1 location	Q2 governance	Q3 freshness	Q4 quality	Q5 cost	Pick
Daily 10M clinical note summarisation	Snowflake	HIPAA	frozen OK	met by 70B	high volume	Cortex
Tier-1 user-facing chat copilot	Snowflake	low	real-time (RAG)	needs frontier	medium volume	hybrid (Cortex retrieve + external generate)
Internal Slack-bot for engineering	Snowflake	low	RAG	met	low volume	Cortex
Public-website "ask our docs" widget	Snowflake	low	RAG	frontier preferred	high QPS	hybrid
Multi-cloud product analytics chat	BigQuery + Snowflake	low	RAG	frontier	high QPS	external (with external vector DB)

Five answers, five different verdicts. The framework doesn't preordain a winner — it surfaces which axis dominates for each workload.

Output:

Verdict	Reasoning
Cortex when data residency + governance dominate	The all-in cost math + zero new vendor wins
External when frontier quality dominates	The quality delta on tier-1 surfaces is worth the ops cost
Hybrid when both pressures are real	Cortex for batch + governance, external for user-facing — same retrieval layer

Why this works — concept by concept:

• Data residency is the gating axis — Cortex is only viable if the data is in Snowflake. Multi-cloud workloads start tilted toward external or hybrid.
• Governance is the cost-multiplier axis — for regulated data, the cost of running external (DPAs, masking, audit re-implementation) often dwarfs the per-token savings. Cortex's bundled governance is a real economic advantage.
• Freshness is solved by RAG, not by engine choice. Cortex's EMBED_TEXT_* + SEARCH SERVICE makes RAG implementable without leaving SQL — that's the differentiator vs external stacks where you assemble pgvector + Python.
• Quality bar is set per surface, not per company — a tier-1 user-facing surface has a different quality bar than a nightly batch. Hybrid lets you serve both correctly.
• Cost — all-in cost (tokens + infra + governance + ops) is the right metric. Per-token sticker price is a trap. At meaningful scale, the all-in cost difference can be $100K-$1M/year in the wrong direction; the 5-question framework prevents that.

SQL
Topic — sql
Cortex decision-tree SQL problems

Practice →

ETL
Topic — etl
Hybrid LLM ETL problems

Practice →

---

Cheat sheet — Cortex recipes

• When Cortex is enough. Snowflake-resident data, governance matters, quality is met by llama3-70b / mistral-large / Claude Sonnet, batch or near-real-time. The 80% case for in-warehouse AI.
• When you need external LLM. Multi-cloud data, absolute frontier-model quality required (Opus / GPT-5-class), custom fine-tuning beyond Cortex's catalogue, tier-1 user-facing surfaces where every quality point compounds business value.
• When you need hybrid. Both pressures are real — Cortex for governed batch + retrieval layer, external LLM for user-facing chat. Shared CORTEX SEARCH SERVICE halves the embedding cost.
• 3-line CORTEX.COMPLETE call. SELECT SNOWFLAKE.CORTEX.COMPLETE('llama3-8b', 'Your prompt here') for ad-hoc; chat-message-array form with temperature=0 and response_format=json_object for structured-output production pipelines.
• Embed + cosine_similarity query. ALTER TABLE docs ADD COLUMN embedding VECTOR(FLOAT, 768) once, UPDATE docs SET embedding = SNOWFLAKE.CORTEX.EMBED_TEXT_768('snowflake-arctic-embed-m', text), then SELECT ..., VECTOR_COSINE_SIMILARITY(embedding, query_vec) AS score ORDER BY score DESC LIMIT K for ad-hoc retrieval.
• Cortex Search Service create. CREATE CORTEX SEARCH SERVICE my_search ON body ATTRIBUTES product, status WAREHOUSE = my_wh TARGET_LAG = '5 minutes' AS SELECT ... then SNOWFLAKE.CORTEX.SEARCH_PREVIEW('my_search', OBJECT_CONSTRUCT('query', ..., 'filter', ..., 'limit', K)) for hybrid retrieval.
• Cortex Agent skeleton. CREATE CORTEX AGENT my_copilot WITH (MODEL = '...', INSTRUCTIONS = '...', TOOLS = (...)) registers a tool-calling orchestrator; AGENT_INVOKE('my_copilot', question) runs the LLM + tool loop.
• Document AI extraction. CREATE DOCUMENT_AI_MODEL my_extractor QUESTIONS = (...) declares the output schema; my_extractor!EXTRACT(BUILD_SCOPED_FILE_URL(@stage, path)) returns a VARIANT with one field per question.
• Cortex Analyst on a semantic model. Define semantic_model.yaml with tables, dimensions, measures, verified queries; SNOWFLAKE.CORTEX.ANALYST_MESSAGE('model_name', 'question') returns SQL + answer constrained by the model.
• Cost meter. SELECT DATE_TRUNC('day', start_time), function_name, SUM(token_credits) FROM SNOWFLAKE.ACCOUNT_USAGE.CORTEX_FUNCTIONS_USAGE_HISTORY GROUP BY 1, 2 — run weekly; alert on 1.2x budget.
• Model picker rule. Start every workload on the cheapest model that might work (llama3-8b or mistral-7b). Sample 1K rows, human-evaluate, only escalate if quality fails the bar. Frontier models are rarely the right default.
• Idempotent batch. WHERE id NOT IN (SELECT id FROM target) makes retries cost-free. Cortex calls are not transactional with the SELECT; idempotency is your safety net.
• Refresh cadence. Cortex Search Service TARGET_LAG should match the freshness need — 1-5 min for copilots, 1 hour for analytics, 1 day for archival corpora. Tighter = more compute.
• Structured output guardrails. temperature=0 + response_format=json_object + TRY_PARSE_JSON + daily parse-failure SLI. The trio catches ~90% of LLM-output bugs before they reach downstream.
• Hybrid pattern. Build retrieval in Cortex even if generation is external. The shared CORTEX SEARCH SERVICE becomes the joinable layer between in-warehouse and external paths.

Frequently asked questions

What is Snowflake Cortex?

Snowflake Cortex is the in-warehouse AI capability set that turns LLM inference, embeddings, vector search, document parsing, and agentic orchestration into ordinary SQL functions billed in Snowflake credits. It bundles three tiers: low-level LLM functions (COMPLETE, EXTRACT_ANSWER, SUMMARIZE, TRANSLATE, SENTIMENT, CLASSIFY_TEXT), embedding + vector primitives (EMBED_TEXT_768, EMBED_TEXT_1024, VECTOR(FLOAT, N) type, VECTOR_COSINE_SIMILARITY and friends), and a managed orchestration tier (Cortex Search Service for hybrid BM25 + vector retrieval, Cortex Agents for tool-calling LLM sessions, Cortex Analyst for governed text-to-SQL over a semantic model, Document AI for PDF → structured rows). The key invariant: every call runs inside the customer's Snowflake-managed account, so data never leaves the warehouse — which is the deciding axis for any regulated or sovereignty-bound workload.

Which models are available in Cortex?

The Cortex model catalogue is curated, not arbitrary. As of 2026, the hosted catalogue spans Meta's Llama-3 family (8B, 70B, 405B), Mistral models (mistral-7b, mixtral-8x7b, mistral-large), Snowflake's own Arctic model (a Mixture-of-Experts model optimised for SQL and enterprise tasks), and Anthropic's Claude family (Sonnet variants) via partner integration. Embedding models include snowflake-arctic-embed-m (768-d, English-optimised) and multilingual / higher-resolution variants for EMBED_TEXT_1024. Model availability varies by region, so always check the model availability matrix in Snowflake's documentation before locking model choice. For frontier-class models like Claude Opus or GPT-5-tier OpenAI models, you go external — Cortex covers the production LLM in SQL surface, not the research-lab surface.

Cortex Search vs external vector DB — when?

Use Cortex Search Service when the source data is in Snowflake and you want hybrid BM25 + vector + metadata-filter retrieval as a managed service — no separate vector DB to operate, no embedding refresh job to maintain, governance carries through from the source table. Use an external vector DB (Pinecone, Weaviate, Qdrant, pgvector) when the source data is multi-cloud or non-Snowflake, when you need fine-grained control over the index parameters, or when the corpus is mature enough that the cost of a managed service exceeds the cost of running the vector DB yourself. For most Snowflake-shop teams up to ~10M-100M-doc scale, Cortex Search Service is the lower-friction answer and the production-grade default — and you can still call external LLMs against the retrieved context if you want a hybrid generation path.

How much does Cortex cost per call?

The cost model is tokens consumed times a model-specific credit rate, plus the warehouse credits for the SQL that wraps the call. Token counts cover input + output for COMPLETE, both prompt and embedding-output for EMBED_TEXT_*, and per-page rates for Document AI. The credit-per-million-tokens rate varies dramatically by model — small models like llama3-8b are often an order of magnitude cheaper than llama3-70b, which in turn is several times cheaper than frontier-class models like Claude Sonnet. The exact rates are in Snowflake's published pricing and updated periodically; always query SNOWFLAKE.ACCOUNT_USAGE.CORTEX_FUNCTIONS_USAGE_HISTORY for actuals. The senior-DE rule is: pick the smallest model that meets the quality bar on a 1K-row sample, lock the model, run the full batch, then meter daily. Cortex Search Service has a separate cost line for index storage + refresh; budget that separately from per-call costs.

Can I fine-tune a model in Cortex?

Cortex offers limited fine-tuning paths — the available routes change as the product evolves, but the typical envelope is parameter-efficient fine-tuning (PEFT / LoRA) on a curated subset of the hosted models. You upload a JSONL training set, Cortex runs the fine-tune in-warehouse, and the resulting adapter is callable like any other Cortex model. For arbitrary fine-tuning (full-parameter, on arbitrary base models, with custom training loops), you still need an external stack — Together AI / Fireworks / your own GPU cluster. The 80/20 answer in 2026: for in-domain language adaptation (legal, medical, customer-support tone) the Cortex fine-tune is usually enough; for research-grade fine-tuning or RLHF, you go external. As Cortex's catalogue expands, the boundary will shift; check Snowflake's docs for the current fine-tuning model list.

Cortex Analyst vs Snowpark for AI use cases?

They solve different problems. Cortex Analyst is governed text-to-SQL — a user asks a question in English, Analyst translates it to SQL constrained by a semantic model (a YAML declaring tables, dimensions, measures, verified queries), runs the SQL, and returns the answer plus the generated SQL for auditability. Use Analyst when you want business users to query data without writing SQL, and you need governance to prevent the LLM from hallucinating columns. Snowpark is a programming framework — Python, Java, or Scala code that runs on Snowflake warehouses, with first-class APIs for DataFrames, ML model training, and UDF deployment. Use Snowpark when you need code-driven data engineering (custom transforms, custom ML training, ad-hoc Python that doesn't fit in SQL). They compose: Snowpark UDFs can call Cortex LLM functions, and Cortex Agents can use Snowpark-deployed functions as registered tools. The right framing: Analyst is for natural-language Q&A on governed data; Snowpark is for code-driven engineering; both can use Cortex underneath.

Practice on PipeCode

• Drill the SQL practice library → for the Cortex COMPLETE / EMBED_TEXT_* / VECTOR_COSINE_SIMILARITY family of in-warehouse AI probes.
• Rehearse on the ETL practice library → when the interviewer wants batch-LLM pipeline design depth.
• Sharpen the joins practice library → for the embedding-join and semantic-similarity patterns.
• Stack the prerequisites by drilling the SQL fundamentals at pipecode.ai → — the home dashboard surfaces topic-tagged paths.
• Layer the schema-design axis with more SQL drills → to build the muscle memory for governed semantic models.
• For end-to-end ingestion pipelines, keep working the ETL drills → — Document AI + Streams + Tasks compose naturally with the patterns there.

Lock in Cortex AI muscle memory

Docs explain the functions. PipeCode drills explain the decision — when Cortex saves the egress bill, when external LLMs beat in-warehouse, when a Cortex Agent replaces a Python service. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face.

Practice SQL problems →
Practice ETL problems →