Gowtham Potureddi

Posted on Jun 27

Snowflake Streams & Tasks: Native CDC + Scheduled Transformations for the Warehouse

#python #sql #dataengineering #interview

snowflake streams and tasks is the single biggest "warehouse-native pipelines" interview surface in 2026 — and the one that separates senior data engineers who actually run Snowflake in production from candidates who only know SELECT. A stream is a row-level change data capture marker on a table; a task is the warehouse's own cron-and-DAG runtime. Wired together they let Snowflake host an entire incremental pipeline — raw to staging to marts — without Airflow, without dbt-cloud, without a single external orchestrator. The catch: the offset semantics, transactional consumption rules, and stale-stream timer break in production unless you understand them at the SQL-engine level.

This guide is the senior-DE deep dive you wished existed the first time an interviewer asked "explain snowflake cdc end-to-end" or "when do you pick a snowflake stream + snowflake task over a Dynamic Table?" It walks through the three stream flavours (standard, append-only stream, insert-only), how snowflake change data capture offsets advance only when the stream is read inside a consuming DML, the standalone-task vs snowflake task graph model, conditional execution with WHEN SYSTEM$STREAM_HAS_DATA, the serverless task cost model, the canonical stream-then-task SCD-2 recipe, and the 2026 decision tree that picks streams+tasks vs Dynamic Tables vs dbt incremental models for a new pipeline. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse on joins drills →, and sharpen the ETL axis with the ETL practice problems →.

On this page

Why Streams + Tasks are the senior Snowflake interview classic
Streams — three flavours and how offsets advance
Tasks — schedule, DAG, conditional execution
Composing CDC pipelines — streams into tasks
Streams + Tasks vs Dynamic Tables vs dbt
Cheat sheet — Streams + Tasks recipes
Frequently asked questions
Practice on PipeCode

1. Why Streams + Tasks are the senior Snowflake interview classic

`snowflake streams and tasks` are the warehouse's native CDC + scheduler — the only Snowflake-only pipeline that needs no Airflow

The one-sentence invariant: a Snowflake stream is a row-level change log over a table; a Snowflake task is a server-side scheduled SQL statement; together they let one warehouse run end-to-end incremental ELT without any external orchestrator. Every senior interview probe on snowflake streams and tasks — stream type, offset behaviour, conditional task firing, serverless cost — follows from those two primitives and how they interlock. Once you internalise "stream advances only when consumed inside a DML" and "task graph fires only when the parent succeeds," the entire surface collapses to a sequence of consequences.

Four axes interviewers actually probe.

Stream type. Standard, append-only, insert-only — each has a different overhead profile, a different supported DML set, and a different "what can I do with it downstream" envelope. Picking the wrong type costs you both performance and the ability to do DELETE propagation.
Stream offset. The offset is not a wall clock — it is a row-level pointer that only advances inside a transaction that successfully consumes the stream in a DML. Reading the stream with a plain SELECT does not advance the offset. Senior candidates who get this wrong write pipelines that re-process the same changes forever.
Task shape. Standalone, task graph (DAG of tasks chained with AFTER), conditional firing with WHEN SYSTEM$STREAM_HAS_DATA('s'). The task graph is Snowflake's "Airflow inside the warehouse" answer; the conditional fires the task only when there is real work.
Compute mode. Warehouse-bound (you specify WAREHOUSE = ..., the warehouse pays per-second while the task runs) vs serverless task (Snowflake picks the size, you pay per-second-of-actual-execution at the serverless rate). The break-even point matters for cost-conscious teams.

The Snowflake CDC primitive in one diagram.

A table has a stream of row-level changes — every INSERT, UPDATE, DELETE produces a logical change record.
A stream object opens a cursor over that change log starting at the moment of creation.
Every read of the stream inside a consuming DML (INSERT INTO ... SELECT * FROM stream, MERGE ... USING stream, CREATE TABLE AS SELECT FROM stream) advances the stream's offset to the new "high water mark" once the DML transaction commits.
A read of the stream outside a DML (a plain SELECT * FROM stream) is a peek — the offset does not advance. This is the single most misunderstood semantic in snowflake change data capture.

The 2026 reality — what changed since 2022.

Dynamic Tables (Snowflake 2023+, GA 2024) compete directly with streams+tasks for the "declarative incremental view" use case. The Blog204 deep dive covers them; in 2026, senior teams pick streams+tasks for imperative control and Dynamic Tables for declarative simplicity.
Serverless tasks are now the default for new pipelines under ~5 minutes of compute per run. The break-even vs a dedicated warehouse has shifted in serverless's favour as Snowflake's per-second billing matured.
Task graphs (formerly "task trees") replaced the single-parent AFTER chain in 2023 with multi-parent DAGs. A child task with two parents fires only after both parents complete successfully — which makes fan-in CDC pipelines much cleaner.
SYSTEM$STREAM_HAS_DATA is the senior idiom for skipping a task when the upstream stream is empty. Without it, a 1-minute-schedule task fires every minute and burns credits even when nothing has changed.

What interviewers listen for.

Do you say "a stream is a cursor, not a copy of the data" in the first sentence? — senior signal.
Do you mention "the offset advances only inside a consuming DML" unprompted? — required answer.
Do you describe the stale-stream trap (offset older than DATA_RETENTION_TIME_IN_DAYS)? — senior signal.
Do you push back on "why not just use Dynamic Tables" with "imperative vs declarative, multi-target writes, complex business logic"? — senior signal.

Worked example — minimal stream + task end-to-end

Detailed explanation. The classic Snowflake CDC Hello World — capture every change to a RAW_ORDERS table, and merge the changes into a STG_ORDERS table every minute. The whole pipeline is six SQL statements: create the source, create the stream, create the staging target, create the task that merges, suspend the task, resume the task. There is no Airflow, no cron, no dbt.

Question. Build the minimal CDC pipeline from RAW_ORDERS to STG_ORDERS using one stream and one task. The task should fire every minute, merge any new changes, and skip when the stream is empty.

Input.

RAW_ORDERS row	Action
(1, 'A', 50)	INSERT
(2, 'B', 30)	INSERT
(1, 'A', 75)	UPDATE amount → 75

Code.

-- 1. Source table (the upstream CDC target)
CREATE OR REPLACE TABLE raw_orders (
  order_id   NUMBER,
  customer   STRING,
  amount     NUMBER
);

-- 2. Stream — opens a cursor over raw_orders changes
CREATE OR REPLACE STREAM raw_orders_stream ON TABLE raw_orders;

-- 3. Staging target (what we merge into)
CREATE OR REPLACE TABLE stg_orders (
  order_id   NUMBER PRIMARY KEY,
  customer   STRING,
  amount     NUMBER,
  updated_at TIMESTAMP
);

-- 4. Task — fires every minute, only when stream has data
CREATE OR REPLACE TASK merge_orders_task
  WAREHOUSE = transform_wh
  SCHEDULE = '1 MINUTE'
  WHEN SYSTEM$STREAM_HAS_DATA('raw_orders_stream')
AS
  MERGE INTO stg_orders t
  USING (SELECT order_id, customer, amount FROM raw_orders_stream) s
    ON t.order_id = s.order_id
  WHEN MATCHED THEN
    UPDATE SET customer = s.customer, amount = s.amount, updated_at = CURRENT_TIMESTAMP()
  WHEN NOT MATCHED THEN
    INSERT (order_id, customer, amount, updated_at)
    VALUES (s.order_id, s.customer, s.amount, CURRENT_TIMESTAMP());

-- 5. Resume — tasks are created in a SUSPENDED state by default
ALTER TASK merge_orders_task RESUME;

Step-by-step explanation.

CREATE STREAM raw_orders_stream ON TABLE raw_orders opens a cursor positioned at the current table state. From this moment forward, every DML on raw_orders produces a logical change record visible through the stream.
The task is declared with SCHEDULE = '1 MINUTE' so Snowflake's scheduler asks "should I fire?" every 60 seconds. The WHEN SYSTEM$STREAM_HAS_DATA(...) predicate skips the firing — and the credit cost — when the stream offset has not moved.
When raw_orders receives the three changes (INSERT, INSERT, UPDATE), the next minute boundary the predicate evaluates TRUE, the task fires, and the MERGE reads the stream.
Because the MERGE consumes the stream inside the implicit transaction of the task, the stream offset advances to the new high water mark when the MERGE commits. The next minute, SYSTEM$STREAM_HAS_DATA returns FALSE and the task skips.
If the next minute the MERGE fails (constraint violation, warehouse suspended mid-run), the transaction aborts and the offset does not advance — the same changes are visible to the next attempt. This is the at-least-once-with-idempotent-merge contract that makes streams+tasks safe.

Output (STG_ORDERS after the first run).

order_id	customer	amount	updated_at
1	A	75	2026-06-22 12:01:03
2	B	30	2026-06-22 12:01:03

Rule of thumb. Every production stream-task pair should be created WAREHOUSE = ... (or serverless) + SCHEDULE = '1 MINUTE' + WHEN SYSTEM$STREAM_HAS_DATA(...). The conditional is not optional — without it you pay for an empty run every minute.

Worked example — the offset advances only inside a DML

Detailed explanation. The single most misunderstood semantic in snowflake streams and tasks. A plain SELECT * FROM stream is a peek: you see the pending changes but the offset does not move. A DML — INSERT, UPDATE, DELETE, MERGE, CREATE TABLE AS — that reads the stream and commits successfully advances the offset. Junior candidates write a pipeline that does SELECT * FROM stream INTO @tmp then INSERT INTO target FROM @tmp in two steps and wonder why the same rows reappear forever.

Question. Given a stream s on a table t, show three reads — a SELECT, an INSERT ... SELECT, and a failed INSERT — and trace the offset after each.

Input.

Step	Statement	Outcome
1	`SELECT * FROM s`	peek
2	`INSERT INTO target SELECT * FROM s` (commits)	consume
3	`INSERT INTO target SELECT * FROM s` (rolls back)	no advance

Code.

-- Step 1 — peek. Offset does NOT advance.
SELECT * FROM s;

-- Step 2 — consume in a DML. Offset advances on commit.
INSERT INTO target (col1, col2)
SELECT col1, col2 FROM s;

-- Step 3 — DML aborts (e.g. constraint violation). Offset does NOT advance.
BEGIN;
INSERT INTO target (col1, col2)
SELECT col1, NULL FROM s;   -- target.col2 is NOT NULL → fails
COMMIT;   -- this commit fails; the BEGIN block rolls back
-- The stream still shows the same pending rows.

-- Anti-pattern — staging the read outside a DML
CREATE OR REPLACE TEMPORARY TABLE tmp AS SELECT * FROM s;
-- ^ This DOES advance the offset because CREATE TABLE AS is a DML.
-- But many teams expect this to be a "peek into a temp" — it is not.

Step-by-step explanation.

SELECT * FROM s returns the pending changes but is read-only — Snowflake treats it as a query, not a DML, so the offset stays put. You can run it 1000 times in a row and get the same rows.
The INSERT INTO target SELECT * FROM s statement opens an implicit transaction. The SELECT reads the stream's pending changes, the INSERT writes them to target, and the implicit commit at statement end advances the stream's offset to the new high water mark.
The failed INSERT (step 3) writes nothing to target because the transaction rolls back. Snowflake's contract: the offset advance is tied to the transaction commit, not the statement start. A rolled-back transaction leaves the stream offset where it was.
The "anti-pattern" matters: many teams stage the stream read into a temporary table to "look at it before deciding what to do." CREATE TABLE AS SELECT FROM s is a DML — the offset advances on the implicit commit. The pending rows are gone from the stream the moment the temp table is created. If the team then realises they need to re-run, the stream cannot help — the only recovery is to recreate the stream (losing prior history) or restore from time travel.
The clean idiom is to always read the stream inside the final, target-affecting DML. If you need to fan out to two targets, use one MERGE that writes both via INSERT ALL, or use two streams on the same source.

Output (offset advance trace).

Step	Statement	Commits?	Offset advances?
1	`SELECT * FROM s`	n/a (read-only)	no
2	`INSERT INTO target SELECT * FROM s`	yes	yes
3	`INSERT ... SELECT FROM s`	no (rolled back)	no
4	`CREATE TEMP TABLE AS SELECT * FROM s`	yes	yes (often surprising)

Rule of thumb. Treat the stream like a Kafka consumer group — read once, commit once, and design your pipeline so the read happens in the final DML, not in a staging step. If you need to peek for debugging, accept it as a peek and do not rely on the offset behaviour.

Worked example — the stale stream trap

Detailed explanation. A stream's offset must stay inside the source table's time-travel window (DATA_RETENTION_TIME_IN_DAYS). If a stream is not consumed for longer than the retention window, the underlying historical rows that the stream pointer references age out — the stream becomes stale and you cannot read it. Recreating the stream loses every pending change. This is the most common production incident in snowflake streams and tasks.

