Currently, a task can execute a single SQL statement, including a call to a stored procedure.

Tasks can be combined with table streams for continuous ELT workflows to process recently changed table rows. Streams ensure exactly once semantics for new or changed data in a table.

Tasks can also be used independently to generate periodic reports by inserting or merging rows into a report table or perform other periodic work.

In this Topic:

Snowflake ensures only one instance of a task with a schedule (i.e. a standalone task or the root task in a tree of tasks) is executed at a given time. If a task is still running when the next scheduled execution time occurs, then that scheduled time is skipped.

### Choosing a Warehouse¶

Suggested best practices when configuring warehouses are described in Warehouse Considerations. We recommend that you analyze the average run time for a given task or tree of tasks using a specific warehouse based on warehouse size and clustering, as well as whether or not the warehouse is shared by multiple processes or is dedicated to running this single task (or tree of tasks).

Query the TASK_HISTORY table function in the Information Schema. The average difference between the scheduled and completed times for a task is the expected average run time for the task, including any period in which the task was queued. A task is queued when other processes are currently using all of the compute resources in the warehouse.

Unless the SQL statements defined for the tasks can be optimized (either by rewriting the statements or using stored procedures), then this would be the expected average run time for the task (or tree of tasks). Choose the right size for the warehouse based on your analysis to ensure the task (or tree of tasks) finishes running within this window.

The following diagram shows a window of 1 minute in which a single task queued for 20 seconds and then ran for 40 seconds.

The following diagram shows a tree of tasks that requires 5 minutes on average to complete for each run. The diagram shows the window for 2 runs of the tree of tasks to complete. This window is calculated from the time the root task is scheduled to start until the last child task in the tree has completed running. In this example, the tree of tasks is shared with other, concurrent operations that queue while each of the 3 tasks in the tree is running. These concurrent operations consume all available resources when each task in the tree 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 relinquish compute resources.

Note that even if this tree of tasks ran on a dedicated warehouse, a brief lag would be expected after a parent task finishes running and any child task is executed; however, no queueing for shared resources with other operations would occur. The warehouse size you choose should be large enough to accommodate multiple child tasks that are triggered simultaneously by parent tasks.

### Task Scheduling and Daylight Saving Time¶

The cron expression in a task definition supports specifying a time zone. A scheduled task runs according to the specified cron expression in the local time for a given time zone. Special care should be taken with regard to scheduling tasks for time zones that recognize daylight saving time. Tasks scheduled during specific times on days when the transition from standard time to daylight saving time (or the reverse) occurs can have unexpected behaviors.

For example:

• During the autumn change from daylight saving time to standard time, a task scheduled to start at 1 AM in the America/Los_Angeles time zone (i.e. 0 1 * * * America/Los_Angeles) would run twice: once at 1 AM and then again when 1:59:59 AM shifts to 1:00:00 AM local time. That is, there are two points in time when the local time is 1 AM.

• During the spring change from standard time to daylight saving time, a task scheduled to start at 2 AM in the America/Los_Angeles time zone (i.e. 0 2 * * * America/Los_Angeles) would not run at all because the local time shifts from 1:59:59 AM to 3:00:00 AM. That is, there is no point during that day when the local time is 2 AM.

To avoid unexpected task executions due to daylight saving time, either:

• Do not schedule tasks to run at a specific time between 1 AM and 3 AM (daily, or on days of the week that include Sundays), or

• Manually adjust the cron expression for tasks scheduled during those hours twice each year to compensate for the time change due to daylight saving time.

Users can define a simple tree-like structure of tasks that starts with a root task and is linked together by task dependencies. The current implementation supports a single path between any two nodes; i.e. an individual task can have only a single predecessor (parent) task. This differs from a Directed Acyclic Graph (DAG) structure, in which a single node can have multiple parents.

A simple tree of tasks is limited to a maximum of 1000 tasks total (including the root task). An individual task in the tree is limited to a single predecessor task; however, a task can have a maximum of 100 child tasks (i.e. other tasks that identify the task as a predecessor).

