1
0
In this blog post, we will delve into a specific scenario concerning the suspension of Child tasks in Snowflake. A colleague recently presented a challenge where he sought to automate the suspension of particular CHILD tasks. In his current setup, there were over 20+ child task intricately connected to a parent TASK.
While Snowflake provides a command to enable all child tasks at once (SELECT SYSTEM$TASK_DEPENDENTS_ENABLE('<<PARENT TASK>>')), there isn’t a corresponding command to suspend all child tasks. Consequently, manual intervention becomes necessary.
While a manual approach suffices for a smaller number of task, it becomes impractical when dealing with a complex TASK hierarchy and an extensive number of child tasks dependent on a single parent. To address this limitation, we’ve crafted the following stored procedure. This procedure efficiently suspends the Parent Task along with all its associated child tasks in a single execution. Additionally, the procedure logs the statements issued for each child task and provides a final count of disabled task. This streamlined process alleviates the burden of manual suspension in scenarios with intricate task hierarchies and numerous child task.
Say we have list of these TASK in or System where TASK_1 is the Parent Task.
I have executed below command to enable all the task in one go:
Verify the status now:
Stored Procedure:
Proc output:
Status of TASK:
