TASKS in Snowflake

user5aX21 0
Viewed 47

I have created two tasks to run once a day

create or replace task TESTDB.TESTSCHEMA.TASK_EXTERNAL_REFRESH
    warehouse=W_TEST_DEVELOPER
    schedule='USING CRON 0 4 * * * UTC'
    TIMESTAMP_INPUT_FORMAT='YYYY-MM-DD HH24'
    as 
    call TESTDB.TESTSCHEMA.TEST_EXTERNAL_TABLE_REFRESH();
    
    
    
create or replace task ESTDB.TESTSCHEMA.TASK_LOAD_TABLES
    warehouse=W_TEST_DEVELOPER
    schedule='USING CRON 0 5 * * * UTC'
    TIMESTAMP_INPUT_FORMAT='YYYY-MM-DD HH24'
    as 
    call TESTDB.TESTSCHEMA.TEST_LOAD_TABLES();

Now I want to ensure that TESTDB.TESTSCHEMA.TASK_EXTERNAL_REFRESH runs before TASK_LOAD_TABLES runs.

How should I do this ? Also, should the error details from task run be captured in config tables? What is "TESTDB.TESTSCHEMA.TASK_EXTERNAL_REFRESH" fails? If this fails, next one should not run.

snowflake-cloud-data-platform
2 Answers

The precedence rule should be added instead of schedule:

ALTER TASK TESTDB.TESTSCHEMA.TASK_LOAD_TABLES  
ADD AFTER  TESTDB.TESTSCHEMA.TASK_EXTERNAL_REFRESH;

CREATE TASK:

AFTER string [ , string , ... ]

Specifies one or more predecessor tasks for the current task. Use this option to create a DAG of tasks or add this task to an existing DAG. A DAG is a series of tasks that starts with a scheduled root task and is linked together by dependencies.

Related: Snowflake - Many tasks dependencies for a task

Lukasz Szozda0

For query on the predecessor and successor tasks, you should use the "after taskname" option

create task task2
after task1
as
insert into t1(ts) values(current_timestamp);

https://docs.snowflake.com/en/sql-reference/sql/create-task.html#single-sql-statement

A few options to check the status of the task and decide on the successor/child task execution are given below.

You can use the SUSPEND_TASK_AFTER_FAILURES = number

https://docs.snowflake.com/en/user-guide/tasks-intro.html#automatically-suspend-tasks-after-failed-runs

Create a task that calls a UDF to check the ACCOUNT_USAGE.TASK_HISTORY or INFORMATION_SCHEMA.TASK_HISTORY views for task status.

You can use external tools to check the status of the task and integrate it.

sprethepa0
Related