Snowflake Document Processing · Topic 5 of 7

Automated Document Pipelines - Orchestration with Streams and Tasks

Automate document processing in Snowflake: directory tables, streams on stages, tasks with SYSTEM$STREAM_HAS_DATA, serverless tasks and privileges.

  • 5 min
In a nutshell
  • Pipeline: stage with directory table, stream on stage, task with SYSTEM$STREAM_HAS_DATA, AI function, results table.
  • CREATE STREAM ... ON STAGE tracks directory table changes. Filter METADATA$ACTION = 'INSERT'.
  • Tasks start suspended. RESUME turns them on. EXECUTE TASK tests one run.

What it is

A document pipeline processes each new file once, without anyone running a query by hand. Snowflake builds it from three parts: a directory table that lists the files on a stage, a stream that records which files are new, and a task that runs the Cortex AI function on those files and stores the result.

The exam asks how the parts connect and which setting makes the pipeline fire only when there is work to do.

New filelands on stagerefreshDirectory tablerecords changeStreamon the stagechecked byTaskWHEN stream has datacallsAI_PARSE_DOCUMENT or AI_EXTRACTinsertResults table

How it works

Orchestration of Snowflake tooling

Orchestration means each tool does one job and hands off to the next.

ToolJob
Stage with directory tableStores files and lists them
StreamTracks new or changed files since the last consumption
TaskRuns SQL on a schedule or when a stream has data
Cortex AI functionParses or extracts, inside the task SQL
Target tableKeeps the structured results

A task graph can chain several tasks, for example one to parse and one to extract. Tasks can run SQL, stored procedures and Snowflake Scripting.

Streams on a stage

A stream on a stage records changes to the stage's directory table.

CREATE OR REPLACE STAGE docs.public.inbox
  ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE')
  DIRECTORY = (ENABLE = TRUE);

CREATE OR REPLACE STREAM docs.public.inbox_stream ON STAGE docs.public.inbox;

Points the exam likes:

  • The stage must have a directory table enabled, because the stream reads it.
  • The stream only sees files after the directory table is refreshed. On an internal stage run ALTER STAGE ... REFRESH after uploading. External stages can use automated refresh.
  • Rows have a METADATA$ACTION column, so filter for 'INSERT' to get new files.
  • Reading the stream in a plain SELECT does not move its offset. A DML statement that uses it, such as an INSERT in the task, consumes it.

Tasks

A task wraps the SQL and decides when it runs.

CREATE OR REPLACE TASK docs.public.parse_new_files
  WAREHOUSE = my_wh
  SCHEDULE = '5 MINUTES'
  WHEN SYSTEM$STREAM_HAS_DATA('docs.public.inbox_stream')
AS
  INSERT INTO docs.public.parsed (file_name, content)
  SELECT relative_path,
         AI_PARSE_DOCUMENT(TO_FILE('@docs.public.inbox', relative_path), {'mode': 'LAYOUT'})
  FROM docs.public.inbox_stream
  WHERE METADATA$ACTION = 'INSERT';

ALTER TASK docs.public.parse_new_files RESUME;

Key facts:

  • WHEN SYSTEM$STREAM_HAS_DATA('stream') runs the task only when the stream contains change records, so an empty inbox costs no compute for the task body.
  • A task is created suspended. Nothing runs until ALTER TASK ... RESUME.
  • EXECUTE TASK starts one manual run, useful for testing.
  • A schedule and a WHEN condition can be combined, and the body only runs when the condition is true at a scheduled time.
  • SUSPEND_TASK_AFTER_NUM_FAILURES suspends a task after consecutive failed runs. TASK_AUTO_RETRY_ATTEMPTS adds automatic retries and is disabled by default.

Compute and privileges

ChoiceHowOwner role needs
User managedSet WAREHOUSE = ...USAGE on that warehouse
ServerlessOmit WAREHOUSEEXECUTE MANAGED TASK

In both cases the owner role needs the account level EXECUTE TASK privilege, plus CREATE TASK on the schema and USAGE on the database and schema. The role that runs the AI function also needs the SNOWFLAKE.CORTEX_USER database role, and access to the stage and the stream.

Use a warehouse no larger than MEDIUM for Cortex document functions, since bigger warehouses do not speed them up.

Chaining AI steps

A second task or a later statement can read the parsed text and extract fields, classify the document with AI_CLASSIFY, or split text into chunks for a Cortex Search service. Keeping parsing and extraction in separate steps lets you re run extraction without paying to parse again.

Streamnew filestask 1AI_PARSE_DOCUMENTLAYOUTstoresParsed tableMarkdowntask 2AI_EXTRACTfieldsstoresResults table
Exam tip

When asked how to avoid reprocessing old files, the answer is a stream that is consumed by the task INSERT, not a timestamp filter you maintain yourself.

Watch out

A freshly created task does nothing. If a scenario says the pipeline never ran, check that the task was resumed and that the directory table was refreshed.

Common mistakes

Forgetting ALTER TASK RESUME. New tasks start suspended.

Not refreshing the directory table. The stream stays empty and the WHEN condition is false.

Creating a stream on a stage with no directory table. The stream needs the directory table.

Selecting from the stream manually and expecting it to empty. Only a DML statement consumes it.

Skipping EXECUTE TASK or EXECUTE MANAGED TASK. The owner role needs the right one for user managed or serverless tasks.

What to remember for the exam

  • Pipeline: stage with directory table, stream on stage, task with SYSTEM$STREAM_HAS_DATA, AI function, results table.
  • CREATE STREAM ... ON STAGE tracks directory table changes. Filter METADATA$ACTION = 'INSERT'.
  • Tasks start suspended. RESUME turns them on. EXECUTE TASK tests one run.
  • Serverless task: omit WAREHOUSE and hold EXECUTE MANAGED TASK. All tasks need EXECUTE TASK.
  • Use a warehouse no larger than MEDIUM for Cortex document functions.

Check yourself

  • How does a task run only when new documents exist?

    Add WHEN SYSTEM$STREAM_HAS_DATA on a stream over the stage, so the body runs only when the stream has change records.

  • A new task never runs. What is the first thing to check?

    Whether it was resumed with ALTER TASK ... RESUME, since tasks are created suspended.

  • Which extra privilege does a serverless task need?

    EXECUTE MANAGED TASK for the owner role, in addition to EXECUTE TASK.

Topic 5 of 7 in Snowflake Document Processing

Test yourself on Snowflake Document Processing

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.