What you will be able to do
- State the privilege and database roles a role needs before a pipeline can call Cortex AI Functions
- Explain why AI Functions suit batch pipelines and when to use the REST API instead
- Use TO_FILE, AI_COUNT_TOKENS and PROMPT to make pipeline calls more reliable
- Predict how AI_COMPLETE and AI_CLASSIFY behave when some rows in a batch fail, and use return_error_details to capture the errors
- Place AI Functions inside dynamic table transformations and call them from the snowflake.cortex Python module
Key concept
AI Functions as batch SQL transformations — Cortex AI Functions are ordinary SQL (and Python) functions that you apply to columns, row by row or across rows. They are built for throughput, so the natural place for them is a set-based pipeline step over a large table, not a one-off interactive call.
1.The SQL interface: who can call it and where it fits
Every Cortex AI Function is exposed as a SQL function, and the same features are available in Python. In a pipeline, that means an LLM step looks like any other column expression: AI_CLASSIFY(text_col, [...]) in a SELECT, AI_FILTER(...) in a WHERE clause, or AI_SUMMARIZE_AGG(...) in a GROUP BY. The task-specific functions are described as "purpose-built managed functions" for routine work that needs no customization, while AI_COMPLETE is the general-purpose choice for most generative tasks.
Before any of this runs, the executing role needs two things. The first is an account-level privilege, USE AI FUNCTIONS. The second is one of two database roles, CORTEX_USER or AI_FUNCTIONS_USER. A pipeline that runs as a service role fails if the privilege was granted only to a developer's own role.
The trade-off is throughput against latency. Snowflake recommends AI Functions for processing "numerous inputs such as text from large SQL tables". That is exactly what a pipeline does. For latency-sensitive work, it points to the REST endpoints: the Complete API, the Embed API and the Agents API.
Checkpoint 1 of 5· Check yourself
A scheduled pipeline runs as role ETL_ROLE, which has the SNOWFLAKE.CORTEX_USER database role. Calls to AI_COMPLETE still fail with a privilege error. What is most likely missing?
The role needs the account-level USE AI FUNCTIONS privilege and either one of the two database roles. It does not need both database roles.
“your role needs the USE AI FUNCTIONS account-level privilege and one of the CORTEX_USER or AI_FUNCTIONS_USER database roles”Source: docs.snowflake.com
Sources1
2.Helper functions that keep batch calls from failing
A pipeline processes thousands of inputs it has never seen, so some of them will be too long, malformed, or stored as files rather than text. Snowflake provides a separate group of helper functions for these cases. They exist to "reduce cases of failures when running other Cortex AI Functions", for example by counting tokens before a call so that it stays within a model's limit.
| Helper | What it does | Pipeline use |
|---|---|---|
| TO_FILE | Creates a reference to a file in an internal or external stage | Pass staged PDFs or images into AI_COMPLETE, AI_PARSE_DOCUMENT, AI_CLASSIFY and other functions that accept files |
| AI_COUNT_TOKENS | Returns the token count of an input text for a specified model or Cortex function | Pre-screen long inputs before calling the model |
| PROMPT | Builds prompt objects for AI_COMPLETE and other functions | Assemble dynamic prompts, such as text plus several images |
Context limits also shape which function you choose for aggregation. If you want one answer across many rows, such as "what are customers complaining about this week?", concatenating every ticket into a single AI_COMPLETE prompt runs into the model's window. AI_AGG (custom prompt) and AI_SUMMARIZE_AGG (summary) aggregate a text column across rows, and both are documented as not subject to context window limitations. AI_SUMMARIZE, by contrast, summarizes one input at a time: text, images or documents.
Checkpoint 2 of 5· Exam question
A pipeline stages incoming support documents in an internal stage. A stream tracks new file arrivals, and a task wakes when the stream has data to classify each new document into one of five support categories before routing it downstream. Which function should the task's SQL call to assign each document to a category label defined by the team?
Correct answer: A — AI_CLASSIFY, passing the document text and the list of candidate category labels
- A. AI_CLASSIFY assigns free text or images into user-supplied categories, exactly matching the routing-by-label requirement here.
- B. AI_EMBED produces embeddings for similarity search or clustering, not a discrete category label out of the box.
- C. AI_FILTER returns booleans for filtering, not a multi-way category assignment.
- D. AI_AGG aggregates text content across rows into a single output, it doesn't label individual documents.
Checkpoint 3 of 5· Match them up
Match each function to the pipeline job it is designed for
Tap a term, then the definition that fits it.
The first three are helper functions. AI_SUMMARIZE_AGG is the aggregate function that escapes context window limits.
“AI_COUNT_TOKENS: Given an input text, returns the token count based on the model or Cortex function specified.”Source: docs.snowflake.com
Sources1
3.What happens when some rows fail
This per-row behaviour is what makes AI Functions usable at scale. One bad document doesn't kill the run. The downside is that failures are silent: a NULL in the output column could mean the input was empty, or it could mean the model call failed. The AI_COMPLETE and AI_CLASSIFY reference pages document the same contract.
To tell these cases apart, pass the optional return_error_details BOOLEAN as the final argument. Every AI_COMPLETE syntax variation accepts it, and AI_CLASSIFY accepts it too. With the flag set, the function returns an OBJECT instead of a bare value.
| return_error_details | Return value on success | Return value on error |
|---|---|---|
| FALSE or not passed | The completion / classification result | NULL |
| TRUE | OBJECT with value populated and error NULL | OBJECT with value NULL and error holding a VARCHAR message |
Checkpoint 4 of 5· Check yourself
A pipeline engineer wants to write the failure reason for each unprocessable row into an audit column, while still letting the AI_CLASSIFY step complete for every other row. What should they do?
By default, a failed row is just NULL. With return_error_details set to TRUE, the function returns an OBJECT whose error field carries the message.
“When set to TRUE, the function returns an OBJECT that contains the value and the error message”Source: docs.snowflake.com
Sources2
4.Data transformations: dynamic tables and the Python module
Because AI Functions are SQL expressions, they can live inside declarative transformation layers. Dynamic tables explicitly support them: you can call Cortex AI Functions in the SELECT clause of a dynamic table that uses incremental refresh mode. Only new or changed source rows are then sent through the model on each refresh, rather than the whole table. The regional and model availability rules that apply to the functions elsewhere also apply inside a dynamic table.
The documentation names the SELECT clause specifically. Treat AI calls as projected columns (an enrichment column, a classification label, a summary) rather than as predicates. The quoted support statement doesn't cover other clauses.
For Python pipelines, the functions are available through the snowflake.cortex package. Snowflake ML contains the older AI Functions, the ones whose names don't begin with "AI", rendered in snake_case. A script running outside Snowflake must first create a Snowpark session.
print(complete("llama3.1-8b", "how do snowflakes get their unique patterns?"))
print(extract_answer(text, "When was snowflake founded?"))
print(sentiment("I really enjoyed this restaurant. Fantastic service!"))
print(summarize(text))
print(translate(text, "en", "fr"))Checkpoint 5 of 5· Check yourself
You want a dynamic table in incremental refresh mode to add an AI_SENTIMENT score to each new review. Where does the documentation say the AI function can go?
Snowflake documents support for Cortex AI Functions in the SELECT clause of dynamic tables that use incremental refresh mode.
“You can use Snowflake Cortex AI Functions (including LLM functions) in the SELECT clause for dynamic tables in incremental refresh mode.”Source: docs.snowflake.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.If one row's input can't be processed, an AI_COMPLETE batch query fails and must be rerun.Why is that wrong?
By default, the failing row returns NULL and the query completes. Use return_error_details to see why the row failed.
Covered in What happens when some rows fail
2.To summarize thousands of rows into one answer, you must chunk them yourself to fit the model's context window.Why is that wrong?
AI_SUMMARIZE_AGG (and AI_AGG) aggregate a text column across rows and are documented as not subject to context window limitations.
Covered in Helper functions that keep batch calls from failing
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Snowflake Cortex features are provided as SQL functions and are also available in Python.”
↩︎ The SQL interface: who can call it and where it fits“your role needs the USE AI FUNCTIONS account-level privilege and one of the CORTEX_USER or AI_FUNCTIONS_USER database roles”
↩︎ The SQL interface: who can call it and where it fits“Helper functions are purpose-built managed functions that reduce cases of failures when running other Cortex AI Functions”
↩︎ Helper functions that keep batch calls from failing“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.”
↩︎ Helper functions that keep batch calls from failing“PROMPT: Helps you build prompt objects for use with AI_COMPLETE and other functions.”
↩︎ Helper functions that keep batch calls from failing“Cortex AI Functions are optimized for throughput.”
↩︎ Key concept“AI_SUMMARIZE_AGG: Aggregates a text column and returns a summary across multiple rows. This function isn’t subject to context window limitations.”
↩︎ Exam trap 2“Batch processing is typically better suited for AI Functions. For more interactive use cases where latency is important, use the REST API.”
↩︎ Prediction“AI_COUNT_TOKENS: Given an input text, returns the token count based on the model or Cortex function specified.”
↩︎ Checkpoint - 2.
“All syntax variations accept an optional return_error_details BOOLEAN argument as the final parameter.”
↩︎ What happens when some rows fail“If the query processes multiple rows, rows with errors return NULL and don’t prevent the query from completing.”
↩︎ Exam trap 1“If the query processes multiple rows, rows with errors return NULL and don’t prevent the query from completing.”
↩︎ Prediction - 3.
“The same availability restrictions as described in Cortex AI functions apply.”
↩︎ Data transformations: dynamic tables and the Python module“You can use Snowflake Cortex AI Functions (including LLM functions) in the SELECT clause for dynamic tables in incremental refresh mode.”
↩︎ Checkpoint - 4.
“If you run your Python script outside of Snowflake, you must create a Snowpark session to use these functions.”
↩︎ Data transformations: dynamic tables and the Python module
Also cited
“When set to TRUE, the function returns an OBJECT that contains the value and the error message”
↩︎ Checkpoint