Question. Given a source table with DATA_RETENTION_TIME_IN_DAYS = 1 and a stream that has not been consumed for 36 hours, show what happens on the next read and the operational fix.

Input.

Object	Property	Value
raw_orders	DATA_RETENTION_TIME_IN_DAYS	1
raw_orders_stream	last consumed offset age	36 hours

Code.

-- Check stream staleness
SELECT SYSTEM$STREAM_GET_TABLE_TIMESTAMP('raw_orders_stream') AS stream_offset_ts,
       (DATEDIFF('hour', SYSTEM$STREAM_GET_TABLE_TIMESTAMP('raw_orders_stream')::TIMESTAMP, CURRENT_TIMESTAMP())) AS age_hours;

-- A read of a stale stream fails:
SELECT * FROM raw_orders_stream;
-- SQL execution error: Stream 'RAW_ORDERS_STREAM' is stale.

-- Operational fix 1 — extend the retention window to give the consumer more slack
ALTER TABLE raw_orders SET DATA_RETENTION_TIME_IN_DAYS = 14;

-- Operational fix 2 — extend the stream's offset retention window directly
ALTER STREAM raw_orders_stream SET DATA_RETENTION_TIME_IN_DAYS = 14;

-- Recovery — recreate the stream (loses pending change history)
CREATE OR REPLACE STREAM raw_orders_stream ON TABLE raw_orders;
-- The new stream starts at "now"; changes between old offset and now are LOST.
-- Real recovery: re-snapshot the target from the source (full refresh).

Step-by-step explanation.

Snowflake's stream offset is a logical pointer into the source table's time-travel history. The stream needs the historical row versions to compute the change set since the offset.
Time-travel history is retained for DATA_RETENTION_TIME_IN_DAYS (default 1 day on Standard edition, configurable up to 90 days on Enterprise+). After that, the old row versions are physically purged from micro-partitions.
If the stream's offset references rows older than the retention window, the change set cannot be computed — the stream becomes stale. A read raises Stream 'X' is stale.
The proximate fix is to increase DATA_RETENTION_TIME_IN_DAYS on the source table (or directly on the stream, which sets a stream-specific override). Snowflake bills the extra retention as Fail-safe storage; the cost is usually negligible compared to a stale-stream outage.
The structural fix is to ensure the consuming task fires often enough that the offset never lags more than a fraction of the retention window. A 1 MINUTE task with a DATA_RETENTION_TIME_IN_DAYS = 14 window has 14 × 24 × 60 = 20,160 minutes of headroom — plenty for any outage that does not destroy the warehouse.
The unrecoverable case: stream went stale, you have no idea which rows were "in flight" since the offset, and you must do a full refresh of the downstream target from the source. The fix is to design the consuming task so that staleness is impossible, not to recover from it.

Output (operational decision table).

Symptom	Root cause	Fix
`Stream is stale` on read	offset older than retention	extend retention; resume task
Task suspended for > retention	manual error or warehouse failure	extend retention before resuming
Source table truncated	DDL invalidates history	recreate stream; full refresh target
Offset advancing but slow	stream consumed too rarely	shorten task schedule or fan out

Rule of thumb. Always set DATA_RETENTION_TIME_IN_DAYS on the source table to at least 5x your worst expected outage window. For a 1-minute task, 14 days is the standard default. Monitoring SYSTEM$STREAM_GET_TABLE_TIMESTAMP lag is a senior-grade SRE practice; alarm at 50% of the retention window.

Senior interview question on Snowflake streams + tasks

A senior interviewer often opens with: "Walk me through how snowflake streams and tasks actually work end-to-end. What is a stream, what is a task, how do you wire them together, and what are the three or four pitfalls you would warn a junior engineer about?"

Solution Using the cursor-plus-scheduler mental model

Snowflake Streams + Tasks — senior mental model
===============================================

Stream
------
- A stream is a CURSOR over a source table's change log.
- It tracks INSERT / UPDATE / DELETE rows since the offset.
- Reading the stream INSIDE a DML that commits advances the offset.
- Reading the stream OUTSIDE a DML (plain SELECT) is a PEEK — no advance.
- The offset must stay within DATA_RETENTION_TIME_IN_DAYS of the source.

Task
----
- A task is a server-side scheduled SQL statement.
- SCHEDULE = '1 MINUTE' or 'USING CRON 0 * * * * UTC'.
- WHEN SYSTEM$STREAM_HAS_DATA('s') is the senior conditional pattern.
- Tasks chain via AFTER to form a task graph (DAG).
- Compute is WAREHOUSE = ... or serverless (no warehouse clause).

Wiring
------
- Stream on raw → Task that MERGEs into staging when stream has data.
- The MERGE reads the stream → consuming DML → offset advances on commit.
- Task graph: staging task fires AFTER raw task succeeds.

Pitfalls
--------
1. Peeking advances nothing — never stage a stream read outside a DML.
2. Stale streams are unrecoverable — set retention 5x worst outage.
3. Conditional firing matters — without WHEN, you pay for empty runs.
4. Multi-consumer = multi-stream — one stream per consumer.

Step-by-step trace.

Step	Action	Stream offset	Cost
1	CREATE STREAM s ON TABLE t	at t.now()	0
2	INSERT 3 rows into t	unchanged (3 pending)	source DML
3	Task fires, SELECT s (peek)	unchanged (3 pending)	warehouse-secs
4	Task MERGEs from s, COMMIT	advanced (0 pending)	warehouse-secs
5	Next minute, SYSTEM$STREAM_HAS_DATA(s) = FALSE	unchanged	0 (skip)

After the trace, the offset advance is tied to the commit, the conditional skip is what keeps the bill down, and the only thing a junior engineer needs to internalise is "read the stream inside the final DML."

Output:

Concept	Senior takeaway
Stream = cursor	not a copy, not a table — a pointer
Offset advance = commit	DML that commits, not statement that runs
Conditional task	WHEN SYSTEM$STREAM_HAS_DATA — always
Stale prevention	retention 5x worst outage; alarm at 50%
Multi-consumer	one stream per consumer, never one shared

Why this works — concept by concept:

Cursor-not-copy mental model — every other consequence (peek vs consume, stale risk, retention window) follows from "the stream is a pointer into the table's history." Asking "what does the stream physically store?" with the answer "just the offset" short-circuits a lot of misconceptions.
Commit-driven offset advance — Snowflake's atomicity guarantee is that the offset moves with the DML commit. This makes the pipeline naturally at-least-once-with-idempotent-merge — exactly the property streaming engineers want.
Conditional firing is the cost knob — without WHEN SYSTEM$STREAM_HAS_DATA, a 1-minute task on a 6-cluster warehouse burns ~60 × 24 × 6 = 8,640 warehouse-minutes/day even if the source is silent. The conditional is the difference between a free idle pipeline and a $500/day idle pipeline.
Retention as outage budget — the retention window is the worst-case "how long can the consuming task be down before we lose changes." Treating it as an SRE budget (5x worst outage, alarm at 50%) prevents stale-stream incidents.
Cost — stream storage is near-free (just an offset). Task compute is O(rows-changed) per fire on the chosen warehouse. Serverless tasks are O(seconds-of-actual-execution) at a higher per-second rate but no idle cost; warehouse tasks are O(warehouse-uptime) including idle. Pick by the math, not the convenience.

SQL
Topic — SQL
Snowflake SQL practice problems

Practice →

ETL Topic — ETL ETL pipeline design problems

Practice →

2. Streams — three flavours and how offsets advance

`snowflake stream` ships in three flavours — standard, append-only, insert-only — each with a different change set and overhead

The mental model in one line: a Snowflake stream is parameterised by what kinds of changes it tracks — standard (INSERT + UPDATE + DELETE), append-only (INSERT only), insert-only (INSERT on external tables) — and the choice trades capability for overhead and storage. Once you say "standard tracks all DML, append-only skips the update/delete bookkeeping," every snowflake stream flavour-pick interview question becomes a deduction from "what does the downstream need to react to?"

The three flavours.

Standard stream (default). Tracks every DML on the source: INSERT, UPDATE, DELETE. Each row in the stream carries METADATA$ACTION (INSERT or DELETE), METADATA$ISUPDATE (TRUE for the two halves of an UPDATE), and METADATA$ROW_ID. Highest overhead because Snowflake must track both the before-image (for DELETE/UPDATE) and the after-image (for INSERT/UPDATE).
Append-only stream. Tracks only INSERTs. UPDATEs and DELETEs on the source are invisible to the stream. Much lower overhead — Snowflake skips the before-image bookkeeping. Used for raw event tables that are write-once: clickstream, IoT, application logs.
Insert-only stream. A stream on an external table (or Iceberg table) that tracks only new files / new rows landing in the external storage. Cannot track updates or deletes because those concepts do not apply to append-only file targets. The natural shape for ingest from cloud object storage.

Metadata columns on every stream.

METADATA$ACTION — 'INSERT' or 'DELETE' (an UPDATE shows up as a DELETE row + an INSERT row).
METADATA$ISUPDATE — TRUE if the row is part of an UPDATE (so the delete and insert pair were one logical update).
METADATA$ROW_ID — a stable row identifier within the source table. Useful for de-duping when the same row updates multiple times within the change window.

The append-only optimisation.

The standard stream maintains a before-image / after-image pair for every changed row. Storage and lookup cost scales with update frequency.
An append-only stream (declare with APPEND_ONLY = TRUE) skips that bookkeeping entirely. Snowflake just tracks which new partitions / which new row IDs appeared since the offset.
Picking append-only when the source is write-once cuts the stream's storage and read cost by roughly 2-5x. The catch: any UPDATE or DELETE on the source after the stream was created is silently invisible to the stream. If you ever expect updates, do not use append-only.

Stream show-and-tell — what's in a row.

For a fresh INSERT into the source: one row in the stream with METADATA$ACTION = 'INSERT', METADATA$ISUPDATE = FALSE.
For an UPDATE on the source: two rows in the stream — one with METADATA$ACTION = 'DELETE', METADATA$ISUPDATE = TRUE (the old version) and one with METADATA$ACTION = 'INSERT', METADATA$ISUPDATE = TRUE (the new version). They share a METADATA$ROW_ID.
For a DELETE on the source: one row in the stream with METADATA$ACTION = 'DELETE', METADATA$ISUPDATE = FALSE.
Net change view: subtract the DELETE rows from the INSERT rows (with the same METADATA$ROW_ID) to compute the net mutation set.

Offset advance semantics — the senior-grade rules.

The offset is a single token per stream, scoped to that stream object.
A DML that reads the stream and commits advances the offset of that stream only. A second stream on the same source table has its own independent offset.
Multi-consumer pattern: create one stream per consumer (typically one per downstream task). Do not share a stream between two consumers — one will lose changes the other consumed.

Common interview probes on stream flavours.

"When do you pick append-only over standard?" — write-once source tables where updates and deletes never happen; lower overhead is the win.
"What is METADATA$ISUPDATE for?" — to tell the two halves of an UPDATE apart from a standalone DELETE+INSERT pair.
"Can two consumers share one stream?" — no, each consumer needs its own stream; the offset is per-stream.
"What happens to the stream if the source schema changes?" — Snowflake handles column adds gracefully; column drops or type changes can invalidate the stream and force recreation.

Worked example — standard stream traces every DML

Detailed explanation. A customers table receives the full DML range: an insert, an update, a delete. The standard stream captures all three, and the consumer can reproduce the exact mutation set on a downstream target via a MERGE. This is the bread-and-butter standard-stream shape.

Question. Create a standard stream on customers, apply one of each DML, and show the resulting stream content and the metadata columns.

Input — customers DML sequence.

Step	DML	Row
1	INSERT	(1, 'Alice', 'gold')
2	INSERT	(2, 'Bob', 'silver')
3	UPDATE	(1, 'Alice', 'platinum')
4	DELETE	(2)

Code.

CREATE OR REPLACE TABLE customers (
  customer_id NUMBER PRIMARY KEY,
  name        STRING,
  tier        STRING
);

-- Standard stream — tracks all DML
CREATE OR REPLACE STREAM customers_stream ON TABLE customers;

-- Apply the DML
INSERT INTO customers VALUES (1, 'Alice', 'gold');
INSERT INTO customers VALUES (2, 'Bob', 'silver');
UPDATE customers SET tier = 'platinum' WHERE customer_id = 1;
DELETE FROM customers WHERE customer_id = 2;

-- Peek at the stream (offset does NOT advance)
SELECT customer_id, name, tier,
       METADATA$ACTION, METADATA$ISUPDATE, METADATA$ROW_ID
FROM customers_stream;

Step-by-step explanation.

