Bird
Raised Fist0
SnowflakeHow-ToBeginner · 4 min read

How to Create a Task Tree in Snowflake

To create a task tree in Snowflake, define multiple TASK objects where each task depends on the completion of its parent task using the AFTER clause. This chaining allows tasks to run in sequence automatically, forming a tree of dependent tasks.
📐

Syntax

In Snowflake, a task tree is created by defining tasks with dependencies using the AFTER keyword. Each task runs after its parent task completes.

  • CREATE TASK task_name: Defines a new task.
  • WAREHOUSE = warehouse_name: Specifies the warehouse to run the task.
  • SCHEDULE = 'USING CRON 0 * * * * UTC': Sets a schedule for the root task.
  • AFTER parent_task: Makes this task run after the specified parent task.
  • AS SQL_statement: The SQL command the task executes.
sql
CREATE TASK task1
  WAREHOUSE = mywh
  SCHEDULE = 'USING CRON 0 * * * * UTC'
AS
  -- SQL for root task
  CALL some_procedure();

CREATE TASK task2
  WAREHOUSE = mywh
  AFTER task1
AS
  -- SQL for child task
  INSERT INTO table2 SELECT * FROM table1;

CREATE TASK task3
  WAREHOUSE = mywh
  AFTER task1
AS
  -- SQL for another child task
  UPDATE table3 SET col = 'value';
💻

Example

This example shows a simple task tree with one root task and two child tasks that run after the root completes. The root task runs every hour, and the child tasks run automatically after it.

sql
CREATE OR REPLACE TASK root_task
  WAREHOUSE = compute_wh
  SCHEDULE = 'USING CRON 0 * * * * UTC'
AS
  INSERT INTO logs (event_time, event) VALUES (CURRENT_TIMESTAMP, 'root task ran');

CREATE OR REPLACE TASK child_task_1
  WAREHOUSE = compute_wh
  AFTER root_task
AS
  INSERT INTO logs (event_time, event) VALUES (CURRENT_TIMESTAMP, 'child task 1 ran');

CREATE OR REPLACE TASK child_task_2
  WAREHOUSE = compute_wh
  AFTER root_task
AS
  INSERT INTO logs (event_time, event) VALUES (CURRENT_TIMESTAMP, 'child task 2 ran');

-- Enable tasks to start the chain
ALTER TASK root_task RESUME;
ALTER TASK child_task_1 RESUME;
ALTER TASK child_task_2 RESUME;
Output
Tasks created and enabled. root_task runs hourly; child_task_1 and child_task_2 run after root_task completes.
⚠️

Common Pitfalls

Common mistakes when creating task trees include:

  • Not enabling tasks after creation, so they never run.
  • Creating circular dependencies, which cause errors.
  • Forgetting to specify AFTER for dependent tasks, causing them to run independently.
  • Using different warehouses inconsistently, which can cause resource issues.
sql
/* Wrong: Circular dependency example */
CREATE TASK task_a
  WAREHOUSE = wh
  AFTER task_b
AS
  SELECT 1;

CREATE TASK task_b
  WAREHOUSE = wh
  AFTER task_a
AS
  SELECT 2;

/* Right: Linear dependency */
CREATE TASK task_a
  WAREHOUSE = wh
AS
  SELECT 1;

CREATE TASK task_b
  WAREHOUSE = wh
  AFTER task_a
AS
  SELECT 2;
📊

Quick Reference

ClauseDescription
CREATE TASK task_nameDefines a new task with the given name
WAREHOUSE = warehouse_nameSpecifies the warehouse to run the task
SCHEDULE = 'USING CRON 0 * * * * UTC'Sets the schedule for the root task only
AFTER parent_taskRuns this task after the specified parent task completes
AS SQL_statementSQL command the task executes
ALTER TASK task_name RESUMEEnables the task to run
✅

Key Takeaways

Create dependent tasks using the AFTER clause to form a task tree.
Always enable tasks with ALTER TASK ... RESUME to start execution.
Avoid circular dependencies to prevent errors.
Schedule only the root task; child tasks run automatically after parents.
Use consistent warehouses for all tasks in the tree.