Cortex AI_COMPLETE, Structured Outputs and Task-Specific AI Functions

1.What Cortex AI Functions are and who can call them

Cortex AI Functions let you run LLM work, such as extraction, classification, summarization and translation, directly in SQL against the data already in your tables. The models run inside Snowflake: the documentation says all the LLMs Snowflake provides access to are deployed within the Snowflake Service perimeter.

You need access before you can call them. The general AI Functions page says your role needs the USE AI FUNCTIONS account-level privilege and one of the CORTEX_USER or AI_FUNCTIONS_USER database roles. The AI_COMPLETE reference page names only the SNOWFLAKE.CORTEX_USER database role. So the two pages are consistent: CORTEX_USER is the role both of them name, and the general page also allows AI_FUNCTIONS_USER as an alternative.

The documentation sorts the functions into two groups. Cortex AI functions do the actual work. AI_COMPLETE is the general one, and the rest are task-specific, built for routine jobs that need no customization. Helper functions keep those calls from failing, for example by counting tokens before you call so you stay under a model limit. The vector functions used for similarity search are SQL functions on the VECTOR data type, and they work alongside AI_EMBED.

The functions are tuned for batch work. Snowflake recommends them for processing many inputs, such as text from large tables. For interactive work where latency matters, the recommendation is the REST API (Complete, Embed and Agents APIs).

Sources12

2.AI_COMPLETE: the general-purpose function

AI_COMPLETE generates a completion from a text string or an image using the LLM you choose. Snowflake recommends it for most generative AI tasks. It accepts three kinds of input: a single text prompt, a single image with a text prompt, or a prompt object that can hold multiple images and text. AI_COMPLETE is the updated version of the older COMPLETE function, and the docs say to use AI_COMPLETE for the latest functionality.

Beyond the model and the prompt, the migration guide gives the call shape as AI_COMPLETE(<model>, <prompt> [, <model_parameters>, <response_format>, <show_details> ]). The model_parameters object holds only the model hyperparameters: temperature, top_p, max_tokens and guardrails. The legacy COMPLETE options object documented the defaults temperature 0, top_p 0, max_tokens 4096 (maximum 8192) and guardrails FALSE, and the migration page says the names and defaults are unchanged. response_format is its own argument and is not recognized inside model_parameters. Set show_details to TRUE to get the detailed JSON object (choices, usage, model and created) instead of a plain string.

SELECT AI_COMPLETE(
    model => '<model>',
    prompt => prompt,
    model_parameters => {'temperature': 0.7, 'max_tokens': 100},
    show_details => TRUE
) AS response
FROM my_table;

This per-row behaviour matters in pipelines: one bad row doesn't abort a large batch. If you need to know *why* a row failed, every syntax variant takes an optional final BOOLEAN argument, return_error_details. When it is TRUE, the function returns an OBJECT with a value field and an error field. One of the two is NULL, depending on whether the call succeeded.

Sources123

3.COMPLETE structured outputs: type literals and JSON schemas

Free-text answers usually need parsing afterwards. With structured output, you give AI_COMPLETE a response_format that the response must follow. AI_COMPLETE checks each generated token against that definition, so the response conforms to your structure. Every model that AI_COMPLETE supports can produce structured output, although the more powerful models typically produce better responses.

There are two ways to define the structure. A type literal uses SQL types: it starts with the TYPE keyword and uses an OBJECT as the top-level type. Snowflake maps STRING/VARCHAR to JSON strings and FIXED types without a scale to JSON integers. The empty OBJECT() isn't allowed, and some types, such as VARIANT, MAP and date/time types, have no mapping and return an error. Type literals work only with the single-string prompt form of AI_COMPLETE.

The optional show_details => TRUE argument returns inference metadata along with the structured output. In the documentation's examples the result is then an object with created, model, structured_output and usage fields, where usage reports the completion, prompt and total token counts.

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)),
     ?  => TRUE
);

For more control, such as constraints and required fields, pass a JSON schema as response_format instead. For simple tasks you don't even need to tell the model to answer in JSON. For complex ones, asking for JSON in the prompt can improve accuracy.

AI_COMPLETE(
    ...
    response_format => {
        'type': 'json',
        'schema': {
            'type': 'object',
            'properties': {
                'property_name': {
                    'type': 'string'
                },
                ...
            },
            'required': ['property_name', ...]
        }
    }
)

Sources4

4.Task-specific functions: pick the managed function for the job

For common jobs, you don't need to write prompts for AI_COMPLETE. Snowflake provides purpose-built managed functions for routine tasks that need no customization. The exam expects you to recognize which function fits which task. Notice which ones take files and which work across many rows.

