Data Transformation · Topic 8 of 14
Cortex AI Functions in Data Pipelines
Use Snowflake Cortex AI functions in SQL pipelines to classify records, extract data from documents and audio, run text analytics and multimodal queries.
- 5 min
- Categorize with
AI_CLASSIFY(single label by default,output_mode'multi'for several). - Fields from files:
AI_EXTRACT; text and layout:AI_PARSE_DOCUMENT(OCR or LAYOUT); audio and video:AI_TRANSCRIBE. - Cross row analysis:
AI_AGGandAI_SUMMARIZE_AGG; row filter:AI_FILTER; PII:AI_REDACT.
What it is
Snowflake Cortex AI functions (also called Cortex AISQL functions) are SQL functions that call large language models and other AI models from inside a query. Data stays in Snowflake, and there is no model to host. For a data engineer they are transformation steps: a column goes in, a label, a JSON object, a sentiment or a summary comes out.
Calling them requires the USE AI FUNCTIONS account privilege plus the SNOWFLAKE.CORTEX_USER or SNOWFLAKE.AI_FUNCTIONS_USER database role. USE AI FUNCTIONS and CORTEX_USER are granted to PUBLIC by default; AI_FUNCTIONS_USER is not.
| Job | Function |
|---|---|
| Categorize | AI_CLASSIFY |
| Extract fields from text, images or documents | AI_EXTRACT |
| Get text and layout out of documents | AI_PARSE_DOCUMENT |
| Transcribe audio and video | AI_TRANSCRIBE |
| Score sentiment, translate, summarize, redact | AI_SENTIMENT, AI_TRANSLATE, AI_SUMMARIZE, AI_REDACT |
| Work across rows | AI_AGG, AI_SUMMARIZE_AGG |
| Free form prompt, text or image | AI_COMPLETE |
| Boolean test for WHERE or JOIN | AI_FILTER |
How it works
Categorizing data
AI_CLASSIFY(input, categories [, config]) assigns text, an image or a document to one of the categories you list. The result is an object with a labels array. Single label is the default; set output_mode to 'multi' in the config object to allow several labels.
SELECT ticket_id,
AI_CLASSIFY(body, ['billing', 'outage', 'feature request']):labels[0]::STRING AS category
FROM tickets;
- You need at least two unique, case sensitive categories. Accuracy can drop beyond about 20.
- Each category can carry a short description to steer the model.
- On an error the function returns NULL unless you ask for error details.
For a fixed label set, AI_CLASSIFY is the intended tool. It is more predictable and cheaper than writing a free form AI_COMPLETE prompt and parsing the answer.
Extracting multimedia data
Files are referenced with TO_FILE('@stage', 'path'), which points at a file in an internal or external stage.
AI_EXTRACTtakes a text string or a file and aresponseFormat(questions as key value pairs, a JSON schema, a list, or a table). It returns JSON withresponseanderrorkeys. It reads PDF, Word, PowerPoint, images, HTML, text and more, and can add confidence scores withscores => TRUE.AI_PARSE_DOCUMENTreturns the document content itself.OCRmode extracts text;LAYOUTmode keeps structure such as headings and tables. Use it when you want the whole text for later steps, such as chunking for search.AI_TRANSCRIBEturns audio and video files in a stage into text, with optional timestamps and speaker labels.
SELECT AI_EXTRACT(
file => TO_FILE('@docs_stage', 'invoice_1042.pdf'),
responseFormat => {'vendor': 'Who issued the invoice?', 'total': 'What is the total amount?'}
);
| Need | Choose |
|---|---|
| Specific fields as JSON | AI_EXTRACT |
| Full text or layout of a document | AI_PARSE_DOCUMENT |
| Spoken content | AI_TRANSCRIBE |
Text analytics in pipelines
Because these are plain SQL functions, they fit anywhere SQL runs: a CREATE TABLE AS SELECT, a dynamic table, a stream and task that enriches new rows, or a view.
- Row level:
AI_SENTIMENT,AI_TRANSLATE,AI_SUMMARIZE,AI_REDACT(masks PII),AI_FILTER. - Aggregate:
AI_AGGanswers a prompt over many rows;AI_SUMMARIZE_AGGsummarizes a whole column. They are not limited by a single model context window. AI_FILTERreturns TRUE or FALSE, so it can sit inWHEREor aJOINcondition.
INSERT INTO tickets_enriched
SELECT t.*, AI_SENTIMENT(t.body) AS sentiment
FROM tickets_stream t
WHERE METADATA$ACTION = 'INSERT';
Process only new rows. Re-running an AI function over the whole table on every load repeats the cost and can return different answers.
Multimodal workflows
AI_COMPLETE accepts images as well as text, using TO_FILE and the PROMPT helper to put file references inside the prompt. AI_CLASSIFY, AI_FILTER and AI_EXTRACT also accept images or documents in supported regions. AI_EMBED turns text or images into vectors, AI_MULTI_EMBED also handles audio and video, and AI_SIMILARITY scores the embedding similarity of two inputs.
SELECT AI_COMPLETE('<model name>',
PROMPT('Describe the damage in {0}', TO_FILE('@claims', 'photo1.jpg')));
Model availability and image or document support depend on the region. Cross region inference can be enabled with the account parameter CORTEX_ENABLED_CROSS_REGION, which means data may be processed in another region.
Common mistakes
Using AI_COMPLETE for everything. A purpose built function (AI_CLASSIFY, AI_EXTRACT, AI_SENTIMENT) is simpler and cheaper.
Confusing AI_EXTRACT and AI_PARSE_DOCUMENT. One returns chosen fields, the other returns the document's text and layout.
Re-scoring old rows. Enrich only new or changed data, for example from a stream.
Forgetting the stage reference. Files must be passed with TO_FILE, not as a plain path string.
What to remember for the exam
- Categorize with
AI_CLASSIFY(single label by default,output_mode'multi'for several). - Fields from files:
AI_EXTRACT; text and layout:AI_PARSE_DOCUMENT(OCR or LAYOUT); audio and video:AI_TRANSCRIBE. - Cross row analysis:
AI_AGGandAI_SUMMARIZE_AGG; row filter:AI_FILTER; PII:AI_REDACT. - Files are referenced with
TO_FILE;PROMPTbuilds multimodal prompts forAI_COMPLETE. - Access needs the
USE AI FUNCTIONSprivilege and theCORTEX_USER(orAI_FUNCTIONS_USER) database role.
Check yourself
Which function gives you named fields from a staged invoice PDF as JSON?
AI_EXTRACT with a responseFormat that lists the fields or questions.
How do you let AI_CLASSIFY return more than one label?
Pass a config object with output_mode set to 'multi'.
How do you reference a staged file inside a multimodal AI_COMPLETE call?
Wrap the stage and file path in TO_FILE and place it in a PROMPT.
Topic 8 of 14 in Data Transformation
Test yourself on Data Transformation
The full Snowflake DEA-C02 question bank has 709 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.