Most Snowflake accounts grow a pipeline layer by accident. Someone creates a task to refresh a table, someone else adds a stored procedure, a third person wires an external scheduler, and six months later nobody can answer "what ran last night, in what order, and what happened when step four failed?"
Snowflake's native orchestration has quietly become good enough that a large share of teams no longer need Airflow for in-warehouse work. This tutorial covers task graphs (DAGs), serverless versus warehouse tasks, triggered tasks that fire on stream data, retries and error handling, and how to monitor the whole thing with account usage views and alerts.
If you are choosing between incremental pipeline styles, read our Dynamic Tables vs Streams & Tasks tutorial first. This one is about operating whatever you chose.
The building blocks
| Object | What it does |
|---|---|
TASK | A scheduled or dependent unit of work: one SQL statement, a call to a procedure, or a multi-statement block |
| Task graph (DAG) | A root task plus tasks declared AFTER it; the graph is scheduled once at the root |
STREAM | Change tracking on a table; combined with WHEN SYSTEM$STREAM_HAS_DATA it makes tasks skip empty runs |
| Serverless compute | USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE instead of WAREHOUSE =; Snowflake sizes and bills per second |
| Triggered tasks | SCHEDULE omitted, WHEN SYSTEM$STREAM_HAS_DATA(...) present — Snowflake runs the task when the stream has changes |
FINALIZER | A task that always runs when a graph ends, success or failure — the place to log and notify |
A real task graph
Load, transform, test, publish. One root, three levels, a finalizer.
USE SCHEMA ops.pipelines;
-- Root: sets the run context and is the only task with a schedule
CREATE OR REPLACE TASK t_root
SCHEDULE = 'USING CRON 15 2 * * * America/Denver'
USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'XSMALL'
USER_TASK_TIMEOUT_MS = 3600000
AS
CALL ops.sp_start_run(SYSTEM$TASK_RUNTIME_INFO('CURRENT_TASK_GRAPH_RUN_GROUP_ID'));
-- Level 1: ingest, only if there is something to ingest
CREATE OR REPLACE TASK t_load_orders
USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'SMALL'
AFTER t_root
WHEN SYSTEM$STREAM_HAS_DATA('raw.orders_stream')
AS
MERGE INTO stage.orders tgt
USING raw.orders_stream src ON tgt.order_id = src.order_id
WHEN MATCHED AND src.METADATA$ACTION = 'DELETE' THEN DELETE
WHEN MATCHED THEN UPDATE SET tgt.amount = src.amount, tgt.status = src.status
WHEN NOT MATCHED AND src.METADATA$ACTION = 'INSERT'
THEN INSERT (order_id, amount, status) VALUES (src.order_id, src.amount, src.status);
CREATE OR REPLACE TASK t_load_customers
USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'XSMALL'
AFTER t_root
AS
CALL stage.sp_load_customers();
-- Level 2: transform, waits for BOTH level-1 tasks
CREATE OR REPLACE TASK t_build_marts
USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'MEDIUM'
AFTER t_load_orders, t_load_customers
AS
CALL marts.sp_build_daily();
-- Level 3: data quality gate
CREATE OR REPLACE TASK t_quality_gate
USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'XSMALL'
AFTER t_build_marts
AS
CALL ops.sp_assert_quality('MARTS.DAILY_ORDERS');
-- Always runs, even if a branch failed
CREATE OR REPLACE TASK t_finalize
FINALIZE = t_root
USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'XSMALL'
AS
CALL ops.sp_finish_run(SYSTEM$TASK_RUNTIME_INFO('CURRENT_TASK_GRAPH_RUN_GROUP_ID'));
-- Resume children first, root last; a graph only runs when the root is resumed
ALTER TASK t_finalize RESUME;
ALTER TASK t_quality_gate RESUME;
ALTER TASK t_build_marts RESUME;
ALTER TASK t_load_customers RESUME;
ALTER TASK t_load_orders RESUME;
ALTER TASK t_root RESUME;
Three things trip people up here:
- Children must be resumed before the root. A suspended child is silently skipped; the graph still reports success.
- A skipped
WHENcondition does not fail the graph. Downstream tasks still run. Ift_build_martsmust not run on an empty load, make the condition explicit inside the procedure rather than relying on the skip. - Only the root carries
SCHEDULE. Putting a schedule on a child makes it a second root and you now have two overlapping graphs.
Serverless or your own warehouse?
| Use serverless when | Use a dedicated warehouse when |
|---|---|
| Runs are short, spiky, or infrequent | Tasks run back-to-back and can share a warm warehouse |
| You want Snowflake to right-size automatically over time | You need a specific size, MAX_CONCURRENCY_LEVEL, or Query Acceleration |
| Idle warehouse time is the dominant cost | You already pay for a warehouse that is up anyway |
Serverless tasks bill per second of actual compute at a slightly higher rate per credit-second, and Snowflake adapts the size based on run history. For a nightly graph with ten small steps, serverless almost always wins on total credits because you stop paying for auto-suspend tails. Measure it: SERVERLESS_TASK_HISTORY versus WAREHOUSE_METERING_HISTORY over the same window.
Triggered tasks: dropping the schedule entirely
A task with no SCHEDULE but a stream condition runs when data arrives, typically within a minute, and costs nothing when the stream is empty. This replaces the old pattern of polling every minute all night for a file that lands at 03:12.
CREATE OR REPLACE TASK t_ingest_events
TARGET_COMPLETION_INTERVAL = '5 MINUTES'
USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'SMALL'
WHEN SYSTEM$STREAM_HAS_DATA('raw.events_stream')
AS
INSERT INTO stage.events SELECT * FROM raw.events_stream;
ALTER TASK t_ingest_events RESUME;
Triggered tasks work on streams over standard tables, directory tables (new files in a stage), and external tables. Keep the work per run small; a triggered task that takes twenty minutes will queue behind itself.
Error handling and retries
Native retry:
ALTER TASK t_build_marts SET
TASK_AUTO_RETRY_ATTEMPTS = 2,
SUSPEND_TASK_AFTER_NUM_FAILURES = 5;
TASK_AUTO_RETRY_ATTEMPTS retries the graph from the failed task. SUSPEND_TASK_AFTER_NUM_FAILURES stops a broken graph from burning credits all weekend — set it on the root and treat an auto-suspended root as a page-worthy alert, because a suspended root means nothing has run since.
For logic-level errors, wrap the work in a procedure with a proper handler so a bad batch is recorded, not just thrown:
CREATE OR REPLACE PROCEDURE marts.sp_build_daily()
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
BEGIN TRANSACTION;
MERGE INTO marts.daily_orders t USING stage.orders s ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET t.amount = s.amount
WHEN NOT MATCHED THEN INSERT (order_id, amount) VALUES (s.order_id, s.amount);
COMMIT;
RETURN 'OK';
EXCEPTION
WHEN OTHER THEN
ROLLBACK;
INSERT INTO ops.pipeline_errors(task_name, sqlcode, sqlerrm, logged_at)
VALUES ('marts.sp_build_daily', :sqlcode, :sqlerrm, CURRENT_TIMESTAMP());
RAISE;
END;
$$;
Re-raising matters: swallow the exception and the task reports success, the graph carries on, and the quality gate blesses stale data.
Monitoring: the three queries to keep
What ran last night, and how long did each step take?
SELECT name, state, scheduled_time, completed_time,
TIMESTAMPDIFF('second', query_start_time, completed_time) AS run_seconds,
error_code, error_message
FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY(
SCHEDULED_TIME_RANGE_START => DATEADD('hour', -24, CURRENT_TIMESTAMP())))
ORDER BY scheduled_time;
Which tasks fail most often (last 30 days)?
SELECT name,
COUNT(*) AS runs,
COUNT_IF(state = 'FAILED') AS failures,
ROUND(100 * COUNT_IF(state = 'FAILED') / COUNT(*), 1) AS failure_pct
FROM SNOWFLAKE.ACCOUNT_USAGE.TASK_HISTORY
WHERE scheduled_time > DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY name
HAVING failures > 0
ORDER BY failures DESC;
Is a graph drifting toward its schedule window? Compare COMPLETED_TIME - SCHEDULED_TIME for the root graph run over time using TASK_HISTORY joined on GRAPH_RUN_GROUP_ID; a nightly graph creeping from 40 minutes to 100 is the earliest warning that a mart needs work.
Wire failures to a human with an alert plus a notification integration:
CREATE OR REPLACE NOTIFICATION INTEGRATION ops_email
TYPE = EMAIL ENABLED = TRUE
ALLOWED_RECIPIENTS = ('data-oncall@example.com');
CREATE OR REPLACE ALERT ops.alert_task_failures
WAREHOUSE = ops_wh
SCHEDULE = '30 MINUTE'
IF (EXISTS (
SELECT 1 FROM SNOWFLAKE.ACCOUNT_USAGE.TASK_HISTORY
WHERE state = 'FAILED' AND scheduled_time > DATEADD('hour', -1, CURRENT_TIMESTAMP())))
THEN CALL SYSTEM$SEND_EMAIL('ops_email', 'data-oncall@example.com',
'Snowflake task failure', 'A task failed in the last hour. Check ops.pipelines.');
ALTER ALERT ops.alert_task_failures RESUME;
Note that ACCOUNT_USAGE.TASK_HISTORY has latency (up to ~45 minutes); for tight loops read INFORMATION_SCHEMA.TASK_HISTORY instead, or log to an event table and alert from there — see our Snowflake Trail tutorial.
Where an external orchestrator still earns its keep
Keep Airflow, Dagster, or Prefect when the pipeline crosses system boundaries: an SFTP pull, a REST extract, a downstream Tableau refresh, a job that must coordinate Snowflake with S3 and a CRM API. The cleanest hybrid is one external DAG node per Snowflake graph — the orchestrator calls EXECUTE TASK t_root and polls, while all in-warehouse dependencies stay in Snowflake where they can see the data.
Signs you should move work into Snowflake: your Airflow DAG is a list of SnowflakeOperator steps with no external calls; your scheduler costs more to run than the queries; or the dependency graph in Airflow no longer matches what the SQL actually reads.
Deployment and governance
Tasks belong in version control like any other object. CREATE OR ALTER TASK makes them idempotent in a CI pipeline (see our Snowflake CI/CD tutorial). A few habits that keep task estates sane:
- Own tasks with a dedicated role (
ROLE_PIPELINE_OPS), never a personal role — tasks run as their owner, and offboarding a person breaks the graph - Suspend the root before deploying changes to children; resume the root last
- Name tasks by graph and level (
t_<graph>_<step>) soSHOW TASKSreads like a runbook - Tag graphs with a cost-center tag so serverless credits attribute correctly
- Keep one graph per business domain; giant 80-node graphs fail as a unit and retry as a unit
A migration checklist
- Inventory every scheduled thing: tasks, external schedulers, cron boxes, someone's laptop
- Group by data domain; each domain becomes one graph with one root
- Replace polling schedules with triggered tasks where a stream exists
- Move short steps to serverless; keep heavy transforms on a sized warehouse
- Add a finalizer that writes a run record and notifies on failure
- Set
SUSPEND_TASK_AFTER_NUM_FAILURESon every root - Build one dashboard from
ACCOUNT_USAGE.TASK_HISTORY: runs, failures, duration trend
PowderInsights builds and rescues Snowflake pipeline estates — task graph design, Airflow-to-native migration, retry and alerting standards, and the cost tuning that follows. Get in touch with your current schedule and we will tell you what to consolidate.