Snowflake - Tasks

User-defined tasks allow scheduled execution of SQL statements. Tasks run according to a specified execution configuration, using any combination of a set interval and/or a flexible schedule using a subset of familiar cron utility syntax. Tasks currently run in user-specified warehouses but will eventually run in a serverless environment using Snowflake-supplied compute resources. 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.

Task Scheduling
There is no event source that can trigger a task; instead, a task runs on a schedule, which can be defined when creating a task (using CREATE TASK) or later (using ALTER TASK). 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.

Task History
Query the TASK_HISTORY table function to verify the task did not run. It is possible that the task ran successfully but the SQL statement in the task definition failed. In particular, note the scheduled and completed times, as well as any error code and message. If the task has a parent task (in a tree of tasks), verify whether the parent task completed successfully.

There is a 60 minute default limit on a single run of a task. This limitation was implemented as a safeguard against non-terminating tasks.

Link: https://docs.snowflake.com/en/user-guide/tasks-intro.html

Snowflake - Stored Procedures

Unlike MS SQL server, Snowflake's stored procedures are written in JavaScript. A stored procedure returns a single value. Although you can run SELECT statements inside a stored procedure, the results must be used within the stored procedure, or be narrowed to a single value to be returned.

Snowflake stored procedures use JavaScript and, in most cases, SQL:

• JavaScript provides the control structures (branching and looping).
• SQL is executed by calling functions in a JavaScript API.

Stored procedures allow:

• Procedural logic (branching and looping), which straight SQL does not support.
• Error handling.
• Dynamically creating a SQL statement and execute it.
• Writing code that executes with the privileges of the role that owns the procedure, rather than with the privileges of the role that runs the procedure. This allows the stored procedure owner to delegate the power to perform specified operations to users who otherwise could not do so. However, there are limitations on these owner’s rights stored procedures.

One common use for stored procedures is to automate a task that requires multiple SQL statements and is performed frequently.

Snowflake API consists of JavaScript objects and the methods in those objects.

Link: https://docs.snowflake.com/en/sql-reference/stored-procedures-api.html