snowflake-cloud-data-platform

How to tell if any of the tasks in the DAG has failed?

In a DAG, how can I tell if any of the tasks in the DAG has failed?

Suppose I have a DAG that looks like this (simplified version):

enter image description here

Any of the DAGs in the middle (201, 202) may fail. But when they fail, the FINAL node is not executed.

Now of course I could check all of the intermediary tasks (201, 202), but that quickly becomes unwieldy as I add more and more tasks. So ideally I would check for a failure of ANY task in a DAG.

My original idea was to check the final task:

SELECT NAME, STATE, ERROR_CODE, ERROR_MESSAGE, QUERY_START_TIME, COMPLETED_TIME
FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY())
WHERE NAME = 'ANRUFGRUENDE_FINAL_TSK'
QUALIFY ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY COMPLETED_TIME DESC NULLS LAST) = 1 -- only show latest run;

but it never executes if an upstream task fails, so that's not feasible. I guess I could take the absence of an expected FINAL task run as an indicator that an upstream task has failed, but I would rather have a direct signal instead.

What is the best way to approach this?

Solution

  • COMPLETE_TASK_GRAPHS can be used to check the status of tasks execution:

    Returns the status of a completed graph run. The function returns details for runs that executed successfully, failed, or were cancelled in the past 60 minutes

    SELECT * 
FROM TABLE(INFORMATION_SCHEMA.COMPLETE_TASK_GRAPHS(ROOT_TASK_NAME=>'<name>'));

    COMPLETE_TASK_GRAPHS view

    Latency for the view may be up to 45 minutes.

    SELECT *
FROM SNOWFLAKE.ACCOUNT_USAGE.COMPLETE_TASK_GRAPHS;