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
In a nutshell
  • 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_details TRUE returns value and error.

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.

Raw tablereviewsstreamStreamnew rows onlytaskTaskINSERT SELECT with AI functionswritesEnriched tablelabels, summaries

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

PieceRole in an AI pipeline
Stage and directory tableLanding area for files; TO_FILE builds FILE values for each file
StreamTracks new rows so only new data is sent to the model
TaskRuns the step on a schedule or when the stream has data
TableStores results so the model is called once per row
Cortex Search serviceIndexes 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 WHERE before the AI function so the model sees fewer rows. AI_FILTER returns a Boolean and can itself be used in a WHERE clause, 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_HISTORY shows credits per function call with QUERY_ID, so you can group by query. The older CORTEX_FUNCTIONS_USAGE_HISTORY and CORTEX_FUNCTIONS_QUERY_USAGE_HISTORY views are no longer updated. Costs are token based, and text generation bills both input and output tokens.
Exam tip

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.

Watch out

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_details TRUE returns value and error.
  • Track spend in CORTEX_AI_FUNCTIONS_USAGE_HISTORY; the older CORTEX_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_TRANSLATE return 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_ID to 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.