Each INSERT produces a single stream row with METADATA$ACTION = 'INSERT' and METADATA$ISUPDATE = FALSE. The METADATA$ROW_ID is a stable identifier assigned by Snowflake.
The UPDATE produces two rows: the old (1, 'Alice', 'gold') with METADATA$ACTION = 'DELETE', METADATA$ISUPDATE = TRUE and the new (1, 'Alice', 'platinum') with METADATA$ACTION = 'INSERT', METADATA$ISUPDATE = TRUE. Both share the same METADATA$ROW_ID.
The DELETE produces one row: (2, 'Bob', 'silver') with METADATA$ACTION = 'DELETE', METADATA$ISUPDATE = FALSE. The METADATA$ROW_ID matches the original INSERT for customer 2.
The peek shows 5 stream rows total — 2 INSERTs, the 2 halves of the UPDATE, and 1 DELETE. The downstream can reconstruct the net state by applying the same actions in order, or compute the net delta with METADATA$ROW_ID grouping.
Because the SELECT is a peek, the offset has not advanced. A subsequent MERGE INTO target USING customers_stream s ON ... WHEN MATCHED THEN UPDATE ... would re-see all 5 rows and advance the offset on commit.

Output (stream content peek).

customer_id	name	tier	METADATA$ACTION	METADATA$ISUPDATE
1	Alice	gold	INSERT	FALSE
2	Bob	silver	INSERT	FALSE
1	Alice	gold	DELETE	TRUE
1	Alice	platinum	INSERT	TRUE
2	Bob	silver	DELETE	FALSE

Rule of thumb. When you need full change propagation (inserts, updates, deletes), use a standard stream and key your MERGE off METADATA$ACTION. Use METADATA$ISUPDATE only when you need to tell "this was an update" from "this was a stand-alone delete + insert" — most pipelines do not care.

Worked example — append-only stream skips update/delete

Detailed explanation. A clickstream_events table is append-only by design — every event is a write-once row, never updated, never deleted. Using a standard stream on it is wasteful: Snowflake maintains the before-image bookkeeping for changes that never happen. An append-only stream declaration drops that cost.

Question. Create an append-only stream on a clickstream_events table. Show what happens when an UPDATE is (incorrectly) applied to the source, and how the stream behaves.

Input.

Step	DML	Row
1	INSERT	(e1, u1, '/home')
2	INSERT	(e2, u2, '/pricing')
3	UPDATE	(e1, u1, '/home/v2')
4	INSERT	(e3, u1, '/checkout')

Code.

CREATE OR REPLACE TABLE clickstream_events (
  event_id   STRING,
  user_id    STRING,
  url        STRING,
  event_ts   TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
);

-- Append-only stream — only INSERTs are tracked
CREATE OR REPLACE STREAM clickstream_events_stream
  ON TABLE clickstream_events
  APPEND_ONLY = TRUE;

INSERT INTO clickstream_events (event_id, user_id, url)
VALUES ('e1', 'u1', '/home'),
       ('e2', 'u2', '/pricing');

-- This UPDATE is INVISIBLE to the append-only stream
UPDATE clickstream_events SET url = '/home/v2' WHERE event_id = 'e1';

INSERT INTO clickstream_events (event_id, user_id, url)
VALUES ('e3', 'u1', '/checkout');

SELECT event_id, user_id, url,
       METADATA$ACTION, METADATA$ISUPDATE
FROM clickstream_events_stream;

Step-by-step explanation.

The append-only stream declaration APPEND_ONLY = TRUE tells Snowflake to skip the before-image bookkeeping. Internally, Snowflake just tracks which new micro-partitions appeared since the offset.
The two INSERTs produce stream rows with METADATA$ACTION = 'INSERT', METADATA$ISUPDATE = FALSE. Standard behaviour.
The UPDATE on e1 is silently invisible to the append-only stream. The downstream consumer never sees the change. This is a footgun: if the source contract ever changes to allow updates, the pipeline silently misses them.
The third INSERT produces another stream row, normal append behaviour.
The peek shows only 3 rows — the 3 inserts. The update never appears. Storage and read cost are lower than a standard stream by roughly 2-5x for typical workloads.

Output (append-only stream content).

event_id	user_id	url	METADATA$ACTION
e1	u1	/home	INSERT
e2	u2	/pricing	INSERT
e3	u1	/checkout	INSERT

Rule of thumb. Use APPEND_ONLY = TRUE only when the source contract is "no updates, no deletes, ever." For raw event tables (clickstream, IoT, log lines), this is the right answer. For mutable dimension tables (customers, products), always use a standard stream — the overhead is small compared to the risk of silently missing updates.

Worked example — multi-consumer fan-out with separate streams

Detailed explanation. Two downstream pipelines need the same change feed from raw_orders: pipeline A merges into a staging table, pipeline B writes an audit log. Sharing one stream is wrong — the first DML to consume it advances the offset, and the second consumer never sees the same rows. The correct pattern is two streams on the same source.

Question. Build two consumers off the same raw_orders table — one merges into stg_orders, one writes to audit_orders. Show why two streams (not one) is the senior pattern.

Input.

Source	Consumer A	Consumer B
raw_orders	merge → stg_orders	append → audit_orders

Code.

-- WRONG: one stream shared between two consumers
-- (whichever task runs first eats the changes; the other sees nothing)
CREATE OR REPLACE STREAM raw_orders_stream ON TABLE raw_orders;

-- Both tasks read from the same stream — race condition
-- CREATE TASK consumer_a ... MERGE INTO stg_orders USING (SELECT * FROM raw_orders_stream) ...
-- CREATE TASK consumer_b ... INSERT INTO audit_orders SELECT * FROM raw_orders_stream ...
-- ^ DO NOT DO THIS

-- RIGHT: one stream per consumer
CREATE OR REPLACE STREAM raw_orders_stream_a ON TABLE raw_orders;
CREATE OR REPLACE STREAM raw_orders_stream_b ON TABLE raw_orders;

CREATE OR REPLACE TASK consumer_a
  WAREHOUSE = transform_wh
  SCHEDULE = '1 MINUTE'
  WHEN SYSTEM$STREAM_HAS_DATA('raw_orders_stream_a')
AS
  MERGE INTO stg_orders t
  USING (SELECT * FROM raw_orders_stream_a) 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);

CREATE OR REPLACE TASK consumer_b
  WAREHOUSE = transform_wh
  SCHEDULE = '5 MINUTE'
  WHEN SYSTEM$STREAM_HAS_DATA('raw_orders_stream_b')
AS
  INSERT INTO audit_orders (order_id, amount, audited_at)
  SELECT order_id, amount, CURRENT_TIMESTAMP()
  FROM raw_orders_stream_b;

ALTER TASK consumer_a RESUME;
ALTER TASK consumer_b RESUME;

Step-by-step explanation.

Each stream is an independent cursor. raw_orders_stream_a and raw_orders_stream_b start at the same point (CREATE time) but advance independently as their respective consumers commit.
Consumer A fires every minute and runs a MERGE. The MERGE consumes stream_a; stream_a's offset advances on commit. stream_b is unaffected.
Consumer B fires every 5 minutes and runs an INSERT. The INSERT consumes stream_b; stream_b's offset advances on commit. stream_a is unaffected.
If raw_orders receives 100 new rows in a minute, both streams see those 100 rows. Consumer A processes them on the next minute boundary, consumer B on the next 5-minute boundary. Neither consumer is starved by the other.
Cost: each stream adds independent offset tracking and (for standard streams) before-image bookkeeping. Two streams on the same source roughly doubles the stream overhead. In exchange you get isolated consumers and no fan-out race condition.

Output (per-stream offset progression).

Time	raw_orders	stream_a offset	stream_b offset
t=0	100 rows	at 0	at 0
t=1m	consumer A merges	advanced to 100	at 0
t=2m	50 new rows	at 100 (50 pending)	at 0 (150 pending)
t=5m	consumer B inserts	at 100 (50 pending)	advanced to 150
t=6m	consumer A merges	advanced to 150	at 150

Rule of thumb. Always create one stream per downstream consumer. The naming convention raw_<source>_stream_<consumer_id> makes the topology readable. Sharing a stream between two consumers is the most common production CDC bug in snowflake streams and tasks.

Senior interview question on choosing a stream flavour

A senior interviewer might ask: "You have three source tables — users (mutable dimension), events (write-once clicks), and an external_iceberg.orders table landing as new files. Pick a stream flavour for each and justify the choice."

Solution Using flavour-by-source-mutability mapping

-- users: standard stream (full DML propagation needed)
CREATE OR REPLACE STREAM users_stream ON TABLE users;
-- Tracks INSERT (signup), UPDATE (tier change), DELETE (account close).

-- events: append-only stream (write-once, lower overhead)
CREATE OR REPLACE STREAM events_stream
  ON TABLE events
  APPEND_ONLY = TRUE;
-- Skips before-image bookkeeping; 2-5x cheaper for high-volume event tables.

-- external_iceberg.orders: insert-only stream (external table)
CREATE OR REPLACE STREAM orders_iceberg_stream
  ON EXTERNAL TABLE external_iceberg.orders
  INSERT_ONLY = TRUE;
-- External tables only support insert-only; tracks new files / new rows.

Step-by-step trace.

Source	Mutability	Stream flavour	Why
users	INSERT + UPDATE + DELETE	standard	need full DML for downstream MERGE
events	INSERT only	append-only	lower overhead, write-once contract
external_iceberg.orders	INSERT (new files)	insert-only	external tables only allow insert-only

After mapping each source to its stream flavour, the downstream tasks each use the corresponding stream + a MERGE or INSERT. The flavour choice is irreversible — you must DROP STREAM and recreate to change, losing all pending history.

Output:

Stream	Use case	Cost profile
Standard	mutable dimensions, fact tables with corrections	highest (before+after images)
Append-only	event logs, IoT, clickstream, write-once raw	2-5x cheaper
Insert-only	external tables, Iceberg, file-landing zones	lowest (just file tracking)

Why this works — concept by concept:

Flavour-by-mutability — pick the stream type from the source contract, not the downstream usage. If the source contract is "no updates ever," append-only is free money. If the source contract is "anything can change," standard is non-negotiable.
Append-only is irreversible by design — Snowflake silently ignores updates and deletes on an append-only stream. If the source contract is wrong, the pipeline silently corrupts. Always verify the source's mutability before declaring APPEND_ONLY = TRUE.
Insert-only is the only option for external tables — Iceberg, file landings, and other append-only storage targets do not support updates or deletes. Snowflake enforces this with the INSERT_ONLY = TRUE flag on external streams.
One stream per consumer — separating consumers prevents offset races. The cost is independent stream bookkeeping (doubles overhead for two consumers), but the cost of a fan-out race is debugging a silent data-loss incident.
Cost — stream storage is roughly proportional to the change rate × retention window. Standard streams are 2-5x append-only at the same change rate. Insert-only is the cheapest. Per-read cost is similar across all three; the difference is mostly in the source-side tracking.

SQL
Topic — SQL
Stream design SQL problems

Practice →

SQL Topic — joins · SQL SQL joins drills

Practice →

3. Tasks — schedule, DAG, conditional execution

`snowflake task` is the warehouse's native scheduler — standalone, task graph, or serverless, with optional stream-driven conditions

The mental model in one line: a Snowflake task is a server-side SQL statement attached to a schedule (interval or cron), optionally chained into a task graph with AFTER, optionally gated on WHEN SYSTEM$STREAM_HAS_DATA, and optionally serverless instead of warehouse-bound. Once you internalise the four orthogonal axes (schedule, chain, condition, compute), every snowflake task interview probe — task graph depth, conditional skipping, serverless break-even — becomes a deduction from those four choices.

The schedule clause — two forms.

Interval form — SCHEDULE = '5 MINUTE' or '1 HOUR'. Snowflake fires the task on a wall-clock interval starting at RESUME time. Simple, predictable, no cron syntax needed.
Cron form — SCHEDULE = 'USING CRON 0 6 * * * UTC' for "every day at 06:00 UTC." Full cron expression (5 fields, no seconds) plus a timezone. Use for time-of-day scheduling and timezone-aware pipelines.

Standalone vs task graph.

Standalone task. One task, one schedule, one DML. The smallest unit and the default for simple CDC pipelines.
Task graph (DAG). A child task with AFTER parent_task does not have its own schedule — it fires when its parent succeeds. A child can list multiple parents (AFTER p1, p2) and waits for both to succeed. Only the root task (no AFTER clause) needs a SCHEDULE; the rest of the graph cascades from the root.
Graph constraints. Maximum graph depth 1000 (in practice keep it under 30 for debuggability). One root per graph. Cycle detection is enforced at RESUME time.

Conditional execution with SYSTEM$STREAM_HAS_DATA.

The WHEN clause is a SQL expression evaluated before each scheduled fire. If it returns FALSE, the task is skipped — no compute charge, no run history entry as "executed."
The canonical condition: WHEN SYSTEM$STREAM_HAS_DATA('my_stream'). This skips the task when the upstream stream is empty, which is the standard way to make a 1-minute-schedule task cost nothing on idle minutes.
Multiple streams: WHEN SYSTEM$STREAM_HAS_DATA('s1') OR SYSTEM$STREAM_HAS_DATA('s2') — fires when either has data.
Other system functions in WHEN: CURRENT_HOUR() for hour-of-day gating, SYSTEM$CURRENT_USER_TASK_NAME() for self-aware logic.

