Task trees and dependencies in Snowflake - Commands & Configuration

Introduction

When you have multiple tasks that need to run in a specific order, task trees help you organize them. Snowflake task trees let you set up tasks that depend on each other, so they run automatically in the right sequence.

When you want to load data step-by-step, like first loading raw data, then cleaning it, then aggregating it.

When you have a daily report that depends on several smaller data processing steps.

When you want to automate a workflow where one task must finish before the next starts.

When you want to reduce manual work by chaining tasks together.

When you want to make sure tasks run only after their dependencies succeed.

Config File - task_tree.sql

task_tree.sql

CREATE OR REPLACE TASK task_a
  WAREHOUSE = compute_wh
  SCHEDULE = 'USING CRON 0 * * * * UTC'
AS
  INSERT INTO raw_data_table SELECT * FROM source_table;

CREATE OR REPLACE TASK task_b
  WAREHOUSE = compute_wh
  AFTER task_a
AS
  INSERT INTO cleaned_data_table SELECT * FROM raw_data_table WHERE valid = TRUE;

CREATE OR REPLACE TASK task_c
  WAREHOUSE = compute_wh
  AFTER task_b
AS
  INSERT INTO aggregated_data_table SELECT category, COUNT(*) FROM cleaned_data_table GROUP BY category;

This file creates three tasks: task_a, task_b, and task_c.

task_a runs every hour and loads raw data.

task_b depends on task_a and cleans the data.

task_c depends on task_b and aggregates the cleaned data.

The AFTER keyword sets the dependency order.

Commands

This command starts the first task so it can run on schedule.

Terminal

ALTER TASK task_a RESUME

Expected Output

Task TASK_A resumed.

This command starts the second task, which depends on task_a.

Terminal

ALTER TASK task_b RESUME

Expected Output

Task TASK_B resumed.

This command starts the third task, which depends on task_b.

Terminal

ALTER TASK task_c RESUME

Expected Output

Task TASK_C resumed.

This command lists all tasks and their current status to verify they are active.

Terminal

SHOW TASKS

Expected Output

name   | database | schema | state   | schedule

-------|----------|--------|---------|----------

task_a | MY_DB    | PUBLIC | started | 0 * * * *

task_b | MY_DB    | PUBLIC | started | 

task_c | MY_DB    | PUBLIC | started | 

This command checks the recent run history of task_c to see if it ran after its dependencies.

Terminal

SELECT SYSTEM$TASK_HISTORY('task_c', DATEADD('hour', -1, CURRENT_TIMESTAMP()), CURRENT_TIMESTAMP())

Expected Output

TASK_NAME | STATUS  | START_TIME           | END_TIME

----------|---------|----------------------|----------------------

task_c    | SUCCESS | 2024-06-01 10:00:00  | 2024-06-01 10:01:00

Key Concept

If you remember nothing else from this pattern, remember: tasks run automatically in order when you set dependencies using AFTER.

Common Mistakes

Not resuming tasks after creating them

Tasks stay paused and never run if you forget to resume them.

Always run ALTER TASK task_name RESUME to activate tasks.

Setting circular dependencies between tasks

Tasks will never run because they wait on each other forever.

Make sure dependencies form a clear chain without loops.

Expecting tasks to run immediately after creation without schedule or manual trigger

Tasks only run on schedule or when triggered; they don't run just by existing.

Set a schedule or manually run tasks to start the workflow.

Summary

Create tasks with SQL commands and set dependencies using AFTER to form a task tree.

Resume tasks to activate them so they can run on schedule or after their dependencies.

Use SHOW TASKS and SYSTEM$TASK_HISTORY to monitor task status and execution order.

Practice

1. What is the main purpose of creating a task tree in Snowflake?

easy

A. To organize tasks so they run in a specific order using dependencies

B. To store data in a hierarchical format

C. To create multiple independent tasks that run simultaneously

D. To backup data automatically

Solution

1. Step 1: Understand task trees concept

   Task trees are used to organize tasks so they run one after another based on dependencies.

2. Step 2: Compare options

   Only To organize tasks so they run in a specific order using dependencies describes organizing tasks with dependencies. Others describe unrelated concepts.

3. Final Answer:

   To organize tasks so they run in a specific order using dependencies -> Option A

4. Quick Check:

   Task trees = ordered tasks [OK]

Hint: Task trees control task order using dependencies [OK]

Common Mistakes:

• Confusing task trees with data storage
• Thinking tasks run independently without order
• Assuming task trees backup data

2. Which Snowflake command correctly sets a task to run after another task named task_A?

easy

A. ALTER TASK task_B SET BEFORE = task_A;

B. CREATE TASK task_B AFTER task_A;

C. ALTER TASK task_B SET AFTER = task_A;

D. ALTER TASK task_A SET AFTER = task_B;

