How to Schedule Task in the Snowflake Data warehouse

Are you looking to find how to schedule the task within the snowflake cloud data warehouse or maybe you are looking for a solution to the scenario where you have to change the schedule time based on the time zone. You may also want to learn how to start or stop the task schedules in the snowflake. In this article, I will take you through a step-by-step process of scheduling the task. After going through this article you will be able to schedule the daily running task, hourly task or schedule a task to run every minute or at some specific interval at specific time. Let’s don’t waste the time and I will take you quickly to the code directly where I will show you how you can schedule the task.

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

Let’s start first with creating one sample table which we will use it for the task creation.

Learn Azure Data Factory Tutorial for Beginners

Learn Azure Databricks Tutorial for Beginners

Create Table in Snowflake

--Creating a Snowflake_Task_Demo table with just two columns. One will be the id and other --- would be current timestamp of the insertion.
 
create or replace table Snowflake_Task_Demo (
 
    ROW_ID INT AUTOINCREMENT,
    INSERTTION_DATETIME TIMESTAMP
  )

Output of the table creation will look like as follows :

Create Task and Schedule in SnowFlake

Let’s create a task and we will schedule the task which will run every minute. This task will insert the record into our Snowflake_Task_Demo table. Our task example is simple one where it just insert the current timestamp as a row in the table.

-- SQL Code for Task creating , which will insert the row in to our demo table 
-- 'Snowflake_Task_Example'
-- We have schedule the task to run every 1 minute using the SCEDHULE command

CREATE OR REPLACE TASK Snowflake_Task_Example
    WAREHOUSE = COMPUTE_WH
    SCHEDULE = '1 MINUTE'  --ALWAYS IN MINUTES like 120 MINUTE
    AS
    INSERT INTO Snowflake_Task_Demo(INSERTTION_DATETIME) VALUES(CURRENT_TIMESTAMP);

You can see that in the above SQL code we have not just only created the task but we have also scheduled the task using the ‘SCHEDULE’ keyword. We have schedule it to run every 1 minte.

Scheduling the task is not only sufficient and it won’t going to execute. By default the snowflake task you create will remains stopped or in the suspended mode only. You have to go to the task and manually enable or start it to execute based on its schedule.

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.

You can see below the task we scheduled runs every minute and inserted these many rows.

How to Stop the task in Snowflake

Once the task get started it won’t going to get stopped automatically until and unless you stop it manually. By default after the creation of the task, it will be in the stop/suspended mode only.

-- Stop the tasks
ALTER TASK Snowflake_Task_Example SUSPEND;
 

Above SQL command will stop the task Snowflake_Task_Example from execution. However once you stop the task you won’t have to re-schedule the task again. Once you again resume the task, it will automatically schedule to same frequency.

How to Schedule the Task in snowflake using the Minute field

In the snowflake we have the functionality to schedule the task even based on the minutes. As a part of this functionality we can provide the time interval in minutes after which we want the task to get executed automatically. 

For example if I want to execute the task after every 5 minutes I can I schedule saying it 5 minutes, or else if I want to schedule the task for every 15 minutes I could give the 15 minutes in the task scheduling.

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

We can give time only in terms of the minutes, so for example if you want to schedule the task which should run after every 3 hours in that case you can give it using 180 minutes

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

CRON time format is one of the most popular scheduling time format in the computer world. In Linux and in Windows both many jobs are scheduled using this cron time format itself.

With the CRON time format you can specify at what time, at what interval, at what frequency this job has to execute automatically.

You can even set the timezone as well 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

Above task will schedule to run every minute.

Here you can change the CRON time to schedule it to run at different time interval.

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

You can also use this online CRON tool to check you timing. Online CRON tool

How to schedule a task in snowflake to run every midnight America/Chicago TimeZone

We can schedule the task in snowflake to run at a specific timezone.  Even the timezone of the task is different from the timezone set in the snowflake account level.

SQL command for the scheduling the task based on the America/Chicago time zone is as follows

-- 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

How to schedule a task in snowflake to run every midnight Europe/London TimeZone

-- Schedule the task for running every midnight  Europe/London Time 

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

How to schedule a task in snowflake to run every midnight Australia/Sydney TimeZone

-- Schedule the task for running every midnight Australia/Sydney Time 

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

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

We can maximum 100 child task possible for a task in the snowflake as of writing this blog.

How to change the schedule of existing Snowflake task?

There is way to, alter the Snowflake task schedule time even after the creation. You can use the Alter task command to reschedule the Snowflake task time.

-- 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?

There is way to alter the, Snowflake task schedule timezone even after the creation. You can use the Alter task command to reschedule the Snowflake task timezone. The following example sets the TIMEZONE and CLIENT_TIMESTAMP_TYPE_MAPPING session parameters for the session in which the task runs:

-- 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?

There is way to alter the, Snowflake task dependency even after the creation of the task. For example you want to change the predecessor of the snowflake task or you want to change the parent task for a child task. The following example will alter the task dependecy.

-- 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.

Snowflake official Documentation Link

Final Thoughts

By this we have reached to end of this insightful article about the Snowflake task scheduling. In this post we have learned how to create a task, how to schedule a task in Snowflake using the minute. I have also taught you, how to schedule a task in Snowflake using the CRON time format expression. Besides this I have also shared the tips to schedule the dependent task in Snowflake as per my long experience in the Snowflake cloud data warehouse. Lastly I have covered about how you can alter the scheduling time of the Snowflake task even after the creation of the task.

Please share your comments and suggestions in the comment section below and I will try to answer all your queries as time permits.

• For Azure Study material Join Telegram group : Telegram group link:
• Azure Jobs and other updates Follow me on LinkedIn: Azure Updates on LinkedIn
• Azure Tutorial Videos: Videos Link

Deepak Goyal

Deepak Goyal is certified Azure Cloud Solution Architect. He is having around decade and half experience in designing, developing and managing enterprise cloud solutions. He is also Big data certified professional and passionate cloud advocate.