Introduction to tasks¶

Limitations¶

• The maximum compute size for a serverless task is equivalent to an X2LARGE virtual warehouse.

Create a task using the serverless compute model¶

When you use CREATE TASK or ALTER TASK, leave out the WAREHOUSE parameter.

The role that executes the Task must have the global EXECUTE MANAGED TASK privilege. For more information about the access control requirements for tasks, see Task security.

You can take some control over the cost and performance of serverless tasks by setting the following parameters:

• SERVERLESS_TASK_MAX_STATEMENT_SIZE: the maximum size of warehouse to prevent unexpected costs.

• SERVERLESS_TASK_MIN_STATEMENT_SIZE: the minimum size of warehouse for predictable performance.

• TARGET_COMPLETION_INTERVAL: the desired task completion time.

When these settings are specified, the precedence is as follows:

• SERVERLESS_TASK_MAX_STATEMENT_SIZE

• SERVERLESS_TASK_MIN_STATEMENT_SIZE

• TARGET_COMPLETION_INTERVAL

How Snowflake estimates serverless compute resources¶

Snowflake automatically estimates the compute resources needed to complete a task based on the target time frame and the performance of previous task runs.

The target time frame can be set by using TARGET_COMPLETION_INTERVAL. If this value is not set, Snowflake resizes serverless compute resources to complete before the next scheduled execution time.

After a task completes, Snowflake estimates compute resources needed as follows:

• If tasks are completing too quickly (within a time frame that is a factor less than the target time frame), Snowflake scales down resources in subsequent runs by one warehouse size.

• If tasks are completing too slowly (within a time frame that is a factor more than the target time frame), Snowflake scales up resources in subsequent runs by one warehouse size.

Examples¶

Example 1: Create a serverless task to run every hour with a target completion interval of 120 minutes:

CREATE TASK SCHEDULED_T1 SCHEDULE='USING CRON 0 * * * * America/Los_Angeles'
TARGET_COMPLETION_INTERVAL='120 MINUTE' AS SELECT 1;

By setting TARGET_COMPLETION_INTERVAL with a relatively long duration, this indicates it’s acceptable for the task execution to run longer. Snowflake automatically sizes serverless compute so that the task execution completes within the specified TARGET_COMPLETION_INTERVAL.

Example 2: Create a serverless task to run every hour with a target completion time of 10 minutes:

CREATE TASK SCHEDULED_T2 SCHEDULE='USING CRON 0 * * * * UTC'
TARGET_COMPLETION_INTERVAL='10 MINUTE'
SERVERLESS_TASK_MAX_STATEMENT_SIZE='LARGE'
AS SELECT 1;

By setting TARGET_COMPLETION_INTERVAL with a relatively short duration, this indicates a preference for the task to quickly complete. Snowflake automatically sizes serverless compute so that the task execution completes within the specified TARGET_COMPLETION_INTERVAL until the SERVERLESS_TASK_MAX_STATEMENT_SIZE is reached.

Example 3: Create a serverless task scheduled once a day at some point within a target completion interval and range of minimum and maximum warehouse sizes:

CREATE TASK SCHEDULED_T3 SCHEDULE='USING CRON 0 0 * * * UTC'
TARGET_COMPLETION_INTERVAL='180 M'
SCHEDULING_MODE = 'FLEXIBLE'
SERVERLESS_TASK_MIN_STATEMENT_SIZE='MEDIUM' SERVERLESS_TASK_MAX_STATEMENT_SIZE='LARGE' AS SELECT 1;

With this configuration, the serverless task runs once a day at midnight on at least a medium warehouse with a TARGET_COMPLETION_INTERVAL of 3 hours. Snowflake automatically sizes serverless compute so that the task execution completes within the specified TARGET_COMPLETION_INTERVAL until the SERVERLESS_TASK_MAX_STATEMENT_SIZE is reached. By setting SCHEDULING_MODE as flexible, the task can begin at any time during the day.

Example 4: Create a serverless task scheduled once a day without a target completion interval:

CREATE TASK SCHEDULED_T4 SCHEDULE='USING CRON 0 0 * * * UTC'
SERVERLESS_TASK_MAX_STATEMENT_SIZE='LARGE' AS SELECT 1;

With this configuration, the serverless task runs once a day at midnight. If a series of task execution durations don’t complete before the next scheduled time, Snowflake automatically sizes serverless compute so that the task execution completes within the specified SCHEDULE until the SERVERLESS_TASK_MAX_STATEMENT_SIZE is reached.

For more information about the parameters and syntax, see CREATE TASK and ALTER TASK.

Choosing a warehouse size¶

If you choose to use existing warehouses to supply the compute resources for individual tasks, follow the best practices described in Warehouse considerations. To understand compute needs for your task, analyze the average run time for a single task or task graph using different warehouses based on warehouse size and clustering. You should also consider whether the warehouse is shared by multiple processes.