Warehouse-bound vs serverless tasks.

Warehouse-bound — declare WAREHOUSE = transform_wh. The named warehouse runs the task. You pay for the warehouse's full uptime (including idle), so you typically pair this with a dedicated transform warehouse with auto-suspend at 60 seconds.
Serverless — omit the WAREHOUSE clause and declare USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'SMALL' (or any size). Snowflake spins up an ephemeral compute for the task's duration and charges per second of actual execution at the serverless rate. No idle cost.
Break-even rule. Serverless wins when total daily compute is < ~30% of a dedicated warehouse's idle floor. Warehouse-bound wins when many tasks share the same warehouse and amortise the idle.

Task observability.

SHOW TASKS — list every task in the schema with its schedule, state (SUSPENDED / STARTED), and predecessors.
TASK_HISTORY() table function — every fire with state (SUCCEEDED, FAILED, SKIPPED), duration, error message, and query id.
COMPLETE_TASK_GRAPHS() — task graph runs as a unit; this view shows the overall DAG run state.
Per-task error notifications via ERROR_INTEGRATION = ... route failures to a webhook (Slack, PagerDuty).

Common interview probes on tasks.

"How do you wire a task to fire only when there's new data?" — WHEN SYSTEM$STREAM_HAS_DATA('s') in the task declaration.
"How do you build a task DAG?" — root task has SCHEDULE; children have AFTER parent_name and no SCHEDULE of their own.
"When is serverless cheaper than warehouse?" — total run time < ~30% of warehouse uptime, and you do not have other workloads to share the warehouse with.
"How do you handle a task failure?" — TASK_HISTORY() for diagnosis, ALTER TASK ... RESUME after fix; for retry behaviour, the task graph re-runs from the failed node on next schedule.

Worked example — standalone task on a cron schedule

Detailed explanation. A daily aggregation task that runs at 06:00 UTC. The simplest task shape — one statement, one cron schedule, one warehouse. The cron form is preferred for time-of-day scheduling because interval form drifts relative to the wall clock over time.

Question. Write a task that aggregates the previous day's orders into a daily summary table, running at 06:00 UTC every day.

Input.

Source	Target	Cadence
stg_orders	daily_order_summary	daily 06:00 UTC

Code.

CREATE OR REPLACE TASK daily_order_summary_task
  WAREHOUSE = transform_wh
  SCHEDULE = 'USING CRON 0 6 * * * UTC'
AS
  INSERT INTO daily_order_summary (summary_date, customer, total_amount, order_count)
  SELECT
    DATE_TRUNC('DAY', order_ts) AS summary_date,
    customer,
    SUM(amount)                  AS total_amount,
    COUNT(*)                     AS order_count
  FROM stg_orders
  WHERE order_ts >= DATEADD('DAY', -1, CURRENT_DATE())
    AND order_ts <  CURRENT_DATE()
  GROUP BY 1, 2;

ALTER TASK daily_order_summary_task RESUME;

Step-by-step explanation.

The SCHEDULE = 'USING CRON 0 6 * * * UTC' clause uses standard 5-field cron syntax (minute, hour, day-of-month, month, day-of-week) plus a timezone. 0 6 * * * means "minute 0 of hour 6 every day." UTC is the standard timezone for warehouse scheduling.
The task body is a regular SQL statement — here, an INSERT ... SELECT that aggregates yesterday's orders. No stream involvement: this task is a pure schedule-driven batch job.
WAREHOUSE = transform_wh binds the task to a named warehouse. The warehouse must be in a state where it can wake up (auto-resume = TRUE on the warehouse settings).
ALTER TASK ... RESUME is required to actually start the schedule. Tasks are created in SUSPENDED state by default — a safety so that a typo in the body does not immediately fire.
Snowflake will fire the task at 06:00 UTC every day. If a fire is missed (warehouse unavailable, account paused), the task does NOT backfill — it waits for the next scheduled fire. Backfills are the user's responsibility.

Output (DAILY_ORDER_SUMMARY after one run).

summary_date	customer	total_amount	order_count
2026-06-21	A	350	7
2026-06-21	B	220	4
2026-06-21	C	90	2

Rule of thumb. Use cron form for daily / hourly / weekly scheduling. Use interval form for "every N minutes" CDC pipelines. Cron schedules are timezone-aware (UTC is the senior default to avoid DST surprises); interval schedules are wall-clock relative to RESUME time.

Worked example — task graph with AFTER chaining

Detailed explanation. A daily ELT pipeline: extract raw orders, transform into staging, then build the mart. Each step depends on the previous one. Without a task graph you would write three independent schedules and pray they fire in order; with a task graph, the dependencies are explicit and cascading.

Question. Build a 3-step task graph: extract → transform → mart. Only the root task has a schedule; the children fire on parent success.

Input.

Step	Action
t_extract	INSERT raw → staging
t_transform	MERGE staging → mart
t_mart	refresh aggregate cube

Code.

-- Root task — has SCHEDULE, no AFTER
CREATE OR REPLACE TASK t_extract
  WAREHOUSE = transform_wh
  SCHEDULE = 'USING CRON 0 6 * * * UTC'
AS
  INSERT INTO staging (col_a, col_b)
  SELECT col_a, col_b FROM raw WHERE ingest_ts >= DATEADD('DAY', -1, CURRENT_DATE());

-- Child 1 — fires after t_extract; no SCHEDULE
CREATE OR REPLACE TASK t_transform
  WAREHOUSE = transform_wh
  AFTER t_extract
AS
  MERGE INTO mart m
  USING staging s
    ON m.key = s.key
  WHEN MATCHED THEN UPDATE SET m.col_b = s.col_b
  WHEN NOT MATCHED THEN INSERT (key, col_a, col_b) VALUES (s.key, s.col_a, s.col_b);

-- Child 2 — fires after t_transform
CREATE OR REPLACE TASK t_mart
  WAREHOUSE = transform_wh
  AFTER t_transform
AS
  INSERT OVERWRITE INTO mart_cube
  SELECT DATE_TRUNC('DAY', updated_at) AS d, COUNT(*) AS row_count
  FROM mart
  GROUP BY 1;

-- Resume the LEAVES first, then the root.
ALTER TASK t_mart RESUME;
ALTER TASK t_transform RESUME;
ALTER TASK t_extract RESUME;   -- root resumed last

Step-by-step explanation.

The root task t_extract declares a schedule (USING CRON 0 6 * * * UTC) and no AFTER clause. It is the entry point of the DAG. When it fires, it starts the graph run.
The child task t_transform declares AFTER t_extract and no SCHEDULE. Snowflake's task scheduler runs t_transform automatically when t_extract succeeds. If t_extract fails or is skipped, t_transform does not fire.
t_mart declares AFTER t_transform similarly — a 3-node chain. Failure at any step halts the downstream nodes; the next scheduled root fire is a fresh attempt from the start.
Resume order matters. Tasks must be resumed from the leaves up: t_mart first, then t_transform, then t_extract last. This is because a task cannot resume if it has any predecessor in SUSPENDED state below it — but children can resume before parents. Snowflake will reject the root's resume if any leaf is suspended.
The graph run is observable in COMPLETE_TASK_GRAPHS() — a single row per graph run with overall state (SUCCEEDED, FAILED, partial). Per-node detail is in TASK_HISTORY().

Output (TASK_HISTORY for one graph run).

task_name	scheduled_time	state	duration (s)
t_extract	06:00:00	SUCCEEDED	12
t_transform	06:00:12	SUCCEEDED	18
t_mart	06:00:30	SUCCEEDED	4

Rule of thumb. Use task graphs for any pipeline with explicit step-to-step dependencies (more than 1 SQL statement). Resume from the leaves; suspend from the root. A 5-step linear DAG is the right size for most pipelines; deeper than 20 nodes is usually a sign you should switch to a dedicated orchestrator or to Dynamic Tables.

Worked example — serverless task vs warehouse task

Detailed explanation. A team is debating: should a new CDC task be warehouse-bound on a shared XSMALL transform warehouse, or serverless? The break-even depends on how much actual compute the task consumes, how much idle the warehouse would otherwise have, and how many tasks share the warehouse.

Question. Compare a warehouse-bound task and a serverless task for a 1-minute CDC job that takes ~3 seconds per fire. Show the cost math.

Input.

Mode	Compute	Fires/day	Time/fire
Warehouse-bound	XSMALL shared	1440	3s
Serverless	initial XSMALL	1440	3s

Code.

-- Warehouse-bound — uses a named warehouse
CREATE OR REPLACE TASK cdc_warehouse_task
  WAREHOUSE = transform_wh
  SCHEDULE = '1 MINUTE'
  WHEN SYSTEM$STREAM_HAS_DATA('raw_orders_stream')
AS
  MERGE INTO stg_orders t USING raw_orders_stream 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);

-- Serverless — Snowflake manages the compute
CREATE OR REPLACE TASK cdc_serverless_task
  USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'XSMALL'
  SCHEDULE = '1 MINUTE'
  WHEN SYSTEM$STREAM_HAS_DATA('raw_orders_stream')
AS
  MERGE INTO stg_orders t USING raw_orders_stream 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);

Step-by-step explanation.

The warehouse-bound task uses transform_wh — an XSMALL with auto-suspend at 60 seconds. Each task fire wakes the warehouse, runs ~3 seconds, then the warehouse stays up for 60 seconds waiting for more work. If no other task fires in those 60 seconds, the warehouse suspends.
With 1440 fires/day and 3 seconds each (assume conditional skips 50% of fires due to idle minutes), actual compute = 720 × 3 = 2160 seconds = 36 minutes/day. But the warehouse stays up for ~60 seconds after every fire — minimum chargeable interval. So billed warehouse time ≈ 720 × 60 = 43,200 seconds = 720 minutes/day = 12 hours/day.
The serverless task charges only for actual execution: 720 × 3 = 2160 seconds = 36 minutes/day. No idle wait. The serverless per-second rate is ~1.5x the warehouse rate, so net cost ≈ 36 × 1.5 = 54 warehouse-minute-equivalents/day.
Result: serverless ≈ 54 minutes/day, warehouse ≈ 720 minutes/day → serverless is ~13x cheaper for this isolated task. The flip side: if the warehouse hosts 50 other CDC tasks that all benefit from the shared idle, the warehouse cost is amortised across them.
Break-even rule of thumb. Serverless wins for isolated tasks under ~10 minutes per fire and under ~30% utilisation. Warehouse-bound wins when ≥ 10 tasks share the warehouse and overall utilisation is > ~50%.

Output (daily cost comparison, XSMALL).

Mode	Actual compute (s)	Billed (s)	Daily cost (XSMALL credits)
Warehouse-bound	2,160	43,200 (60s minimum)	~12 credits
Serverless	2,160	2,160 × 1.5 = 3,240	~0.9 credits

Rule of thumb. Default new isolated CDC tasks to serverless. Move to a shared warehouse only when you have 10+ tasks that can all share the same warehouse and benefit from the shared idle. The serverless rate premium is small; the savings on idle are large.

Senior interview question on task graph design

A senior interviewer might ask: "You have a CDC pipeline with 5 source tables, each landing into its own staging table, and one fact table that joins them all. How would you wire this in snowflake task graph form? What is the dependency shape?"

Solution Using a fan-in task graph with per-source root tasks

-- 5 source-specific extract tasks (each is a root with its own stream + schedule)
CREATE OR REPLACE TASK t_extract_users
  WAREHOUSE = transform_wh
  SCHEDULE = '1 MINUTE'
  WHEN SYSTEM$STREAM_HAS_DATA('users_stream')
AS
  MERGE INTO stg_users USING users_stream s ON stg_users.id = s.id
  WHEN MATCHED THEN UPDATE SET stg_users.name = s.name, stg_users.tier = s.tier
  WHEN NOT MATCHED THEN INSERT (id, name, tier) VALUES (s.id, s.name, s.tier);

-- ... repeat for orders, products, sessions, payments ...

-- Build a tracker task whose ONLY job is to fan-in
CREATE OR REPLACE TASK t_build_fact
  WAREHOUSE = transform_wh
  AFTER t_extract_users, t_extract_orders, t_extract_products,
        t_extract_sessions, t_extract_payments
AS
  INSERT OVERWRITE INTO fact_orders
  SELECT u.id, u.tier, o.order_id, o.amount, p.product_name, s.session_id, py.method
  FROM stg_users u
  JOIN stg_orders o ON o.user_id = u.id
  JOIN stg_products p ON o.product_id = p.id
  JOIN stg_sessions s ON s.user_id = u.id
  JOIN stg_payments py ON py.order_id = o.order_id;

-- Resume leaves first, then roots
ALTER TASK t_build_fact RESUME;
ALTER TASK t_extract_payments RESUME;
ALTER TASK t_extract_sessions RESUME;
ALTER TASK t_extract_products RESUME;
ALTER TASK t_extract_orders RESUME;
ALTER TASK t_extract_users RESUME;

