How to Schedule a Snowflake task as depended task ?

In the snowflake we have the functionality to create the hierarchy of the tasks. For example we can have a situation where, we want to execute the Task 2 only after Task 1 get executed successfully.

To know more about TASK & Scheduling a task (Click Here).

Here the Task 1 would be called as the root task and the Task 2 would be called as the child task or dependent task to Task 1and Task 3 would be called as the child task or dependent task to Task 2.

Frequency of the Task 2 execution would solely dependent on the Task 1 frequency and Frequency of the Task 3 execution would solely dependent on the Task 2 frequency. Explained in below diagram.

TASK 3 run dependent on TASK 1 and TASK 2

Syntax:
Create three task in such a way that second task will be executed only after the first task get executed and third task will be executed only after the second task get executed.

*/
CREATE OR REPLACE TASK Snowflake_Task_SAMPLE_1_PARENT
WAREHOUSE = COMPUTE_WH
SCHEDULE = ‘1 MINUTE’ — This will run for every 1 minute
AS
— Your Task logic put it here

CREATE TASK Snowflake_Task_SAMPLE_2_CHILD
WAREHOUSE = COMPUTE_WH
AFTER Snowflake_Task_SAMPLE_1_PARENT — This is the keyword used to schedule depended task
AS
— Your Task logic put it here

CREATE TASK Snowflake_Task_SAMPLE_3_CHILD
WAREHOUSE = COMPUTE_WH
AFTER Snowflake_Task_SAMPLE_1_PARENT — This is the keyword used to schedule depended task
AS
— Your Task logic put it here

*/

Example: (Source code)

Created three procedure and scheduled via task which will get executed based on dependency mentioned in TASK.

CREATE OR REPLACE DATABASE SNOWFLAKE_DEMO;

CREATE OR REPLACE SCHEMA TASK_DEMO;

CREATE OR REPLACE TABLE TASK_TABLE(TBL_NAME VARCHAR, LAST_INSERTED_DATE TIMESTAMP);

— — PROECEDURE TO INSERT PARENT RECORDS IN TO THE TABLE

CREATE or replace PROCEDURE PARENT_PRO()
RETURNS VARIANT not null
LANGUAGE JAVASCRIPT
AS
$$
var response_as_json = {};
response_as_json[‘status’] = ‘Success’;
var str = ‘’;
var stmt = snowflake.createStatement({sqlText: “INSERT INTO TASK_TABLE VALUES (‘PARENT_TABLE’,SYSDATE())”});
try
{
stmt.execute();
}
catch (err)
{
snowflake.execute({sqlText: “ROLLBACK WORK;”});
response_as_json[‘status’] = ‘Fail’;
response_as_json[‘error_message’] = err;
}
return response_as_json;
$$;

— TASK TO TRIGGER THE PARENT TASK

CREATE OR REPLACE TASK PARENT_TASK
WAREHOUSE = COMPUTE_WH
SCHEDULE = ‘1 MINUTE’
AS
call PARENT_PRO();

— — PROECEDURE TO INSERT CHILD RECORD 1 IN TO THE TABLE

CREATE OR REPLACE PROCEDURE CHILD_PRO1()
RETURNS VARIANT NOT NULL
LANGUAGE JAVASCRIPT
AS
$$
var response_as_json = {};
response_as_json[‘status’] = ‘Success’;
var str = ‘’;
var stmt = snowflake.createStatement({sqlText: “INSERT INTO TASK_TABLE VALUES (‘CHILD_TABLE1’,SYSDATE())”});
try
{
stmt.execute();
}
catch (err)
{
snowflake.execute({sqlText: “ROLLBACK WORK;”});
response_as_json[‘status’] = ‘Fail’;
response_as_json[‘error_message’] = err;
}
return response_as_json;
$$;

— TASK TO TRIGGER THE CHILD TASK 1

CREATE OR REPLACE TASK CHILD_TASK1
WAREHOUSE = COMPUTE_WH
AFTER PARENT_TASK
AS
call CHILD_PRO1();

— — PROECEDURE TO INSERT CHILD RECORD 2 IN TO THE TABLE

CREATE OR REPLACE PROCEDURE CHILD_PRO2()
RETURNS VARIANT NOT NULL
LANGUAGE JAVASCRIPT
AS
$$
var response_as_json = {};
response_as_json[‘status’] = ‘Success’;
var str = ‘’;
var stmt = snowflake.createStatement({sqlText: “INSERT INTO TASK_TABLE VALUES (‘CHILD_TABLE2’,SYSDATE())”});
try
{
stmt.execute();
}
catch (err)
{
snowflake.execute({sqlText: “ROLLBACK WORK;”});
response_as_json[‘status’] = ‘Fail’;
response_as_json[‘error_message’] = err;
}
return response_as_json;
$$;

— TASK TO TRIGGER THE CHILD TASK 2

CREATE OR REPLACE TASK CHILD_TASK2
WAREHOUSE = COMPUTE_WH
AFTER PARENT_TASK
AS
call CHILD_PRO2();

SHOW TASK()

LAST_INSERTED_DATE column present in the table describes about when records got inserted,

SELECT * FROM TASK_TABLE ORDER BY LAST_INSERTED_DATE DESC;

In this screenshot PARENT_TABLE which got completed, post successful completion of PARENT_TABLE — CHILD TABLE 1 and CHILD TABLE 2 records got inserted.

Note: (Points to remember)

According to snowflake documentation, Executing DDL commands on any task in a tree of tasks requires that the root task is suspended. If the root task is currently resumed, then the command returns a user error.

For above the example the task should be resumed in following sequence,

ALTER TASK CHILD_TASK2 RESUME;
ALTER TASK CHILD_TASK1 RESUME;
ALTER TASK PARENT_TASK RESUME;

In case if you want to suspend the task and that should be in below sequence,

ALTER TASK PARENT_TASK SUSPEND;
ALTER TASK CHILD_TASK1 SUSPEND;
ALTER TASK CHILD_TASK2 SUSPEND;

In snowflake you cannot resume the parent task before child tasks. Always resume child tasks before parent task. Resuming child tasks before parent task does not cause any issues because they are dependent on its parent.