Skip to main content

Task Monitor & Task History

Tasks are the native orchestration engine in Snowflake, allowing you to schedule SQL scripts and stored procedures automatically.

If you are running a complex Directed Acyclic Graph (DAG) of tasks overnight to load your Data Warehouse, you must have a robust way to monitor their execution, trace failures, and track the credits they consume.

Topics Covered​

  • Querying TASK_HISTORY
  • Understanding Task States
  • Monitoring Task DAGs
  • Serverless vs Warehouse Compute Costs
  • Setting up Task Failure Alerts

1. Querying TASK_HISTORY​

There are two ways to view task history:

  1. INFORMATION_SCHEMA.TASK_HISTORY: A table function that provides real-time status of tasks run in the last 7 days.
  2. SNOWFLAKE.ACCOUNT_USAGE.TASK_HISTORY: A view that provides 365 days of history (with up to a 45-minute delay).

(To query these, you need the ACCOUNTADMIN role or a role with MONITOR EXECUTION privilege. Verify your role: SELECT CURRENT_ROLE();)

Hands-On: Finding Failed Tasks​

Let's use the real-time INFORMATION_SCHEMA function to find any tasks that failed during last night's batch load.

SELECT 
NAME AS TASK_NAME,
DATABASE_NAME,
STATE,
ERROR_CODE,
ERROR_MESSAGE,
SCHEDULED_TIME,
COMPLETED_TIME
FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY(
SCHEDULED_TIME_RANGE_START => DATEADD('hour', -12, CURRENT_TIMESTAMP())
))
WHERE STATE = 'FAILED'
ORDER BY SCHEDULED_TIME DESC;

2. Understanding Task States​

When querying the history, the STATE column is the most important indicator.

  • SUCCEEDED: The task executed successfully.
  • FAILED: The task encountered a SQL error or timed out. (Check the ERROR_MESSAGE column).
  • SKIPPED: The task was scheduled to run, but the previous run had not yet finished. Snowflake will not start a new instance of a task if the old one is still running.
  • CANCELLED: An administrator manually killed the task while it was running using SYSTEM$CANCEL_QUERY().

3. Monitoring Task DAGs (Dependent Tasks)​

In a DAG, you have a Root Task and multiple Child Tasks. If the Root Task fails, the Child Tasks will not execute.

To troubleshoot a DAG, you need to find the specific "Run ID" of the Root Task and view the execution tree.

-- View the entire DAG execution for a specific Root Task
SELECT
ROOT_TASK_ID,
NAME AS TASK_NAME,
STATE,
SCHEDULED_TIME
FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY(
ROOT_TASK_NAME => 'MASTER_ETL_ROOT_TASK',
SCHEDULED_TIME_RANGE_START => DATEADD('day', -1, CURRENT_TIMESTAMP())
))
ORDER BY SCHEDULED_TIME ASC;

4. Serverless Tasks vs Virtual Warehouse Tasks​

When you create a Task, you must assign it compute resources. You have two options, and you must monitor them differently.

Option A: Virtual Warehouse Task

CREATE TASK MY_TASK WAREHOUSE = COMPUTE_WH ...
  • The task wakes up your existing warehouse.
  • You monitor the cost using ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY.

Option B: Serverless Task

CREATE TASK MY_TASK USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'SMALL' ...
  • Snowflake automatically provisions and manages the compute. You only pay for the exact seconds the task runs.
  • You monitor the cost using a different view: ACCOUNT_USAGE.SERVERLESS_TASK_HISTORY.
-- Monitor Serverless Task Costs
SELECT
TASK_NAME,
SUM(CREDITS_USED) AS TOTAL_SERVERLESS_CREDITS
FROM SNOWFLAKE.ACCOUNT_USAGE.SERVERLESS_TASK_HISTORY
WHERE START_TIME >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY TASK_NAME
ORDER BY TOTAL_SERVERLESS_CREDITS DESC;

5. Setting up Alerts for Task Failures​

You should not manually query TASK_HISTORY every morning. You should build a Snowflake Alert that automatically emails the Data Engineering team if a task fails.

-- 1. Create a Notification Integration (Assume AWS SNS or Email is configured)
-- (Setup code omitted for brevity)

-- 2. Create the Alert
CREATE OR REPLACE ALERT ALERT_TASK_FAILURE
WAREHOUSE = COMPUTE_WH
SCHEDULE = '30 MINUTE'
IF (EXISTS (
SELECT 1
FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY(
SCHEDULED_TIME_RANGE_START => DATEADD('minute', -30, CURRENT_TIMESTAMP())
))
WHERE STATE = 'FAILED'
))
THEN
CALL SYSTEM$SEND_EMAIL(
'MY_EMAIL_INT',
'data-eng-team@beems.tech',
'ALERT: Snowflake Task Failed',
'One or more tasks failed in the last 30 minutes. Please check TASK_HISTORY immediately.'
);

-- 3. Resume the Alert
ALTER ALERT ALERT_TASK_FAILURE RESUME;

Summary​

  • Use INFORMATION_SCHEMA.TASK_HISTORY for real-time debugging of last night's batch loads.
  • Pay attention to the SKIPPED state, which indicates your task schedule is too aggressive (it runs faster than it can finish).
  • Serverless Tasks are billed differently than Warehouse Tasks; monitor them using SERVERLESS_TASK_HISTORY.
  • Always set up Snowflake Alerts to proactively notify your team of task failures.