Snowflake Gen AI Functions · Topic 23 of 26
Applying Snowflake Cortex Functions in Data Pipelines with the SQL Interface
Run Cortex AI functions inside Snowflake data pipelines with plain SQL, streams and tasks, controlling cost, errors and re-processing at scale.
- 4 min
- Cortex AI functions are SQL functions that run per row inside a query.
- Use streams and tasks so each row is processed once; store results in a table.
- Failed rows usually return NULL;
return_error_detailsTRUE returnsvalueanderror.
What it is
A data pipeline moves data from a source to a target and changes it on the way. Cortex AI functions are ordinary SQL functions, so they can sit inside any SELECT of a pipeline step. The data is analyzed where it lives, with no export to an outside service.
This is the SQL interface: AI_COMPLETE, AI_CLASSIFY, AI_EXTRACT, AI_TRANSLATE and the others are called in a query, and Snowflake runs the model for every row the query touches.
How it works
AI functions are row level SQL
INSERT INTO reviews_enriched
SELECT review_id,
review_text,
AI_SENTIMENT(review_text) AS sentiment,
AI_CLASSIFY(review_text, ['shipping', 'quality', 'price']) AS topic,
AI_TRANSLATE(review_text, '', 'en') AS review_en
FROM reviews_raw_stream;
Each function returns a value you store in a column. The empty string as source_language in AI_TRANSLATE auto detects the language. AI_CLASSIFY takes at least two unique categories and returns an object whose labels array holds the match. Set output_mode to 'multi' for several labels. AI_SENTIMENT always returns an overall entry and can score extra aspects you list.
Pipeline building blocks
| Piece | Role in an AI pipeline |
|---|---|
| Stage and directory table | Landing area for files; TO_FILE builds FILE values for each file |
| Stream | Tracks new rows so only new data is sent to the model |
| Task | Runs the step on a schedule or when the stream has data |
| Table | Stores results so the model is called once per row |
| Cortex Search service | Indexes enriched text for retrieval |
Snowflake's own AI function guidance explains the fit: these SQL functions are optimized for throughput, so batch processing over large tables is the intended use.
Control cost and re-processing
- Process each row once. Write results to a table. Do not wrap AI functions in a view that re-runs the model on every query.
- Use a stream so each run handles only new or changed rows.
- Filter early. Apply
WHEREbefore the AI function so the model sees fewer rows.AI_FILTERreturns a Boolean and can itself be used in aWHEREclause, using natural language as the condition. - Choose the smallest model that works for each step.
- Warehouse size. MEDIUM or smaller is enough, because a bigger warehouse does not make the model faster.
- Track credits.
SNOWFLAKE.ACCOUNT_USAGE.CORTEX_AI_FUNCTIONS_USAGE_HISTORYshows credits per function call withQUERY_ID, so you can group by query. The olderCORTEX_FUNCTIONS_USAGE_HISTORYandCORTEX_FUNCTIONS_QUERY_USAGE_HISTORYviews are no longer updated. Costs are token based, and text generation bills both input and output tokens.
A scenario that says "the nightly job re-classifies every review and the bill keeps rising" wants a stream or a processed flag, so each row is classified once.
Handle errors without stopping the load
For functions such as AI_TRANSLATE, AI_CLASSIFY and AI_SENTIMENT, a row that fails in a multi row query returns NULL and the statement continues. Check each function page, because AI_REDACT fails the query by default. If you need to know why, set return_error_details to TRUE. The function then returns an OBJECT with value and error fields.
SELECT r.id,
r.res:value::STRING AS translation,
r.res:error::STRING AS error_msg
FROM (SELECT id, AI_TRANSLATE(text, 'fr', 'en', TRUE) AS res FROM docs) r;
Store the error so failed rows can be retried later. AI_EXTRACT is different: with cross region inference a bad file puts an error in that document's error field and the statement continues, while without it a bad file fails the statement.
Files in a pipeline
A common pipeline for documents is: new files land on a stage, a directory table lists them, AI_PARSE_DOCUMENT or AI_EXTRACT runs over each TO_FILE value, and the result lands in a table. Check each function page for the stage types and encryption it supports.
Token limits
Check input size with AI_COUNT_TOKENS before a long input goes to a model. AI_AGG and AI_SUMMARIZE_AGG aggregate across many rows without being subject to the context window.
Common mistakes
Putting AI functions in a view that business users query often. Every query calls the model again.
Ignoring NULLs. A failed call is usually a NULL, not an error. Check for them after the load.
Reprocessing the whole table every run. Use a stream.
Using a very large model for simple tagging. It costs more with no accuracy gain.
CORTEX_AI_FUNCTIONS_USAGE_HISTORY is in the SNOWFLAKE shared database. A role needs imported privileges on that database to read it.
What to remember for the exam
- Cortex AI functions are SQL functions that run per row inside a query.
- Use streams and tasks so each row is processed once; store results in a table.
- Failed rows usually return NULL;
return_error_detailsTRUE returnsvalueanderror. - Track spend in
CORTEX_AI_FUNCTIONS_USAGE_HISTORY; the olderCORTEX_FUNCTIONS_*views are no longer updated. - Batch with SQL functions; use REST APIs for low latency.
Check yourself
How do you stop a pipeline from re-running the model on old rows?
Read new rows with a stream and write results to a table.
What does an AI function such as
AI_TRANSLATEreturn for a row that fails in a multi row query?NULL, and the query continues.
Which view shows AI function credits per call, with a
QUERY_IDto group by query?CORTEX_AI_FUNCTIONS_USAGE_HISTORY.
Topic 23 of 26 in Snowflake Gen AI Functions
Test yourself on Snowflake Gen AI Functions
The full Snowflake GES-C02 question bank has 585 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.