Skip to main content

Tasks – Scheduling, DAGs & Task Trees

Data pipelines rarely consist of a single SQL statement. Usually, you must ingest data, transform it, aggregate it, and move it to a reporting layer in a specific, dependent order.

In Snowflake, Tasks allow you to schedule single SQL statements or Stored Procedures. When you link multiple tasks together using dependencies, you create a Directed Acyclic Graph (DAG) or "Task Tree".

Topics Covered​

  • Serverless vs Warehouse Tasks
  • Creating Standalone Scheduled Tasks
  • Building a Task DAG (Parent & Child Tasks)
  • Task Management (Resume, Suspend, Execute)
  • Monitoring TASK_HISTORY

Serverless vs Warehouse Tasks​

When you create a task, you must decide how it will get the compute power to execute.

  1. Warehouse Tasks: You specify a WAREHOUSE = MY_WH. When the task triggers, it wakes up the warehouse, runs the query, and suspends it. Good for heavy, predictable ETL workloads.
  2. Serverless Tasks: You omit the WAREHOUSE parameter. Snowflake provisions compute from a shared pool and charges you per second. Good for lightweight tasks (like running a quick DELETE statement) or tasks with highly variable execution times.

Hands-On Demonstration: Building a DAG​

We will simulate a daily data pipeline for a logistics company.

  1. A root task runs at midnight to ingest raw data.
  2. A child task waits for the ingestion to finish, then transforms the data.
  3. Another child task waits for the transformation, then builds an aggregated report.

1. Database & Schema Setup​

Copy and paste the following code into your Snowflake worksheet.

USE ROLE SYSADMIN;

CREATE DATABASE IF NOT EXISTS BEEMS_PIPELINE_DB;
USE DATABASE BEEMS_PIPELINE_DB;

CREATE SCHEMA IF NOT EXISTS LOGISTICS_DAG;
USE SCHEMA LOGISTICS_DAG;

-- Mock tables
CREATE OR REPLACE TABLE RAW_LOGISTICS (DRIVER STRING, ROUTE STRING, DISTANCE_KM NUMBER);
CREATE OR REPLACE TABLE CLEAN_LOGISTICS (DRIVER STRING, STATE STRING, DISTANCE_KM NUMBER);
CREATE OR REPLACE TABLE DRIVER_SUMMARY (DRIVER STRING, TOTAL_DISTANCE NUMBER);

2. The Root Task (Scheduled)​

The root task is the only task in the DAG that has a schedule. It dictates when the entire tree starts.

-- Create a Warehouse Task that runs every night at Midnight UTC
CREATE OR REPLACE TASK LOGISTICS_DAG.INGEST_RAW_DATA
WAREHOUSE = COMPUTE_WH
SCHEDULE = 'USING CRON 0 0 * * * UTC'
AS
-- Simulating a data load
INSERT INTO RAW_LOGISTICS VALUES
('Arjun Nair', 'Kerala-TN', 450),
('Priya Sharma', 'Delhi-UP', 320),
('Vikram Singh', 'Punjab-Haryana', 150);

3. Child Tasks (Dependencies)​

Child tasks do not have a schedule. Instead, they use the AFTER clause to define their parent. They only execute if the parent completes successfully.

-- Child Task 1: Cleans the data AFTER ingestion
CREATE OR REPLACE TASK LOGISTICS_DAG.CLEAN_DATA
WAREHOUSE = COMPUTE_WH
AFTER LOGISTICS_DAG.INGEST_RAW_DATA
AS
INSERT INTO CLEAN_LOGISTICS
SELECT DRIVER, SPLIT_PART(ROUTE, '-', 1) AS STATE, DISTANCE_KM
FROM RAW_LOGISTICS;

-- Child Task 2: Summarizes the data AFTER cleaning
-- This task is Serverless (no WAREHOUSE specified)
CREATE OR REPLACE TASK LOGISTICS_DAG.SUMMARIZE_DATA
AFTER LOGISTICS_DAG.CLEAN_DATA
AS
INSERT INTO DRIVER_SUMMARY
SELECT DRIVER, SUM(DISTANCE_KM)
FROM CLEAN_LOGISTICS
GROUP BY DRIVER;

4. Task Management (Resume & Execute)​

By default, when you create a task, it is Suspended. It will not run until you resume it. Crucially, you must resume the child tasks before resuming the root task. If you resume the root task first, the tree might trigger before the children are active.

(To check your active user and ensure you have the OPERATE privilege on these tasks, run SELECT CURRENT_USER();)

-- Resume the children first
ALTER TASK LOGISTICS_DAG.SUMMARIZE_DATA RESUME;
ALTER TASK LOGISTICS_DAG.CLEAN_DATA RESUME;

-- Resume the root task last
ALTER TASK LOGISTICS_DAG.INGEST_RAW_DATA RESUME;

Instead of waiting for midnight, you can manually trigger the DAG for testing:

-- Manually execute the root task (which triggers the whole DAG)
EXECUTE TASK LOGISTICS_DAG.INGEST_RAW_DATA;

5. Monitoring Task History​

To see if your DAG ran successfully, or if a child task failed, query the TASK_HISTORY table function.

-- View the status of the tasks in the DAG
SELECT
NAME,
STATE,
RETURN_VALUE,
QUERY_ID,
SCHEDULED_TIME,
COMPLETED_TIME,
ERROR_MESSAGE
FROM table(information_schema.task_history(
scheduled_time_range_start=>dateadd('hour',-1,current_timestamp())
))
ORDER BY SCHEDULED_TIME DESC;

Common States in TASK_HISTORY:

  • SUCCEEDED: The task ran perfectly.
  • FAILED: The SQL statement inside the task errored out. Read ERROR_MESSAGE.
  • SKIPPED: A child task was skipped because its parent failed, or a root task was skipped because the previous run hasn't finished yet.

Summary​

  • Tasks allow you to schedule SQL execution.
  • Use Warehouse Tasks for heavy lifting; use Serverless Tasks for lightweight/variable work.
  • Build DAGs by linking tasks using the AFTER clause.
  • Tasks are created in a Suspended state; always resume child tasks before the root task.
  • Use EXECUTE TASK for manual runs and TASK_HISTORY for troubleshooting.