CertSafari
    Snowflake SnowPro Specialty: Gen AI (GES-C02)· Lessons

    Domain 2 · Lesson 3/15

    Cortex AI functions: AI_COMPLETE, structured outputs and task-specific functions

    Apply AI functions in Snowflake.

    19 min read
    7.6% of exam
    10 sources
    Published 21 Oct 2026
    Docs as of 18 Oct 2026

    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?

    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.

    Generation options passed to complete() in Python, including response_formatpython
    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
        )

    Checkpoint 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?

    Sources123

    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.

    A type literal shaping the response into a nested note objectsql
    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.

    Skeleton of a JSON-schema response_format with a required fieldsql
    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', ...]
            }
        }
    )

    Checkpoint 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?

    Sources45

    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.

    PROMPT combining two columns into the condition evaluated by AI_FILTERsql
    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.

    Classifying every image in a stage by combining PROMPT, TO_FILE and DIRECTORYsql
    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?

    Sources6178

    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.

    Managed Cortex AI functions and the job each one does
    FunctionJobNotes
    AI_CLASSIFYSorts text or images into user-defined categoriesConfig object controls single or multi-label output
    AI_FILTERReturns True or False for a text or image inputUsable in SELECT, WHERE and JOIN … ON; accepts a PROMPT object
    AI_EXTRACTPulls information from a string or a file (text, images, documents)Questions go in responseFormat; optional scores argument adds confidence
    AI_SENTIMENTExtracts sentiment from textReturns an object with a categories array; optional entities argument
    SUMMARIZELegacy SNOWFLAKE.CORTEX summarizerReplaced by AI_SUMMARIZE; arguments and output unchanged
    AI_SUMMARIZESummarizes text, images and documentsOne summary per input
    AI_SUMMARIZE_AGGSummary across many rows of a text columnAggregate; not subject to context window limits
    AI_AGGInsights across many rows from a user-defined promptAggregate; not subject to context window limits
    AI_TRANSLATETranslates text between supported languagesTakes the text, a source language and a target language
    AI_SIMILARITYEmbedding similarity between two inputsTakes two inputs; options specify the model
    AI_TRANSCRIBETranscribes audio and video files stored in a stageReturns text, timestamps and speaker information
    AI_REDACTRedacts personally identifiable information (PII) from textReplaces manual PII scrubbing
    AI_PARSE_DOCUMENTExtracts text (OCR mode) or text with layout (LAYOUT mode) from staged documentsCan 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.

    AI_EXTRACT asking one named question of a text column, with confidence scoressql
    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.

    AI_SENTIMENT with entities for aspect-based sentimentsql
    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.

    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?

    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?

    Sources911085

    Sources

    (10)

    Every claim above is drawn from one of these pages, quoted as it was written on the date shown.

    1. 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. 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. 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. 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. 5.
      “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. 6.
      “PROMPT does not perform any string formatting itself.”
      ↩︎ Building inputs with PROMPT and TO_FILE
    7. 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. 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. 9.
      “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. 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

    Continue to part 2 of 2

    Embeddings, vector functions and RAG in Snowflake Cortex

    Spotted a mistake, or was something unclear? Tell us.