To analyze the average run time of your tasks, query the TASK_HISTORY account usage view. The average difference between the scheduled and completed times for a task is the expected average run time for the task. This difference includes the time the task was queued while other processes are using the compute resources in the warehouse.

For task graphs, there is a brief lag after a predecessor task finishes running and any child task starts. Choose a warehouse size large enough to accommodate multiple child tasks running simultaneously.

The following diagram shows a window of 1 minute in which a single task queued for 20 seconds and then ran for 40 seconds. This means that other processes were using the warehouse resources for the first 20 seconds after the task was scheduled.

The following diagram shows a task graph that requires 5 minutes on average to complete for each run. The diagram shows the window for 2 runs of the task graph to complete. This window is calculated from the time the root task is scheduled to start until the last child task has completed running.

In this example, the warehouse the task graph is running on is shared with other concurrent operations. These concurrent operations consume all available resources when each task in the task graph finishes running and before the next task starts running. As a result, the window for each task includes some amount of queuing while it waits for other operations to finish and free up compute resources.

Task scheduling and daylight saving time¶

The cron expression in a task definition supports specifying a time zone. Tasks scheduled when the transition from standard time to daylight saving time, or the reverse, occurs can have unexpected behaviors.

For example:

• During the change from daylight saving time to standard time, a task scheduled to start at 1 AM in the America/Los_Angeles time zone (0 1 * * * America/Los_Angeles) would run twice. At 1 AM and then again when 1:59:59 AM shifts to 1:00:00 AM local time.

• During the change from standard time to daylight saving time, a task scheduled to start at 2 AM in the America/Los_Angeles time zone (0 2 * * * America/Los_Angeles) would not run because the local time shifts from 1:59:59 AM to 3:00:00 AM.

To avoid unexpected task executions due to daylight saving time, consider the following:

• Don’t schedule tasks to run between 1 AM and 3 AM.

• Manually adjust the cron expression for tasks scheduled between 1 AM and 3 AM twice each year to compensate for the time change.

• Use a time format that does not apply daylight savings time, such as UTC.

Create a Triggered Task¶

Use CREATE TASK, and define the target stream using the WHEN clause. (Do not include the SCHEDULE parameter.)

Assign compute resources for the task by choosing one of the following:

• To manually manage the resources (user-managed warehouse task), add the WAREHOUSE parameter.

• To allow Snowflake to manage resources (serverless task), add the TARGET_COMPLETION_INTERVAL parameter. Snowflake estimates the resources needed and adjusts to complete the task in this time.

Migrate an existing task from a scheduled task to a Triggered Task¶

Suspend the task, unset the SCHEDULE parameter, and resume the task. The existing task must have a target stream defined in the WHEN clause.

ALTER TASK task SUSPEND;
ALTER TASK task UNSET SCHEDULE;
ALTER TASK task RESUME;

Migrate an existing user-managed Triggered Task to a serverless Triggered Task¶

Suspend the task. Remove the WAREHOUSE parameter and add the TARGET_COMPLETION_INTERVAL parameter. Resume the task.

Triggered Tasks considerations¶

• In the SHOW TASKS and DESC TASK output, the SCHEDULE property displays NULL for Triggered Tasks.

• In the output of the task_history view of the information_schema and account_usage schemas, the SCHEDULED_FROM column displays TRIGGER.

• When working with multiple data streams, you can manage when tasks start by using conditional parameters: WHEN … AND and WHEN … OR.

Triggered Tasks Limitations¶

• By default, Triggered Tasks run at most every 30 seconds. You can modify the USER_TASK_MINIMUM_TRIGGER_INTERVAL_IN_SECONDS parameter to run more frequently, up to every 10 seconds.

• Streams on Directory Tables, External Tables, and Hybrid Tables are not supported.

Triggered Tasks examples¶

Example 1: Create a user-managed task that runs whenever data changes in either of two streams:

CREATE TASK my_task
  WHEN SYSTEM$STREAM_HAS_DATA('my_return_stream')
  OR   SYSTEM$STREAM_HAS_DATA('my_order_stream')
  WAREHOUSE = my_warehouse
  AS
    INSERT INTO customer_activity
    SELECT customer_id, return_total, return_date, ‘return’
    FROM my_return_stream
    UNION ALL
    SELECT customer_id, order_total, order_date, ‘order’
    FROM my_order_stream;

Example 2: Create a task to run whenever data changes are detected in two different data streams. Because the task uses the AND conditional, the task is skipped if only one of the two streams has new data.

Because TARGET_COMPLETION_INTERVAL is 120 minutes, Snowflake estimates and adjusts compute resources necessary to complete the task in this time.

