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

{tocify} $title={Table of Contents}


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

cron expression in snowflake

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'            

                     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


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


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

AS CALL SPROC_Object_Dataupsert();




CREATE OR REPLACE TASK Object_Dataupsert_Task_Weekends


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



If you have any suggestions or questions or want to share something then please drop a comment

Previous Post Next Post