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.