Arguments and return shapes the sources spell out:

- AI_CLASSIFY takes the input and an array of categories, for example ['refund','shipping','other']. It returns an object whose labels field is an array, so you read :labels[0] (the legacy CLASSIFY_TEXT returned a single :label). The AI_COUNT_TOKENS examples for ai_classify also show categories written as objects with label and description, plus an options object with task_description and examples. - AI_EXTRACT takes the input in text and the questions to answer in the responseFormat argument. It returns a JSON object, not a string. The optional scores argument, set to TRUE, adds confidence scores. It replaces the legacy EXTRACT_ANSWER. - AI_SENTIMENT takes the text and returns an object with a categories array, not the legacy FLOAT score. Each category record has a sentiment field with a value such as positive, negative, neutral, mixed or unknown. Pass entities as a second argument for entity-level (aspect-based) sentiment. - AI_TRANSLATE works on input text with a source language and a target language: the AI_COUNT_TOKENS syntax for ai_translate lists input_text, source_language and target_language. - AI_FILTER takes a text or file input, usually built with PROMPT, and returns a boolean, so it can sit in a WHERE clause. - AI_AGG takes AI_AGG(<expr>, <instruction>) and returns a string. The instruction should be a declarative plain-English description of the data and the goal. It works with GROUP BY, and you can combine several columns with CONCAT or ||. - AI_SIMILARITY computes a similarity score based on the vector cosine similarity of the embeddings of its two inputs. - AI_REDACT detects and redacts PII from unstructured text; the sources give no further argument detail.

SELECT AI_CLASSIFY(review, ['refund','shipping','other']):labels[0] AS topic
FROM tickets;

SELECT AI_EXTRACT(
    text => doc_text,
    responseFormat => {'total': 'What is the invoice total?'},
    scores => TRUE
) AS answer
FROM docs;

Two distinctions show up often. First, AI_AGG and AI_SUMMARIZE_AGG are *aggregate* functions: they combine many rows into one result and aren't limited by the model's context window. AI_SUMMARIZE works on a single input. Second, AI_PARSE_DOCUMENT and AI_TRANSCRIBE read files from a stage rather than text in a column.

The exam guide also lists SUMMARIZE. This is the legacy function in the SNOWFLAKE.CORTEX namespace. It returns a summary of the given text, and it is meant for English-language input (the Snowflake CLI command for it summarizes English-language text). It handles one text value per call, so it is not the multi-row aggregate. The migration guide lists AI_SUMMARIZE as its direct replacement, with arguments and output unchanged. AI_SUMMARIZE takes either text or a file, for example AI_SUMMARIZE(TO_FILE('@my_docs', 'cartoon_image.jpeg')). For a summary across many rows, use AI_SUMMARIZE_AGG.

Sources1356789

5.PROMPT and TO_FILE: passing columns and files to AI functions

Many of the functions above accept files and combinations of columns. Two helper functions prepare those inputs. TO_FILE creates a reference to a file in an internal or external stage, for AI_COMPLETE and other functions that accept files. You pass the stage and the relative path, as in TO_FILE('@my_stage', 'document.pdf'). PROMPT builds a prompt object: a template string with numbered placeholders {0}, {1}, … plus the expressions that fill them. Those expressions can be column values or FILE values.

PROMPT doesn't format the string itself. It returns an OBJECT with template and args keys, and the AI function that receives it uses that object. Using a placeholder that has no matching expression is an error. Passing extra expressions that the template doesn't use is not an error.

SELECT AI_PARSE_DOCUMENT(
    TO_FILE('@my_stage', 'document.pdf'),
    {'mode': 'LAYOUT', 'page_split': true}
) AS parsed_doc;

AI_COMPLETE('claude-4-sonnet',
    PROMPT('Classify the input image {0} in no more than 2 words. Respond in JSON', img_file)) AS image_classification
FROM image_table;

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));

Sources1310

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.A SQL type literal can define structured output for any form of AI_COMPLETE, including image and prompt-object calls.Why is that wrong?

   Type literals work only with the single-string text prompt version of AI_COMPLETE. For other forms, use a JSON schema.

   Covered in COMPLETE structured outputs: type literals and JSON schemas

2. 2.If one row's AI_COMPLETE call fails, the whole multi-row query aborts.Why is that wrong?

   By default, rows with errors return NULL and the query still completes. Pass return_error_details => TRUE to get the error message for each row.

   Covered in AI_COMPLETE: the general-purpose function