How to create Task in Snowflake

Are you looking to find how to create the task within the snowflake cloud data warehouse or maybe you are looking for a solution to the scenario where you should create the task. 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 creating the task. After going through this article you will be able to know what are the different types of the task in Snowflake and what are the different ways in which you can create the task. Let’s don’t waste the time and I will take you quickly to the code directly where I will show you how to create the task.

There are two ways to create the Task in Snowflake as follows

1. Using the Snowflake web UI : Run the create task query.
2. Using the SnowSql command line tool: Run the create task query in command line tool.

Before creating the task in a Snowflake first let’s talk about the task itself.

Schedule Task

What is the Task in the Snowflake?

The task can be defined as a single SQL operation or a set SQL operation that can be run on a scheduled time based upon time frequency.  The task can be run only on a specific interval or we can have a chain of task where one task will be executed after the other one depending on the condition provided.

By using the parent and child task we can have a tree of the tasks where the first task needs to be scheduled and the rest of the other task can be the dependent task.

How to create Task in Snowflake using the Snowflake Web UI Portal

Like creating the table or stage or file format in the Snowflake,  we don’t have any UI where we can go into the Snowflake portal and create the task using the web user interface.

We need to write the SQL command which will create the task based on our needs. The basic syntax for the creation of the task could be as follows

-- Sample Query Syntax for task creation 

CREATE [ OR REPLACE ] TASK [ IF NOT EXISTS ] <name>
  [ { WAREHOUSE = <string> } | { USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = <string> } ]
  [ SCHEDULE = '{ <num> MINUTE | USING CRON <expr> <time_zone> }' ]
  [ ALLOW_OVERLAPPING_EXECUTION = TRUE | FALSE ]
  [ <session_parameter> = <value> [ , <session_parameter> = <value> ... ] ]
  [ USER_TASK_TIMEOUT_MS = <num> ]
  [ COPY GRANTS ]
  [ COMMENT = '<string_literal>' ]
  [ AFTER <string> ]
[ WHEN <boolean_expr> ]
AS
  <sql>

•  Name –  could be any valid name with Snowflake.

•  Warehouse – you have to provide which computation power will be used while running the task. there could be a user-defined virtual warehouse or another one could be a Snowflake managed warehouse.

If it is the root task then you must need to define the scheduled time if it is a dependent task then we can have added the dependency.

Example of Snowflake task

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

How to create the task using the SnowSQL as the command line tool?

In order to create the task first let’s log in to the snowsql command-line tool. In case you want to create the task within a specific database and a schema then please go to that specific database and schema using the use command.

For example, my database is Azurelib and my schema name is Snowflake_example hence I can  use the following command

Use database azurelib;
Use schema Snowflake_example

Now to create the task you have to use the create task SQL query which could be as follows.

-- 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_SQL
    WAREHOUSE = COMPUTE_WH
    SCHEDULE = '1 MINUTE'  --ALWAYS IN MINUTES like 120 MINUTE
    AS
    INSERT INTO Snowflake_Task_Demo(INSERTTION_DATETIME) VALUES(CURRENT_TIMESTAMP);

What do you mean by the dependent task in the Snowflake?

In the Snowflake we can create the hierarchy of the task, for example, we have a situation where we want to run task1 based on the time and the task2 should be executed only once task1 gets finished. If I have this kind of situation then I can say that task 2 is the dependent task on task 1

Task 2 will not get executed until unless task 1 is finished work using this concept we can create the hierarchy of the task or tree of tasks.

What is the prerequisite for creating the dependent task in the Snowflake?

To create the dependent task in the Snowflake you need to have at least one task gets created already using the scheduled time. 

How to create the dependent task in the Snowflake?

In order to create the dependent task in the Snowflake, we need to use the SQL command. The procedure will remain the same, only the thing that will change is the schedule. Now while creating the dependent task, we will not give any scheduled time instead we will provide the dependency using the dependent on keyword.

An example of the dependent task in the Snowflake is as follows

/*
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_Dependent_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_Dependent_Task_Example_1     -- This is the keyword used to schedule depended task
AS
 -- Your Task logic put it here

 Congratulation you have treated the dependent us successfully

How to start the Task in the Snowflake?

Once you create the task into the Snowflake it is not automatically started. You have to manually go and resume the task, after that only the task will be scheduled for execution.  SQL Query which is used for enabling the task for execution is as follows

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

This query will start the task in the Snowflake for execution, now based upon the schedule time this task will be e scheduled to execute.

How to stop the task in the Snowflake?

Once the task get enabled and started it will keep on running at the scheduled time until unless you go and stop the task manually. There is a suspend command which needs to be run to stop the task from execution. Once you suspend a task, that task is still there but it will not be scheduled for execution.  You can start, stop tasks any number of times there is no limit on that.

 Query to stop the task is as follows

-- Stop the tasks
ALTER TASK Snowflake_Task_Example SUSPEND;

 This query will stop the task from execution and the state of the task is suspended

How to delete the task in the Snowflake?

As the task gets to stop it is still there in the system. It is not get deleted unless you explicitly go and drop the task. So using the Drop command you can drop the task and once the task gets drop you cannot revert back. 

Hence you need to be careful before deleting the task.  SQL command to delete the task is as follows:

 DROP TASK Snowflake_Task_Example 

What are the privileges required to create tasks in Snowflake?

Following access rights are required to create the task in Snowflake:

Real-world Use case Scenario for Task with in the Snowflake?

• Assume that there is a certain process that needs to be executed every midnight and this process will copy the data from one table to another table. Now in this case, if you want to automate this process which should run automatically every midnight, then you can use the Snowflake task. You can use the task as a tool to automate the SQL execution based on the scheduled time interval.

• Sometimes there are multiple applications within the system. Assume that there is a third-party application that is generating some file and dropping it into a specific folder location into the cloud. Now using the SnowPipe you are reading all those files within the Snowflake table using the stream. Once you receive the new records, you may want to execute some operations daily basis on this newly received data. This work also can be scheduled using the task.

Snowflake official Documentation Link

Final Thoughts

By this, we have reached the end of our insightful article on how to create the Snowflake task.  In this article, we have learned what is the task in the Snowflake. We have also gone through how you can create the task using the Snowflake portal as well as how you can create the task using the snow SQL command-line tool.  I have shared the practical Real-world scenarios where you need to use the task in the Snowflake. Lastly, I have explained how to start, stop the task and how we can permanently delete the task from the Snowflake.  I have already written one dedicated article on how to schedule the task in case you have any queries on how to schedule the task please visit this blog to know much more about it.

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.