All topics
52 / 66
Snowflake

Tasks: Scheduling & Orchestrating SQL Workflows

Automate recurring SQL execution and chain dependent steps into pipelines.

A task runs one SQL statement on a schedule, or after another task finishes. It is Snowflake's built-in scheduler, so simple pipelines need no external orchestrator.

Tasks are like a chain of dominoes you set on a timer — the first domino falls on schedule, and each subsequent domino only falls because the one before it did, with an optional sensor that skips a domino if there's nothing worth knocking over.

Key Concepts

1
    CREATE TASK load_orders
      WAREHOUSE = etl_wh
      SCHEDULE = 'USING CRON 0 * * * * Asia/Kolkata'
    AS
      INSERT INTO target SELECT * FROM orders_stream;
2
Tasks start suspended. A newly created task does nothing until ALTER TASK ... RESUME. This catches everyone once — the pipeline is defined, deployed, and silently never runs.
ALTER TASK ... RESUME
3
Pair them with streams and skip the empty runs. A task can test for work before doing any:
4
    CREATE TASK load_orders
      ...
      WHEN SYSTEM$STREAM_HAS_DATA('orders_stream')
    AS ...
5
Without the WHEN, an hourly task starts a warehouse 24 times a day to discover there is nothing to do. With it, the check is free and the warehouse only starts when there is work.
WHEN
6
Chaining builds a DAG. A task can run after another instead of on its own schedule:
7
    root_task           SCHEDULE = '1 hour'
      ├── load_customers    AFTER root_task
      ├── load_orders       AFTER root_task
      └── build_summary     AFTER load_customers, load_orders
8
Only the root holds a schedule. Everything else fires when its predecessors succeed, so ordering is expressed once in the structure instead of by guessing at timings.
9
A child does not run if its predecessor fails — which is what you want, and also means a silent failure upstream stops the whole branch.
10
Serverless tasks remove the warehouse decision. Leave out WAREHOUSE and give USER_TASK_MANAGED_ INITIAL_WAREHOUSE_SIZE instead, and Snowflake provides compute, billed per second, sizing it from observed runs.
WAREHOUSE
11
    dedicated warehouse -> you size it; pays for start-up on every run
    serverless          -> Snowflake sizes it; better for short or bursty tasks
12
For a task running every minute for five seconds, serverless is usually both simpler and cheaper.
13
What tasks do not give you. No retries beyond the next scheduled run, no backfill, no cross-account dependencies, and limited visibility compared with a real orchestrator. TASK_HISTORY is where you look, and it is worth alerting on failures there — because a failed task is silent otherwise.
TASK_HISTORY
14
For a handful of dependent SQL steps, tasks are the right tool. For dozens of steps with retries, backfills and dependencies outside Snowflake, use a proper orchestrator and let it call Snowflake.
15
What the interviewer is probing.1. "How do you build a pipeline from tasks?" Probing: the model. Stalls: "Several schedules." Moves up: a root task on a schedule with child tasks declaring AFTER, forming a DAG that runs in dependency order.
16
2. "How do you avoid running a task when there is nothing to do?" Probing: the pattern. Stalls: "Check in the SQL." Moves up: WHEN SYSTEM$STREAM_HAS_DATA, so the task skips without consuming compute.
17
3. "Serverless or warehouse-backed tasks?" Probing: the trade. Stalls: "Serverless always." Moves up: serverless sizes itself and suits spiky short work; a warehouse is cheaper when tasks are frequent enough to keep it warm.
18
4. "How do you debug a failed task?" Probing: the tooling. Stalls: "Logs." Moves up: TASK_HISTORY in the information schema, and remember a resumed root task must be explicitly resumed after any DAG change.
Use cases:tasksorchestrationschedulingdag