What you will be able to do
- Explain what Cortex AI Functions are, which privileges they require and when to prefer the REST API
- Choose the task-specific function (AI_CLASSIFY, AI_SENTIMENT, AI_EXTRACT, AI_SUMMARIZE, AI_FILTER, AI_TRANSLATE) for a given text task
- Shape AI_COMPLETE output with prompt wording, model_parameters and structured outputs, and reason about token cost
Key concept
Cortex AI Functions — Snowflake exposes hosted LLMs as ordinary SQL functions (also callable from Python), so you run generative and task-specific AI over table columns right where the data lives, governed by roles and billed by tokens.
1.LLMs as SQL functions: access, cost and batch design
Snowflake Cortex makes large language models available as functions you call inside a query. The docs list these use cases: extracting entities, aggregating insights across customer tickets, filtering and classifying content by natural language, sentiment analysis, translation, and parsing documents for RAG pipelines. The models run inside Snowflake: all LLMs offered through Snowflake AI Features are deployed within the Snowflake service perimeter, so your text does not leave the platform to reach a third-party endpoint.
The functions come in two groups. Cortex AI functions do the work, for example AI_COMPLETE, AI_CLASSIFY, AI_SENTIMENT and AI_EMBED. Helper functions keep those calls from failing, for example AI_COUNT_TOKENS and PROMPT. Older material and some exam items still use the legacy names. AI_COMPLETE is the updated version of COMPLETE, and AI_EMBED replaces EMBED_TEXT_768 and EMBED_TEXT_1024. Treat the old and new names as the same capability, and use the AI_ names in new code.
Access is role-based. A warehouse alone does not let you call these functions. Per the AI Functions overview, the role needs the USE AI FUNCTIONS account-level privilege plus either the CORTEX_USER or the AI_FUNCTIONS_USER database role. The AI_COMPLETE reference states the requirement as the SNOWFLAKE.CORTEX_USER database role. So an "insufficient privileges" error on an AI function usually means a missing database-role grant, not a warehouse problem.
Cost is per token, and so is the design. AI_COMPLETE generates new text, so both input and output tokens count toward its cost. That makes prompt length and response length the two levers on spend. Cortex AI Functions are optimized for throughput, so they suit batch work over large tables. When one user is waiting on one answer and latency matters, Snowflake points you to the REST API instead (Complete API, Embed API, Agents API).
Batch runs need to survive bad rows. By default, if AI_COMPLETE can't process an input it returns NULL, and in a multi-row query the failing rows return NULL without stopping the query. Pass the optional return_error_details argument as TRUE to get back an OBJECT with value and error fields, so you can see why a row failed.
Checkpoint 1 of 6· Check yourself
A team wants an interactive chat widget where a single user waits on each LLM answer. Which interface does Snowflake recommend for this latency-sensitive case?
AI Functions in SQL are tuned for throughput over many rows. For interactive, latency-sensitive use, the docs recommend the REST API.
“For more interactive use cases where latency is important, use the REST API.”Source: docs.snowflake.com
Checkpoint 2 of 6· Exam question
A global retailer keeps product reviews in English, German, Japanese and Portuguese in one column. A data scientist wants a single embedding model so that semantically similar reviews match across languages in a vector similarity search. Which approach is MOST appropriate?
Correct answer: A — Call `SNOWFLAKE.CORTEX.EMBED_TEXT_1024` with multilingual `snowflake-arctic-embed-l-v2.0` and store results in a `VECTOR(FLOAT, 1024)` column.
- A. The arctic-embed-l-v2.0 model is multilingual and produces 1024-dimension vectors, so reviews with the same meaning land close together regardless of language. The column type matches the output dimension.
- B. This model is English-focused, so non-English reviews would be embedded poorly and cross-language matching would be unreliable, even though the dimensions are internally consistent.
- C. nv-embed-qa-4 produces 1024 dimensions but is an English model, so matching the dimension is not enough to get multilingual semantic retrieval.
- D. e5-base-v2 is an English model, and switching the distance metric does not make its vectors language-aware.
2.Task-specific functions: classify, sentiment, extract, summarize, filter, translate
For many jobs you don't need to write a prompt. Snowflake ships task-specific functions that already know their job. You pass in the text, plus labels or questions where the task needs them, and Snowflake supplies the prompting. Reach for one of these first when the task matches. Fall back to AI_COMPLETE only when you need free-form generation or behaviour the task functions don't cover.
| Function | What it does |
|---|---|
| AI_CLASSIFY | Classifies text or images into user-defined categories |
| AI_FILTER | Returns True or False for an input, for use in SELECT, WHERE or JOIN … ON |
| AI_SENTIMENT | Extracts sentiment from text |
| AI_EXTRACT | Extracts information from an input string or file (text, images, documents) |
| AI_SUMMARIZE | Summarizes text, images and documents |
| AI_SUMMARIZE_AGG / AI_AGG | Summarize, or return insights, across many rows of a text column without context-window limits |
| AI_TRANSLATE | Translates text between supported languages |
| AI_REDACT | Redacts personally identifiable information (PII) from text |
Categorization. AI_CLASSIFY takes the input and an array of labels. By default it returns a single label. Setting output_mode to multi lets it return every label that applies. Limits to know: at most 100 categories per call, and English only. To keep rows that match a natural-language condition without any labelled data (for example, "the customer threatens to cancel"), use AI_FILTER in the WHERE clause.
SELECT AI_CLASSIFY('One day I will see the world', ['travel', 'cooking']);SELECT AI_CLASSIFY(
'One day I will see the world and learn to cook my favorite dishes',
['travel', 'cooking', 'reading', 'driving'],
{'output_mode': 'multi'}
);Sentiment. AI_SENTIMENT runs a custom Snowflake LLM and works across languages. Called with only text, it returns the overall sentiment. Add an optional array of up to ten categories (each at most 30 characters, for example ['cost', 'quality', 'service', 'wait time']) and you also get aspect-based sentiment for each of those topics.
Information extraction. AI_EXTRACT pulls structured data out of text or staged documents. It supports three output shapes. An *entity* is a natural-language question such as the city or ZIP code. A *list* is defined by a JSON schema, for example all account holders on a statement. A *table* is defined by a JSON schema giving the table title and columns. It runs on arctic-extract, a vision-based LLM, so it can also read tables, checkmarks and handwritten signatures.
Summarization. AI_SUMMARIZE condenses one input. AI_SUMMARIZE_AGG and AI_AGG work across many rows of a column and are not bound by a single context window.
Checkpoint 3 of 6· Match them up
Match each requirement to the task-specific function that meets it
Tap a term, then the definition that fits it.
AI_FILTER returns a boolean for row filtering. AI_SENTIMENT's categories enable aspect-based scores. AI_EXTRACT answers questions about documents. AI_CLASSIFY's multi output mode returns every label that applies.
“Returns True or False for a given text or image input, allowing you to filter results in SELECT, WHERE, or JOIN”Source: docs.snowflake.com
Checkpoint 4 of 6· Exam question
An analyst runs the query below to find the five support articles most similar to a user question, but the results are the least relevant articles in the table. ```sql SELECT title FROM kb_articles ORDER BY VECTOR_COSINE_SIMILARITY(embedding, :query_vec) ASC LIMIT 5; ``` What is the cause and the correct fix?
Correct answer: A — Cosine similarity rises as vectors align, so the ascending sort returns the least similar rows; sort `DESC` to get the nearest articles.
- A. VECTOR_COSINE_SIMILARITY returns a higher value for more similar vectors, so ordering ascending surfaces the least similar rows. Sorting descending is the correct fix.
- B. Cosine similarity is a similarity, not a distance, so the premise is wrong. Raising the limit would not change which end of the ranking is returned.
- C. Embeddings stay in the VECTOR type for similarity functions; converting them to text would break the comparison, and sort direction has nothing to do with text types.
- D. Inner product is also a similarity-style measure where larger means more aligned, so keeping the ascending sort would still return the weakest matches.
3.Prompt engineering with AI_COMPLETE and the task functions
AI_COMPLETE is the general-purpose function: the docs recommend it for most generative AI tasks. It accepts three kinds of input: a single text string, an image plus a prompt, or a prompt object that can mix several images with text. With this much freedom, the wording of the prompt is the main thing you control. A common pattern is to build the prompt in SQL by concatenating a fixed instruction with column values, such as an "Answer this question: … using this text: …" template. The PROMPT helper function builds prompt objects for AI_COMPLETE, and AI_COUNT_TOKENS returns the token count of an input for a given model or function. Use it to check that a prompt fits a model's limit before you run it over a whole table.
Beyond the wording, model_parameters control how the model responds:
- temperature (0–1, default 0): randomness. Higher values such as 0.7 give more diverse output, and lower values make it more deterministic. - top_p (0–1): an alternative to temperature that restricts the set of tokens the model can choose from. - max_tokens (default 4096): caps output tokens. Because output tokens are billed, a tight cap saves money, but too small a value truncates the response. - guardrails: TRUE filters unsafe or harmful responses using Cortex Guard.
When downstream code needs predictable fields, ask for structured output: use response_format with the single-string form of AI_COMPLETE. You cannot pass a JSON schema with the prompt-object form.
The task-specific functions are still prompted. You just steer them through arguments rather than free text. AI_CLASSIFY accepts a task_description, a description for each label, and few-shot examples. Note that it adds its own prompt to your input, so the tokens billed are more than the text you supplied. AI_EXTRACT answers improve when questions follow the documented guidelines. Write in plain English, ask for a single value per question, and be specific: in a document with both an issuing date and a signature date, don't just ask "What is the date?". Don't expect the model to guess your intent or to bring deep domain knowledge.
Checkpoint 5 of 6· Check yourself
A nightly AI_COMPLETE job produces long answers, but downstream code only reads a short verdict. Which change cuts token cost without changing the task?
AI_COMPLETE bills output tokens as well as input tokens, so a shorter requested answer and a lower max_tokens cap both cut spend. Temperature and guardrails change behaviour, not volume.
“For the COMPLETE function, which generates new text in the response, both input and output tokens are counted.”Source: docs.snowflake.com
Checkpoint 6 of 6· Check yourself
Which AI_EXTRACT question follows Snowflake's guidance for an accurate answer on a contract that has both an issuing date and a signature date?
The guidance asks for specific questions that request a single value and that don't rely on the model guessing intent or domain knowledge.
“Be specific; for example, if the document includes several dates (such as issuing date and signature date), do not ask”Source: docs.snowflake.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.An AI function privilege error means the warehouse is too small or not granted.Why is that wrong?
Calling AI functions requires an AI-specific grant: the USE AI FUNCTIONS privilege plus a database role such as SNOWFLAKE.CORTEX_USER. Warehouse access alone is not enough.
Covered in LLMs as SQL functions: access, cost and batch design
2.Task-specific functions such as AI_CLASSIFY bill only the tokens of the text you pass in.Why is that wrong?
AI_CLASSIFY wraps your input in its own prompt, so the billed token count is higher than your text alone.
Covered in Prompt engineering with AI_COMPLETE and the task functions
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Cortex AI Functions are optimized for throughput.”
↩︎ LLMs as SQL functions: access, cost and batch design“These task-specific functions are purpose-built managed functions that automate routine tasks, like simple summaries and quick translations”
↩︎ Task-specific functions: classify, sentiment, extract, summarize, filter, translate“PROMPT: Helps you build prompt objects for use with AI_COMPLETE and other functions.”
↩︎ Prompt engineering with AI_COMPLETE and the task functions“Snowflake Cortex features are provided as SQL functions and are also available in Python.”
↩︎ Key concept“To call any of these functions, your role needs the USE AI FUNCTIONS account-level privilege and one of the CORTEX_USER or AI_FUNCTIONS_USER database roles.”
↩︎ Exam trap 1“For more interactive use cases where latency is important, use the REST API.”
↩︎ Checkpoint“Returns True or False for a given text or image input, allowing you to filter results in SELECT, WHERE, or JOIN”
↩︎ Checkpoint - 2.
“AI_COMPLETE is the updated version of COMPLETE.”
↩︎ LLMs as SQL functions: access, cost and batch design“rows with errors return NULL”
↩︎ LLMs as SQL functions: access, cost and batch design - 3.
“If you do not provide this argument, AI_SENTIMENT returns only the overall sentiment.”
↩︎ Task-specific functions: classify, sentiment, extract, summarize, filter, translate - 4.
“Entity: Ask questions in natural language or describe the information to be extracted (such as city, street, or ZIP code).”
↩︎ Task-specific functions: classify, sentiment, extract, summarize, filter, translate“Be specific; for example, if the document includes several dates (such as issuing date and signature date), do not ask”
↩︎ Checkpoint - 5.
“max_tokens: Sets the maximum number of output tokens in the response. Small values can result in truncated responses.”
↩︎ Prompt engineering with AI_COMPLETE and the task functions“To get a structured output as the response, use the response_format parameter with AI_COMPLETE (Single string).”
↩︎ Prompt engineering with AI_COMPLETE and the task functions“a lower temperature (such as 0.2) makes the output more deterministic and focused”
↩︎ Prediction - 6.
“The following example passes in a task description, label descriptions, and few-shot examples”
↩︎ Prompt engineering with AI_COMPLETE and the task functions“AI_CLASSIFY adds a prompt to your input to generate its response.”
↩︎ Exam trap 2
Also cited
“For the COMPLETE function, which generates new text in the response, both input and output tokens are counted.”
↩︎ Checkpoint