Course contents

SnowPro Core COF-C03 Study GuidePart 7: AI featuresCortex LLM Functions

Cortex LLM Functions

Part 7

Snowflake AI Features

Everything under the Cortex umbrella, treated properly: the LLM functions you call from SQL, the two retrieval-and-answer services, document extraction and the Snowsight coding assistant, the classic ML functions, and how to govern and pay for all of it. This part is where you learn to choose between these tools and run them in production.

COF-C03 added AI to Domain 1. What you need is mostly tool selection (which Cortex feature for this job) and the billing category. Nothing here needs ML background; it needs the same discipline as the rest of the exam, knowing which named thing does which job.

Cortex AI, In Depth

Cortex LLM Functions

Definition

Cortex AI functions (older docs call them LLM functions) are SQL functions backed by large language models that Snowflake hosts. The model work runs on Snowflake's compute inside its security boundary and bills AI credits per million tokens, at a rate set per function and model; the query that calls them runs on your warehouse as usual. Your data never leaves Snowflake to reach a third-party API, which is the governance argument that gets them approved at companies like IsmaFrost in the first place.

Two families exist, and choosing between them is the first exam-relevant decision. Task-specific functions do one job with a model Snowflake picked and tuned: you hand them text, they hand back an answer, no prompt engineering involved. The general function, AI_COMPLETE, takes any prompt plus a model name you choose, which buys flexibility at the cost of writing and maintaining prompts.

FunctionJobExample return
AI_SENTIMENT(text)Sentiment of a text, overall or per category you nameAn object whose categories carry labels: positive, negative, neutral, mixed or unknown
AI_TRANSLATE(text, 'fr', 'en')Language translationThe English text
AI_SUMMARIZE(text)Condense one textA short summary string
AI_EXTRACT(text or file, responseFormat)Pull named values out of text or a documentJSON with your field names and their values
AI_CLASSIFY(text, categories)Sort text into your labelsAn object whose labels array holds the matching category
AI_FILTER(condition)Keep or drop rows by a plain-language testTRUE or FALSE, so it fits in WHERE or a join condition
AI_AGG(text, instruction) · AI_SUMMARIZE_AGG(text)One answer across many rowsOne string per group, used with GROUP BY
AI_PARSE_DOCUMENT(file, {'mode': 'LAYOUT'})The full text of a staged documentOCR mode (the default): plain text. LAYOUT mode: Markdown that keeps headings and tables
AI_EMBED(model, text)Turn text into a vectorA VECTOR value for similarity search
AI_COMPLETE(model, prompt)Anything, with your prompt and model choiceWhatever you asked for

These AI_ functions replaced the older SNOWFLAKE.CORTEX functions (SENTIMENT, TRANSLATE, SUMMARIZE, EXTRACT_ANSWER, EMBED_TEXT_768, COMPLETE and others). The old ones still work in most accounts, but Snowflake is removing them, and some return a different shape: the legacy SENTIMENT returned a number from -1 to 1, while AI_SENTIMENT returns labels.

-- Safia's team, scoring last week's support tickets in one statement
SELECT ticket_id,
       AI_SENTIMENT(body)                                  AS mood,
       AI_TRANSLATE(body, 'fr', 'en')                      AS body_en,
       AI_EXTRACT(body, ['Which product is mentioned?'])   AS product
FROM support_tickets
WHERE created_at >= DATEADD('day', -7, CURRENT_DATE());

-- The general function: model is your choice, prompt is your problem
SELECT AI_COMPLETE(
  'mistral-large2',
  'Rewrite this release note for a non-technical audience: ' || note_text
) FROM release_notes;
One AI function call. Safia's SELECT runs on warehouse BI_WH and reads the BODY column of SUPPORT_TICKETS. For each row, AI_SENTIMENT sends the text to a large language model that Snowflake hosts, and the answer comes back as a new column. The model may run in another Snowflake region only if the account parameter CORTEX_ENABLED_CROSS_REGION allows it. Two meters run: warehouse credits for the query, and AI credits per token for the model. Budgets can track both; resource monitors see only the warehouse.INSIDE SNOWFLAKEWHAT YOU PAY: TWO METERSTABLESUPPORT_TICKETSBODY: ticket textYOUR QUERYSELECT ...AI_SENTIMENT(body)runs on BI_WHAI MODELHosted LLMrun by Snowflake,not a third partyRESULTNew columnone answerper rowMay run in another Snowflake regiononly if this account parameter allows:CORTEX_ENABLED_CROSS_REGIONBILLED ASWarehouse creditsper second whileBI_WH runsBILLED ASAI creditsper token, ratedepends on model✓Budgets: see both✗Resource monitors:warehouse onlyrowstextanswerbillsbills One AI function call. Safia's SELECT runs on warehouse BI_WH and reads the BODY column of SUPPORT_TICKETS. For each row, AI_SENTIMENT sends the text to a large language model that Snowflake hosts, and the answer comes back as a new column. The model may run in another Snowflake region only if the account parameter CORTEX_ENABLED_CROSS_REGION allows it. Two meters run: warehouse credits for the query, and AI credits per token for the model. Budgets can track both; resource monitors see only the warehouse.INSIDE SNOWFLAKETABLESUPPORT_TICKETSBODY: ticket textYOUR QUERYSELECT ...AI_SENTIMENT(body)runs on BI_WHAI MODELHosted LLMrun by Snowflake,not a third partyRESULTNew columnone answerper rowrowstextanswerMay run in anotherSnowflake region only ifthis account parameterallows:CORTEX_ENABLED_CROSS_REGIONWHAT YOU PAY: TWO METERSBILLED ASWarehouse creditsper second whileBI_WH runsBILLED ASAI creditsper token, ratedepends on modelbillsbills✓Budgets: see both✗Resource monitors: warehouse only
Figure 7.1 An AI function runs inside your query: each row's text goes to a model that Snowflake hosts, and the answer comes back as a new column. You pay on two meters, warehouse credits for the query and AI credits per token, and only a budget sees both. The legacy SNOWFLAKE.CORTEX.SENTIMENT returned a number from -1 to 1; AI_SENTIMENT returns labels such as positive or mixed.

The row-level shape matters operationally. Most of these are scalar functions, one call per row, so the ticket query above made three model calls per ticket. AI_AGG and AI_SUMMARIZE_AGG are aggregates: they read a group of rows and return one answer. On ten thousand tickets that's thirty thousand calls billed by token, which is fine once and expensive nightly. Thomas's habit: materialize AI outputs into a column with a stream-and-task or dynamic-table pipeline so each ticket is scored once, instead of re-scoring the whole table every time a dashboard refreshes.

Exam traps
  • The model work bills AI credits per token, separately from the warehouse running your query, and resource monitors never see it. Use a warehouse no larger than Medium for AI functions: a bigger one doesn't make the model calls faster; it only raises the warehouse part of the bill.
  • Functions that write text (AI_COMPLETE, AI_CLASSIFY, AI_TRANSLATE and others) bill input and output tokens, and the prompt Snowflake adds counts as input. AI_EMBED and AI_SIMILARITY bill input tokens only.
  • Task-specific functions take no model argument: an option showing AI_SENTIMENT or AI_TRANSLATE with a model name is invented syntax. Embeddings are the exception: AI_EMBED takes the embedding model first, and the model sets the vector's size.
  • Data stays inside Snowflake. Any answer implying Cortex ships your rows to an external API is wrong by design.