9.2 Task Orchestration, DAGs & Serverless Scheduling

9.2 Task Orchestration, DAGs & Serverless Scheduling

While Snowflake Streams capture raw delta changes, continuous data pipelines require an automated execution engine to process those deltas. Snowflake Tasks provide native, enterprise-grade orchestration within the Snowflake platform. A task represents a discrete unit of scheduled execution that runs a single SQL statement, stored procedure, or procedural script.

By chaining tasks together, architects can construct sophisticated Directed Acyclic Graphs (DAGs) to coordinate multi-stage extract, transform, and load (ETL) workflows. On the SnowPro Advanced: Architect exam, you must demonstrate a deep understanding of compute sizing models (user-managed vs. serverless), conditional execution triggers, task graph lifecycle management, and failure recovery mechanisms.

---

Task Architecture & Compute Models

When defining a task, an architect must make a fundamental compute architecture decision: execute on a dedicated User-Managed Virtual Warehouse or leverage Snowflake's Serverless Compute model.

1. User-Managed Warehouse Tasks

In a user-managed task, you explicitly specify the virtual warehouse that powers task execution via the WAREHOUSE = <warehouse_name> clause.

-- Create a user-managed task executing a stored procedure
CREATE OR REPLACE TASK orchestrate_db.tasks.process_orders_task
  WAREHOUSE = etl_wh
  SCHEDULE = '15 MINUTES'
AS
  CALL cdc_db.procedures.sp_process_order_deltas();

Architectural Characteristics:

• Warehouse Control: The task runs on an existing warehouse configured with your choice of sizing, auto-suspend timers, and multi-cluster policies.
• Warehouse Re-Use: Multiple tasks can share the same running warehouse. If consecutive tasks execute within the warehouse's auto-suspend interval, they avoid cold-start warehouse spinning latency.
• Billing Model: Standard virtual warehouse billing applies (1-minute minimum runtime per cluster start, followed by per-second billing).

2. Serverless Tasks

To configure a serverless task, simply omit the WAREHOUSE parameter. Snowflake dynamically provisions and manages the compute resources required to execute the task statement.

-- Create a serverless task with dynamic compute management
CREATE OR REPLACE TASK orchestrate_db.tasks.serverless_ingest_task
  USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'XSMALL'
  SCHEDULE = 'USING CRON 0 * * * * UTC'
AS
  INSERT INTO analytics.fact_events 
  SELECT * FROM staging.events_stream;

Architectural Characteristics:

• Autonomous Sizing: Snowflake analyzes previous execution runs of the task and automatically right-sizes compute allocation up or down to optimize performance and cost.
• Initial Size Guidance: The optional USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE parameter gives Snowflake an initial hint (e.g., 'XSMALL', 'MEDIUM', 'XLARGE') before dynamic sizing heuristics calibrate.
• No Idle Waste: Billed for the compute resources the task actually uses; there is no warehouse to keep running or auto-suspend.
• Serverless Pricing: Serverless task compute is billed at the rates in Snowflake's Service Consumption Table, which differ from warehouse credit rates.
• Size Limit & Targets: A serverless task can scale up to the equivalent of an XXLARGE warehouse; SERVERLESS_TASK_MIN_STATEMENT_SIZE/MAX_STATEMENT_SIZE bound the size, and a TARGET_COMPLETION_INTERVAL tells Snowflake how quickly the task must finish.

User-Managed vs. Serverless Tasks Comparison

Directed Acyclic Graphs (DAGs) & Dependency Management

Enterprise data pipelines rarely consist of isolated, independent tasks. Instead, raw data ingestion must trigger intermediate cleansing, which in turn feeds parallel dimensional loading and aggregated data mart generation. Snowflake supports this through native Task Graphs (DAGs).

Root Tasks and Child Tasks

• Root Task: The entry point of the graph. The root task must define a schedule (SCHEDULE = '...'). Child tasks cannot define a schedule.
• Child Tasks: Downstream tasks that execute only after one or more upstream tasks complete. A child task specifies its dependencies using the AFTER <predecessor_task> clause.

-- Step 1: Define the Root Task (Scheduled)
CREATE OR REPLACE TASK pipeline_db.tasks.root_ingest_task
  SCHEDULE = 'USING CRON 0 2 * * * America/New_York'
  USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'SMALL'
AS
  CALL pipeline_db.procs.ingest_raw_data();

-- Step 2: Define Child Task 1 (Fan-Out from Root)
CREATE OR REPLACE TASK pipeline_db.tasks.child_transform_customers
  AFTER pipeline_db.tasks.root_ingest_task
  WAREHOUSE = transform_wh