Solution

1. Step 1: Recall syntax for setting dependencies

   Snowflake uses ALTER TASK task_name SET AFTER = other_task; to set dependencies.

2. Step 2: Check each option

   Only ALTER TASK task_B SET AFTER = task_A; matches correct syntax. The option using BEFORE has an invalid keyword. The CREATE TASK option uses AFTER incorrectly. The option altering task_A reverses dependency.

3. Final Answer:

   ALTER TASK task_B SET AFTER = task_A; -> Option C

4. Quick Check:

   SET AFTER syntax = ALTER TASK task_B SET AFTER = task_A; [OK]

Hint: Use ALTER TASK ... SET AFTER = task_name to link tasks [OK]

Common Mistakes:

• Using BEFORE instead of AFTER
• Trying to set dependency during CREATE TASK
• Reversing task order in dependency

3. Given the following tasks and dependencies:

CREATE TASK task_A WAREHOUSE = wh1 AS SELECT 1;
CREATE TASK task_B WAREHOUSE = wh1 AS SELECT 2;
CREATE TASK task_C WAREHOUSE = wh1 AS SELECT 3;
ALTER TASK task_B SET AFTER = task_A;
ALTER TASK task_C SET AFTER = task_B;

If you start task_A, which task runs last?

medium

A. task_A

B. task_B

C. All run simultaneously

D. task_C

Solution

1. Step 1: Analyze task dependencies

   task_B runs after task_A, and task_C runs after task_B, so order is task_A -> task_B -> task_C.

2. Step 2: Identify last task in sequence

   Since task_C depends on task_B, which depends on task_A, task_C runs last.

3. Final Answer:

   task_C -> Option D

4. Quick Check:

   Dependency chain ends with task_C [OK]

Hint: Follow AFTER links to find last task [OK]

Common Mistakes:

• Thinking tasks run in parallel
• Choosing the first task as last
• Ignoring dependency order

4. You have two tasks: task_X and task_Y. You want task_Y to run after task_X. Which command fixes this if task_Y currently runs before task_X?

medium

A. CREATE TASK task_Y SET AFTER = task_X;

B. ALTER TASK task_Y SET AFTER = task_X;

C. ALTER TASK task_Y SET BEFORE = task_X;

D. ALTER TASK task_X SET AFTER = task_Y;

Solution

1. Step 1: Identify correct command to set dependency

   To make task_Y run after task_X, use ALTER TASK task_Y SET AFTER = task_X;.

2. Step 2: Check incorrect options

   ALTER TASK task_X SET AFTER = task_Y; reverses order. ALTER TASK task_Y SET BEFORE = task_X; uses invalid BEFORE keyword. CREATE TASK task_Y SET AFTER = task_X; uses CREATE incorrectly.

3. Final Answer:

   ALTER TASK task_Y SET AFTER = task_X; -> Option B

4. Quick Check:

   SET AFTER fixes order [OK]

Hint: Use ALTER TASK task_Y SET AFTER = task_X to fix order [OK]

Common Mistakes:

• Using BEFORE keyword which is invalid
• Setting dependency on wrong task
• Trying to fix order with CREATE TASK

5. You have a task tree where task_1 runs first, then task_2 and task_3 run after task_1 in parallel, and finally task_4 runs after both task_2 and task_3. How do you configure task_4 to run only after both tasks finish?

hard

A. ALTER TASK task_4 SET AFTER = task_2, task_3;

B. ALTER TASK task_4 SET AFTER = task_2; ALTER TASK task_4 SET AFTER = task_3;

C. ALTER TASK task_4 SET AFTER = task_2 AND task_3;

D. ALTER TASK task_4 SET AFTER = task_2; ALTER TASK task_3 SET AFTER = task_4;

Solution

1. Step 1: Understand multiple dependencies syntax

   Snowflake allows setting multiple tasks in AFTER clause separated by commas to require all to finish.

2. Step 2: Evaluate options

   ALTER TASK task_4 SET AFTER = task_2, task_3; correctly sets task_4 to run after both task_2 and task_3. ALTER TASK task_4 SET AFTER = task_2; ALTER TASK task_4 SET AFTER = task_3; overwrites AFTER twice, only last applies. ALTER TASK task_4 SET AFTER = task_2 AND task_3; uses invalid AND keyword. ALTER TASK task_4 SET AFTER = task_2; ALTER TASK task_3 SET AFTER = task_4; reverses dependency.

3. Final Answer:

   ALTER TASK task_4 SET AFTER = task_2, task_3; -> Option A

4. Quick Check:

   Multiple AFTER tasks separated by commas [OK]

Hint: Use commas to list multiple tasks in SET AFTER [OK]

Common Mistakes:

• Using multiple SET AFTER commands overwriting each other
• Using AND instead of comma
• Setting wrong dependency direction