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.
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';A dynamic table has no task-style SCHEDULE. You set TARGET_LAG to say how fresh the result must be, and Snowflake refreshes it to stay within that lag.
Source: docs.snowflake.comCheckpoint 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?
Dynamic tables suit logic you can write as one SELECT, and their definitions cannot contain stored procedures. Multi-statement logic that writes several tables and calls a procedure belongs in a task.
“Need stored procedures or external functions in the definition.”Source: docs.snowflake.com
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?
Correct answer: B — TRY_TO_DATE(order_date_raw, 'YYYY-MM-DD'), which returns NULL for unparseable values so those rows can be filtered into a reject table.
- A. Incorrect.
ON_ERRORis a COPY option and not a session parameter for SELECT, andTO_DATEstill raises an error on the first bad value. - B. Correct.
TRY_TO_DATEreturns NULL instead of raising an error, so all rows load and the NULL results identify the records for the reject table. - C. Incorrect.
COALESCEruns only afterTO_DATEhas evaluated, so the conversion error is raised first, and a fallback date would hide bad rows anyway. - D. Incorrect. The
CASEhandles only the literalN/A; an impossible value like2026-13-45still makesCASTfail, so the query errors out.
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.
| Factor | Serverless task | User-managed task |
|---|---|---|
| How to declare it | Leave out the WAREHOUSE parameter | Include WAREHOUSE='COMPUTE_WH' (or another warehouse) |
| Billing | Actual compute resource usage | Warehouse size, with a 60-second minimum each time the warehouse resumes |
| Best fit | Under-used warehouses; short tasks with stable run times | Fully used warehouses running many tasks at once; unpredictable loads |
| Schedule adherence | Preferred when keeping to the interval matters; Snowflake scales up when a run overruns | Preferred when keeping to the interval matters less |
| Size ceiling | Equivalent to XXLARGE | Whatever 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?
Serverless compute tops out at the equivalent of an XXLARGE warehouse. The size parameters and TARGET_COMPLETION_INTERVAL only work inside that limit. A job that needs more has to run as a user-managed task on a warehouse you size yourself.
“If a task workload requires a larger warehouse, create a user-managed task with a warehouse of the required size.”Source: docs.snowflake.com
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.
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.
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;A WHEN condition using SYSTEM$STREAM_HAS_DATA turns the task into a triggered task. When the stream is empty, the run is skipped before any warehouse starts. A schedule, whether an interval or CRON, only sets when the task runs and cannot skip runs that have no work.
Source: docs.snowflake.comCheckpoint 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?
Correct answer: C — Add WHEN SYSTEM$STREAM_HAS_DATA('RAW_ORDERS_STRM') to the task definition so runs with an empty stream are skipped before the warehouse resumes.
- A. Incorrect. A CRON expression only defines when runs are attempted; Snowflake does not merge or skip runs based on whether a stream holds data.
- B. Incorrect. Disabling auto-suspend keeps the warehouse billing continuously, which increases credit usage rather than reducing it.
- C. Correct. The
WHENcondition is evaluated by the cloud services layer before compute is started, so an empty stream skips the run and the warehouse never resumes. - D. Incorrect. The stored procedure executes on the task's warehouse, so the warehouse still resumes every minute just to discover that there is nothing to merge.
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.
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?
With the default NO_OVERLAP, the root is not scheduled again until every task in the current run has finished, so that slot is skipped. When the root is skipped, the finalizer does not start either, so it cannot report missed runs.
“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.”Source: docs.snowflake.com
Sources5
Sources
(5)
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 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.
“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.https://docs.snowflake.com/en/user-guide/tasks-introOfficial docs
“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.
“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.
“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