1
0
Snowflake provides tasks—a powerful way to schedule and automate SQL operations. However, when workflows grow complex, a single-task approach is not enough. Enter task graphs, a feature that allows us to define dependencies, sequence executions, and optimize performance across multiple tasks.
This blog explores the significance of Snowflake task graphs, how to define dependencies between tasks, and the advantages of having multiple parent tasks leading to a final dependent task. We’ll explore a real-world use case where an e-commerce system processes orders, updates related tables, calculates sales summaries, and finally notifies the administrator.
Key Components of a Task Graph
- Root Task: The starting point that triggers the task graph.
- Child Tasks: Tasks that process specific data based on dependencies.
- Finalizer Task: The last task that runs after all dependencies are resolved, usually used for logging, notifications, or summary aggregation.
- Task Dependencies: Defines which tasks should execute before a particular task runs
Use Case: Automating Order Processing with Task Graphs
Use Case: Automating Order Processing with Task Graphs
Let’s assume we have an e-commerce order processing system that involves:
- Updating customer details
- Processing product data
- Maintaining order timestamps
- Generating a sales summary report
We use Snowflake tasks to automate these processes with dependencies ensuring data integrity
1.Core Tables: We have below three core tables which has been used in Task processing.
CREATE OR REPLACE TABLE customers (
customer_id STRING PRIMARY KEY,
customer_name STRING,
region STRING
);
CREATE OR REPLACE TABLE products (
product_id STRING PRIMARY KEY,
product_name STRING,
price DECIMAL(10,2)
);
CREATE OR REPLACE TABLE orders (
order_id STRING PRIMARY KEY,
customer_id STRING,
product_id STRING,
order_date TIMESTAMP,
quantity INT
);
2.Root Task – The Entry Point
The root task task_process_orders initializes the workflow. It simply logs that order processing has started.
CREATE OR REPLACE TASK task_process_orders
SCHEDULE = '1 minute'
AS
BEGIN
CALL SYSTEM$SET_RETURN_VALUE('Orders processed successfully');
END;
;
3. Defining Dependent Tasks
We now define three child tasks that will run after the root task.
- a) Processing Customer Orders and Processing Product Orders
Capturing the Order Processing Timestamp.
4.Aggregation Task with Multiple Parent Dependencies
The sales summary task depends on all three previous tasks (customer processing, product processing, and timestamp recording).
5. Finalizer Task – Sending Notification
Once all tasks are completed, we trigger an email notification using an external function.
a) Create Notification Integration
CREATE OR REPLACE NOTIFICATION INTEGRATION
TASK_FAILURE_ALERT
TYPE=EMAIL
ENABLED=TRUE
ALLOWED_RECIPIENTS=('sachin.mittal04@gmail.com')
COMMENT = 'Snowflake Email Notifications';
b) Finalizer Task and Function ( Credit to Snowflake Documentation)
Once we RESUME all the TASKS we get the below TASK GRAPH:
Key Takeaways: Why Task Graphs Matter?
- Defined Dependencies: Ensures a logical sequence where tasks run only when required parent tasks succeed.
- Multiple Parent Tasks: The sales summary task waits for three different tasks, ensuring data integrity.
- Better Performance: Tasks run in parallel where possible, reducing execution time.
- Error Handling: The finalizer task helps in monitoring and notifying administrators if something fails.