Note

A brief lag occurs after a parent task finishes running and any child task is executed.

All tasks in a simple tree must have the same task owner (i.e. a single role must have the OWNERSHIP privilege on all of the tasks in the tree) and be stored in the same database and schema.

When ownership of all tasks in a tree of tasks is transferred at once, through either of the following activities, the links between all tasks in the tree are retained:

• The current owner of all tasks that compose the tree of tasks is dropped (using DROP ROLE). Ownership of the objects owned by the dropped role is transferred to the role that executes the DROP ROLE command.

• Ownership of all tasks that compose the tree of tasks is explicitly transferred to another role (e.g. by executing GRANT OWNERSHIP on all tasks in a schema).

### Overlapping Tree of Task Runs¶

By default, Snowflake ensures only one instance of a particular tree of tasks is allowed to run at a time. The next run of a root task is scheduled only after all child tasks in the tree have finished running. This means that if the cumulative time required to run all tasks in the tree exceeds the explicit scheduled time set in the definition of the root task, at least one run of the task tree is skipped.

The behavior is controlled by the ALLOW_OVERLAPPING_EXECUTION parameter on the root task; the default value is FALSE.

In the following example, a run of a task tree is scheduled to start when a prior run has not completed yet. The period of overlap, or concurrency, is identified in red. The diagram also identifies the span of time when each task queued before running in the warehouse:

Overlapping runs may be tolerated (or even desirable) when read/write SQL operations executed by overlapping runs of a task tree do not produce incorrect or duplicate data. However, for other trees, task owners (i.e. the role with the OWNERSHIP privilege on all tasks in the tree) should set an appropriate schedule on the root task and choose an appropriate warehouse size to ensure an instance of the task tree finishes to completion before the root task is next scheduled to run.

To better align a task tree with the schedule defined in the root task:

1. If feasible, increase the scheduling time between runs of the root task.

2. Consider increasing the size of the warehouse that runs large or complex SQL statements or stored procedures in the task tree.

3. Analyze the SQL statements or stored procedure executed by each task. Determine if code could be rewritten to leverage parallel processing.

If none of the above solutions help, consider whether it is necessary to allow concurrent runs of the tree by setting ALLOW_OVERLAPPING_EXECUTION = TRUE on the root task. This parameter can be defined when creating a task (using CREATE TASK) or later (using ALTER TASK).

## Versioning of Runs¶

When a scheduled task (either a standalone task or the root task in a tree) starts a scheduled run, a version of the task is established. If the task is a root task, then a version of the entire task tree, including all properties for all tasks in the tree, is set. The standalone task or task tree completes its current run using this version.

After task properties in the tree are modified and the root task is resumed, those changes are not applied until the next scheduled run. At that time, a new version of the tree of tasks is set.

Note

If the definition of a stored procedure called by a task changes while the tree of tasks is executing, the new programming could be executed when the stored procedure is called by the task in the current run.

When the root task is resumed, a new version of the tree of tasks is set when the root task starts its next run. This new version includes the modification to Task B.

## Setting Session Parameters for Tasks¶

You can set session parameters for the session in which a task runs. To do so, modify an existing task and set the desired parameter values (using ALTER TASKSET session_parameter = value[, session_parameter = value ... ]).

A task supports all session parameters. For the complete list, see Parameters.

Note that a task does not support account or user parameters.

The following roles (or roles with the specified privileges) can use SQL to view the task history within a specified date range:

• Task owner (i.e. role that has the OWNERSHIP privilege on a task).

• Any role that has the global MONITOR EXECUTION privilege.

SQL

Query the TASK_HISTORY table function (in the Information Schema).

## Understanding the System Service¶

