Snowflake Cortex AI and LLM Functions Study Guide — GES-C02

Snowflake Cortex AI and LLM Functions

This is the deepest and most heavily tested domain in the exam. It covers hands-on use of every LLM and AI SQL function, the embeddings/vector-search stack, Cortex Search for retrieval, Cortex Analyst and semantic views for text-to-SQL, fine-tuning, and Cortex Agents. Expect questions that give you a business requirement and ask which exact function or service applies, questions that give you a function name and ask what it returns, and questions that test the old-name-versus-new-name function families side by side.

Generation: COMPLETE and AI_COMPLETE

The general-purpose generation function takes a model name and a prompt and returns generated text.

SELECT AI_COMPLETE('mistral-large2', 'Summarize the following review: ' || review_text)
FROM reviews;

The older, still-common form is SNOWFLAKE.CORTEX.COMPLETE, called the same way:

SELECT SNOWFLAKE.CORTEX.COMPLETE('mistral-large2', 'Summarize: ' || review_text)
FROM reviews;

AI_COMPLETE also supports structured output — passing a response schema so the model's reply conforms to JSON you can parse and load into columns directly, instead of free text you have to post-process. This matters whenever a scenario says the output needs to feed a downstream pipeline step or land in a structured column.

Two Function Families — Know Which Is Which

Both families are real and both appear in the wild: older material and existing production code still uses the SNOWFLAKE.CORTEX.* names (SUMMARIZE, TRANSLATE, SENTIMENT, EXTRACT_ANSWER, CLASSIFY_TEXT); the current, actively developed surface is the AI_* family, collectively branded Cortex AI Functions (formerly Cortex AISQL). The exam can present either naming and expects you to recognize both without confusing what each one does.

Task-Specific Functions in Depth

• AI_SENTIMENT / SENTIMENT — returns a sentiment signal for a piece of text (e.g., positive/negative/neutral or a numeric score).
• SUMMARIZE — condenses long text into a shorter summary without a custom prompt.
• TRANSLATE — translates text between languages.
• EXTRACT_ANSWER — given a passage and a question, returns the answer found in that passage (extractive QA, not generative).
• AI_CLASSIFY / CLASSIFY_TEXT — assigns each input to one of a supplied list of categories. Good fit for routing/tagging at volume with a cheap, predictable function rather than a free-form prompt.
• AI_FILTER — evaluates a natural-language condition against each row and returns a boolean, so it can be used directly in a WHERE clause to filter rows by a semantic condition an ordinary WHERE couldn't express.
• AI_EXTRACT — pulls specific named fields (e.g., invoice number, due date) out of unstructured text into structured, queryable values.
• AI_AGG — the aggregate-style counterpart: instead of returning one result per row, it applies an instruction across a whole group of rows and returns a single synthesized answer (e.g., "summarize the themes across all of these support tickets").

Worked scenario: "We need to flag which of 50,000 support tickets mention a competitor by name, then produce one summary paragraph of the themes across all tickets mentioning that competitor." The flagging step is a per-row boolean condition → AI_FILTER. The single synthesized summary across the filtered rows → AI_AGG.

Embeddings and Vector Search

EMBED_TEXT_768 and EMBED_TEXT_1024 convert text into a numeric embedding stored in Snowflake's native VECTOR data type (the number identifies the embedding dimensionality the function produces). Once text is embedded, similarity between two vectors is measured with VECTOR_COSINE_SIMILARITY.

SELECT chunk,
       VECTOR_COSINE_SIMILARITY(
           embedding,
           SNOWFLAKE.CORTEX.EMBED_TEXT_768('e5-base-v2', :query)
       ) AS score
FROM doc_chunks
ORDER BY score DESC
LIMIT 5;

This hand-rolled pattern — embed a query, compare it against pre-computed embeddings with cosine similarity, take the top-N — is a from-scratch RAG retrieval step. It's exactly what Cortex Search automates for you when you don't want to manage the embedding/index/refresh lifecycle yourself.