Snowflake Tasks: Serverless vs User-Managed, Configuring and Scheduling

1.Two ways to give a task compute

A task automates work in a data pipeline. It can run a single SQL statement, call a stored procedure, or run Snowflake Scripting logic, either on a schedule or when an event happens. Every run needs compute, and the first decision you make about any task is where that compute comes from.

The exam guide calls the first option Snowflake-managed tasks. The documentation calls them serverless tasks, and this lesson uses that name from here on. You create one by leaving out the WAREHOUSE parameter. Snowflake then picks the resources from a dynamic analysis of the most recent runs of the same task. A serverless run can grow to the equivalent of an XXLARGE warehouse and no further.

A user-managed task names a virtual warehouse with the WAREHOUSE parameter, which gives you full control of the compute each workload uses. If you take this route, weigh what the task does to other processes that share the warehouse.

CREATE TASK SCHEDULED_T1
  SCHEDULE='60 MINUTES'
  AS SELECT 1;

CREATE TASK SCHEDULED_T1
   ? ='COMPUTE_WH'
  SCHEDULE='60 MINUTES'
  AS SELECT 1;

Which model should you use? Snowflake's guidance turns on how busy your warehouses are and how strictly the task has to keep to its schedule:

Adding compute does not always help. Snowflake notes that more resources shorten the runtime of some SQL code but not all of it, and they never guarantee that a run finishes inside its batch window. For user-managed tasks with unpredictable load, the docs suggest multi-cluster warehouses with auto-suspend and auto-resume enabled to keep credit use moderate.

Sources1

2.Configuring compute limits, timeouts and failure handling

Serverless does not have to mean unbounded. Two parameters set the range Snowflake may choose from:

- SERVERLESS_TASK_MIN_STATEMENT_SIZE sets a floor for predictable performance. The default is XSMALL. - SERVERLESS_TASK_MAX_STATEMENT_SIZE sets a ceiling to prevent unexpected costs. The default is XXLARGE.

After each run, Snowflake reviews performance and adjusts the compute for future runs within these limits.

You can also give a serverless task TARGET_COMPLETION_INTERVAL, an earlier target by which it should finish. Snowflake then scales resources to try to meet that target. It is optional for scheduled tasks but required for serverless triggered tasks. Three more parameters control how runs end:

- USER_TASK_TIMEOUT_MS sets how long a single run may last. - SUSPEND_TASK_AFTER_NUM_FAILURES suspends the task after that many consecutive failures. A task graph is suspended after 10 consecutive failures by default, and you change that number on the root task. - TASK_AUTO_RETRY_ATTEMPTS, set on the root task, retries the whole task graph immediately when a child task fails.

CREATE TASK SCHEDULED_T3
  SCHEDULE='USING CRON 24 * * * * America/Los_Angeles'  -- Use a random minute such as 24
  TARGET_COMPLETION_INTERVAL='120 MINUTE'
  SERVERLESS_TASK_MAX_STATEMENT_SIZE='LARGE'
  USER_TASK_TIMEOUT_MS = 10800000         -- (3 hours)
  SUSPEND_TASK_AFTER_NUM_FAILURES = 3
  AS SELECT 1;

To change a task you already have, use ALTER TASK or CREATE OR ALTER TASK. The second form can change the schedule, timeout, predecessors, the WHEN condition and the AS body. CREATE OR REPLACE TASK is a heavier operation. The recreated task is suspended by default, and if it is a standalone or root task, its next scheduled run is cancelled. A run already in progress still completes. To stop that run first, call SYSTEM$USER_TASK_CANCEL_ONGOING_EXECUTIONS.

Setting permissions for creating and executing tasks. Creating a task and running a task need different privileges, and the compute model changes what is required.

To create a task, a role needs at minimum:

- USAGE on the database - USAGE and CREATE TASK on the schema - EXECUTE MANAGED TASK on the account, only for serverless tasks - USAGE on the warehouse, only for user-managed tasks

Once the task exists, its owner role needs these privileges for the task to run:

- EXECUTE TASK on the account, required to run any task the role owns - EXECUTE MANAGED TASK on the account, only for serverless tasks - USAGE on the database, the schema and the task - USAGE on the warehouse, only for user-managed tasks - whatever permissions the SQL statement inside the task needs

