Study guide
9.2 Task Orchestration, DAGs & Serverless Scheduling
SnowPro Advanced: Architect ARA-C01
Track your progress as you read
Finished sections get a check mark.
1 Introduction: The ARA-C01 Exam
About the SnowPro Advanced: Architect (ARA-C01) Exam
2 Chapter 1: Account Strategy & Organization Architecture
1.1 Multi-Account Strategy & Snowflake Organizations 1.2 Snowflake Parameter Hierarchy & Configuration 1.3 Database and Schema Design Architecture
3 Chapter 2: Identity, Access Control & Storage Integrations
2.1 Role-Based Access Control Architecture & Hierarchy 2.2 Secondary Roles, Object Ownership & Delegated Administration 2.3 Cloud Storage Integrations & IAM Trust Policies 2.4 Identity Federation, SSO, SCIM & Authentication Policies
4 Chapter 3: Governance, Data Privacy, Network Security & Compliance
3.1 Column-Level Masking & Row Access Policies 3.2 Object Tagging, Tag-Based Masking & Audit Governance 3.3 Encryption, Key Management & Tri-Secret Secure 3.4 Aggregation Policies, Projection Policies & External Tokenization 3.5 Network Policies, Network Rules, External Access & Private Connectivity 3.6 Snowflake Editions & Compliance Architecture (PCI DSS, PII & PHI)
5 Chapter 4: Data Modeling in Snowflake
4.1 Dimensional Modeling: Star vs. Snowflake Schemas 4.2 Data Vault 2.0 Architecture in Snowflake 4.3 Relational Constraints & Query Optimizer Integration
6 Chapter 5: Data Sharing & Collaboration Solutions
5.1 Direct Data Sharing Architecture & Security 5.2 Managed Reader Accounts for External Consumers 5.3 Snowflake Marketplace, Data Exchange & Clean Rooms
7 Chapter 6: Multi-Cloud, Multi-Region & Business Continuity
6.1 Database & Account Replication Mechanics 6.2 Disaster Recovery Architecture & Client Redirect 6.3 Cross-Cloud Auto-Fulfillment & Listing Replication 6.4 Data Protection: Time Travel, Fail-safe & Storage Costs
8 Chapter 7: Environments, Lakehouse, DevOps & AI/ML Applications
7.1 Multi-Tier Environment Strategy & Zero-Copy Cloning 7.2 Data Lakehouse Architecture & Apache Iceberg 7.3 DataOps, CI/CD & Snowflake Git Integration 7.4 AI/ML Pipelines & Applications: Cortex AI, ML Functions, Snowpark Container Services, Streamlit & Native Apps
9 Chapter 8: Data Ingestion Architecture
8.1 Bulk Ingestion Architecture: COPY INTO Optimization 8.2 Snowpipe Continuous Automated Ingestion 8.3 Snowpipe Streaming & Real-Time Ingestion 8.4 Data Unloading Architecture & Cloud Export Patterns 8.5 Schema Detection, Schema Evolution & Source-Change Architecture
10 Chapter 9: Continuous Pipelines & Stream Transformation
9.1 Change Data Capture (CDC) with Snowflake Streams 9.2 Task Orchestration, DAGs & Serverless Scheduling 9.3 Declarative Data Pipelines with Dynamic Tables 9.4 Views, Materialized Views & Transformation Table Design
11 Chapter 10: Semi-Structured Data & Ecosystem Tooling
10.1 Semi-Structured Data: VARIANT, OBJECT & ARRAY Processing 10.2 Snowflake Ecosystem Connectors, Drivers & SQL API 10.3 Snowpark, User-Defined Functions & Stored Procedures
12 Chapter 11: Virtual Warehouse Architecture & Concurrency
11.1 Virtual Warehouse Architecture, Sizing & Credit Economics 11.2 Multi-Cluster Warehouses & Concurrency Scaling Policies 11.3 Warehouse Types & Query Acceleration Service
13 Chapter 12: Query Profiling & Performance Diagnostics
12.1 Query Profile Deep-Dive & Bottleneck Analysis 12.2 Memory Spilling, Warehouse Queuing & Timeout Governance 12.3 Three-Tier Caching Architecture & Result Reuse
14 Chapter 13: Storage Optimization, Cost Governance & Observability
13.1 Micro-Partitioning & Automatic Clustering 13.2 Search Optimization Service (SOS) & Point Lookup Acceleration 13.3 Resource Monitors, Financial Governance & Usage Observability 13.4 Warehouse Monitoring, Alerts, Event Tables & Performance Explorer
9.2 Task Orchestration, DAGs & Serverless Scheduling
Key Takeaways Explain This Topic AI
Snowflake Tasks execute scheduled SQL statements or stored procedures using either User-Managed Virtual Warehouses or Snowflake-managed Serverless compute.
Directed Acyclic Graphs (DAGs) link tasks via the AFTER <predecessor_task> clause, supporting complex fan-out and fan-in workflows up to 1,000 tasks per graph.
Conditional execution via WHEN SYSTEM$STREAM_HAS_DATA('<stream_name>') is evaluated entirely within the Cloud Services layer, preventing warehouse startup and credit waste when no delta records exist.
A task graph's OVERLAP_POLICY (set on the root task) defaults to NO_OVERLAP, so a scheduled run is skipped while the previous graph run is still executing; ALLOW_CHILD_OVERLAP and ALLOW_ALL_OVERLAP permit concurrent runs.
Suspend the root task before modifying tasks in a graph; resume child tasks before the root, or call SYSTEM$TASK_DEPENDENTS_ENABLE('<root_task>') to resume the root's dependents recursively.
Last updated: September 2026
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
- Architectural Dimension User-Managed Warehouse Task Serverless Task
- DDL Definition Explicit WAREHOUSE = <wh_name> Omit WAREHOUSE parameter entirely
- Compute Sizing Static (Fixed warehouse T-shirt size) Autonomous & dynamic (Snowflake managed)
- Minimum Billing 60 seconds per warehouse start Actual compute used
- Idle Compute Cost Dependent on warehouse AUTO_SUSPEND No idle warehouse
- Pricing Standard warehouse credit rate Serverless task rate (Service Consumption Table)
Best Architectural Fit Predictable, continuously running pipelines sharing a warm warehouse Sporadic, short-running, or bursty tasks with unpredictable run frequencies
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
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 ) .
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.
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):
OVERLAP_POLICY Behavior when the next scheduled time arrives during a run
NO_OVERLAP (default) The next run of the graph starts only after all tasks in the current run finish; if the run takes longer than the interval, at least one scheduled run is skipped.
ALLOW_CHILD_OVERLAP A new graph run can start while child tasks are still running, but not while the root task is still running.
ALLOW_ALL_OVERLAP A new instance of the whole graph starts on schedule even if the previous one is still running.
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:
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.
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:
Suspend the root task: ALTER TASK <root_task> SUSPEND;
Modify child tasks or dependencies.
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