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:
INFORMATION_SCHEMA.TASK_HISTORY: A table function that provides real-time status of tasks run in the last 7 days.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 theERROR_MESSAGEcolumn).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 usingSYSTEM$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_HISTORYfor real-time debugging of last night's batch loads. - Pay attention to the
SKIPPEDstate, 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.