AS
  CALL pipeline_db.procs.transform_customers();

-- Step 3: Define Child Task 2 (Parallel Fan-Out from Root)
CREATE OR REPLACE TASK pipeline_db.tasks.child_transform_orders
  AFTER pipeline_db.tasks.root_ingest_task
  WAREHOUSE = transform_wh
AS
  CALL pipeline_db.procs.transform_orders();

-- Step 4: Define Terminal Task (Fan-In Join Dependency)
CREATE OR REPLACE TASK pipeline_db.tasks.terminal_build_marts
  AFTER pipeline_db.tasks.child_transform_customers, 
        pipeline_db.tasks.child_transform_orders
  WAREHOUSE = transform_wh
AS
  CALL pipeline_db.procs.build_daily_marts();

Graph Execution Semantics & Topological Limits

1. Fan-In Dependency Execution Rule: In the example above, terminal_build_marts specifies multiple predecessors (AFTER child_transform_customers, child_transform_orders). The terminal task will execute only when ALL predecessors complete successfully (SUCCEEDED).
2. Failure Propagation: If a predecessor task fails, tasks that depend on it do not run, and by default the whole graph run is considered failed. Set TASK_AUTO_RETRY_ATTEMPTS on the root task for immediate graph retries, or run EXECUTE TASK ... RETRY LAST to resume from the last failed task. A suspended child task is treated as though it succeeded, so its dependents still run, and a task may still run when some parent tasks were skipped.
3. DAG Limits:
   • Maximum of 1,000 tasks total in a single DAG (including root and child tasks).
   • A single task can have at most 100 predecessor tasks (fan-in limit).
   • A single task can have at most 100 child tasks (fan-out limit).
   • Cycles are strictly forbidden; Snowflake's compiler validates graph acyclicity at DDL creation time.

---

Task Scheduling & Conditional Execution with Streams

Executing tasks on a strict timer when no new data has arrived wastes valuable compute credits and clutters operational logs. Snowflake solves this through conditional triggers.

1. Schedule Specification Options

A root task can be scheduled using either interval minutes or standard cron syntax:

• Interval Syntax: SCHEDULE = '30 MINUTES' (minimum interval is 1 minute).
• Cron Syntax: SCHEDULE = 'USING CRON 0 */2 * * * UTC'.
  • Supports standard 5-field cron syntax (minute hour day-of-month month day-of-week).
  • Supports explicit time zone specification (e.g., 'America/Los_Angeles', 'UTC').

2. Triggered Tasks

A triggered task has a WHEN SYSTEM$STREAM_HAS_DATA(...) condition and no schedule: it runs whenever the stream has new data, which avoids frequent polling and reduces latency. Serverless triggered tasks require a TARGET_COMPLETION_INTERVAL.

CREATE TASK pipeline_db.tasks.on_new_orders
  TARGET_COMPLETION_INTERVAL = '5 MINUTES'
  WHEN SYSTEM$STREAM_HAS_DATA('cdc_db.staging.orders_stream')
AS
  INSERT INTO analytics.fact_orders SELECT * FROM cdc_db.staging.orders_stream;

3. Conditional Triggers on a Schedule: WHEN SYSTEM$STREAM_HAS_DATA

The WHEN clause allows you to specify a boolean SQL expression evaluated before the task body executes. If the WHEN expression evaluates to FALSE, the task execution is skipped.

CREATE OR REPLACE TASK pipeline_db.tasks.root_cdc_task
  SCHEDULE = '5 MINUTES'
  USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'XSMALL'
  WHEN SYSTEM$STREAM_HAS_DATA('cdc_db.staging.orders_stream')
AS
  MERGE INTO analytics.fact_orders USING ...;

The Cloud Services Optimization Advantage

A critical architectural concept frequently tested on the exam is how SYSTEM$STREAM_HAS_DATA evaluates:

• Evaluated in the Cloud Services Layer: Snowflake evaluates the WHEN condition in cloud services without starting or resuming a virtual warehouse.
• No Warehouse Credits When Empty: If the stream has no unconsumed records, the run is skipped and no warehouse runs. (The condition check is cloud services usage, which counts toward the normal cloud services billing rules.)
• Deterministic CDC Pairing: Combining a stream with a periodic task guarded by WHEN SYSTEM$STREAM_HAS_DATA creates a fully automated, cost-optimized continuous ingestion loop.

Task Overlap Prevention & Concurrency Rules

A frequent real-world challenge in ETL pipelines occurs when a workload takes longer to execute than its scheduled interval (for example, a batch that normally takes 5 minutes runs for 15 minutes due to an upstream data volume spike).

Overlap Prevention Semantics