Revoking EXECUTE TASK from a role stops every later run of its tasks. When a task is resumed, Snowflake checks that the owner role holds these privileges.

By default a task runs as a system service with the privileges of the owner role, so it keeps running even if a user is dropped or locked. To run it as a named user instead, add EXECUTE AS USER. The owner role then needs the IMPERSONATE privilege on that user, and the user must be granted the owner role. Apart from the owner, a role with OPERATE on the task can suspend and resume it, provided it has USAGE on the containing database and schema.

The practical pattern is a custom role. Create a role such as taskadmin, grant it the account-level privileges, and grant that role to any task owner role that needs to run tasks. Revoking the custom role takes the ability away again. Use ACCOUNTADMIN for the account-level grants and SECURITYADMIN to grant the role onward.

GRANT EXECUTE TASK, EXECUTE MANAGED TASK ON ACCOUNT TO ROLE taskadmin;

Sources123

3.Schedules, CRON and stream-triggered tasks

The SCHEDULE parameter accepts either a fixed interval, such as '10 SECONDS' or '60 MINUTES', or 'USING CRON <expr> <time_zone>' when a run must happen at a particular time or on a particular day. You can set it in CREATE TASK, in ALTER TASK, or by editing the task in Snowsight.

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;

Snowflake runs only one instance of a scheduled task at a time, so an overrunning task loses its next slot rather than piling up.

When new data arrives at unpredictable times, a triggered task suits better than a schedule. You add a WHEN SYSTEM$STREAM_HAS_DATA(...) condition, and the task runs whenever the stream has new data. This avoids polling the source frequently and lowers latency, which makes it a good fit for ELT. You can also combine the two: a SCHEDULE = '1 HOUR' with the same WHEN clause checks the stream once an hour.

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;

Sources1

4.From suspended to running: managing tasks and task graphs

A schedule on its own does nothing yet. Every task, whether new or cloned, starts in the suspended state. You have two ways to make it run:

- EXECUTE TASK runs it once, which is how you test it. - ALTER TASK … RESUME lets it follow its schedule or watch for events continuously.

Snowflake's recommended workflow builds on this. Create a task-administrator role, define the task, test it by hand, resume it, monitor its cost, and refine it with ALTER TASK.

Complex workflows chain tasks into a task graph. A graph has a root task that holds the schedule, child tasks created with CREATE TASK … AFTER, and an optional finalizer. The same suspended-by-default rule applies to every task in it. You can resume each child and then the root, or you can call SYSTEM$TASK_DEPENDENTS_ENABLE on the root to resume them all at once. EXECUTE TASK on the root runs a single instance of the graph, made up of every child that is already resumed.

To change a task inside a scheduled graph, suspend the root. A graph run already in progress finishes, future scheduled runs are cancelled, and the child tasks keep their state. Suspending a child task makes the graph skip it, and the graph continues as though the child had succeeded.

Sources23

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Serverless tasks scale without limit, so they suit even the heaviest workloads.Why is that wrong?

   A serverless run can be no larger than the equivalent of an XXLARGE warehouse. A workload that needs more must be a user-managed task on a warehouse of the right size.

   Covered in Two ways to give a task compute

2. 2.A role that was able to create a task can always run it, because CREATE TASK is the only privilege that matters.Why is that wrong?

   Running a task is a separate requirement. The owner role also needs the account-level EXECUTE TASK privilege, and revoking it stops all later runs of that role's tasks.

   Covered in Configuring compute limits, timeouts and failure handling

3. 3.If a scheduled run overruns, the next run is queued and starts as soon as the current one finishes.Why is that wrong?

   Snowflake runs only one instance of a scheduled task at a time. The scheduled time that collides with a running instance is skipped, not queued.

   Covered in Schedules, CRON and stream-triggered tasks

4. 4.A task with a SCHEDULE starts running as soon as CREATE TASK succeeds.Why is that wrong?

   New, cloned and replaced tasks all start suspended. You need ALTER TASK … RESUME, or EXECUTE TASK for a single run.

   Covered in From suspended to running: managing tasks and task graphs