CREATE TASK triggeredTask
  WHEN SYSTEM$STREAM_HAS_DATA('orders_stream')
  TARGET_COMPLETION_INTERVAL='120 MINUTES'
  AS
    INSERT INTO completed_promotions
    SELECT order_id, order_total, order_time, promotion_id
    FROM orders_stream
    WHERE promotion_id IS NOT NULL;

Creating tasks¶

Creating tasks requires a role with a minimum of the following privileges:

Running tasks¶

After a task is created, the task owner must have the following privileges for the task to run:

In addition, the role must have the permissions required to run the SQL statement executed by the task.

Viewing task history¶

To view tasks, you must have one or more of the following privileges:

• The ACCOUNTADMIN role

• The OWNERSHIP privilege on the task

• The global MONITOR EXECUTION privilege

Resuming or suspending tasks¶

In addition to the task owner, a role that has the OPERATE privilege on the task can suspend or resume the task. This role must have the USAGE privilege on the database and schema that contain the task. No other privileges are required.

When a task is resumed, Snowflake verifies that the task owner role has the privileges listed in Running tasks.

Create a custom role to create tasks¶

Snowflake requires different permissions to create serverless and user-managed tasks.

For example, to create user-managed tasks, create a custom role named warehouse_task_creation and grant that role the CREATE TASK and USAGE privileges on the warehouse that the role can create tasks in.

USE SYSADMIN;

CREATE ROLE warehouse_task_creation
  COMMENT = 'This role can create user-managed tasks.';

from snowflake.core.role import Role

root.session.use_role("SYSADMIN")

my_role = Role(
    name="warehouse_task_creation",
    comment="This role can create user-managed tasks."
)
root.roles.create(my_role)

USE ACCOUNTADMIN;

GRANT CREATE TASK
  ON SCHEMA schema1
  TO ROLE warehouse_task_creation;

from snowflake.core.role import Securable

root.session.use_role("ACCOUNTADMIN")

root.roles['warehouse_task_creation'].grant_privileges(
    privileges=["CREATE TASK"], securable_type="schema", securable=Securable(name='schema1')
)

GRANT USAGE
  ON WAREHOUSE warehouse1
  TO ROLE warehouse_task_creation;

from snowflake.core.role import Securable

root.roles['warehouse_task_creation'].grant_privileges(
    privileges=["USAGE"], securable_type="warehouse", securable=Securable(name='warehouse1')
)

As an example of a role that can create serverless tasks; create a custom role named serverless_task_creation and grant the role the CREATE TASK privilege and the account level EXECUTE MANAGED TASK privilege.

USE SYSADMIN;

CREATE ROLE serverless_task_creation
  COMMENT = 'This role can create serverless tasks.';

from snowflake.core.role import Role

root.session.use_role("SYSADMIN")

my_role = Role(
    name="serverless_task_creation",
    comment="This role can create serverless tasks."
)
root.roles.create(my_role)

USE ACCOUNTADMIN;

GRANT CREATE TASK
  ON SCHEMA schema1
  TO ROLE serverless_task_creation;

from snowflake.core.role import Securable

root.session.use_role("ACCOUNTADMIN")

root.roles['serverless_task_creation'].grant_privileges(
    privileges=["CREATE TASK"], securable_type="schema", securable=Securable(name='schema1')
)

GRANT EXECUTE MANAGED TASK ON ACCOUNT
  TO ROLE serverless_task_creation;

root.roles['serverless_task_creation'].grant_privileges(
    privileges=["EXECUTE MANAGED TASK"], securable_type="account"
)

Create a custom role to administer tasks¶

Create a custom role and with the EXECUTE TASK privilege and grant this custom role to any task owner role to allow altering their own tasks. To remove the ability for the task owner role to execute the task, revoke this custom role from the task owner role.

For example, create a custom role name taskadmin and grant that role the EXECUTE TASK privilege. Assign the taskadmin role to a task owner role named myrole:

USE ROLE securityadmin;

CREATE ROLE taskadmin;

from snowflake.core.role import Role

root.session.use_role("securityadmin")

root.roles.create(Role(name="taskadmin"))

Set the active role to ACCOUNTADMIN before granting the account-level privileges to the new role

USE ROLE accountadmin;

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

root.session.use_role("accountadmin")

root.roles['taskadmin'].grant_privileges(
    privileges=["EXECUTE TASK", "EXECUTE MANAGED TASK"], securable_type="account"
)

Set the active role to SECURITYADMIN to show that this role can grant a role to another role

USE ROLE securityadmin;

GRANT ROLE taskadmin TO ROLE myrole;

from snowflake.core.role import Securable

root.session.use_role("securityadmin")

root.roles['myrole'].grant_role(role_type="ROLE", role=Securable(name='taskadmin'))

For more information on creating custom roles and role hierarchies, see Configuring access control.