What you will be able to do
- Choose between AI_COMPLETE and a task-specific Cortex AI function for a given job
- Call AI_COMPLETE with a model, a prompt and generation options, and handle its NULL-on-error behaviour
- Force a completion into a fixed shape with a response_format type literal or JSON schema
- Build prompts from column values with PROMPT and pass staged files with TO_FILE
- Configure AI_CLASSIFY for multi-label output and pick the AI_PARSE_DOCUMENT mode that keeps tables and images
Key concept
General versus task-specific AI functions — AI_COMPLETE is the one Cortex function where you choose the model and write the prompt. The task-specific functions each do one fixed job with a managed model and need no prompt engineering. Most design decisions on this topic come down to which of the two kinds fits the task.
1.Three families of Cortex AI functions
Snowflake Cortex AI functions run large language models from inside SQL. A query can summarise, classify, translate or extract from text, images and documents without exporting anything. Every model behind these functions is deployed inside the Snowflake service perimeter, and the same functions can also be called from Python.
Before writing a call, decide which of the three families the job belongs to:
- AI_COMPLETE, the general-purpose function. You pick the model and write the prompt, and it suits most generative tasks.
- Task-specific functions such as AI_CLASSIFY, AI_SENTIMENT and AI_TRANSLATE. Each one is managed and purpose-built, so there is no model to choose and no prompt to tune.
- Helper functions: TO_FILE, PROMPT and AI_COUNT_TOKENS. They build inputs and head off failures, for example by checking that a prompt fits within a model's limit.
These functions are built for throughput, not latency. Point them at large SQL tables and process rows in batch. For an interactive application where every second counts, use the REST APIs instead: Complete for inference, Embed for embeddings and Agents for agentic apps. To call any of the functions, a role needs the USE AI FUNCTIONS account privilege plus the CORTEX_USER or AI_FUNCTIONS_USER database role. Granting those is covered in Cortex model access controls.
Checkpoint 1 of 9· Check yourself
A team is building a customer-facing chat app where every second of response time matters. It needs LLM completions one request at a time as users type. Which approach fits Snowflake's guidance?
Cortex AI functions in SQL are built for batch throughput over large tables. For interactive, latency-sensitive use, Snowflake points to the REST APIs: Complete for inference, Embed for embeddings and Agents for agentic apps.
“For more interactive use cases where latency is important, use the REST API.”Source: docs.snowflake.com
Sources1
2.Calling AI_COMPLETE: model, prompt, options and errors
AI_COMPLETE takes a model name and a prompt and returns the model's completion. The prompt can be one of three things: a single text string, a single image with a text prompt, or a prompt object that holds several images alongside text. You choose among the hosted LLMs by changing the model string, which is explored further in running third-party models. The older SNOWFLAKE.CORTEX.COMPLETE does the same job, but AI_COMPLETE is its updated version and the one that receives new functionality.
Generation options are passed alongside the prompt. The Python snowflake.cortex example below sets max_tokens, temperature, top_p, guardrails and response_format in a single CompleteOptions object. Only one of these settings constrains the structure of the answer, and that is response_format. In SQL, show_details => TRUE makes AI_COMPLETE return a metadata object instead of bare text. The object includes created, model, the output and a usage block with prompt, completion and total token counts.
options = CompleteOptions(
max_tokens=4096,
temperature=0.7,
top_p=1,
guardrails=False,
response_format=response_format
)A failed row does not fail the query. By default, AI_COMPLETE returns NULL for any input it can't process, and the remaining rows still complete. If you pass return_error_details as TRUE in the final argument position, each row returns an OBJECT with a value field and an error field instead, and one of the two is NULL. A pipeline can then record why a row failed rather than only seeing that it did.
TRY_COMPLETE is the legacy SNOWFLAKE.CORTEX way to get the same tolerance. It performs the same operation as COMPLETE, but returns NULL where COMPLETE would raise an error. It is kept for backward compatibility and is due to be deprecated by the end of 2026. TRY_COMPLETE only changes what happens on failure. A successful completion from TRY_COMPLETE is still free text in whatever shape the model chose.
Checkpoint 2 of 9· Fill the gap
A Python pipeline breaks when completions come back in inconsistent shapes. Which CompleteOptions setting makes the model's answer follow a fixed schema?
options = CompleteOptions(
max_tokens=4096,
temperature=0.7,
top_p=1,
guardrails=False,
? =response_format
)response_format is the only one of these settings that controls the structure of the answer. The others set length limits, sampling randomness or safety filtering, but the output is still free-form text.
Source: docs.snowflake.comCheckpoint 3 of 9· Check yourself
A nightly batch job runs AI_COMPLETE over millions of rows. The team wants the run to finish even when some rows fail, and to store the reason for each failure. What should they do?
By default, failed rows return NULL and the query still completes, but the reason for the failure is lost. With return_error_details set to TRUE, each row returns an OBJECT with a value and an error message. TRY_COMPLETE also only returns NULL, and show_details returns usage metadata, not error reasons.
“When set to TRUE, the function returns an OBJECT that contains the value and the error message”Source: docs.snowflake.com
3.Structured outputs with response_format
The response_format argument makes AI_COMPLETE produce output that downstream code can parse every time. As the model generates, AI_COMPLETE checks each token against the structure you supplied, so the response conforms by construction. You don't have to hope the model followed an instruction. Every model AI_COMPLETE supports can produce structured output, although the stronger models tend to fill the fields more accurately.
In SQL, response_format is its own named argument of AI_COMPLETE. The legacy COMPLETE function took it as a key inside the options object, and that placement no longer works: left inside model_parameters it isn't recognized, and the response comes back as an unstructured string.
You can define the structure in two ways. A type literal uses SQL types. It starts with the TYPE keyword, its top level must be an OBJECT, and Snowflake maps the SQL types to JSON for you. STRING and VARCHAR become JSON strings, and FIXED types without a scale become JSON integers. Some types are rejected: the empty OBJECT(), VARIANT, MAP and the date and time types all return an error.
SELECT AI_COMPLETE(
model => 'llama3.3-70b',
prompt => 'Extract structured data from this customer interaction note: Customer Sarah Jones complained about the mobile app crashing during checkout. She tried to purchase 3 items: a red XL jacket ($89.99), blue running shoes ($129.50), and a fitness tracker ($199.00). The app crashed after she entered her shipping address at 123 Main St, Portland OR, 97201. She has been a premium member since January 2024.',
response_format => TYPE OBJECT(note OBJECT(items_count NUMBER, price ARRAY(STRING), address STRING, member_date STRING)),
show_details => TRUE
);A JSON schema gives you more control. It defines the structure, the data types and the constraints, including which fields are required. OpenAI (GPT) models are stricter than the others: every node of the schema must set additionalProperties to false, and required must list every property. Other models accept that form too, so writing every schema that way means one schema serves every model.
AI_COMPLETE(
...
response_format => {
'type': 'json',
'schema': {
'type': 'object',
'properties': {
'property_name': {
'type': 'string'
},
...
},
'required': ['property_name', ...]
}
}
)For simple tasks, the schema alone is enough, and you don't even need to tell the model to answer in JSON. For complex tasks, asking for JSON in the prompt as well can improve accuracy. The other tools that come to mind don't enforce a shape. TRY_COMPLETE only converts failures to NULL. AI_FILTER returns TRUE or FALSE for a row. A larger max_tokens leaves room for a longer answer but does nothing to its structure.
Checkpoint 4 of 9· Fill the gap
This JSON-schema response_format must guarantee that property_name is always present in the completion. Which key goes in the blank?
AI_COMPLETE(
...
response_format => {
'type': 'json',
'schema': {
'type': 'object',
'properties': {
'property_name': {
'type': 'string'
},
...
},
' ? ': ['property_name', ...]
}
}
)The JSON schema's required list names the fields the response must contain. additionalProperties only controls whether fields outside the schema are allowed, so it does not make any field mandatory. OpenAI GPT models also need required to list every property and additionalProperties set to false in every node.
Source: docs.snowflake.comCheckpoint 5 of 9· Exam question
A data engineer needs every completion returned by an LLM call to conform to a fixed JSON schema so downstream ETL code can parse fields without try/catch logic. Which capability should they configure?
Correct answer: A — Pass a response_format JSON schema object in the options argument of AI_COMPLETE
- A. This is correct: the structured outputs feature accepts a JSON schema in the options object and constrains the model's response to that schema, giving predictable, parseable fields.
- B. This is incorrect: the NULL-on-error wrapper only changes what happens when the call fails outright; it does not constrain the shape of a successful completion.
- C. This is incorrect: the boolean row-filtering function decides whether a row passes a predicate, it has no mechanism for shaping a completion's JSON structure.
- D. This is incorrect: raising the token budget only prevents truncation; it does not force the response into a specific schema.
4.Building inputs with PROMPT and TO_FILE
PROMPT inserts column values into an instruction without any string concatenation. It takes a template with numbered placeholders, {0}, {1} and so on, followed by one expression per placeholder. It returns an OBJECT of the form {'template': ..., 'args': [...]}. PROMPT does no formatting itself. The Cortex function that receives the object fills in the placeholders. A placeholder with no matching expression is an error, but an expression that no placeholder uses is allowed. The arguments can be any type that coerces to a string, or a FILE.
WITH reviews AS (
SELECT 'Wow... Loved this place.' AS review, 5 AS rating
UNION ALL
SELECT 'Crust is not good.', 2 AS rating
)
SELECT * FROM reviews
WHERE AI_FILTER(PROMPT('The reviewer enjoyed the restaurant: {0}, Rating: {1}', review, rating));TO_FILE turns a stage location and a path into a FILE object, which is how a staged image or document reaches AI_COMPLETE and the other functions that accept files. Put the FILE in a PROMPT argument and its placeholder marks where the file sits in the instruction. If you use more than one placeholder, a single call can compare two images. Running against DIRECTORY(@stage) with RELATIVE_PATH processes every file in the stage, one row per file.
SELECT AI_COMPLETE('claude-sonnet-4-6',
PROMPT('Classify the input image {0} in no more than 2 words. Respond in JSON',
TO_FILE('@myimages', RELATIVE_PATH))) as image_classification
FROM DIRECTORY(@myimages);The stage is where most AI_COMPLETE file calls go wrong. For images and documents it must use server-side encryption, and client-side encrypted stages are rejected. AI_PARSE_DOCUMENT is the exception to carry over carefully: it accepts stages with either client-side or server-side encryption. Stage names are case-insensitive, but paths are case-sensitive, so Invoice.PDF and invoice.pdf are different files. An image prompt object accepts text and images only. Audio and video can't go into it.
Checkpoint 6 of 9· Check yourself
Photos of damaged parts sit in the stage @claims. You want one AI_COMPLETE call per photo that answers 'Describe the damage in {0}', with the photo itself as {0}. How do you build that input?
Only TO_FILE turns a stage location and path into a FILE object that AI_COMPLETE can accept. PROMPT then places that FILE at the {0} placeholder. PROMPT does no loading or formatting of its own, so a bare path is passed in as text. String concatenation doesn't produce a FILE input, and AI_COUNT_TOKENS accepts only text.
“TO_FILE: Creates a reference to a file in an internal or external stage for use with AI_COMPLETE and other functions that accept files.”Source: docs.snowflake.com
5.Task-specific functions and when they win
For one well-defined job, a managed function is usually the better choice. It has no model to pick and no prompt to tune, and it is built for exactly that task. AI_COMPLETE is the better choice when you need a particular model, a custom instruction, or a job that no managed function covers. For document work in particular, AI_COMPLETE's advantage is that you can choose the model.
| Function | Job | Notes |
|---|---|---|
| AI_CLASSIFY | Sorts text or images into user-defined categories | Config object controls single or multi-label output |
| AI_FILTER | Returns True or False for a text or image input | Usable in SELECT, WHERE and JOIN … ON; accepts a PROMPT object |
| AI_EXTRACT | Pulls information from a string or a file (text, images, documents) | Questions go in responseFormat; optional scores argument adds confidence |
| AI_SENTIMENT | Extracts sentiment from text | Returns an object with a categories array; optional entities argument |
| SUMMARIZE | Legacy SNOWFLAKE.CORTEX summarizer | Replaced by AI_SUMMARIZE; arguments and output unchanged |
| AI_SUMMARIZE | Summarizes text, images and documents | One summary per input |
| AI_SUMMARIZE_AGG | Summary across many rows of a text column | Aggregate; not subject to context window limits |
| AI_AGG | Insights across many rows from a user-defined prompt | Aggregate; not subject to context window limits |
| AI_TRANSLATE | Translates text between supported languages | Takes the text, a source language and a target language |
| AI_SIMILARITY | Embedding similarity between two inputs | Takes two inputs; options specify the model |
| AI_TRANSCRIBE | Transcribes audio and video files stored in a stage | Returns text, timestamps and speaker information |
| AI_REDACT | Redacts personally identifiable information (PII) from text | Replaces manual PII scrubbing |
| AI_PARSE_DOCUMENT | Extracts text (OCR mode) or text with layout (LAYOUT mode) from staged documents | Can also extract images |
The two aggregate functions behave differently from the rest. AI_AGG and AI_SUMMARIZE_AGG read a whole column across many rows and return one result per group. Because they aren't subject to context window limits, they can summarise a year of support tickets that would never fit into a single prompt. Every other function in the table returns one output per input row.
AI_CLASSIFY has the richest set of arguments: AI_CLASSIFY( <input> , <list_of_categories> [, <config_object> ] [, <return_error_details> ] ). The category list needs at least two unique values. Accuracy can drop beyond about twenty categories, and categories are case sensitive. Each category can be an object with a label and an optional description of up to 25 words. Descriptions improve accuracy, but they count as input tokens. The config object accepts three keys:
output_mode: the default is'single'. Set it to'multi'when a row can carry several labels, such as both 'Shipping Delay' and 'Damaged Item'.task_description: up to 50 words of context about the task. It informs the model but does not switch on multi-label output.examples: few-shot examples, each with an input, its labels and an explanation.
When the input is a document, output_mode must be 'single' and examples aren't allowed.
AI_PARSE_DOCUMENT has two modes for staged documents. OCR returns text only. LAYOUT is the preferred mode for most work and for complex documents in particular: it keeps tables, headers and layout relationships, and it is the mode that image extraction (the extract_images option) requires. A scanned invoice whose table rows must survive and whose logo must be pulled out separately therefore needs LAYOUT with image extraction switched on. The full parameter set is in AI_PARSE_DOCUMENT options.
AI_EXTRACT replaces the legacy EXTRACT_ANSWER, and the call looks different. It returns a JSON object rather than a string. The questions to answer go in the responseFormat argument, keyed by the field name you want back, and setting the optional scores argument to TRUE adds confidence scores.
SELECT AI_EXTRACT(
text => doc_text,
responseFormat => {'total': 'What is the invoice total?'},
scores => TRUE
) AS answer
FROM docs;AI_SENTIMENT likewise differs from the legacy SENTIMENT, which returned a single FLOAT score. AI_SENTIMENT returns an object with a categories array, and each category record has a sentiment field holding a value such as positive, negative, neutral, mixed or unknown. Passing a list of entities as the second argument switches it to entity-level (aspect-based) sentiment, so one review can score the food and the service separately.
SELECT AI_SENTIMENT(review, entities) AS sentiment
FROM reviews;Two more functions follow the same pattern as the table above. AI_FILTER takes a text or image input, or a PROMPT object as in the earlier review example, and its Boolean result goes straight into a WHERE clause. The legacy SUMMARIZE is a direct replacement case: swapping it for AI_SUMMARIZE needs no change to arguments or output.
Checkpoint 7 of 9· Match them up
Match each requirement to the managed Cortex function that does that job without AI_COMPLETE.
Tap a term, then the definition that fits it.
Each of these managed functions does one fixed job. AI_SUMMARIZE_AGG is an aggregate that is not limited by the context window and returns one summary across many rows. AI_SUMMARIZE returns one summary for each input row.
“AI_SUMMARIZE_AGG: Aggregates a text column and returns a summary across multiple rows. This function isn’t subject to context window limitations.”Source: docs.snowflake.com
Checkpoint 8 of 9· Exam question
A product feedback table has millions of rows and support tickets can belong to more than one issue type at once (e.g. both 'Shipping Delay' and 'Damaged Item'). Which AI_CLASSIFY configuration is required to return more than one label per row?
Correct answer: A — Set 'output_mode': 'multi' in the config object argument
- A. This is correct: multi-label output is enabled by setting the output_mode field to multi in the optional config object; the default single-label mode returns exactly one category.
- B. This is incorrect: duplicating the categories array does not change the function's labeling mode and would just create redundant category entries.
- C. This is incorrect: this works but requires N separate LLM calls and manual result merging, which is unnecessary given the built-in multi-label mode.
- D. This is incorrect: task_description clarifies the classification task in natural language but does not toggle the structural single-vs-multi label behavior.
Checkpoint 9 of 9· Exam question
A pipeline lands scanned invoice PDFs in a stage. The team needs both the raw text and the table structure (rows/columns) preserved for downstream parsing, and some invoices also contain embedded logos that must be pulled out separately. Which AI_PARSE_DOCUMENT configuration satisfies this?
Correct answer: A — mode set to LAYOUT with extract_images set to TRUE
- A. This is correct: layout mode extracts text plus structural content such as tables, and image extraction is only available when layout mode is selected, so combining both satisfies every requirement.
- B. This is incorrect: OCR mode extracts text only and does not preserve table structure, and image extraction requires layout mode rather than OCR mode.
- C. This is incorrect: the default mode is OCR, which extracts text only and would not preserve the table structure the team needs.
- D. This is incorrect: disabling page splitting does not enable image extraction, and extract_images was never set here so embedded logos would not be returned.
Sources
(10)
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“All the LLMs that Snowflake provides access to via our Snowflake AI Features are deployed within the Snowflake Service perimeter.”
↩︎ Three families of Cortex AI functions“Cortex AI Functions are optimized for throughput.”
↩︎ Three families of Cortex AI functions“your role needs the USE AI FUNCTIONS account-level privilege and one of the CORTEX_USER or AI_FUNCTIONS_USER database roles”
↩︎ Three families of Cortex AI functions“AI_COMPLETE: Generates a completion for a given text string or image using a selected LLM. Use this function for most generative AI tasks.”
↩︎ Calling AI_COMPLETE: model, prompt, options and errors“TO_FILE: Creates a reference to a file in an internal or external stage for use with AI_COMPLETE and other functions that accept files.”
↩︎ Building inputs with PROMPT and TO_FILE“This function isn’t subject to context window limitations.”
↩︎ Task-specific functions and when they win“These task-specific functions are purpose-built managed functions that automate routine tasks, like simple summaries and quick translations, that don’t require any customization.”
↩︎ Key concept“For more interactive use cases where latency is important, use the REST API.”
↩︎ Checkpoint“AI_SUMMARIZE_AGG: Aggregates a text column and returns a summary across multiple rows. This function isn’t subject to context window limitations.”
↩︎ Checkpoint - 2.
“By default, if AI_COMPLETE can’t process the input, the function returns NULL.”
↩︎ Calling AI_COMPLETE: model, prompt, options and errors“AI_COMPLETE is the updated version of COMPLETE. For the latest functionality, use AI_COMPLETE.”
↩︎ Calling AI_COMPLETE: model, prompt, options and errors“When set to TRUE, the function returns an OBJECT that contains the value and the error message”
↩︎ Checkpoint - 3.
“This legacy function will be deprecated by the end of 2026.”
↩︎ Calling AI_COMPLETE: model, prompt, options and errors“Performs the same operation as the COMPLETE function but returns NULL instead of raising an error when the operation cannot be performed.”
↩︎ Exam trap 1 - 4.
“AI_COMPLETE verifies each generated token against your structured output definition to ensure that the response conforms to your type structure.”
↩︎ Structured outputs with response_format“For more control over structured output, use a JSON schema as the value for response_format.”
↩︎ Structured outputs with response_format“The empty object OBJECT() isn’t allowed as a type literal.”
↩︎ Structured outputs with response_format“additionalProperties field must be set to false in every node of the schema.”
↩︎ Exam trap 3“Type literals are supported only for the single string text prompt version of AI_COMPLETE.”
↩︎ Prediction - 5.https://docs.snowflake.com/en/user-guide/snowflake-cortex/aisql-migrate-legacy-functionsOfficial docs
“response_format moves out of the object into its own argument.”
↩︎ Structured outputs with response_format“AI_EXTRACT returns a JSON object instead of a string.”
↩︎ Task-specific functions and when they win“AI_SENTIMENT returns an object with a categories array instead of a FLOAT score.”
↩︎ Task-specific functions and when they win“SNOWFLAKE.CORTEX.SUMMARIZE to AI_SUMMARIZE”
↩︎ Task-specific functions and when they win - 6.
“PROMPT does not perform any string formatting itself.”
↩︎ Building inputs with PROMPT and TO_FILE - 7.
“The stage containing the images must have server-side encryption enabled. Client-side encrypted stages are not supported.”
↩︎ Building inputs with PROMPT and TO_FILE - 8.
“AI_PARSE_DOCUMENT supports documents on stages that use client-side or server-side encryption”
↩︎ Building inputs with PROMPT and TO_FILE“It is optimized for extracting structured content such as tables, headers, and layout relationships”
↩︎ Task-specific functions and when they win - 9.https://docs.snowflake.com/en/user-guide/snowflake-cortex/ai-complete-document-intelligenceOfficial docs
“An advantage of AI_COMPLETE over other document processing AI Functions is the ability to choose a model”
↩︎ Task-specific functions and when they win - 10.
“When the input is a document, output_mode must be 'single' and examples is not supported.”
↩︎ Task-specific functions and when they win“output_mode: Set to 'multi' for multi-label classification. Defaults to 'single' for single-label classification.”
↩︎ Exam trap 2