CertSafari
    Snowflake SnowPro Advanced: Data Analyst (DAA-C01)· Lessons

    Domain 1 · Lesson 5/19

    Snowflake tasks and task graphs for data pipelines

    Implement data processing solutions.

    15 min read
    2.43% of exam
    5 sources
    Published 20 Oct 2026
    Docs as of 18 Oct 2026

    What you will be able to do

    • Write a cleansing step that turns unparseable values into NULLs you can route to a reject table, instead of failing the load
    • Choose between serverless and user-managed compute for a task, and say what each one costs
    • Schedule a task with an interval, a CRON expression or a stream trigger, and explain how a trigger avoids wasted warehouse resumes
    • Build a task graph with a root task, parallel child tasks and a finalizer, and start or change it safely

    Key concept

    Task — A task is a Snowflake object that runs a SQL statement or a stored procedure on a schedule or when an event fires, such as new rows arriving in a stream. A task graph chains tasks so that each pipeline step starts only after the steps it depends on have finished.

    1.Cleansing, conforming and enriching inside a pipeline step

    A processing pipeline takes raw rows that have landed in Snowflake and turns them into something downstream users can trust. That work comes in three kinds. Cleansing deals with bad values. Conforming makes values consistent, for example by trimming and upper-casing product names so widget and WIDGET match. Enriching adds context by joining reference data. The SQL for all three is ordinary. What makes it a pipeline is that it runs again and again, unattended, on whatever arrives next.

    Cleansing has to survive values it has never seen before. Say a VARCHAR staging column holds proper ISO dates, filler such as N/A, and impossible dates such as 2026-13-45. TO_DATE raises an error on the first bad value, so the whole statement fails. TRY_TO_DATE does the same conversion but has error handling: if the conversion cannot be performed, it returns NULL. That makes routing the bad rows simple. Load every row with TRY_TO_DATE(order_date_raw, 'YYYY-MM-DD'), then send the rows where the result is NULL but the raw value is not to a reject table.

    The alternatives that look tempting each break something. A CASE that checks only for the literal N/A still passes 2026-13-45 to a strict cast, which then fails. Wrapping TO_DATE in COALESCE(..., CURRENT_DATE()) makes up a date, so a bad row turns into a plausible-looking order dated today and can no longer be told apart from a real one. ON_ERROR = CONTINUE is not a session parameter that softens conversion functions. It is a load option of COPY INTO, covered in COPY INTO and load error handling, and it skips rows instead of keeping them with a NULL.

    If a step can be written as one SELECT, a dynamic table can do the conforming and refreshing for you. The example below trims and upper-cases product names, derives a line total and filters out returned orders. Snowflake keeps the result within ten minutes of raw_orders. Each refresh is applied atomically, so readers never see a half-finished refresh. To enrich, add another dynamic table on top that joins this one to a reference table such as dim_customers. Snowflake works out the dependency order from the queries themselves. Dynamic tables cannot contain stored procedures, though. Multi-statement logic, such as "load good rows, write rejects, log counts", belongs in a task.

    A dynamic table that conforms product names, derives a line total and filters returned orders, refreshed to a 10-minute target lagsql
    CREATE OR ALTER DYNAMIC TABLE dt_orders
        TARGET_LAG = '10 minutes'
        WAREHOUSE = transform_wh
        REFRESH_MODE = INCREMENTAL
    AS
        SELECT
            order_id,
            customer_id,
            order_date,
            TRIM(UPPER(product_name)) AS product_name,
            quantity,
            unit_price,
            quantity * unit_price AS line_total,
            order_status
        FROM raw_orders
        WHERE order_status != 'returned';

    Checkpoint 1 of 7· Fill the gap

    This dynamic table conforms product names and must stay no more than 10 minutes behind raw_orders. Which parameter completes the definition?

    CREATE OR ALTER DYNAMIC TABLE dt_orders
         ?  = '10 minutes'
        WAREHOUSE = transform_wh
        REFRESH_MODE = INCREMENTAL
    AS
        SELECT
            order_id,
            customer_id,
            order_date,
            TRIM(UPPER(product_name)) AS product_name,
            quantity,
            unit_price,
            quantity * unit_price AS line_total,
            order_status
        FROM raw_orders
        WHERE order_status != 'returned';

    Checkpoint 2 of 7· Check yourself

    One pipeline step has to load valid order rows into a target table, write rejected rows to a reject table, and record row counts by calling a logging stored procedure. How should you build this step?

    Checkpoint 3 of 7· Exam question

    A staging column order_date_raw is VARCHAR and holds valid ISO dates, values such as N/A, and impossible dates like 2026-13-45. The cleansing step must load every row, store NULL for unparseable dates, and send those rows to a reject table. Which expression meets this?

    Sources12

    2.What a task runs and which compute it uses

    A task's body can be a single SQL statement or a call to a stored procedure written in JavaScript, Python, Java, Scala or Snowflake Scripting. A procedure call is the usual way to package a step with several statements, such as MERGE, write rejects, then insert an audit row, behind one task. How procedures run, under owner's or caller's rights, and how they raise exceptions is covered in Stored procedures, owner's vs caller's rights, exceptions. Here the point is simply that the task calls the procedure, so the whole step shares one schedule and one failure record.

    Every task needs compute, and the choice comes down to one parameter. Leave out WAREHOUSE and the task is serverless: Snowflake predicts the resources each run needs, based on recent runs, and assigns them. Include WAREHOUSE='COMPUTE_WH' and the task is user-managed, running on a warehouse you size yourself. Serverless compute tops out at the equivalent of an XXLARGE warehouse. SERVERLESS_TASK_MIN_STATEMENT_SIZE (default XSMALL) and SERVERLESS_TASK_MAX_STATEMENT_SIZE (default XXLARGE) let you narrow that range, putting a floor under performance and a ceiling on cost.

    Serverless versus user-managed tasks
    FactorServerless taskUser-managed task
    How to declare itLeave out the WAREHOUSE parameterInclude WAREHOUSE='COMPUTE_WH' (or another warehouse)
    BillingActual compute resource usageWarehouse size, with a 60-second minimum each time the warehouse resumes
    Best fitUnder-used warehouses; short tasks with stable run timesFully used warehouses running many tasks at once; unpredictable loads
    Schedule adherencePreferred when keeping to the interval matters; Snowflake scales up when a run overrunsPreferred when keeping to the interval matters less
    Size ceilingEquivalent to XXLARGEWhatever warehouse size you create

    That 60-second minimum is why a user-managed task that runs every minute but finds nothing to do still costs money: each run wakes the warehouse and pays for at least a minute of it. A job that needs more than XXLARGE has to be user-managed. A serverless task can also take TARGET_COMPLETION_INTERVAL, which tells Snowflake to scale so the run finishes within that window. Triggered serverless tasks require it.

    Checkpoint 4 of 7· Check yourself

    A transformation task runs a MERGE that needs more compute than an XXLARGE warehouse can give it. How should you define the task?

    Sources3

    3.Scheduling with intervals, CRON and stream triggers

    A new task is created suspended, so it does nothing until you run ALTER TASK … RESUME. EXECUTE TASK runs it once, which is how you test it. The SCHEDULE parameter takes one of two forms. An interval such as '60 MINUTES' or '10 SECONDS' sets a fixed gap between runs. 'USING CRON …' with a time zone pins runs to clock times and weekdays.

    A CRON schedule: every Sunday at 3:07 a.m. Los Angeles timesql
    CREATE TASK task_sunday_3_07_am_pacific_time_zone
      SCHEDULE='USING CRON 7 3 * * SUN America/Los_Angeles'  -- Use a random minute such as 7
    AS SELECT 1;

    A schedule decides *when* a task runs, never *whether* there is work to do. Writing the same one-minute schedule as CRON (* * * * *) still produces a run every minute. To make runs depend on data, add a WHEN condition with SYSTEM$STREAM_HAS_DATA, which reports whether a stream holds change data capture (CDC) records. If the stream is empty, the task skips the current run, so no warehouse is started or resumed. A row-count check inside a stored procedure cannot do this, because the procedure only starts after the compute is already running. Without a schedule, the WHEN clause makes a triggered task that runs as new data arrives. With a schedule (SCHEDULE = '1 HOUR' plus WHEN), the task checks the stream on that cadence.

    A triggered task that processes a stream only when it has datasql
    CREATE TASK triggered_task_stream
      WHEN SYSTEM$STREAM_HAS_DATA('orders_stream')
      AS
        INSERT INTO completed_promotions
        SELECT order_id, order_total, order_time, promotion_id
        FROM orders_stream;

    Checkpoint 5 of 7· Fill the gap

    Without a SCHEDULE, this task must run only when orders_stream holds new change records, and must not resume any compute while the stream is empty. Which function completes the WHEN condition?

    CREATE TASK triggered_task_stream
      WHEN  ? ('orders_stream')
      AS
        INSERT INTO completed_promotions
        SELECT order_id, order_total, order_time, promotion_id
        FROM orders_stream;

    Checkpoint 6 of 7· Exam question

    A task runs every minute to merge CDC rows from the stream RAW_ORDERS_STRM into a conformed ORDERS table. Most runs find no new rows, yet the warehouse resumes each minute and credit usage is high. Which change removes the wasted compute while keeping the one-minute check?

    Sources34

    4.Chaining steps into a task graph

    A task graph, also called a directed acyclic graph (DAG), runs pipeline steps in dependency order with no loops. The root task holds the schedule or trigger for the whole graph. Each child task is created with CREATE TASK … AFTER <parent>. Children that share a parent run in parallel. A task with several parents waits until all of them have completed successfully. Limits: a graph can hold up to 1000 tasks, and a single task can have at most 100 parents and 100 children. Every task in a graph must have the same owner and live in the same database and schema.

    A root task with two parallel children and a third child that waits for bothsql
    CREATE TASK task_root
      SCHEDULE = '1 MINUTE'
      AS SELECT 1;
    
    CREATE TASK task_a
      AFTER task_root
      AS SELECT 1;
    
    CREATE TASK task_b
      AFTER task_root
      AS SELECT 1;
    
    CREATE TASK task_c
      AFTER task_a, task_b
      AS SELECT 1;

    A graph can branch on what happened earlier in the run, because a task body can read runtime values, graph-level configuration and the return values of its parents. That makes a quality gate possible. Data metric functions measure things like null counts and freshness against expectations, as covered in Data metric functions (DMFs) and expectations. A gate task placed after the load reads those measured results and returns a verdict. The publish task downstream checks that verdict and either goes ahead or writes the batch to a quarantine table.

    The finalizer is an optional last task, created with CREATE TASK … FINALIZE = <root>. It runs after every other task has completed or failed, which makes it the place to delete intermediate data or send a success or failure notice. A root task can have only one finalizer, and a finalizer cannot have children. If the root task is skipped, for example because the previous run of the graph is still going, the finalizer does not start either.

    Starting and changing a graph follow a fixed order. Resume each child task and the finalizer, then resume the root, or resume them all at once with SYSTEM$TASK_DEPENDENTS_ENABLE('<root>'). To edit a task, suspend the root first. A run already in progress finishes, and future runs are cancelled until you resume the root. By default OVERLAP_POLICY = NO_OVERLAP, so the root is scheduled again only after every task in the current run has finished. A graph that takes longer than its interval therefore skips runs. Before switching to ALLOW_CHILD_OVERLAP or ALLOW_ALL_OVERLAP, confirm that overlapping runs cannot produce duplicate or incorrect writes.

    Checkpoint 7 of 7· Check yourself

    Your task graph's root runs every 5 minutes with the default OVERLAP_POLICY, and its finalizer sends a notice whether the run succeeded or failed. One graph run takes 8 minutes. What happens at the scheduled slot that comes up while that run is still going?

    Sources5

    Sources

    (5)

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

    1. 1.
      “if the conversion cannot be performed, it returns a NULL value instead of raising an error”
      ↩︎ Cleansing, conforming and enriching inside a pipeline step
    2. 2.
      “The new results are applied to the dynamic table atomically, so readers never see a partial refresh.”
      ↩︎ Cleansing, conforming and enriching inside a pipeline step
      “Snowflake infers the dependency graph automatically from the queries you write.”
      ↩︎ Cleansing, conforming and enriching inside a pipeline step
      “Need stored procedures or external functions in the definition.”
      ↩︎ Cleansing, conforming and enriching inside a pipeline step
    3. 3.
      “Tasks can run SQL commands and stored procedures that use supported languages and tools, including JavaScript, Python, Java, Scala, and Snowflake scripting.”
      ↩︎ What a task runs and which compute it uses
      “The maximum compute size for a serverless task is equivalent to an XXLARGE virtual warehouse.”
      ↩︎ What a task runs and which compute it uses
      “For user-managed tasks, billing for warehouses is based on warehouse size, with a 60-second minimum each time the warehouse is resumed.”
      ↩︎ What a task runs and which compute it uses
      “A target completion interval is required for serverless triggered tasks.”
      ↩︎ What a task runs and which compute it uses
      “When a task is created, it starts as suspended.”
      ↩︎ Scheduling with intervals, CRON and stream triggers
      “Tasks can run at scheduled times or can be triggered by events, such as when new data arrives in a stream.”
      ↩︎ Key concept
      “If a task workload requires a larger warehouse, create a user-managed task with a warehouse of the required size.”
      ↩︎ Checkpoint
      “If a task is still running when the next scheduled run time occurs, then that scheduled time is skipped.”
      ↩︎ Prediction
    4. 4.
      “This check can help avoid starting or resuming a warehouse unnecessarily.”
      ↩︎ Scheduling with intervals, CRON and stream triggers
      “the function is designed to avoid false negatives”
      ↩︎ Scheduling with intervals, CRON and stream triggers
      “If the specified stream contains no change data, the task skips the current run.”
      ↩︎ Exam trap 1
    5. 5.
      “When a task has multiple parents, the task waits for all preceding tasks to successfully complete before starting.”
      ↩︎ Chaining steps into a task graph
      “All tasks in a task graph must have the same task owner and be stored in the same database and schema.”
      ↩︎ Chaining steps into a task graph
      “using runtime values, graph level configuration, and return values of parent tasks”
      ↩︎ Chaining steps into a task graph
      “OVERLAP_POLICY = NO_OVERLAP (default): Executes tasks serially with no parallelism.”
      ↩︎ Chaining steps into a task graph
      “When the root task of a task graph is skipped (for example, because of overlap task graph runs), the finalizer task won’t be started.”
      ↩︎ Exam trap 2
      “When the root task of a task graph is skipped (for example, because of overlap task graph runs), the finalizer task won’t be started.”
      ↩︎ Checkpoint

    Continue to part 2 of 2

    Task failures, monitoring, auditing and data lineage

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