Snowflake runs tasks with the privileges of the task owner (i.e. the role that has OWNERSHIP privilege on the task), but task runs are not associated with a user. Instead, each run is executed by a system service. Tasks are decoupled from specific users to avoid complications that can arise when users are dropped, locked due to authentication issues, or have roles removed.

Because task runs are decoupled from a user, the query history for task runs are associated with the system service. SYSTEM is not a user in the account; it is a behind-the-scenes service. As such, there are no user credentials for this service, and no individual (from Snowflake or in your account) can assume its identity. Activity for the system service is limited to your account. The same encryption protections and other security protocols are built into this service as are enforced for other operations.

To support creating and managing tasks, Snowflake provides the following set of special DDL commands:

In addition, providers can view, grant, or revoke access to the necessary database objects for ELT using the following standard access control DDL:

To support retrieving information about tasks, Snowflake provides the following set of SQL functions:

### Access Control Privileges¶

Creating, managing, and executing tasks requires a role with a minimum of the following privileges:

Object

Privilege

Notes

Account

Required to run any tasks the role owns. Revoking the EXECUTE TASK privilege on a role prevents all subsequent task runs from starting under that role.

Database

USAGE

Schema

Warehouse

USAGE

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

After a task is created, the task owner (i.e. the role that has the OWNERSHIP privilege on the task) must have the following privileges:

Object

Privilege

Notes

Account

Required to run any tasks the role owns. Revoking the EXECUTE TASK privilege on a role prevents all subsequent task runs from starting under that role.

Database

USAGE

Schema

USAGE

OWNERSHIP

Warehouse

USAGE

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

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 Owning Tasks (in this topic).

USE ROLE securityadmin;

-- set the active role to ACCOUNTADMIN before granting the EXECUTE TASK privilege to the new role

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



For more information on creating custom roles and role hierarchies, see Configuring Access Control.

### Dropping a Task Owner Role¶

When the owner role of a given task (i.e. the role with the OWNERSHIP privilege on the task) is deleted, the task is “re-possessed” by the role that dropped the owner role. This ensures that ownership moves to a role that is closer to the root of the role hierarchy. When a task is re-possessed, it is automatically paused, i.e., all executions currently in flight complete processing, but new executions will not be scheduled until the task is resumed explicitly by the new owner. The rationale for this is to prevent a user with access to a particular role from leaving behind tasks that suddenly execute with higher permissions when the role is removed.

If the role that a running task is executing under is dropped while the task is running, the task completes processing under the dropped role.

## Workflow¶

This section provides a high-level overview of the task setup workflow.

1. Complete the steps in Creating a Task Administrator Role (in this topic) to create a role that can be used to execute the commands in the following steps.
{"extraction_info": {"found_math": true, "script_math_tex": 0, "script_math_asciimath": 0, "math_annotations": 0, "math_alttext": 0, "mathml": 0, "mathjax_tag": 0, "mathjax_inline_tex": 0, "mathjax_display_tex": 0, "mathjax_asciimath": 1, "img_math": 0, "codecogs_latex": 0, "wp_latex": 0, "mimetex.cgi": 0, "/images/math/codecogs": 0, "mathtex.cgi": 0, "katex": 0, "math-container": 0, "wp-katex-eq": 0, "align": 0, "equation": 0, "x-ck12": 0, "texerror": 0, "math_score": 0.18100307881832123, "perplexity": 1779.0489566990384}, "config": {"markdown_headings": true, "markdown_code": true, "boilerplate_config": {"ratio_threshold": 0.18, "absolute_threshold": 10, "end_threshold": 15, "enable": true}, "remove_buttons": true, "remove_image_figures": true, "remove_link_clusters": true, "table_config": {"min_rows": 2, "min_cols": 3, "format": "plain"}, "remove_chinese": true, "remove_edit_buttons": true, "extract_latex": true}, "warc_path": "s3://commoncrawl/crawl-data/CC-MAIN-2021-31/segments/1627046154302.46/warc/CC-MAIN-20210802012641-20210802042641-00120.warc.gz"}