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

Need a 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

Need a 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

Need a 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

Need a hint?

Use ALTER TASK task_name RESUME; to enable each task.