What you will be able to do
- Place Cortex AI function calls in pipeline SQL and choose the helper functions (TO_FILE, PROMPT, AI_COUNT_TOKENS) that keep batch runs from failing
- Stage a pipeline as extraction, enrichment and augmentation, and pick the right function for each stage
- Run AI functions inside an incremental dynamic table and explain what target lag means for freshness and cost
- Use the snowflake.cortex and Snowpark Python functions as the DataFrame alternative to SQL transformations
- Find and retry rows that failed inside a batch AI call using NULL results, TRY_COMPLETE and return_error_details
Key concept
Staged Cortex pipeline — A pipeline that calls Cortex AI functions row by row inside ordinary SQL or DataFrame transformations, in three stages: extraction turns raw files into text and fields, enrichment adds labels and signals as new columns, and augmentation adds derived content such as confidence scores, embeddings and chunks.
1.Cortex functions as pipeline SQL
Cortex AI functions are ordinary SQL functions. A pipeline calls them the same way it calls CONCAT or UPPER: inside a SELECT whose result lands in a table. You can also call Cortex from Python and over REST; see Ways to reach Cortex: AI & ML Studio, SQL, Python and REST. For pipelines, the SQL interface is the main route, because these functions are tuned for volume rather than speed. Snowflake recommends them for processing many inputs, such as text from large tables, and points latency-sensitive interactive work to the REST API instead.
Batch runs fail in predictable ways: a file can't be referenced, a prompt is built wrongly, or an input is too long. Snowflake provides helper functions that exist to reduce these failures:
TO_FILEcreates a reference to a file in an internal or external stage.AI_PARSE_DOCUMENT, a multimodalAI_COMPLETEcall and the other file-accepting functions take this reference as their input.PROMPTbuilds prompt objects forAI_COMPLETEand similar functions, so one prompt template can be filled from each row's columns.AI_COUNT_TOKENSreturns the token count of an input for a given model or function.
Run AI_COUNT_TOKENS over the source table before a large job. Rows that exceed the model's context window can then be split or skipped up front, so they don't fail partway through the run. The function itself is covered in AI_COUNT_TOKENS.
Access control is the other thing that fails pipelines. To call any Cortex AI function, a role needs two grants: the account-level USE AI FUNCTIONS privilege, and a database role such as SNOWFLAKE.CORTEX_USER. Both are explained in The two gates: USE AI FUNCTIONS privilege and the SNOWFLAKE.CORTEX_USER database role. In a pipeline, the role that needs them is not the engineer's. Dynamic table refreshes run as the table's owner role with a special SYSTEM user, so the owning role must hold both grants. A query that works in your worksheet can still fail when the scheduled refresh runs.
Checkpoint 1 of 10· Check yourself
An engineer's AI_CLASSIFY query runs fine in their worksheet. They put it in a dynamic table owned by the role PIPELINE_OWNER, which has been granted SNOWFLAKE.CORTEX_USER. The scheduled refresh then fails with a privilege error. Which change fixes the refresh?
Calling a Cortex AI function takes two grants: the account-level USE AI FUNCTIONS privilege and a database role such as CORTEX_USER. Dynamic table refreshes run as the table's owner role, so PIPELINE_OWNER needs both grants, and right now it is missing USE AI FUNCTIONS. The engineer's own role and the SYSTEM user are the wrong targets for the grant, and IMPORTED PRIVILEGES does not control access to Cortex.
“To call any of these functions, 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
Checkpoint 2 of 10· Exam question
A pipeline needs to pass a reference to a file sitting in a stage into a Cortex function such as AI_PARSE_DOCUMENT or a multimodal AI_COMPLETE call. Which helper function constructs that reference?
Correct answer: A — TO_FILE, which creates a file reference usable by stage-based AI functions
- A. TO_FILE builds the file reference object that stage-based AI functions, including document parsing and multimodal completion calls, expect as their input.
- B. TO_VARIANT converts data into Snowflake's semi-structured VARIANT type, it is not the mechanism for referencing a staged file for AI functions.
- C. AI_EXTRACT performs structured extraction from a document but still needs a proper file reference as input, it does not itself construct that reference.
- D. A scoped file URL function is a general Snowflake file-access mechanism, not a requirement specific to every Cortex function, and it is not the documented helper used with AI functions.
Checkpoint 3 of 10· Exam question
Which privilege must a role hold, in addition to an appropriate database role, to call Snowflake Cortex AI functions such as AI_COMPLETE?
Correct answer: A — The account-level USE AI FUNCTIONS privilege
- A. Calling Cortex AI functions requires the account-level USE AI FUNCTIONS privilege, together with a database role such as CORTEX_USER or AI_FUNCTIONS_USER.
- B. CREATE ACCOUNT is an unrelated account-management privilege used for creating new Snowflake accounts, not for invoking AI functions.
- C. IMPORTED PRIVILEGES is a broader privilege historically used for imported shares and databases, it is not the specific gate for Cortex AI function usage.
- D. MANAGE GRANTS allows a role to grant and revoke privileges on behalf of others, it does not itself grant access to call AI functions.
2.Extraction: turning staged files into rows
Extraction is the first stage. It turns raw input sitting in a stage, such as PDFs, scans, slides or audio, into text and fields that later SQL can work on. Nothing downstream can classify or score a document until its content is in a column.
AI_PARSE_DOCUMENT handles full-document text. It reads a staged file through a TO_FILE reference and returns its text, or text plus layout. The OCR and LAYOUT modes and the page_split option are covered in AI_PARSE_DOCUMENT (OCR vs LAYOUT, page_split). In a pipeline, the useful part is the output: a JSON object with page-level content that you write to a table for the enrichment stage to read. LAYOUT is the preferred mode for most documents.
SELECT AI_PARSE_DOCUMENT (
TO_FILE('@docs.doc_stage','research-paper-example.pdf'),
{'mode': 'LAYOUT' , 'page_split': true}) AS research_paper_example;AI_EXTRACT is the tool when you need specific fields rather than the whole text, such as an invoice number, a list of account holders or a table of line items. You describe the fields in a responseFormat, covered in AI_EXTRACT and responseFormat, and the function returns them under a response key. Audio and video have their own extractor: AI_TRANSCRIBE turns staged recordings into text with timestamps and speaker information. After this stage, every pipeline is working with rows of text.
| Stage | Typical functions | What lands in the table |
|---|---|---|
| Extraction | AI_PARSE_DOCUMENT, AI_EXTRACT, AI_TRANSCRIBE | Text, layout or named fields pulled out of staged files |
| Enrichment | AI_CLASSIFY, AI_SENTIMENT, AI_TRANSLATE, AI_FILTER, AI_REDACT | One new column per row: a label, sentiment, translation, boolean flag or redacted text |
| Augmentation | AI_EXTRACT with scores, AI_EMBED, AI_COMPLETE | Derived content: confidence scores, embedding vectors, generated text |
Checkpoint 4 of 10· Fill the gap
The first stage of a pipeline must take each staged PDF and write its full text, split by page and with its layout kept, to a table that the enrichment stage will read. Which function completes this extraction query?
SELECT ? (
TO_FILE('@docs.doc_stage','research-paper-example.pdf'),
{'mode': 'LAYOUT' , 'page_split': true}) AS research_paper_example;AI_PARSE_DOCUMENT reads a staged file through a TO_FILE reference and returns its full text, or text plus layout, with the LAYOUT mode and page_split options. AI_EXTRACT returns only the specific fields you describe in a responseFormat. AI_TRANSCRIBE is for audio and video. AI_COMPLETE generates new text and is not the extraction tool for full-document text.
Source: docs.snowflake.com3.Enrichment: adding labels and signals as columns
Enrichment runs a task-specific function once per row and stores the result as a new column. Examples are a support category, a sentiment value or an English translation. The functions themselves are covered in Task-specific AI functions (AI_CLASSIFY, AI_SENTIMENT, AI_SUMMARIZE_AGG, AI_FILTER, AI_TRANSLATE, etc.). The pipeline decision is choosing the function whose output shape matches the column you need.
Take a team that wants each incoming document routed into one of five support categories it has defined. AI_CLASSIFY is the function for that: you pass the input and an array of category labels, and it returns the label or labels that apply. The other candidates return the wrong shape:
AI_FILTERreturns True or False for one condition. That works as a routing flag, but it can't choose among five labels.AI_EMBEDreturns a vector for similarity search and clustering, not a label your team can read.AI_AGGandAI_SUMMARIZE_AGGaggregate a column across many rows into one result, so they can't label rows one at a time.
WITH food_pictures AS (
SELECT
TO_FILE(file_url) AS img
FROM DIRECTORY(@file_stage)
)
SELECT
*,
AI_CLASSIFY(img, ['dessert', 'drink', 'main dish', 'side dish']):labels AS classification
FROM food_pictures;Classification settings affect cost on every row. Each label, description and few-shot example adds input tokens to every AI_CLASSIFY call. On a million-row table, one extra sentence of label description is paid for a million times. Accuracy also drops in practice once you go past about twenty categories, so a long category list costs more and classifies worse.
For free-form enrichment with AI_COMPLETE, require a schema. With a JSON schema as the response_format, every row comes back in the same structure, so the output can be cast straight into typed, queryable columns. Without one, a few rows out of millions come back in a different shape and break the parsing step. Schemas are covered in Structured outputs: type literals and JSON schemas.
Checkpoint 5 of 10· Match them up
Match each function to what it returns. That return value decides whether the function can produce the enrichment column you need.
Tap a term, then the definition that fits it.
In enrichment, you pick the function whose return value has the shape of the column you need. Only AI_CLASSIFY assigns each row one of your own categories. AI_FILTER returns only a boolean, AI_EMBED returns a vector, and AI_SUMMARIZE_AGG combines many rows into a single result.
“AI_FILTER: Returns True or False for a given text or image input”Source: docs.snowflake.com
Checkpoint 6 of 10· 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.
4.Augmentation: scores, vectors and review queues
Augmentation adds content derived from what the earlier stages produced. Enrichment labels a row. Augmentation attaches something new to it: a confidence score, an embedding vector or text chunks ready for retrieval. These columns serve downstream consumers such as a search service, a feature table or a human reviewer.
AI_EXTRACT confidence scores are a standard example. With scores => TRUE, the result includes a scoring object next to response, holding a value between 0 and 1 for each extracted field. Store those scores in columns beside the extracted values, and a WHERE score < threshold query becomes your human-review queue. Three details matter when building this:
scoresworks only with the named-argument syntax.- Requesting scores costs nothing extra.
- Lists and tables get a single aggregate score. Individual list items and table cells are not scored, so a review rule can flag a whole table but not one cell.
SELECT AI_EXTRACT(
file => TO_FILE('@db.schema.files', 'document.pdf'),
responseFormat => {'name': 'What is the last name of the employee?', 'date': 'What is the inspection date?'},
scores => TRUE
);Retrieval-ready content is the other kind of augmentation. AI_EMBED turns text or images into vectors for similarity search, clustering and classification. Chunking comes first: the split helpers in Text splitting helpers (SPLIT_TEXT_RECURSIVE_CHARACTER, SPLIT_TEXT_MARKDOWN_HEADER) break parsed documents into passages. Put the chunking and embedding inside a dynamic table, and the table a search service reads from stays current as new documents arrive, without re-embedding the whole corpus.
Checkpoint 7 of 10· Check yourself
An invoice pipeline calls AI_EXTRACT with scores => TRUE and pulls out a line_items table along with single fields such as invoice_number. The review team wants a human to check any line-item cell the model was unsure about. What can a WHERE score < 0.7 review rule flag for the line items?
AI_EXTRACT gives a list or table one aggregate score for the whole object. Individual cells are not scored, so the rule can send the whole table to review but cannot point to one cell. Requesting scores also costs nothing extra, so there is no paid per-cell option.
“Per-element scores for individual list items and table cells are not available.”Source: docs.snowflake.com
5.Transformations: AI functions inside dynamic tables
A dynamic table is a common way to run AI functions as an ongoing transformation. You write the AI call into the table's defining query, and Snowflake keeps the result up to date as the source changes. Cortex AI functions are supported in the SELECT clause of dynamic tables in incremental refresh mode. Snowflake's documentation of supported dynamic table queries lists AI_CLASSIFY, AI_COMPLETE, AI_EMBED, AI_EXTRACT and AI_TRANSLATE as supported in the SELECT clause, for both incremental and full refresh.
CREATE OR REPLACE DYNAMIC TABLE dt_review_sentiment
TARGET_LAG = '10 minutes'
WAREHOUSE = transform_wh
REFRESH_MODE = INCREMENTAL
AS
SELECT review, AI_FILTER(CONCAT('The reviewer enjoyed the restaurant', review), {'model': 'llama3.1-70b'}) AS enjoyed FROM reviews;Incremental refresh is what keeps this affordable. An incremental refresh processes only the rows that changed since the last refresh and merges the results into the table. A review that has already been scored is not sent to the model again. Snowflake's ADAPTIVE refresh mode is built around the same cost: when a definition contains Cortex AI functions, it usually skips rebuilding the table from scratch, because re-running those functions over every row costs more than a fresh build saves.
The AI call must stay in the SELECT list. AI functions in WHERE, GROUP BY, HAVING or QUALIFY are not supported for incremental refresh. If you need the model's output as a filter, compute it as a column in one dynamic table and filter on that column in a downstream table.
Target lag sets how fresh the data must be, and freshness has a price. Snowflake schedules refreshes to keep the table within the lag, and each refresh runs on the named warehouse. A tight lag therefore means more frequent, smaller refreshes, and a loose lag means fewer, larger ones. The minimum is 60 seconds. With incremental refresh, the token cost of the AI calls follows the number of new rows, not the number of refreshes. Set the lag from how stale downstream consumers can tolerate the labels being. A review dashboard checked hourly gains nothing from a one-minute lag.
Checkpoint 8 of 10· Check yourself
A dynamic table in INCREMENTAL refresh mode uses AI_CLASSIFY in its SELECT list to label new support tickets, with TARGET_LAG = '1 hour'. The number of new tickets per day stays the same. Which change will raise the token cost of the AI calls?
With incremental refresh, each new ticket goes to the model once, so token cost follows the number of new rows, not how often the table refreshes. Label descriptions are sent with every AI_CLASSIFY call, so they add input tokens to every row. A downstream filter on the stored label makes no new AI calls. Warehouse size changes compute cost, not token cost.
“Each label, description, and example increases the number of input tokens for every AI_CLASSIFY call, which affects cost.”Source: docs.snowflake.com
6.Python DataFrames as the alternative to SQL
Teams whose transformations are written in Snowpark Python can call Cortex from the DataFrame API instead of SQL. The AI functions appear in snowflake.snowpark.functions under snake_case names such as ai_classify, ai_complete, ai_filter and ai_agg. The older functions, such as summarize, sentiment, translate and complete, come from the snowflake.cortex module in Snowflake ML. Either way, the call becomes a column expression, so it adds a column the same way a SQL SELECT does.
from snowflake.cortex import summarize
from snowflake.snowpark.functions import col
article_df = session.table("articles")
article_df = article_df.withColumn(
"abstract_summary",
summarize(col("abstract_text"))
)
article_df.collect()Choose Python when the surrounding pipeline is already DataFrame code, or when the prompt has to be assembled in code; the Snowpark prompt function fills a template from column values. Choose SQL when the transformation should live in a dynamic table, because the AI call has to sit in the table's defining query. One gap: the multi-message, chat-style form of complete is not supported in Snowflake ML Python.
Checkpoint 9 of 10· Check yourself
Your team's nightly pipeline is written as Snowpark Python DataFrame transformations. It now needs a sentiment column on a large reviews table, and nothing else in the pipeline uses SQL. What is the best way to add it?
Cortex AI functions can be called from SQL and also from Python. In Snowpark, the call becomes a column expression on the DataFrame, so a pipeline that is already DataFrame code can stay in Python. The REST API is meant for latency-sensitive interactive work, not batch processing of large tables. Collecting rows to the client gives up the in-Snowflake batch processing that these functions are optimized for.
“Snowflake Cortex features are provided as SQL functions and are also available in Python.”Source: docs.snowflake.com
Sources10
7.When some rows fail: TRY_ variants and NULL results
In a batch, the question is whether one bad row takes the whole statement down with it. With the legacy COMPLETE function it does: an input it can't process raises an error, and the statement fails. SNOWFLAKE.CORTEX.TRY_COMPLETE exists to prevent that. It does the same work as COMPLETE but returns NULL for that row instead of raising an error, so the rest of the batch completes. Like the other legacy functions, it is due to be deprecated by the end of 2026.
The current AI_ functions behave like the TRY_ variant by default. AI_COMPLETE, AI_CLASSIFY and AI_FILTER all return NULL for an input they can't process, and failing rows don't stop a multi-row query. For new pipelines, AI_COMPLETE replaces COMPLETE and TRY_COMPLETE.
A NULL tells you that a row failed but not why. For that, pass return_error_details as TRUE in the final argument. The function then returns an OBJECT with two fields, value and error, and exactly one of them is NULL. Store both. The retry loop then becomes a query: select the rows whose result is NULL, read the error text to decide whether the input needs splitting (check it with AI_COUNT_TOKENS) or just needs to be run again, and process only those rows. The two million rows that succeeded are never sent to the model a second time.
A failed statement writes nothing, so one bad row costs the whole night's run and the rerun pays for every row again. With NULL-on-failure, the good rows are saved and charged once, and the bad ones are easy to find with a WHERE result IS NULL query and retry on their own.
Checkpoint 10 of 10· Match them up
A batch job sends millions of rows to a model, and some inputs can't be processed. Match each way of calling the model to how it handles a row that fails.
Tap a term, then the definition that fits it.
Only the legacy COMPLETE function lets one bad input fail the whole statement. TRY_COMPLETE was created to avoid that, and AI_COMPLETE now returns NULL for failing rows by default. Passing return_error_details as TRUE adds the error message, so a retry can tell an input that needs splitting from one that just needs to be run again.
“If the query processes multiple rows, rows with errors return NULL and don’t prevent the query from completing.”Source: docs.snowflake.com
Sources
(11)
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.”
↩︎ Cortex functions as pipeline SQL“For more interactive use cases where latency is important, use the REST API.”
↩︎ Cortex functions as pipeline SQL“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.”
↩︎ Cortex functions as pipeline SQL“PROMPT: Helps you build prompt objects for use with AI_COMPLETE and other functions.”
↩︎ Cortex functions as pipeline SQL“Helper functions are purpose-built managed functions that reduce cases of failures when running other Cortex AI Functions”
↩︎ Cortex functions as pipeline SQL“To call any of these functions, your role needs the USE AI FUNCTIONS account-level privilege and one of the CORTEX_USER or AI_FUNCTIONS_USER database roles.”
↩︎ Cortex functions as pipeline SQL“AI_TRANSCRIBE: Transcribes audio and video files stored in a stage, extracting text, timestamps, and speaker information.”
↩︎ Extraction: turning staged files into rows“AI_FILTER: Returns True or False for a given text or image input”
↩︎ Enrichment: adding labels and signals as columns“AI_AGG: Aggregates a text column and returns insights across multiple rows based on a user-defined prompt.”
↩︎ Enrichment: adding labels and signals as columns“AI_EMBED: Generates an embedding vector for a text or image input, which can be used for similarity search, clustering, and classification tasks.”
↩︎ Augmentation: scores, vectors and review queues“your role needs the USE AI FUNCTIONS account-level privilege and one of the CORTEX_USER or AI_FUNCTIONS_USER database roles.”
↩︎ Exam trap 1“AI_CLASSIFY: Classifies text or images into user-defined categories.”
↩︎ Exam trap 2“Snowflake Cortex features are provided as SQL functions and are also available in Python.”
↩︎ Checkpoint - 2.
“Dynamic table refreshes act as their owner role with a special SYSTEM user.”
↩︎ Cortex functions as pipeline SQL“You can use Snowflake Cortex AI Functions (including LLM functions) in the SELECT clause for dynamic tables in incremental refresh mode.”
↩︎ Transformations: AI functions inside dynamic tables“AI_COMPLETE | Supported in the SELECT clause | Supported”
↩︎ Transformations: AI functions inside dynamic tables“Placing them in WHERE, GROUP BY, HAVING, or QUALIFY clauses is not supported.”
↩︎ Exam trap 3 - 3.
“LAYOUT mode is the preferred choice for most use cases, especially for complex documents.”
↩︎ Extraction: turning staged files into rows“It can be combined with other Cortex AI functions to build advanced workflows for extraction, classification, enrichment, and analysis.”
↩︎ Key concept - 4.
“It can be used with other functions to create custom document processing pipelines for a variety of use cases”
↩︎ Extraction: turning staged files into rows - 5.
“Each label, description, and example increases the number of input tokens for every AI_CLASSIFY call, which affects cost.”
↩︎ Enrichment: adding labels and signals as columns“in practice, exceeding twenty categories might reduce classification accuracy.”
↩︎ Enrichment: adding labels and signals as columns“Each label, description, and example increases the number of input tokens for every AI_CLASSIFY call, which affects cost.”
↩︎ Checkpoint“If the query processes multiple rows, rows with errors return NULL and don’t prevent the query from completing.”
↩︎ Prediction - 6.
“response_format: A JSON schema that the response should follow.”
↩︎ Enrichment: adding labels and signals as columns“Performs the same operation as the COMPLETE function but returns NULL instead of raising an error when the operation cannot be performed.”
↩︎ When some rows fail: TRY_ variants and NULL results“This legacy function will be deprecated by the end of 2026.”
↩︎ When some rows fail: TRY_ variants and NULL results - 7.
“You can use these scores to set thresholds for business logic, such as flagging low-scoring extractions for human review.”
↩︎ Augmentation: scores, vectors and review queues“Requesting scores does not incur additional cost.”
↩︎ Augmentation: scores, vectors and review queues“Supported only in the named-argument syntax shown above.”
↩︎ Augmentation: scores, vectors and review queues“Per-element scores for individual list items and table cells are not available.”
↩︎ Exam trap 4“Per-element scores for individual list items and table cells are not available.”
↩︎ Checkpoint - 8.
“An incremental refresh analyzes only the data that changed since the last refresh and merges those changes into the dynamic table.”
↩︎ Transformations: AI functions inside dynamic tables“the heuristic typically skips reinitialization because the cost of re-executing those operators across all rows outweighs the benefit of a fresh build.”
↩︎ Transformations: AI functions inside dynamic tables - 9.
“The minimum target lag is 60 seconds.”
↩︎ Transformations: AI functions inside dynamic tables“Target lag is a staleness target, not a refresh interval.”
↩︎ Prediction - 10.
“You can call an AI function on a table column, as shown below.”
↩︎ Python DataFrames as the alternative to SQL“Note that the functions in Snowpark Python have names in Pythonic “snake_case” format”
↩︎ Python DataFrames as the alternative to SQL“The advanced chat-style (multi-message) form of COMPLETE is not currently supported in Snowflake ML Python.”
↩︎ Python DataFrames as the alternative to SQL - 11.
“When set to TRUE, the function returns an OBJECT that contains the value and the error message”
↩︎ When some rows fail: TRY_ variants and NULL results“AI_COMPLETE is the updated version of COMPLETE.”
↩︎ When some rows fail: TRY_ variants and NULL results“By default, if AI_COMPLETE can’t process the input, the function returns NULL.”
↩︎ Exam trap 5“If the query processes multiple rows, rows with errors return NULL and don’t prevent the query from completing.”
↩︎ Checkpoint