Step-by-step trace.

Step	Task	Triggered by	Output
1	t_extract_users fires	schedule + stream has data	stg_users merged
2	t_extract_orders fires	schedule + stream has data	stg_orders merged
3	... 3 more parallel extractors ...	schedules	stg_X merged
4	t_build_fact fires	all 5 parents succeeded	fact_orders rebuilt

The graph is a 5-to-1 fan-in: five independent root tasks (each with its own stream and schedule), one child task that waits on all five. When all five succeed in the same scheduling window, the fact rebuild fires. If any one of the five has WHEN ... = FALSE and is skipped, the child does not fire — Snowflake treats skipped predecessors as not-yet-completed.

Output:

Pattern	Snowflake idiom
5 sources, 1 fact	5 root tasks + 1 child with `AFTER p1, p2, p3, p4, p5`
Per-source CDC	one stream per source, one task per source
Fan-in semantics	child waits for ALL parents to succeed
Skipped parent	child does NOT fire (treated as incomplete)

Why this works — concept by concept:

Fan-in via multi-parent AFTER — AFTER p1, p2, p3 is Snowflake's native multi-parent dependency. The child fires when every named parent completes successfully in the same graph run. No external orchestrator needed.
One stream + one task per source — each extract is independent: own stream, own schedule, own conditional. Failure on one source does not cascade to the others. The fact build is the single fan-in point that orchestrates them.
Skipped parents block the child — if one of the five streams is empty for an hour, that source's task is skipped (cost-saving), but the fact build is also skipped. This is usually what you want: do not rebuild the fact with stale partial data.
Resume from leaves — Snowflake enforces that a parent cannot resume while a child is suspended. Resume order is leaves first (child), then roots (parents). Suspend goes the other way.
Cost — 5 conditional root tasks + 1 child = 6 tasks. With serverless, daily cost ≈ Σ(actual-fire-seconds × 1.5). With warehouse-bound on a shared XSMALL, cost ≈ warehouse uptime (amortised). For high-frequency CDC (1-minute), serverless usually wins. For daily batch (1 hour+), warehouse-bound wins.

ETL
Topic — ETL
Task graph design problems

Practice →

ETL Topic — ETL · medium Medium ETL drills

Practice →

4. Composing CDC pipelines — streams into tasks

The canonical `snowflake change data capture` pattern wires a stream onto raw, a task that MERGEs into staging, and a downstream task graph for marts

The mental model in one line: the standard snowflake streams and tasks pipeline is raw_table → stream → conditional task → MERGE into staging → AFTER-chained task → MERGE into mart, and the safety guarantees come from running each MERGE inside its own transactional task fire so the stream offset advances atomically with the target write. Once you internalise the stream-then-task recipe, every CDC pipeline interview probe — multi-target writes, SCD-2 dimensions, transactional consumption — becomes a deduction from "what does my idempotent MERGE need to look like?"

The canonical recipe.

Step 1 — stream on raw. CREATE STREAM raw_X_stream ON TABLE raw_X — opens the cursor. Pick the flavour by source mutability (standard / append-only / insert-only).
Step 2 — task that consumes the stream. A 1-minute schedule with WHEN SYSTEM$STREAM_HAS_DATA('raw_X_stream'). The task body is a MERGE INTO staging USING raw_X_stream. The MERGE is the consuming DML — offset advances on commit.
Step 3 — downstream task graph. Children with AFTER build derived tables (staging → mart, mart → cube). They do not need their own streams unless they have their own change-detection logic.
Step 4 — observability. TASK_HISTORY() for per-fire state, alarms on FAILED and SKIPPED patterns, SYSTEM$STREAM_GET_TABLE_TIMESTAMP lag for stale-stream prevention.

Transactional consumption — the atomicity contract.

A task's body runs inside an implicit transaction. If the MERGE succeeds and commits, the stream offset advances and the target rows are visible — atomic across both.
If the MERGE fails (constraint violation, mid-statement warehouse failure, statement timeout), the transaction rolls back. The stream offset stays put; the target is unchanged. Next fire sees the same pending changes.
This is the at-least-once-with-idempotent-merge contract: MERGE is idempotent (same input → same output), so reprocessing is safe.

Idempotency — designing the MERGE so re-runs are safe.

Deterministic merge key. The ON clause must use a stable, deterministic key (order_id, event_id). Never use CURRENT_TIMESTAMP() or a generated row number.
Source-side dedupe. If the source can emit duplicates (e.g. CDC connector retries), de-dupe in the USING clause with QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY change_ts DESC) = 1.
Action discrimination. Use METADATA$ACTION to pick WHEN MATCHED THEN UPDATE vs WHEN MATCHED THEN DELETE vs WHEN NOT MATCHED THEN INSERT. This is how you propagate deletes from a standard stream.

Multi-target writes — one stream, multiple targets.

Naive answer: read the stream once, write to two targets in one statement. Snowflake's INSERT ALL lets you split a SELECT across two targets.
Senior answer: two streams, two tasks. Avoids the multi-target failure mode where one target succeeds and the other fails (mid-statement) — Snowflake's transactional contract is per-statement, so if a multi-target statement fails halfway, the offset still advances on commit of the partial write.
Compromise: one stream, one task with MERGE INTO target1 + MERGE INTO target2 in the body, wrapped in BEGIN; ... COMMIT;. The explicit transaction makes both writes atomic together.

SCD-2 on a stream — the senior interview classic.

A customers standard stream propagates inserts (new customer), updates (tier change), deletes (account closure) to a dim_customers table.
SCD-2 = keep history: on update, close the old row (is_current = FALSE, valid_to = now) and insert a new row (is_current = TRUE, valid_from = now).
The MERGE handles WHEN MATCHED AND METADATA$ACTION = 'INSERT' AND METADATA$ISUPDATE = TRUE (the "after" half of an update) → insert new version; and WHEN MATCHED AND METADATA$ACTION = 'DELETE' → close the current row.

Common interview probes on composition.

"How do you make a multi-target write atomic?" — wrap in BEGIN; ... COMMIT; to span both DMLs in one transaction.
"Two consumers off the same source — one stream or two?" — always two. One stream per consumer.
"How do you build SCD-2 on a stream?" — use METADATA$ACTION + METADATA$ISUPDATE to discriminate insert / update / delete, close old rows and insert new rows in the same MERGE.
"What if the task body fails mid-run?" — transaction rolls back, offset stays put, next fire re-processes; design the MERGE to be idempotent.

Worked example — canonical CDC merge from raw to staging

Detailed explanation. The simplest production CDC pattern: stream on raw_orders, task that MERGEs into stg_orders every minute when the stream has data. The MERGE propagates inserts, updates, and deletes correctly using METADATA$ACTION.

Question. Build the canonical raw-to-staging CDC pipeline with full DML propagation. Show the MERGE that handles insert, update, and delete via the stream's metadata columns.

Input.

raw_orders DML	Stream content
INSERT (o1, A, 50)	(o1, A, 50, INSERT)
UPDATE o1 amount=75	(o1, A, 50, DELETE, isupdate=TRUE), (o1, A, 75, INSERT, isupdate=TRUE)
DELETE o1	(o1, A, 75, DELETE)

Code.

-- Source + stream
CREATE OR REPLACE TABLE raw_orders (order_id STRING, customer STRING, amount NUMBER);
CREATE OR REPLACE STREAM raw_orders_stream ON TABLE raw_orders;

-- Target
CREATE OR REPLACE TABLE stg_orders (
  order_id   STRING PRIMARY KEY,
  customer   STRING,
  amount     NUMBER,
  updated_at TIMESTAMP
);

-- CDC task — handles INSERT, UPDATE, DELETE via METADATA$ACTION
CREATE OR REPLACE TASK cdc_orders_task
  WAREHOUSE = transform_wh
  SCHEDULE = '1 MINUTE'
  WHEN SYSTEM$STREAM_HAS_DATA('raw_orders_stream')
AS
  MERGE INTO stg_orders t
  USING (
    SELECT
      order_id, customer, amount,
      METADATA$ACTION   AS action,
      METADATA$ISUPDATE AS isupdate
    FROM raw_orders_stream
    QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY METADATA$ROW_ID DESC) = 1
  ) s
    ON t.order_id = s.order_id
  WHEN MATCHED AND s.action = 'DELETE' AND s.isupdate = FALSE THEN
    DELETE
  WHEN MATCHED AND s.action = 'INSERT' THEN
    UPDATE SET t.customer = s.customer, t.amount = s.amount, t.updated_at = CURRENT_TIMESTAMP()
  WHEN NOT MATCHED AND s.action = 'INSERT' THEN
    INSERT (order_id, customer, amount, updated_at)
    VALUES (s.order_id, s.customer, s.amount, CURRENT_TIMESTAMP());

ALTER TASK cdc_orders_task RESUME;

Step-by-step explanation.

The USING clause applies a QUALIFY ROW_NUMBER() to collapse multiple changes to the same order_id within the same fire — if the source updated o1 three times, the stream has three sets of change rows; we take only the latest.
The WHEN MATCHED AND s.action = 'DELETE' AND s.isupdate = FALSE THEN DELETE branch propagates standalone deletes: a customer cancelled their order, the target row is removed.
The WHEN MATCHED AND s.action = 'INSERT' THEN UPDATE branch handles two cases at once: a new row arriving with a key that already exists (update-as-upsert), or the "after" half of an update on the source (the stream emits the after-image as INSERT).
The WHEN NOT MATCHED AND s.action = 'INSERT' branch handles fresh inserts — new key, new row.
The MERGE runs inside the task's implicit transaction. On commit, the stream offset advances and the target rows are visible. On rollback, neither happens. Next fire sees the same pending changes (idempotent re-run).

Output (STG_ORDERS after the trace).

Step	DML on raw	stg_orders state
1	INSERT o1 (A, 50)	(o1, A, 50)
2	UPDATE o1 amount=75	(o1, A, 75)
3	DELETE o1	(empty)

Rule of thumb. Always discriminate on METADATA$ACTION for full DML propagation. Use QUALIFY ROW_NUMBER() in the USING clause to collapse multi-change-per-key cases. Use the WHEN MATCHED AND action = 'DELETE' AND isupdate = FALSE form to distinguish standalone deletes from the "before" half of an update (isupdate = TRUE).

Worked example — SCD-2 dimension from a CDC stream

Detailed explanation. A dim_customers table needs full SCD-2 history: every tier change should produce a new row, and the old row's valid_to should be set to the change timestamp. The source is raw_customers; the stream is standard. The MERGE is more elaborate because we need to do two writes per update — close the old row and insert the new row.

Question. Build an SCD-2 dim_customers from a CDC stream on raw_customers. Show how the standard stream's metadata columns drive the close-old + insert-new pattern.

Input — raw_customers DML.

Step	Action	Row
1	INSERT	(c1, Alice, gold)
2	UPDATE	(c1, Alice, platinum)
3	UPDATE	(c1, Alice, diamond)

Code.

CREATE OR REPLACE TABLE raw_customers (customer_id STRING, name STRING, tier STRING);
CREATE OR REPLACE STREAM raw_customers_stream ON TABLE raw_customers;

CREATE OR REPLACE TABLE dim_customers (
  customer_sk  NUMBER AUTOINCREMENT,
  customer_id  STRING,
  name         STRING,
  tier         STRING,
  valid_from   TIMESTAMP,
  valid_to     TIMESTAMP,
  is_current   BOOLEAN
);

CREATE OR REPLACE TASK scd2_customers_task
  WAREHOUSE = transform_wh
  SCHEDULE = '1 MINUTE'
  WHEN SYSTEM$STREAM_HAS_DATA('raw_customers_stream')
AS
BEGIN
  -- Step 1: close current rows for keys that just changed (insert after-image)
  MERGE INTO dim_customers t
  USING (
    SELECT DISTINCT customer_id
    FROM raw_customers_stream
    WHERE METADATA$ACTION = 'INSERT'
      AND METADATA$ISUPDATE = TRUE
  ) s
    ON t.customer_id = s.customer_id AND t.is_current = TRUE
  WHEN MATCHED THEN UPDATE SET t.valid_to = CURRENT_TIMESTAMP(), t.is_current = FALSE;

  -- Step 2: insert new "current" row for every after-image
  INSERT INTO dim_customers (customer_id, name, tier, valid_from, valid_to, is_current)
  SELECT customer_id, name, tier, CURRENT_TIMESTAMP(), NULL, TRUE
  FROM raw_customers_stream
  WHERE METADATA$ACTION = 'INSERT';

  -- Step 3: close current rows for hard deletes
  MERGE INTO dim_customers t
  USING (
    SELECT customer_id
    FROM raw_customers_stream
    WHERE METADATA$ACTION = 'DELETE' AND METADATA$ISUPDATE = FALSE
  ) s
    ON t.customer_id = s.customer_id AND t.is_current = TRUE
  WHEN MATCHED THEN UPDATE SET t.valid_to = CURRENT_TIMESTAMP(), t.is_current = FALSE;
