Task trees and dependencies in Snowflake - Mini Project: Build & Apply

Task Trees and Dependencies in Snowflake

📖 Scenario: You are managing a data pipeline in Snowflake. You want to organize tasks so that some run only after others finish. This helps keep your data updated in the right order.

🎯 Goal: Build a simple task tree in Snowflake where one task depends on another. You will create tasks, set a schedule, and link them with dependencies.

📋 What You'll Learn

Create a task named task_load_data that runs a simple SQL statement.

Create a task named task_transform_data that depends on task_load_data.

Set task_load_data to run every hour.

Ensure task_transform_data runs only after task_load_data completes.

💡 Why This Matters

🌍 Real World

Task trees help automate data workflows in Snowflake, ensuring steps run in the correct order without manual intervention.

💼 Career

Understanding task dependencies is key for data engineers and cloud architects managing automated pipelines in Snowflake.

1

Create the initial task task_load_data

Write a Snowflake SQL statement to create a task called task_load_data that runs the SQL command SELECT CURRENT_TIMESTAMP(). Do not set any schedule or dependencies yet.

Snowflake

-- Your code here to create task_load_data

Hint

Use CREATE OR REPLACE TASK task_load_data and specify a warehouse. The SQL statement can be simple like SELECT CURRENT_TIMESTAMP().

2

Add a schedule to task_load_data

Alter the existing task_load_data to run every hour using the schedule 'USING CRON 0 * * * * UTC'.

Snowflake

CREATE OR REPLACE TASK task_load_data
  WAREHOUSE = my_warehouse
AS
  SELECT CURRENT_TIMESTAMP();
-- Add schedule to run every hour below

Hint

Use SCHEDULE = 'USING CRON 0 * * * * UTC' to run the task hourly.

3

Create task_transform_data dependent on task_load_data

Create a new task called task_transform_data that runs the SQL command SELECT CURRENT_DATE(). Set it to run after task_load_data using the AFTER task_load_data clause. Use the same warehouse my_warehouse.

Snowflake

CREATE OR REPLACE TASK task_load_data
  WAREHOUSE = my_warehouse
  SCHEDULE = 'USING CRON 0 * * * * UTC'
AS
  SELECT CURRENT_TIMESTAMP();

-- Your code here to create task_transform_data dependent on task_load_data

Hint

Use CREATE OR REPLACE TASK task_transform_data with AFTER task_load_data to set the dependency.

4

Enable both tasks to activate the task tree

Write commands to enable both task_load_data and task_transform_data so they can run as scheduled and in order.

Snowflake

CREATE OR REPLACE TASK task_load_data
  WAREHOUSE = my_warehouse
  SCHEDULE = 'USING CRON 0 * * * * UTC'
AS
  SELECT CURRENT_TIMESTAMP();

CREATE OR REPLACE TASK task_transform_data
  WAREHOUSE = my_warehouse
  AFTER task_load_data
AS
  SELECT CURRENT_DATE();

-- Enable both tasks below

Hint

Use ALTER TASK task_name RESUME; to enable each task.

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