How to check Task History in Snowflake?

Are you looking to find how to check the history of the task execution within the snowflake cloud data warehouse or maybe you are looking for a solution to the find the status of the old task execution runs. You may also want to check what could be the next schedule time of the task in Snowflake. In this article, I will take you through a step-by-step process of finding the task history and get the status of the task. After going through this article you will be able to know how to debug the task execution and identify, was there any issue with the task in the Snowflake. Let’s don’t waste the time and I will take you quickly to the code directly where I will show you how to check the task history.

How to create Task in Snowflake

How to schedule task in Snowflake

To check the history of the task use below SQL query:

select * from table ( information_schema.task_history( [ SCHEDULED_TIME_RANGE_START => ] [, SCHEDULED_TIME_RANGE_END => ] [, RESULT_LIMIT => ] [, TASK_NAME => ” ] ))

Example to check Task History in Snowflake:

-- This query will show the task history for last 1 hour for task name  = Snowflake_task_name

  select *
  from table(information_schema.task_history(
    scheduled_time_range_start=>dateadd('hour',-1,current_timestamp()),
    result_limit => 10,
    task_name=>'Snowflake_task_name'))

How to check the history of all the task in the Snowflake?

In the Snowflake using the Task_history command we can retrieve the history for all the task. We have the option to provide the task name in the task history query. Once you not provide the task name within the Task_History, it will return the historical information for all the task.

Example query to check history of all the task in Snowflake

Query is follows:

select *
  from table(information_schema.task_history(
    result_limit => 100))

Above query will return last 100 task execution based on the timestamp.

How to check the history of all the task executed in last one hour in the Snowflake?

In the Snowflake using the Task_history command we can retrieve the history for all the task. We have the option to provide the task name in the task history query. Once you not provide the task name within the Task_History, it will return the historical information for all the task. However once you add the start time range as last one hour within the Task_History query then it will return all the task executed in last hour.

Example query to check history of all the task executed in last hour in Snowflake

Query is follows:

select *
  from table(information_schema.task_history(
    scheduled_time_range_start=>dateadd('hour',-1,current_timestamp()),
    result_limit => 100))

Above query will return last 100 task executed in last one hour.

How to check if snowflake task is not running?

There could be multiple reasons because that your Snowflask task might not be running.

There could be set of few possible reasons.

1. Your task’s SQL might be throwing some error.

2. Task’s parent task not executed yet.

3. Task in Suspended state

4. Task schedule time yet to arrive.

Let’s understand each reason in detail.

Reason 1:

Task has been executed, but the SQL which is used within the task might have caused some exceptions. Hence this could be one of the reasons that’s why you are not able to see any changes.

You can verify the task execution history to check the whether any exception occurred or not. Run the task histroy query for the specific task which you want to analyze. Once you get the task history result just there STATE and ERROR CODE.

In case your task is failed then you will get the error code and there will be the reason mentioned in the error_message column. Now you can analyze the error message, make the changes and restart your task.

Reason 2:

Task might be scheduled as the dependent task. In that case, it is possible that the parent task has not been executed due to which this task is not executed.

You can run the Task_history query to check the execution summary of the task. By this you will get know whether the task is executed yet or not. In case this case is dependent on other task, then check the Task history for the parent task.

There you will get to know whether there could be some issue with the parent task itself or not.

Reason 3:

Your task might be in a suspended state that’s why it is not running. Again you can use the task history query to identify whether your task is scheduled to run or not.

What is the maximum number of rows can be return by Task_history query?

The maximum number of rows that can be returned by the task history query is 10000.

By default, the number of rows returned by the task history to query is 100 and we can have a range from 1 to 10000

What could be the maximum date range for the task history?

 We can only get the task history for the past seven days. We cannot get the task history for more than the past 7 days same for the scheduled time as well. We can only get task schedule time detail for the next eight days.

How to check snowflake task status?

You can use the task history to get the task status. For example in the task history, you have to pass the task name and the time duration for which you want to check the status of the task. By this, you will get the complete detail of the task status like whether this has got succeeded failed, or canceled. You can find it by checking the state of the task.

What are the different states of a Snowflake task?

 There could be five different states of the task as follows:

SUCCEEDED, FAILED, or CANCELLED, SCHEDULED, EXECUTING.

How to get schedule task details only, from task history in Snowflake?

Task history return multiple properties. One of the property or column name is Query_id. This query id represent the id for the query executed for the task. Hence if a task is not executed then query id will come out to be null only.

Hence to get only the scheduled task detail, you can use the where clause using the query_id = null.

For example:

-- Query to get only schedule task detail only using query_id null

  select *
  from table(information_schema.task_history(
    scheduled_time_range_start=>dateadd('hour',-1,current_timestamp()),
    result_limit => 10,
    task_name=>'My_Task_name')) where QUERY_ID is  null 

What are the list of the column return by Task_History function in Snowflake?

The list of the columns returned by the task_history function is as follows:

Snowflake official Documentation Link

Final Thoughts

By this, we have reached the end of our insightful article on how to check the history of the Snowflake task.  In this article, we have learned how to check task history in the Snowflake. We have also gone through how you can check why Snowflake task is not running and what could be the possible reason or may be how you can debug. I have shared the practical Real-world scenarios which could be cause of failed task status in the Snowflake. I have already written one dedicated article on how to create task and how to schedule the task in case you have any queries on how to schedule the task please visit this blog to know much more about it.

Please share your comments and suggestions in the comment section below and I will try to answer all your queries as time permits.

• For Azure Study material Join Telegram group : Telegram group link:
• Azure Jobs and other updates Follow me on LinkedIn: Azure Updates on LinkedIn
• Azure Tutorial Videos: Videos Link

Deepak Goyal

Deepak Goyal is certified Azure Cloud Solution Architect. He is having around decade and half experience in designing, developing and managing enterprise cloud solutions. He is also Big data certified professional and passionate cloud advocate.