END;

ALTER TASK scd2_customers_task RESUME;

Step-by-step explanation.

The task body wraps three statements in a BEGIN; ... END; block. All three run inside the implicit transaction of the task fire — atomic together, and the stream offset advances on the END's commit. This is the multi-statement transactional pattern.
Step 1 finds every customer_id whose update arrived (after-image rows: METADATA$ACTION = 'INSERT' AND METADATA$ISUPDATE = TRUE). For each, the matching current row in dim_customers is closed (valid_to = now, is_current = FALSE).
Step 2 inserts a new current row for every after-image — both new customers (isupdate = FALSE) and updated customers (isupdate = TRUE). The new row gets valid_from = now, valid_to = NULL, is_current = TRUE.
Step 3 handles standalone deletes: close the current row, do not insert a new one. The customer's history is preserved (rows with old valid_to remain).
After 3 rounds of updates on the same customer_id, dim_customers has 3 rows: the original (closed), the platinum version (closed), and the diamond version (current).

Output (DIM_CUSTOMERS after the trace).

customer_sk	customer_id	tier	valid_from	valid_to	is_current
1	c1	gold	12:01:00	12:02:00	FALSE
2	c1	platinum	12:02:00	12:03:00	FALSE
3	c1	diamond	12:03:00	NULL	TRUE

Rule of thumb. SCD-2 from a stream needs the explicit BEGIN; ... END; block to make the close-old + insert-new pair atomic. Without it, a crash between the close and the insert leaves the dimension in an inconsistent state. Always wrap multi-statement SCD-2 logic in a transaction.

Worked example — atomic multi-target write with BEGIN/COMMIT

Detailed explanation. A pipeline needs to write the same change set to two targets: a staging table (current state) and an audit table (history). Naively running two separate INSERTs leaves a window where the first succeeded and the second failed. Wrapping both in an explicit transaction makes them atomic together.

Question. Write a task that reads one stream and writes to two targets — stg_orders (MERGE) and audit_orders (INSERT) — atomically.

Input.

Stream	Target 1	Target 2
raw_orders_stream	stg_orders (MERGE)	audit_orders (INSERT)

Code.

CREATE OR REPLACE TASK multi_target_task
  WAREHOUSE = transform_wh
  SCHEDULE = '1 MINUTE'
  WHEN SYSTEM$STREAM_HAS_DATA('raw_orders_stream')
AS
BEGIN
  -- Cache the stream content into a TEMP table so we read it once
  CREATE OR REPLACE TEMPORARY TABLE _tmp_changes AS
  SELECT order_id, customer, amount, METADATA$ACTION AS action
  FROM raw_orders_stream;

  -- Target 1: MERGE into staging
  MERGE INTO stg_orders t
  USING _tmp_changes s ON t.order_id = s.order_id
  WHEN MATCHED AND s.action = 'INSERT' THEN UPDATE SET t.amount = s.amount
  WHEN MATCHED AND s.action = 'DELETE' THEN DELETE
  WHEN NOT MATCHED AND s.action = 'INSERT' THEN INSERT (order_id, customer, amount)
    VALUES (s.order_id, s.customer, s.amount);

  -- Target 2: INSERT into audit
  INSERT INTO audit_orders (order_id, action, audited_at)
  SELECT order_id, action, CURRENT_TIMESTAMP() FROM _tmp_changes;
END;

ALTER TASK multi_target_task RESUME;

Step-by-step explanation.

The BEGIN; ... END; block opens an explicit transaction that spans the temp table creation, the MERGE, and the INSERT. All three commit together or roll back together.
The temp table _tmp_changes is the senior idiom for multi-target writes. Reading the stream into the temp table is itself a DML — the offset advances on commit. The subsequent MERGE and INSERT read from the temp table, not from the stream, so they see consistent data.
Without the temp table, reading the stream twice (once in MERGE, once in INSERT) is allowed but Snowflake's semantics about "same stream read twice in the same transaction" are subtle: the second read sees the same offset, so both reads see the same data. But the staging trick makes the intent explicit and is the recommended pattern.
If any of the three statements fails, the whole BEGIN; END; rolls back. Stream offset stays put, neither target is updated, next fire re-processes the same change set.
The temp table is automatically scoped to the session — it goes away when the task fire ends. No cleanup needed.

Output (per-fire atomicity trace).

Fire	Stream content	Outcome	stg_orders	audit_orders
1	3 changes	both succeed	3 rows merged	3 rows inserted
2	5 changes	INSERT fails	unchanged	unchanged
3 (retry)	same 5 changes	both succeed	5 rows merged	5 rows inserted

Rule of thumb. Multi-target writes from one stream always need BEGIN; ... END; and ideally a _tmp_changes staging step. The pattern guarantees atomic visibility across targets and idempotent retries on failure.

Senior interview question on stream-driven SCD-2 design

A senior interviewer might ask: "Design an end-to-end CDC pipeline that maintains a slowly-changing dimension on a customer table. Walk me through stream choice, task topology, transactional boundaries, and how you handle a 4-hour outage of the consuming task."

Solution Using stream + transactional SCD-2 task + retention buffer

-- Source with 14 days of retention as outage buffer
ALTER TABLE raw_customers SET DATA_RETENTION_TIME_IN_DAYS = 14;

-- Standard stream (need full DML for SCD-2 close-old + insert-new)
CREATE OR REPLACE STREAM raw_customers_stream ON TABLE raw_customers;

-- SCD-2 task with explicit transaction
CREATE OR REPLACE TASK scd2_customers_task
  WAREHOUSE = transform_wh
  SCHEDULE = '1 MINUTE'
  WHEN SYSTEM$STREAM_HAS_DATA('raw_customers_stream')
AS
BEGIN
  -- close old current rows
  MERGE INTO dim_customers t
  USING (SELECT DISTINCT customer_id FROM raw_customers_stream
         WHERE METADATA$ACTION = 'INSERT' AND METADATA$ISUPDATE = TRUE) s
    ON t.customer_id = s.customer_id AND t.is_current = TRUE
  WHEN MATCHED THEN UPDATE SET t.valid_to = CURRENT_TIMESTAMP(), t.is_current = FALSE;

  -- insert new current rows
  INSERT INTO dim_customers (customer_id, name, tier, valid_from, is_current)
  SELECT customer_id, name, tier, CURRENT_TIMESTAMP(), TRUE
  FROM raw_customers_stream
  WHERE METADATA$ACTION = 'INSERT';

  -- close on hard delete
  MERGE INTO dim_customers t
  USING (SELECT customer_id FROM raw_customers_stream
         WHERE METADATA$ACTION = 'DELETE' AND METADATA$ISUPDATE = FALSE) s
    ON t.customer_id = s.customer_id AND t.is_current = TRUE
  WHEN MATCHED THEN UPDATE SET t.valid_to = CURRENT_TIMESTAMP(), t.is_current = FALSE;
END;

-- Lag monitoring task to alarm at 50% of retention
CREATE OR REPLACE TASK monitor_stream_lag_task
  WAREHOUSE = transform_wh
  SCHEDULE = '15 MINUTE'
AS
  INSERT INTO ops_alerts (alert_name, payload, alert_ts)
  SELECT 'stream_lag_warn', OBJECT_CONSTRUCT('stream', 'raw_customers_stream',
         'age_hours', DATEDIFF('hour', SYSTEM$STREAM_GET_TABLE_TIMESTAMP('raw_customers_stream')::TIMESTAMP, CURRENT_TIMESTAMP())),
         CURRENT_TIMESTAMP()
  WHERE DATEDIFF('hour', SYSTEM$STREAM_GET_TABLE_TIMESTAMP('raw_customers_stream')::TIMESTAMP, CURRENT_TIMESTAMP()) > 168;  -- 7 days = 50% of 14

Step-by-step trace.

Time	Event	Stream state	dim_customers state
t=0	task running normally	offset moving	1000 current rows
t=1h	task suspended (incident)	offset frozen, 60 pending	unchanged
t=5h	task resumed	5h × 1min × pending merge	300 changes applied atomically
t=5h+1	monitor checks lag	< 7d → no alert	—
t=14d (worst case)	stream stale check	> 14d would stale	retention buffer covers it

The 14-day retention window gives the team a 14 × 24 = 336 hour budget to fix any outage. The lag monitor alarms at 50% (7 days), giving a full week of slack before the stream goes stale. The transactional BEGIN; END; block makes every SCD-2 update atomic — no half-closed-half-inserted state ever exists.

Output:

Property	Senior design
Stream type	standard (full DML needed for SCD-2)
Retention window	14 days (5x worst-case outage)
Task body	BEGIN/END transaction wrapping close + insert
Lag monitor	alarms at 50% of retention
Recovery from 4h outage	resume task → 4h backlog processed in one fire

Why this works — concept by concept:

Stream flavour by SCD requirement — SCD-2 needs full DML propagation (UPDATE becomes close-old + insert-new; DELETE closes the current row). Only the standard stream gives you METADATA$ACTION and METADATA$ISUPDATE to discriminate. Append-only would silently miss tier changes.
Retention as outage budget — 14 days × 5x the worst expected outage = headroom for any realistic incident. Combined with a lag monitor at 50%, the stream is functionally immune to stale-stream incidents.
BEGIN/END transactional consumption — wrapping the close + insert + delete-close in one block makes them atomic with each other AND with the stream offset advance. No partial dimension state is ever visible.
Idempotent re-runs — if the task fails mid-block, everything rolls back including the offset. The next fire sees the same pending changes and re-runs cleanly. SCD-2 is naturally idempotent when wrapped in a transaction.
Cost — stream storage is O(change-volume × retention). Task compute is O(changed-rows) per fire. 14-day retention on a typical mutable-dimension is < ~$5/month. Lag monitor task is near-free (single SELECT every 15 minutes). The whole pipeline is dramatically cheaper than running Airflow + dbt-cloud + an external CDC connector.

SQL
Topic — joins
SCD and join dimension problems

Practice →

ETL Topic — ETL · medium ETL medium-difficulty drills

Practice →

5. Streams + Tasks vs Dynamic Tables vs dbt

Senior engineers pick `snowflake streams and tasks` vs Dynamic Tables vs dbt from a 5-question decision tree — never from "which is faster"

The mental model in one line: the choice between streams+tasks, Dynamic Tables, and dbt incremental models is a "who owns the imperative DAG, who owns the scheduler, who owns the SLA" decision — driven by 5 questions about freshness target, control needs, multi-target writes, ops capacity, and team skill profile, not by raw throughput numbers. Once you internalise the tree, every snowflake streams and tasks vs Dynamic Tables interview probe collapses to "what does your pipeline actually need?"

The 5 questions in order.

Q1 — Freshness target. Sub-minute? → streams+tasks (1-minute schedules). 1-15 minute target-lag? → Dynamic Tables. Daily / hourly batches? → dbt incremental or scheduled tasks.
Q2 — Control granularity. Need imperative control over the DAG (multi-statement transactions, custom error handling, SCD-2 logic)? → streams+tasks. Just need "keep this view fresh"? → Dynamic Tables.
Q3 — Multi-target writes. Stream to two targets atomically? → streams+tasks with BEGIN/END. Single derived table? → Dynamic Tables.
Q4 — Ops capacity. Already running Airflow / dbt-cloud / Prefect? → dbt incremental fits the team's muscle memory. No external orchestrator? → streams+tasks or Dynamic Tables (warehouse-native).
Q5 — Team skill profile. Strong SQL + Snowflake-native team? → streams+tasks or Dynamic Tables. dbt-fluent analytics engineer team? → dbt incremental, even at the cost of an external scheduler.

The three patterns side by side.

Streams + Tasks — imperative, fine-grained control, multi-target capable, full DML propagation via METADATA$ACTION, transactional consumption via BEGIN/END. Hardest to reason about (you own the DAG), but most powerful for complex CDC pipelines.
Dynamic Tables — declarative, target-lag-driven, automatic incremental refresh, no offset semantics to manage. Easiest to reason about ("this view stays within N minutes of source"), but limited to single-target derivations.
dbt incremental models — external scheduler-driven (dbt Cloud, Airflow), MERGE-based, manual incremental predicate, no native CDC (you write the watermark logic). Best fit for teams already invested in dbt; worst fit for sub-minute freshness.

Ops cost — the hidden line item.

Streams + Tasks ops. Stream lag monitoring, task graph state (TASK_HISTORY), retention-window alarms, conditional firing tuning. Roughly 1 SRE-day per pipeline per quarter once stable.
Dynamic Tables ops. Target-lag monitoring (Snowflake exposes refresh status), refresh cost monitoring. Roughly 0.5 SRE-day per pipeline per quarter — the runtime owns most of the orchestration.
dbt ops. dbt-cloud or Airflow run history, model dependency graph, incremental predicate validation, full-refresh storage. Highest ops surface but matches existing data team skills.

