Snowflake Task with multiple schedule | Can we have a single snowflake Task with two schedules

{tocify} $title={Table of Contents}

Introduction

We have to update a table whenever new data arrives but at a predefined time i.e every 45 mins on weekdays and on weekends at 3:30 am and 5:30 pm.

So for that we create a stored procedure having the update query and  call that in a task.

And to schedule it, we use Cron expression

Using CRON expression to create schedule

A cron expression is a way to describe a schedule, it is string consisting of six or seven fields (subexpressions) which describe individual details of the schedule. 

However in Snowflake, cron expression is a string consisting of 5 fields

               

Note: L stands for last

Below is the code to add schedule to Task

 

                             schedule='USING CRON 15 * * * MON UTC'

Now the requirement is to have two different schedules i.e. one for weekdays  and another for weekends.

                       Every 45 mins in week days (Monday-Friday)

                             Saturday, Sunday – 3:30am, 5:30pm 

So the schedules will be 

                     schedule='USING CRON 45 * * * MON-FRI UTC'            

                              

                                                                     and 

                     schedule='USING CRON 30 3,17 * * SAT,SUN UTC'

Note - UTC is the time zone used here for example, you can add as per the location

Now the question arises,

Can we have a single snowflake Task with two schedules?

The answer is no, it is not supported . 

Tasks has some constraints, like

•  Only one schedule can be associated with a Task
•  Only one SQL statement or
•  Only one call to stored procedure can be made in a Task

Solution

We need to create two Tasks (performing the same activity - calling a stored procedure), one for weekdays and another for weekends with their respective schedules.

CREATE OR REPLACE TASK Object_Dataupsert_Task_Weekdays WAREHOUSE=DEV schedule='USING CRON 45 * * * MON-FRI UTC' AS CALL SPROC_Object_Dataupsert();

 

 

CREATE OR REPLACE TASK Object_Dataupsert_Task_Weekends WAREHOUSE=WH_DEV_OPSDATAETL schedule='USING CRON 30 3,17 * * SAT,SUN UTC' AS CALL SPROC_Object_Dataupsert();

 

Do not forget to Resume task after creation as by default when you create Task, it is in suspended state

                                      Alter  TASK Object_Dataupsert_Task_Weekdays Resume

                                      Alter  TASK Object_Dataupsert_Task_Weekends Resume

If you have questions or suggestions, feel free to do in comments section below !!!

Do share if you find this helpful .......

 

                          Knowledge Sharing is Caring !!!!!!

Learn More about Snowflake

• How to fetch history data from Snowflake without using UI | Programmatically get the queries executed on Snowflake
• Snowflake Task with multiple schedule | Can we have a single snowflake Task with two schedules
• Frequently used queries in Snowflake | Snowflake query samples

.