Task trees and dependencies in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ service_behavior

intermediate

Understanding Task Dependency Execution Order

Given a Snowflake task tree where Task A triggers Task B and Task C, and Task B triggers Task D, which task(s) will run immediately after Task A completes?

A Task C runs immediately after Task A, then Task B and Task D run together.

B Only Task D runs immediately after Task A.

C Task B runs immediately after Task A, then Task C and Task D run together.

D Only Task B and Task C run immediately after Task A.

💡 Hint

Think about which tasks are directly dependent on Task A.

Task B and Task C depend directly on Task A, so they run immediately after Task A completes. Task D depends on Task B, so it runs only after Task B finishes.

❓ Architecture

intermediate

Designing a Task Tree for Sequential and Parallel Execution

You want to design a Snowflake task tree where Task 1 runs first, then Task 2 and Task 3 run in parallel, and finally Task 4 runs after both Task 2 and Task 3 complete. Which dependency setup achieves this?

A Task 2 and Task 3 depend on Task 1; Task 4 depends on Task 2 and Task 3.

B Task 1 depends on Task 2 and Task 3; Task 4 depends on Task 1.

C Task 4 depends on Task 1; Task 2 and Task 3 depend on Task 4.

D Task 2 depends on Task 1; Task 3 depends on Task 4; Task 4 depends on Task 1.

💡 Hint

Consider which tasks must finish before others start.

Setting Task 2 and Task 3 to depend on Task 1 allows them to run in parallel after Task 1 completes. Task 4 depending on both ensures it runs only after both finish.

❓ Configuration

advanced

Identifying the Error in Task Dependency SQL

Examine the following Snowflake task creation statements. Which option correctly identifies the error causing the task tree to fail?

CREATE TASK task_a WAREHOUSE = wh1 AS SELECT 1;
CREATE TASK task_b WAREHOUSE = wh1 AFTER task_a AS SELECT 2;
CREATE TASK task_c WAREHOUSE = wh1 AFTER task_b AS SELECT 3;
CREATE OR REPLACE TASK task_a WAREHOUSE = wh1 AFTER task_c AS SELECT 4;

Snowflake

CREATE TASK task_a WAREHOUSE = wh1 AS SELECT 1;
CREATE TASK task_b WAREHOUSE = wh1 AFTER task_a AS SELECT 2;
CREATE TASK task_c WAREHOUSE = wh1 AFTER task_b AS SELECT 3;
CREATE OR REPLACE TASK task_a WAREHOUSE = wh1 AFTER task_c AS SELECT 4;

A Task_c is created twice with conflicting definitions.

B The warehouse wh1 is not assigned to task_c, causing failure.

C The task tree has a circular dependency because task_a depends on task_c, which depends on task_b, which depends on task_a.

D Task_b is missing the AS keyword before the SELECT statement.

💡 Hint

Look for loops in the task dependencies.

Task_a depends on task_c, which depends on task_b, which depends on task_a, creating a circular dependency that Snowflake does not allow.

❓ security

advanced

Task Ownership and Permission Impact

In Snowflake, if a task is created by User A but needs to run SQL statements accessing a secure schema owned by User B, what is the best practice to ensure the task runs successfully?

A Create the task owned by User B so it inherits User B's privileges on the secure schema.

B Grant privileges to the warehouse used by the task instead of users.

C Grant USAGE and SELECT privileges on the secure schema to User A and create the task owned by User A.

D No special permissions are needed if the task runs in the same account.

💡 Hint

Consider how task ownership affects privilege inheritance.

Tasks run with the privileges of their owner. Creating the task owned by User B ensures it can access the secure schema owned by User B without extra grants.

✅ Best Practice

expert

Optimizing Task Tree for Minimal Latency

You have a complex Snowflake task tree with multiple branches and dependencies. To minimize total execution time, which approach is best?

A Chain all tasks sequentially to avoid concurrency issues and reduce warehouse usage.

B Design the task tree so independent tasks run in parallel and dependent tasks run sequentially, ensuring warehouses are sized appropriately.

C Create multiple tasks with the same dependencies to run redundant queries faster.

D Use a single large warehouse for all tasks regardless of their concurrency to simplify billing.

💡 Hint

Think about parallelism and resource allocation.

Running independent tasks in parallel reduces total time. Proper warehouse sizing ensures tasks have enough resources without waste.

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