Cost comparison — back of envelope.

Streams + Tasks (serverless). Σ(actual-fire-seconds × serverless-rate). For 1-minute CDC with 50% conditional skips and 3 sec/fire, ~24 minutes/day of serverless compute = ~0.6 credits/day per pipeline.
Dynamic Tables. Refresh cost is proportional to source change rate + warehouse size. For 1-minute target-lag, comparable to streams+tasks; for longer target-lag, cheaper because batches are larger.
dbt incremental. Same MERGE cost as a manual task, plus dbt-cloud subscription (or self-hosted Airflow ops cost). Typically more expensive at scale.

Common interview probes on the choice.

"When does Dynamic Tables win?" — single-target declarative views with 1-15 minute target-lag, no multi-statement transactional logic needed.
"When do streams+tasks win?" — multi-target writes, SCD-2 with explicit BEGIN/END, custom error handling, sub-minute freshness.
"When does dbt win?" — established dbt team, no need for sub-minute freshness, want jinja templating and ref() lineage.
"Can you mix them?" — yes — streams+tasks for the raw-to-staging CDC layer, dbt incremental for the mart layer that builds on staging. Common pattern in 2026.

Worked example — Q1 freshness forces streams+tasks

Detailed explanation. A team needs to detect new fraud signals within 30 seconds of the source event arriving. Dynamic Tables' minimum target-lag is 1 minute; dbt's schedule cadence is typically 5+ minutes. Streams + tasks with a 1-minute schedule and stream-driven firing get you to sub-minute latency easily.

Question. Given a 30-second freshness requirement, walk through Q1 and show which pattern is feasible.

Input.

Pattern	Min freshness	Feasible?
streams + tasks (1 min schedule)	~60s (next fire)	yes
Dynamic Tables	60s target-lag	borderline
dbt incremental	5+ min cadence	no

Code.

-- Streams + tasks — 1-minute schedule, conditional firing
CREATE OR REPLACE STREAM fraud_signals_stream ON TABLE raw_events;

CREATE OR REPLACE TASK fraud_detect_task
  USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'XSMALL'   -- serverless
  SCHEDULE = '1 MINUTE'
  WHEN SYSTEM$STREAM_HAS_DATA('fraud_signals_stream')
AS
  INSERT INTO fraud_alerts (event_id, risk_score, detected_at)
  SELECT event_id, score_risk(features), CURRENT_TIMESTAMP()
  FROM fraud_signals_stream
  WHERE METADATA$ACTION = 'INSERT' AND score_risk(features) > 0.85;

-- Dynamic Tables alternative — 60s target-lag
-- CREATE OR REPLACE DYNAMIC TABLE fraud_alerts_dt
--   TARGET_LAG = '60 seconds'
--   WAREHOUSE = transform_wh
-- AS
--   SELECT event_id, score_risk(features), CURRENT_TIMESTAMP() AS detected_at
--   FROM raw_events
--   WHERE score_risk(features) > 0.85;

Step-by-step explanation.

The streams+tasks pattern fires every minute (or sooner if the stream has data). End-to-end latency from source insert to alert = ~30s worst case (event lands at 12:00:30, task fires at 12:01:00, alert at 12:01:03).
Dynamic Tables with 60s target-lag gives similar freshness in theory, but the refresh planner can run later than the target if change volume spikes — observed lag is 60-90s in practice.
dbt incremental models typically run on a 5-minute cadence at minimum (lower bound is the dbt-cloud scheduler interval, which is 5 min on most plans). End-to-end latency = 5-10 minutes.
For 30-second freshness, streams+tasks with a 1 MINUTE schedule is the only pattern that reliably meets the SLA. Dynamic Tables can be tuned to 1 minute target-lag but with less predictability.
The senior answer to "why streams+tasks?" is "deterministic freshness floor at the 1-minute schedule mark, with the conditional firing keeping cost down on idle minutes."

Output (latency comparison).

Pattern	Median latency	p99 latency	Cost on 100% idle
streams + tasks (1 min + conditional)	~30s	~60s	~0 (conditional skips)
Dynamic Tables (60s target-lag)	~60s	~90s	refresh cost on every cycle
dbt incremental (5 min cadence)	~150s	~300s	dbt-cloud run cost

Rule of thumb. For sub-minute freshness on Snowflake, streams + tasks with 1 MINUTE schedule and WHEN SYSTEM$STREAM_HAS_DATA is the answer. For 1-15 minute freshness, Dynamic Tables is usually simpler. For > 15 minute freshness, the choice comes down to existing tooling investment.

Worked example — Q3 multi-target write forces streams+tasks

Detailed explanation. A pipeline reads CDC from a transactions table and writes to two targets: stg_transactions (current state) and fraud_audit (immutable history). Dynamic Tables is single-target by definition; streams+tasks with BEGIN/END is the natural fit.

Question. Given a 1-stream-to-2-targets requirement, show why streams+tasks wins Q3 and Dynamic Tables cannot.

Input.

Source	Target 1	Target 2
transactions	stg_transactions	fraud_audit

Code.

-- Streams + tasks — multi-target with explicit transaction
CREATE OR REPLACE STREAM transactions_stream ON TABLE transactions;

CREATE OR REPLACE TASK multi_target_txn_task
  WAREHOUSE = transform_wh
  SCHEDULE = '1 MINUTE'
  WHEN SYSTEM$STREAM_HAS_DATA('transactions_stream')
AS
BEGIN
  CREATE OR REPLACE TEMPORARY TABLE _tmp_txns AS
  SELECT txn_id, account_id, amount, METADATA$ACTION AS action FROM transactions_stream;

  -- Target 1: current state
  MERGE INTO stg_transactions t USING _tmp_txns s ON t.txn_id = s.txn_id
  WHEN MATCHED AND s.action = 'INSERT' THEN UPDATE SET t.amount = s.amount
  WHEN MATCHED AND s.action = 'DELETE' THEN DELETE
  WHEN NOT MATCHED AND s.action = 'INSERT' THEN INSERT (txn_id, account_id, amount)
    VALUES (s.txn_id, s.account_id, s.amount);

  -- Target 2: immutable audit history
  INSERT INTO fraud_audit (txn_id, action, amount, audited_at)
  SELECT txn_id, action, amount, CURRENT_TIMESTAMP() FROM _tmp_txns;
END;

-- Dynamic Tables CANNOT do this — single target only
-- You would need TWO Dynamic Tables, each independently consuming the source,
-- and Snowflake offers no atomic-across-tables guarantee.

Step-by-step explanation.

The streams+tasks pattern wraps both writes in BEGIN/END. Atomicity is guaranteed: both targets update together or neither does.
The temp table _tmp_txns is the senior idiom — read the stream once, then write to two targets from the temp. Stream offset advances when the BEGIN/END block commits.
Dynamic Tables are inherently single-target — each Dynamic Table is one declarative view backed by one underlying derived table. You cannot express "this change set lands in both T1 and T2 atomically" in Dynamic Tables alone.
The "use two Dynamic Tables" workaround sacrifices atomicity: each refreshes independently, so there's a window where T1 has the change but T2 does not. For the fraud-audit pattern, this is a hard correctness violation.
Streams+tasks is the only Snowflake-native pattern that gives you cross-target atomicity for stream-driven writes.

Output (atomicity comparison).

Pattern	Cross-target atomicity	Idempotent retry
streams + tasks with BEGIN/END	yes	yes
2 × Dynamic Tables	no (independent refresh)	independent
2 × dbt incremental models	depends on dbt run order	depends

Rule of thumb. Whenever a pipeline needs to write the same change set to two or more targets atomically, streams+tasks is the answer. Dynamic Tables and dbt incremental models are single-target by design.

Worked example — Q4 ops capacity forces Dynamic Tables

Detailed explanation. A small team without an SRE function is building their first incremental pipeline. The streams+tasks pattern requires lag monitoring, retention-window alarms, conditional-firing tuning, and DML idempotency design. Dynamic Tables collapses all of that into a single TARGET_LAG = '5 minute' clause.

Question. Given a small-team-no-SRE constraint, walk through Q4 and show why Dynamic Tables wins.

Input.

Constraint	Value
Data engineers	2
SRE / platform engineers	0
Pipeline freshness target	5 minute
Pattern complexity tolerance	low

Code.

-- Dynamic Table — declarative incremental view, target-lag = 5 minutes
CREATE OR REPLACE DYNAMIC TABLE customer_360
  TARGET_LAG = '5 minute'
  WAREHOUSE = transform_wh
AS
  SELECT
    c.customer_id,
    c.name,
    c.tier,
    COUNT(o.order_id)            AS lifetime_orders,
    SUM(o.amount)                AS lifetime_revenue,
    MAX(o.order_ts)              AS last_order_ts
  FROM customers c
  LEFT JOIN orders o ON c.customer_id = o.customer_id
  GROUP BY 1, 2, 3;

-- Equivalent streams+tasks setup would need:
-- 1. CREATE STREAM customers_stream
-- 2. CREATE STREAM orders_stream
-- 3. CREATE TASK refresh_customer_360 ... MERGE INTO customer_360 ...
-- 4. Conditional firing on both streams
-- 5. Retention monitoring on both streams
-- 6. Idempotency design for the MERGE
-- ~50 lines of SQL + ~5 SRE-days of ongoing tuning.

Step-by-step explanation.

CREATE DYNAMIC TABLE ... TARGET_LAG = '5 minute' declares a target-lag SLA. Snowflake's runtime computes the refresh schedule automatically — fires often enough to stay within 5 minutes of source.
No streams to manage. No offset semantics. No conditional firing. No stale-stream risk. Snowflake's refresh planner owns the orchestration.
The streams+tasks equivalent would require ~50 lines of SQL across 6 objects, plus ongoing operational care for each stream. The Dynamic Table is 8 lines.
The trade-off: less control. You cannot do multi-target writes, custom error handling, or fine-grained transactional logic. For a "keep this view fresh" use case, none of that matters.
For the small team, Dynamic Tables is the senior recommendation: same freshness, ~1/10 the ops surface, no SRE skills required.

Output (effort comparison).

Aspect	streams + tasks	Dynamic Tables
Initial SQL	~50 LOC	~8 LOC
Objects to manage	2 streams + 1 task + monitoring	1 dynamic table
Ongoing ops	lag, stale, conditional tuning	refresh-status check
SRE skill required	medium	low

Rule of thumb. For "keep a single derived view fresh" use cases on small teams, Dynamic Tables is the senior recommendation. Switch to streams+tasks only when you need multi-target writes, complex transactional logic, or sub-minute freshness.

Senior interview question on pipeline pattern selection in 2026

A senior interviewer might frame this as: "You join a Snowflake-only team. They want a new real-time customer-360 pipeline plus an SCD-2 dimension plus a daily reporting cube. Walk me through how you'd pick between streams+tasks, Dynamic Tables, and dbt for each layer."

Solution Using the 5-question framework + per-layer pattern selection

Customer-360 layer (real-time view, single target)
--------------------------------------------------
Q1 freshness  → 5 min target-lag is fine
Q2 control    → just keep the view fresh
Q3 targets    → single derived view
Q4 ops        → low ops surface preferred
Q5 skill      → SQL-fluent team
→ PICK Dynamic Tables

SCD-2 dim_customers (mutable dimension, transactional)
------------------------------------------------------
Q1 freshness  → 1 minute
Q2 control    → close-old + insert-new atomic per change
Q3 targets    → single target but multi-statement
Q4 ops        → SRE capacity acceptable
Q5 skill      → senior SQL team
→ PICK streams + tasks (BEGIN/END SCD-2 pattern)

Daily reporting cube (batch aggregations)
-----------------------------------------
Q1 freshness  → daily (06:00 UTC)
Q2 control    → many models with ref() lineage
Q3 targets    → single output per model
Q4 ops        → dbt-cloud already in place
Q5 skill      → dbt-fluent analytics engineer team
→ PICK dbt incremental models

Step-by-step trace.

Layer	Freshness	Pattern	Why
customer_360 view	5 min	Dynamic Tables	declarative single-target
dim_customers (SCD-2)	1 min	streams + tasks	atomic multi-statement
daily_reporting_cube	daily 06:00	dbt incremental	team skill + tooling fit

Three layers, three different patterns. The 5-question tree picks each pattern from the layer's actual requirements, not from a religious "use Pattern X for everything" stance. Senior teams routinely mix patterns within a single warehouse.

Output:

Verdict	Reasoning
customer_360 → Dynamic Tables	low ops, declarative, single-target
dim_customers → streams + tasks	SCD-2 needs atomic close + insert
daily_cube → dbt incremental	matches existing team workflow

Why this works — concept by concept:

