Snowflake Tasks: Execute SQL Statements on Schedule

To Nha Notes | Feb. 5, 2023, 6:42 p.m.

A Snowflake Task allows scheduled execution of SQL statements including calling a stored procedure or Procedural logic using Snowflake Scripting.

Few key points to be noted before we get into building Snowflake Tasks

• Only one SQL statement is allowed to be executed through a task. If you need to execute multiple statements, build a procedure.
• Once task is created it will be in suspended state. You need to be manually resume the task using ALTER TASK.
• The Schedule parameter takes only minutes. It does not support second or hour.
• The minimum value of a schedule parameter is 1 minute and the maximum value that can be assigned to schedule parameter is 8 days i.e 11520 minutes.

Snowflake task can be schedule in three ways as follows:

1. Schedule based on the CRON timing

2. Schedule based on time duration in minutes.

3. Schedule to run task, depending on other task execution

How to Start the task in Snowflake

You can start the task by altering the task once it gets created. You have to explicitly needed to start the task.

--SQL Code statement  to Start the tasks
ALTER TASK Snowflake_Task_Example RESUME;

Above SQL statement will enable the task. Now based on the schedule of the task it will going to get executed repeatedly.

How to Stop the task in Snowflake

-- Stop the tasks
ALTER TASK Snowflake_Task_Example SUSPEND;

How to Schedule the Task in snowflake using the Minute field

CREATE OR REPLACE TASK Snowflake_Task_Example
    WAREHOUSE = COMPUTE_WH
    SCHEDULE = '180 MINUTE'  -- This will run for every 3 Hours
    AS
    -- Your Task logic put it here

How to Schedule the Task in snowflake using the CRON Time Format.

Example of the snowflake task is scheduled based on the cron time format

-- Schedule the task for running every minute

CREATE OR REPLACE TASK EX_TABLE_INSERT
    WAREHOUSE = COMPUTE_WH
    SCHEDULE = 'USING CRON * * * * * UTC'
    AS
      -- Your Task logic put it here

-- Schedule the task for running every midnight America/Chicago Time 

CREATE OR REPLACE TASK EX_TABLE_INSERT
    WAREHOUSE = COMPUTE_WH
    SCHEDULE = 'USING CRON 0 0 * * * America/Chicago'
    AS
      -- Your Task logic put it here

Below screenshot help you to define the CRON time as per your need.

How to Schedule a Snowflake task as depended task

In the snowflake we have the functionality to create the hierarchy of the tasks. For example we can have a situation where, we want to execute the Task 2 only after Task 1 get executed successfully.

Here the Task 1 would be called as the root task and the Task 2 would be called as the child task or dependent task. Frequency of the Task 2 execution would solely dependent on the Task 1 frequency.

/*
Create Two task in such a way that second task will be executed only after the first task get execute.

*/

CREATE OR REPLACE TASK Snowflake_Task_Example_1
    WAREHOUSE = COMPUTE_WH
    SCHEDULE = '180 MINUTE'  -- This will run for every 3 Hours
    AS
    -- Your Task logic put it here



CREATE TASK Snowflake_Task_Example_2
  WAREHOUSE = COMPUTE_WH
  AFTER Snowflake_Task_Example_1      -- This is the keyword used to schedule depended task
AS
 -- Your Task logic put it here

How to change the schedule of existing Snowflake task?

-- This will change the schedule time of the Snowflake task

Alter task your_task_name set schedule = 'USING CRON *  * * * * UTC'

How to change the schedule timezone of the existing Snowflake task?

-- This will change the schedule timezone of the Snowflake task

Alter task your_task_name set timezone = 'Australia/Sydney', client_timestamp_type_mapping = timestamp_ltz;

How to change the current task dependency of the existing Snowflake task?

-- Alter the snowflake task scheduled dependency

Alter task Snowflake_Task_Example_2 remove after Snowflake_Task_Example_1_Old;

Alter task Snowflake_Task_Example_2  add after Snowflake_Task_Example_1_New;

What is the maximum schedule time of Snowflake Task in minute?

The maximum schedule time value can be given in minute is 11520 (8 days). Tasks that have a greater than 11520 MINUTE will not run.

Stored Procedure

Create a task named my_copy_task that calls a stored procedure to unload data from the mytable table to the named mystage stage (using COPY INTO <location>) every hour:

-- Create a stored procedure that unloads data from a table
-- The COPY statement in the stored procedure unloads data to files in a path identified by epoch time (using the Date.now() method)
create or replace procedure my_unload_sp()
  returns string not null
  language javascript
  as
  $$
    var my_sql_command = ""
    var my_sql_command = my_sql_command.concat("copy into @mystage","/",Date.now(),"/"," from mytable overwrite=true;");
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
  return my_sql_command; // Statement returned for info/debug purposes
  $$;

-- Create a task that calls the stored procedure every hour
create task my_copy_task
  warehouse = mywh
  schedule = '60 minute'
as
  call my_unload_sp();

Multiple SQL Statements Using SnowSQL

To execute the CREATE TASK statement, you must temporarily set a character other than a semicolon as the delimiter for SQL statements; otherwise, the CREATE TASK statement would return a user error. The command to change the SQL delimiter in SnowSQL is !set sql_delimiter = <character>.

!set sql_delimiter=/
CREATE OR REPLACE TASK test_logging
  USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'XSMALL'
  SCHEDULE = 'USING CRON  0 * * * * America/Los_Angeles'
AS
BEGIN
  ALTER SESSION SET TIMESTAMP_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
  SELECT CURRENT_TIMESTAMP;
END;/
!set sql_delimiter=";"

Procedural Logic Using Snowflake Scripting

Create a task that declares a variable, uses the variable, and returns the value of the variable every 2 minutes:

CREATE TASK t1
  USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'XSMALL'
  SCHEDULE = '2 minute'
  AS
  EXECUTE IMMEDIATE
  $$
  DECLARE
    radius_of_circle float;
    area_of_circle float;
  BEGIN
    radius_of_circle := 3;
    area_of_circle := pi() * radius_of_circle * radius_of_circle;
    return area_of_circle;
  END;
  $$;

References

https://thinketl.com/snowflake-tasks-execute-sql-statements-on-schedule/

https://azurelib.com/how-to-schedule-task-in-the-snowflake-data-warehouse/

https://docs.snowflake.com/en/sql-reference/sql/create-task.html