Task trees and dependencies in Snowflake - Step-by-Step Execution

Process Flow - Task trees and dependencies

Define Tasks

↓

Set Dependencies

↓

Build Task Tree

↓

Execute Root Task

↓

Check Dependencies

↓

Execute Dependent Task 1

↓

Complete

↓

Execute Dependent Task 2

↓

Complete

↓

Complete Root Task

↓

All Tasks Done

Tasks are defined and linked by dependencies forming a tree. Executing a root task triggers dependent tasks first, ensuring order.

Execution Sample

Snowflake

CREATE TASK task_A
  WAREHOUSE = wh
  AS SELECT 1;

CREATE TASK task_B
  WAREHOUSE = wh
  AFTER task_A
  AS SELECT 2;

Defines two tasks where task_B runs only after task_A completes.

Process Table

Step	Action	Task Executed	Dependency Check	Result
1	Start execution of task_B	task_B	Depends on task_A	Wait for task_A to complete
2	Execute task_A	task_A	No dependencies	task_A runs and completes
3	Re-check task_B dependencies	task_B	task_A completed	task_B runs and completes
4	All tasks done	-	-	Execution finished successfully

💡 All tasks executed respecting dependencies; task_B waited for task_A to finish before running.

Status Tracker

Task	Initial State	After Step 1	After Step 2	After Step 3	Final State
task_A	Not started	Not started	Completed	Completed	Completed
task_B	Not started	Waiting	Waiting	Completed	Completed

Key Moments - 2 Insights

Why does task_B not run immediately when triggered?

Because task_B depends on task_A, it waits until task_A completes first (see execution_table step 1 and 2).

What happens if a dependency task fails?

The dependent task will not run, as dependencies must complete successfully before proceeding (implied by dependency check in execution_table).

Visual Quiz - 3 Questions

Look at the execution_table, what is the state of task_B after step 1?

A Running

B Completed

C Waiting

D Not started

💡 Hint

Check the 'Dependency Check' and 'Result' columns in execution_table row 1.

At which step does task_A complete execution?

A Step 2

B Step 1

C Step 3

D Step 4

💡 Hint

Look at the 'Result' column in execution_table for when task_A runs and completes.

If task_A failed, what would happen to task_B?

A task_B runs anyway

B task_B is skipped

C task_B runs before task_A

D task_B waits indefinitely

💡 Hint

Dependencies must complete successfully before dependent tasks run, see key_moments explanation.

Concept Snapshot

Task trees link tasks with dependencies.
A task runs only after its dependencies complete.
Use AFTER clause to set dependencies.
Executing a root task triggers dependent tasks in order.
Failures in dependencies stop dependent tasks from running.

Full Transcript

In Snowflake, tasks can be linked in a tree structure using dependencies. You define tasks and specify which tasks must finish before others run using the AFTER clause. When you execute a root task, Snowflake checks its dependencies and runs them first if needed. For example, task_B depends on task_A, so task_B waits until task_A finishes. This ensures tasks run in the correct order. If a dependency fails, dependent tasks do not run. This helps manage complex workflows reliably.

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