5 questions per layer — applied per layer, not per pipeline. A single warehouse can host all three patterns; the 5-question tree picks the right one for each layer.
Pattern-by-control-requirement — streams + tasks for imperative control, Dynamic Tables for declarative simplicity, dbt for ref()-driven model lineage. None of them is "best"; each is best for a specific control requirement.
Ops surface is the hidden cost — streams + tasks has the highest ops surface (lag, stale, conditional tuning); Dynamic Tables has the lowest (target-lag SLA); dbt has medium (model graph + scheduler). Pick by what the team can actually operate.
Mix freely — the same team using all three patterns is a 2026 senior signal. Each layer is sized to its actual freshness, control, and team-skill needs.
Cost — pattern choice is a multi-year commitment. The migration cost between patterns is high (re-implement the DAG, re-validate the data, re-train the team). Get Q1-Q5 right per layer the first time.

ETL
Topic — ETL
Pipeline pattern selection problems

Practice →

SQL
Topic — SQL
Snowflake SQL design drills

Practice →

Cheat sheet — Streams + Tasks recipes

Minimal stream + task. CREATE STREAM s ON TABLE t; then CREATE TASK t1 WAREHOUSE = wh SCHEDULE = '1 MINUTE' WHEN SYSTEM$STREAM_HAS_DATA('s') AS MERGE INTO target USING s ...; and ALTER TASK t1 RESUME;. Five SQL statements for end-to-end CDC.
Stream flavour matrix. Standard for mutable sources (full DML propagation); APPEND_ONLY = TRUE for write-once events (2-5x cheaper); INSERT_ONLY = TRUE for external/Iceberg tables. Pick by source mutability contract, not downstream usage.
Offset rule. Offset advances only when the stream is read inside a DML that commits. Plain SELECT * FROM stream is a peek. CREATE TEMP TABLE AS SELECT * FROM stream advances the offset (often surprising).
Stale-stream prevention. ALTER TABLE src SET DATA_RETENTION_TIME_IN_DAYS = 14; gives 14 days of outage budget. Alarm at 50% (7 days) with a SYSTEM$STREAM_GET_TABLE_TIMESTAMP lag monitor task.
Conditional firing. Always wrap stream-driven tasks in WHEN SYSTEM$STREAM_HAS_DATA('s'). A 1-minute task without the conditional burns credits on every idle minute.
Task graph (DAG). Root task has SCHEDULE; children declare AFTER parent_name (and may declare multiple parents for fan-in). Resume from leaves, suspend from root.
Serverless vs warehouse. Serverless wins for isolated tasks under 10 min/fire and < 30% utilisation. Warehouse-bound wins when 10+ tasks share the same warehouse and overall utilisation > 50%.
Atomic multi-target. Wrap multi-statement task bodies in BEGIN; ... END;. Use a _tmp_changes temp table to read the stream once and write to multiple targets atomically.
Standard stream DML propagation. MERGE WHEN MATCHED AND s.action = 'DELETE' AND s.isupdate = FALSE THEN DELETE handles standalone deletes; WHEN MATCHED AND s.action = 'INSERT' handles updates and upserts; WHEN NOT MATCHED AND s.action = 'INSERT' handles fresh inserts.
SCD-2 from a stream. BEGIN → close is_current = TRUE rows for keys with METADATA$ISUPDATE = TRUE → INSERT new rows for every METADATA$ACTION = 'INSERT' → close current rows for METADATA$ACTION = 'DELETE' AND METADATA$ISUPDATE = FALSE → END.
Multi-consumer fan-out. Always one stream per consumer. raw_X_stream_consumerA, raw_X_stream_consumerB. Sharing a stream causes silent data loss for whichever consumer runs second.
Cron vs interval schedule. Cron form (USING CRON 0 6 * * * UTC) for time-of-day; interval form ('1 MINUTE') for high-frequency CDC. Cron is timezone-aware; interval is wall-clock relative to RESUME time.
Observability. TASK_HISTORY() for per-fire state; COMPLETE_TASK_GRAPHS() for DAG run state; SYSTEM$STREAM_GET_TABLE_TIMESTAMP for stream lag; ERROR_INTEGRATION for webhook alerts.
Pattern choice. Sub-minute + multi-target + custom logic → streams + tasks. 1-15 min freshness + single derived view → Dynamic Tables. Daily/hourly batch + existing dbt team → dbt incremental.

Frequently asked questions

What are Snowflake streams and tasks?

A Snowflake stream is a row-level change-data-capture marker on a table — it tracks INSERT / UPDATE / DELETE rows since the stream's offset, and exposes the change set with metadata columns (METADATA$ACTION, METADATA$ISUPDATE, METADATA$ROW_ID). A Snowflake task is a server-side scheduled SQL statement — you give it a SCHEDULE (cron or interval) and a body, optionally chain children with AFTER, optionally gate firing with WHEN SYSTEM$STREAM_HAS_DATA. Wired together, snowflake streams and tasks give the warehouse a native CDC + scheduler pair — incremental ELT without Airflow, dbt-cloud, or any external orchestrator. The key semantic to remember: stream offsets advance only when the stream is consumed inside a DML that commits successfully.

Append-only vs standard stream — when do I pick which?

Pick a standard stream when the source table can have UPDATEs or DELETEs — mutable dimension tables (customers, products), fact tables that allow corrections, anything with an OLTP-style mutation pattern. The standard stream gives you METADATA$ACTION ('INSERT' / 'DELETE') and METADATA$ISUPDATE (TRUE for the two halves of an update), so the downstream MERGE can propagate the full DML set. Pick an append-only stream (declare with APPEND_ONLY = TRUE) only when the source contract is "no updates, no deletes, ever" — clickstream, IoT, application logs, audit tables. The append-only stream skips before-image bookkeeping for 2-5x lower overhead but silently misses any update or delete on the source. Pick insert-only for streams on external tables / Iceberg — those storage targets only support inserts at the format level, so insert-only is the only legal flavour.

How do I prevent a stale stream?

Set DATA_RETENTION_TIME_IN_DAYS on the source table (or directly on the stream via ALTER STREAM ...) to at least 5x your worst expected outage window. Default is 1 day on Standard edition; for serious CDC pipelines, 14 days is the senior default (the maximum on Standard; up to 90 days on Enterprise+). Combine with a lag monitor task: a 15-minute-schedule task that reads SYSTEM$STREAM_GET_TABLE_TIMESTAMP('stream_name'), computes the offset age, and inserts to an ops_alerts table when the age exceeds 50% of the retention window. The retention window is your outage budget; the alarm gives you half of it as warning slack. Structurally, ensure every stream has a consuming task with WHEN SYSTEM$STREAM_HAS_DATA and a 1-minute schedule — under normal operation, the offset never lags more than a few minutes, and the 14-day buffer is pure insurance.

Streams + Tasks vs Dynamic Tables — which?

Pick snowflake streams and tasks when you need imperative control: multi-target writes wrapped in BEGIN/END, SCD-2 dimensions with explicit close-old + insert-new logic, custom error handling, sub-minute freshness, or per-statement transactional semantics. Pick Dynamic Tables when you need declarative simplicity: a single derived view, 1-15 minute target-lag SLA, no multi-statement logic, and you want Snowflake's refresh planner to own the orchestration. The 2026 senior pattern is to mix freely — Dynamic Tables for "keep this single view fresh" layers (customer_360, latest_state aggregations) and streams + tasks for "atomic CDC into multiple targets" layers (SCD-2 dimensions, audit fan-outs). Dynamic Tables is roughly 1/10 the ops surface of streams + tasks; streams + tasks is the only pattern that gives you cross-target atomicity. Choose per layer, not per warehouse.

Serverless tasks vs warehouse tasks — cost model?

A warehouse-bound task declares WAREHOUSE = some_wh and pays for the warehouse's full uptime, including the 60-second minimum auto-suspend window after each fire. For a 1-minute-schedule task that takes 3 seconds per fire, you pay ~60 seconds × 1440 fires/day = 24 hours of warehouse time per day — even though actual compute is only ~72 minutes. A serverless task (omit WAREHOUSE, declare USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'XSMALL') charges only for actual execution at the serverless rate (~1.5x the warehouse rate). For the same 1440 × 3-second fires, total billed compute is ~72 minutes × 1.5 = 108 warehouse-minute-equivalents. Break-even: serverless wins for isolated tasks under 10 minutes per fire and < 30% warehouse utilisation; warehouse-bound wins when ≥ 10 tasks share the warehouse and amortise the idle. Default new isolated CDC tasks to serverless; move to warehouse-bound only when you have heavy sharing.

Can I run dbt models on top of a stream?

Yes, but the canonical pattern in 2026 is to use streams + tasks for the raw-to-staging CDC layer and dbt incremental models for the mart layer that builds on staging. The dbt-on-stream pattern works because dbt can read from anything Snowflake exposes — the stream is just a table in the SQL sense — but you lose the offset-advance-on-commit semantic if dbt's run is separate from the consuming DML. Specifically: if dbt does SELECT * FROM stream INTO @dbt_temp and then writes to the target, the offset advanced when the temp table was created (peek-into-temp anti-pattern). The senior pattern is to have a streams + tasks pipeline land the staging tables, and dbt incremental models read from the staging tables (not from streams) — this gives dbt's ref() graph the staging table as a stable source, and the CDC layer keeps its native Snowflake transactional semantics. Mixing the two layers is the 2026 mainstream choice for warehouse-native pipelines.

Practice on PipeCode

Drill the SQL practice library → for the MERGE, window-function, and CDC family of probes.
Rehearse on joins drills → when the interviewer wants stream-to-dimension join depth.
Sharpen SQL joins drills → for the keyed MERGE patterns at the heart of every CDC pipeline.
Stack the ETL practice library → for the pipeline-design and task-graph probes.
Layer the medium ETL drills → for senior-level pattern selection.

Lock in Streams + Tasks muscle memory

Docs explain streams. PipeCode drills explain the decision — when stream + task beats dynamic tables, when a stale stream hides a bug, when serverless tasks pay back. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face.

Practice SQL problems →
Practice ETL problems →

1. Why Streams + Tasks are the senior Snowflake interview classic

snowflake streams and tasks are the warehouse's native CDC + scheduler — the only Snowflake-only pipeline that needs no Airflow

Worked example — minimal stream + task end-to-end

Worked example — the offset advances only inside a DML

Worked example — the stale stream trap

Senior interview question on Snowflake streams + tasks

Solution Using the cursor-plus-scheduler mental model

2. Streams — three flavours and how offsets advance

snowflake stream ships in three flavours — standard, append-only, insert-only — each with a different change set and overhead

Worked example — standard stream traces every DML

Worked example — append-only stream skips update/delete

Worked example — multi-consumer fan-out with separate streams

Senior interview question on choosing a stream flavour

Solution Using flavour-by-source-mutability mapping

3. Tasks — schedule, DAG, conditional execution

snowflake task is the warehouse's native scheduler — standalone, task graph, or serverless, with optional stream-driven conditions

Worked example — standalone task on a cron schedule

Worked example — task graph with AFTER chaining

Worked example — serverless task vs warehouse task

Senior interview question on task graph design

Solution Using a fan-in task graph with per-source root tasks

4. Composing CDC pipelines — streams into tasks

The canonical snowflake change data capture pattern wires a stream onto raw, a task that MERGEs into staging, and a downstream task graph for marts

Worked example — canonical CDC merge from raw to staging

Worked example — SCD-2 dimension from a CDC stream

Worked example — atomic multi-target write with BEGIN/COMMIT

Senior interview question on stream-driven SCD-2 design

Solution Using stream + transactional SCD-2 task + retention buffer

5. Streams + Tasks vs Dynamic Tables vs dbt

Senior engineers pick snowflake streams and tasks vs Dynamic Tables vs dbt from a 5-question decision tree — never from "which is faster"

Worked example — Q1 freshness forces streams+tasks

Worked example — Q3 multi-target write forces streams+tasks

Worked example — Q4 ops capacity forces Dynamic Tables

Senior interview question on pipeline pattern selection in 2026

Solution Using the 5-question framework + per-layer pattern selection

Cheat sheet — Streams + Tasks recipes

Frequently asked questions

What are Snowflake streams and tasks?

Append-only vs standard stream — when do I pick which?

How do I prevent a stale stream?

Streams + Tasks vs Dynamic Tables — which?

Serverless tasks vs warehouse tasks — cost model?

Can I run dbt models on top of a stream?

Practice on PipeCode

Lock in Streams + Tasks muscle memory

`snowflake streams and tasks` are the warehouse's native CDC + scheduler — the only Snowflake-only pipeline that needs no Airflow

`snowflake stream` ships in three flavours — standard, append-only, insert-only — each with a different change set and overhead

`snowflake task` is the warehouse's native scheduler — standalone, task graph, or serverless, with optional stream-driven conditions

The canonical `snowflake change data capture` pattern wires a stream onto raw, a task that MERGEs into staging, and a downstream task graph for marts

Senior engineers pick `snowflake streams and tasks` vs Dynamic Tables vs dbt from a 5-question decision tree — never from "which is faster"