CertSafari
    Snowflake SnowPro Specialty: Gen AI (GES-C02)· Lessons

    Domain 2 · Lesson 6/15

    Cortex AI functions in data pipelines: extract, enrich, augment

    Apply Snowflake Cortex functions in data pipelines.

    21 min read
    7.6% of exam
    11 sources
    Published 21 Oct 2026
    Docs as of 4 Oct 2026

    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_FILE creates a reference to a file in an internal or external stage. AI_PARSE_DOCUMENT, a multimodal AI_COMPLETE call and the other file-accepting functions take this reference as their input.
    • PROMPT builds prompt objects for AI_COMPLETE and similar functions, so one prompt template can be filled from each row's columns.
    • AI_COUNT_TOKENS returns 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?

    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?

    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?

    Sources12

    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.

    Extraction from a staged file: TO_FILE builds the reference, AI_PARSE_DOCUMENT returns page-split Markdownsql
    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.

    The three pipeline stages and the Cortex functions that do the work in each
    StageTypical functionsWhat lands in the table
    ExtractionAI_PARSE_DOCUMENT, AI_EXTRACT, AI_TRANSCRIBEText, layout or named fields pulled out of staged files
    EnrichmentAI_CLASSIFY, AI_SENTIMENT, AI_TRANSLATE, AI_FILTER, AI_REDACTOne new column per row: a label, sentiment, translation, boolean flag or redacted text
    AugmentationAI_EXTRACT with scores, AI_EMBED, AI_COMPLETEDerived 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;

    Sources341

    3.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_FILTER returns True or False for one condition. That works as a routing flag, but it can't choose among five labels.
    • AI_EMBED returns a vector for similarity search and clustering, not a label your team can read.
    • AI_AGG and AI_SUMMARIZE_AGG aggregate a column across many rows into one result, so they can't label rows one at a time.
    Per-row enrichment over a stage directory: TO_FILE references each image and AI_CLASSIFY adds a label columnsql
    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.

    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?

    Sources156

    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:

    • scores works 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.
    Requesting per-field confidence scores alongside extracted valuessql
    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?

    Sources71

    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.

    A dynamic table that runs AI_FILTER on each review and refreshes incrementallysql
    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?

    Sources289

    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.

    Enrichment as a DataFrame transformation: snowflake.cortex summarize adds a column to a Snowpark tablepython
    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?

    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.

    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.

    Sources611

    Sources

    (11)

    Every claim above is drawn from one of these pages, quoted as it was written on the date shown.

    1. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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

    Ready to test yourself?

    Practise the 27 questions on this subdomain.

    Spotted a mistake, or was something unclear? Tell us.