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

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