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
In a nutshell
  • 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_AGG and AI_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.

JobFunction
CategorizeAI_CLASSIFY
Extract fields from text, images or documentsAI_EXTRACT
Get text and layout out of documentsAI_PARSE_DOCUMENT
Transcribe audio and videoAI_TRANSCRIBE
Score sentiment, translate, summarize, redactAI_SENTIMENT, AI_TRANSLATE, AI_SUMMARIZE, AI_REDACT
Work across rowsAI_AGG, AI_SUMMARIZE_AGG
Free form prompt, text or imageAI_COMPLETE
Boolean test for WHERE or JOINAI_FILTER
StagePDFs, audio, imagesTO_FILEAI functionAI_EXTRACT, AI_TRANSCRIBEJSON or textStaging tableVARIANT outputparseCurated tableRaw rowssupport ticketsAI_CLASSIFY, AI_SENTIMENTEnriched tablelabel and score columns

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.
Exam tip

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_EXTRACT takes a text string or a file and a responseFormat (questions as key value pairs, a JSON schema, a list, or a table). It returns JSON with response and error keys. It reads PDF, Word, PowerPoint, images, HTML, text and more, and can add confidence scores with scores => TRUE.
  • AI_PARSE_DOCUMENT returns the document content itself. OCR mode extracts text; LAYOUT mode keeps structure such as headings and tables. Use it when you want the whole text for later steps, such as chunking for search.
  • AI_TRANSCRIBE turns 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?'}
);
NeedChoose
Specific fields as JSONAI_EXTRACT
Full text or layout of a documentAI_PARSE_DOCUMENT
Spoken contentAI_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_AGG answers a prompt over many rows; AI_SUMMARIZE_AGG summarizes a whole column. They are not limited by a single model context window.
  • AI_FILTER returns TRUE or FALSE, so it can sit in WHERE or a JOIN condition.
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')));
Watch out

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_AGG and AI_SUMMARIZE_AGG; row filter: AI_FILTER; PII: AI_REDACT.
  • Files are referenced with TO_FILE; PROMPT builds multimodal prompts for AI_COMPLETE.
  • Access needs the USE AI FUNCTIONS privilege and the CORTEX_USER (or AI_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.