The root task's OVERLAP_POLICY controls what happens (it replaces the deprecated ALLOW_OVERLAPPING_EXECUTION parameter):

• No Run Queuing under the default: the skipped run is not queued; the graph resumes at the next scheduled time.
• Overlap is risky for tasks that consume streams or write the same targets, which is why NO_OVERLAP is the default.

---

Inter-Task State & Telemetry Propagation

When orchestrating complex DAGs, downstream tasks frequently need access to operational metadata generated by upstream tasks (such as batch IDs, row counts processed, or file names ingested).

Passing State via Task Return Values

Snowflake provides two system functions to enable state propagation between tasks without requiring temporary database tables:

1. SYSTEM$SET_RETURN_VALUE('string'): Called within an upstream task (or stored procedure) to set a return string (maximum 10,000 bytes) for downstream consumers.
2. SYSTEM$GET_PREDECESSOR_RETURN_VALUE('predecessor_name'): Called in a child task to retrieve the return value set by its immediate predecessor.

-- Upstream Task: Sets a batch UUID token
CREATE OR REPLACE TASK pipeline_db.tasks.task_a
  SCHEDULE = '10 MINUTES'
  WAREHOUSE = etl_wh
AS
  CALL pipeline_db.procs.ingest_and_return_batch_id();
  -- Inside the stored procedure: 
  -- SYSTEM$SET_RETURN_VALUE('BATCH_20260923_UUID_99182');

-- Downstream Task: Reads the batch token
CREATE OR REPLACE TASK pipeline_db.tasks.task_b
  AFTER pipeline_db.tasks.task_a
  WAREHOUSE = etl_wh
AS
  INSERT INTO pipeline_db.analytics.batch_log (batch_id, processed_at)
  VALUES (SYSTEM$GET_PREDECESSOR_RETURN_VALUE('TASK_A'), CURRENT_TIMESTAMP());

---

Task Graph Operations: Resuming, Suspending & Monitoring

Managing the lifecycle of a production DAG requires following precise operational sequences. Deviating from these sequences is one of the most common causes of pipeline failure.

DAG Modification Rules (Suspend Root First)

You cannot add, alter, or drop tasks within a DAG while the root task is active. To modify an existing DAG:

1. Suspend the root task: ALTER TASK <root_task> SUSPEND;
2. Modify child tasks or dependencies.
3. Resume the DAG.

Resuming a DAG: The Strict Ordering Rule

By default, all tasks are created in the SUSPENDED state. When activating a DAG, the order of resumption is critical:

• Manual Resumption (Bottom-Up): You must resume all child tasks FIRST, working backwards from terminal tasks up to intermediate tasks, and resume the root task LAST.
• The Orphaned Child Trap: If you resume the root task first while its child tasks are still SUSPENDED, the root task will execute at its scheduled time, but downstream child tasks will never execute! When the root task completes, Snowflake checks child tasks; encountering them in a suspended state, it terminates graph execution.

Recursive Activation: SYSTEM$TASK_DEPENDENTS_ENABLE

Instead of resuming each child task by hand, call the system function on the root task; it recursively resumes all dependent tasks tied to that root:

-- Resume all dependents of the root task (and then the root)
SELECT SYSTEM$TASK_DEPENDENTS_ENABLE('pipeline_db.tasks.root_ingest_task');

-- Suspend the graph before changing it
ALTER TASK pipeline_db.tasks.root_ingest_task SUSPEND;

There is no RESUME RECURSIVE clause on ALTER TASK. Other useful tools: SUSPEND_TASK_AFTER_NUM_FAILURES auto-suspends a task after repeated failures, TASK_AUTO_RETRY_ATTEMPTS retries failed graphs, and a finalizer task (FINALIZE = <root_task>) runs after every graph run for cleanup or notification.

Telemetry and Error Notifications

Architects monitor and alert on task execution using native Snowflake views and notification integrations:

-- Query operational history for a specific task over the past 7 days
SELECT 
  name,
  state,
  scheduled_time,
  query_start_time,
  next_scheduled_time,
  error_code,
  error_message
FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY(
  TASK_NAME => 'ROOT_INGEST_TASK',
  SCHEDULED_TIME_RANGE_START => DATEADD('day', -7, CURRENT_TIMESTAMP())
))
ORDER BY scheduled_time DESC;

To automate alerting, attach an ERROR_INTEGRATION (a notification integration for Amazon SNS, Azure Event Grid, or Google Cloud Pub/Sub) to the task, or use a SUCCESS_INTEGRATION for completion notices:

ALTER TASK pipeline_db.tasks.root_ingest_task 
  SET ERROR_INTEGRATION = enterprise_cloud_pubsub_integration;