ADA-C01 Question 257
Single answerConfigure tasksA data engineering team is building a nightly ELT pipeline in Snowflake. They created three tasks: TASK_STAGE loads raw files into a staging table, TASK_TRANSFORM merges staged data into a curated table, and TASK_AGG_REFRESH updates aggregate tables. They want TASK_STAGE to run every night at 01:00 UTC, TASK_TRANSFORM to run only after TASK_STAGE succeeds, and TASK_AGG_REFRESH to run only after TASK_TRANSFORM succeeds. They also want to minimize warehouse costs when the pipeline is idle and ensure the task graph can be started and stopped centrally. Which configuration should the Snowflake administrator implement?
- A
Create TASK_STAGE as a standalone scheduled task with a CRON schedule and serverless compute, then create TASK_TRANSFORM with AFTER TASK_STAGE and TASK_AGG_REFRESH with AFTER TASK_TRANSFORM. Resume the root task to activate the graph.
- B
Create all three tasks with identical CRON schedules at 01:00 UTC on the same warehouse, and rely on statement ordering within Snowflake to ensure TASK_TRANSFORM and TASK_AGG_REFRESH wait for prior tasks to finish.
- C
Create TASK_STAGE on a user-managed warehouse with a CRON schedule, then configure TASK_TRANSFORM and TASK_AGG_REFRESH as separate scheduled tasks using WHEN SYSTEM$STREAM_HAS_DATA to enforce dependency order.
- D
Create TASK_STAGE as a scheduled root task and configure TASK_TRANSFORM and TASK_AGG_REFRESH as child tasks using AFTER dependencies, but each child task must also define its own CRON schedule to be eligible to run.
Show answer and explanation
Correct answer: A
Explanation
The best implementation is to create a task graph with TASK_STAGE as the scheduled root task and TASK_TRANSFORM and TASK_AGG_REFRESH as child tasks chained with AFTER dependencies. In Snowflake, only root tasks use SCHEDULE; child tasks are triggered by predecessor completion. This design gives deterministic ordering based on successful completion of prior tasks. To reduce cost for a nightly pipeline with idle periods, serverless tasks are often the best fit because Snowflake provisions compute only for task execution instead of requiring a warehouse to remain available. For administration, task graphs are typically controlled by suspending or resuming the root task, which centrally governs graph activation. These behaviors align with Snowflake documentation for CREATE TASK, task graphs, AFTER dependencies, and serverless tasks.
- A. Correct.
Correct. In Snowflake, a task graph is built with one scheduled root task and downstream child tasks that use AFTER dependencies. Only the root task needs a schedule; child tasks run when predecessors complete successfully. Using serverless tasks helps minimize idle warehouse cost because compute is provisioned only when the task runs. Operationally, resuming or suspending the root task is the normal way to control graph execution.
- B. Incorrect.
Incorrect. Separate scheduled tasks do not create execution dependencies. If all three tasks are scheduled for the same time, Snowflake does not guarantee that they will execute in the intended sequence. This is a common misconception from traditional schedulers, but in Snowflake task orchestration should use AFTER relationships for ordered execution.
- C. Incorrect.
Incorrect. SYSTEM$STREAM_HAS_DATA is used to make a task conditional on whether a stream contains change data, not to enforce ordered execution between independent scheduled tasks. While a user-managed warehouse can be used for tasks, this design does not provide reliable predecessor-success dependency chaining and does not minimize idle warehouse cost as effectively as serverless compute for intermittent nightly work.
- D. Incorrect.
Incorrect. Child tasks in a task graph must not define their own schedules. A task is either scheduled or triggered by an AFTER dependency. Adding schedules to child tasks is not how Snowflake task graphs work and would conflict with the intended orchestration model.