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

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




Amazon_India Great Festival Sale | Getupto 70%OFF on Amazon Brands & More


Learn More about Snowflake




.

Post a Comment

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

Previous Post Next Post