Snowflake Interview Questions and Answers Part 5 - Streams, Tasks and Dynamic Tables

Snowflake Interview Q&A · Part 5 of 10

In this post we are going to cover streams, tasks and dynamic tables in Snowflake: how a stream tracks changes, why a stream goes stale, tasks and task graphs, monitoring and alerts, dynamic tables with target lag, and refresh modes. These are 7 real Snowflake interview questions, asked the way they come up on a real project, and the last one is a scenario where the silver and gold layers must refresh every ten minutes and we decide between streams and tasks or dynamic tables.

Once data lands in a raw table, something has to move the changes into silver and gold. In Snowflake there are two ways to do it. You can build it by hand, with a stream that tracks the changes and a task that runs the MERGE. Or you can declare it, with a dynamic table that Snowflake keeps fresh for you. Interviewers ask about it because the mistakes are expensive and quiet: a stream that goes stale, a task that fails at 3 AM with nobody noticing, or a dynamic table that does a full refresh every time.

Part 5 of the Real Time Snowflake Interview Questions and Answers series on TechBrothersIT.

On this page

1. The short answer
2. Question 1: You need only the changed rows from a raw table each hour. What is a stream, and how does it track changes?
3. Question 2: A stream that nobody read for a month now shows an error. What is stream staleness, and how do you avoid it?
4. Question 3: The transformation must run every fifteen minutes, but only when new data arrives. How do tasks and task graphs work?
5. Question 4: A task failed at 3 AM and no one noticed. How do you monitor tasks and get alerted?
6. Question 5: Your team wants to stop writing streams and tasks by hand. What are dynamic tables, and how does target lag work?
7. Question 6: A dynamic table does a full refresh every time and costs too much. Why, and how do you make it incremental?
8. Scenario 7: The silver and gold layers must refresh every ten minutes from raw data. Do you build streams and tasks or dynamic tables, and why?

The short answer

Say this first

A stream is an offset on a table that returns the net changes since it was last consumed, and it goes stale if nobody reads it within the retention. Tasks run SQL on a schedule or a trigger, and task graphs chain them. Dynamic tables replace hand-written pipelines with a query and a target lag; the refresh mode decides the cost.

Keep that in mind. Every question below is one piece of it.

Question 1: You need only the changed rows from a raw table each hour. What is a stream, and how does it track changes?

A stream is change tracking on a table: a bookmark, an offset on the table's versions. It stores no data. It just remembers where you stopped reading.

Let's create one and query it:

orders_stream.sql

CREATE STREAM raw.orders_stream ON TABLE raw.orders;

SELECT order_id, status,
       METADATA$ACTION, METADATA$ISUPDATE
FROM raw.orders_stream;

When you query it, you get the net change since that offset, plus metadata columns. METADATA$ACTION says insert or delete, and METADATA$ISUPDATE flags updates. An update shows up as a delete and insert pair, with METADATA$ISUPDATE set to true:

raw.orders_stream4 rows

order_id	status	METADATA$ACTION	METADATA$ISUPDATE
1002	Open	DELETE	TRUE
1002	Shipped	INSERT	TRUE
1005	Open	INSERT	FALSE
1003	Shipped	DELETE	FALSE

Here order 1002 changed from Open to Shipped, so it appears twice as an update pair. Order 1005 is a new insert, and order 1003 was deleted.

There are three types of streams:

• Standard: tracks inserts, updates and deletes.
• Append-only: tracks inserts only, and is cheaper for raw tables that only get new rows.
• Insert-only: for external tables and externally managed Iceberg tables.

Now the key point. Just selecting from a stream doesn't move the offset. A DML statement that reads it, like a MERGE into silver, consumes all the change data and moves the offset forward.

The mistake I see

Two jobs reading one stream. The first job consumes it, and the second one sees nothing. Give each consumer its own stream.

Question 2: A stream that nobody read for a month now shows an error. What is stream staleness, and how do you avoid it?

A stream reads its changes from the source table's Time Travel history. If the offset falls outside that retention window, the history it needs is gone, and the stream becomes stale.

Snowflake helps a little. If the table's retention is under 14 days, it extends it for the stream, up to MAX_DATA_EXTENSION_TIME_IN_DAYS, which is 14 by default. That extended retention costs storage on the source table. But after a month unread, that has run out too.

You can see it coming. SHOW STREAMS and DESCRIBE STREAM have a stale_after column: the time when the stream goes stale if nobody reads it. Let's check it, and recreate the stream if it's already stale:

stream_health.sql

SHOW STREAMS LIKE 'ORDERS_STREAM' IN SCHEMA raw;
DESCRIBE STREAM raw.orders_stream;
-- check the stale_after column

-- stale: recreate it, then reconcile what you missed
CREATE OR REPLACE STREAM raw.orders_stream
  ON TABLE raw.orders;

No repair

A stale stream can't be repaired. You recreate it, and then reload or reconcile the changes you missed from the source.

So how do we avoid it in the first place?

• Consume on a schedule: read every stream regularly, even when there is little data.
• Alert on stale_after: so you hear about it before it happens.
• Triggered tasks: if one hasn't run for twelve hours, Snowflake runs a health check so the stream doesn't go stale.

Question 3: The transformation must run every fifteen minutes, but only when new data arrives. How do tasks and task graphs work?

A task runs one SQL statement or procedure call on a schedule. Here, we schedule it every fifteen minutes, with a WHEN condition on SYSTEM$STREAM_HAS_DATA. If the stream is empty, the run is skipped.

load_orders_task.sql

CREATE TASK silver.load_orders
  WAREHOUSE = transform_wh
  SCHEDULE = '15 MINUTE'
  WHEN SYSTEM$STREAM_HAS_DATA('raw.orders_stream')
AS
  CALL silver.merge_orders();

ALTER TASK silver.load_orders RESUME;

A task runs on a warehouse you name, or it's serverless if you leave the warehouse out, and Snowflake manages the compute. A new task starts suspended, so you have to resume it.

Or drop the schedule completely. A triggered task has only the WHEN condition, and runs when the stream gets data, at most every thirty seconds by default:

triggered_task.sql

CREATE TASK silver.load_orders_trg
  WAREHOUSE = transform_wh
  WHEN SYSTEM$STREAM_HAS_DATA('raw.orders_stream')
AS CALL silver.merge_orders();

For many steps, build a task graph. The root has the schedule, children use AFTER, and a child with two parents waits for both. A finalizer task always runs at the end, for cleanup or notifications. Let's look at one where build_gold waits for load_a and load_b:

task_graph.sql

CREATE TASK root WAREHOUSE = transform_wh
  SCHEDULE = '15 MINUTE' AS CALL ops.start_batch();
CREATE TASK load_a AFTER root AS CALL ops.load('a');
CREATE TASK load_b AFTER root AS CALL ops.load('b');
CREATE TASK build_gold AFTER load_a, load_b
  AS CALL ops.build_gold();
CREATE TASK cleanup FINALIZE = root
  AS CALL ops.notify_and_cleanup();

Tip

To change a graph, suspend the root first. The overlap policy on the root defaults to NO_OVERLAP, so if a run is still going at the next scheduled time, that tick is skipped.

Question 4: A task failed at 3 AM and no one noticed. How do you monitor tasks and get alerted?

Tasks fail quietly, so you push failures out instead of waiting for someone to look.

The TASK_HISTORY table function shows every run, with its state and the error message:

failed_tasks.sql

SELECT name, state, error_message, scheduled_time
FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY())
WHERE state LIKE 'FAILED%'
ORDER BY scheduled_time DESC;

TASK_HISTORY1 row

name	state	scheduled_time
BUILD_GOLD	FAILED	2026-10-22 03:00

There is our 3 AM failure: BUILD_GOLD failed. But nobody looks at it at 3 AM.

So first, error notifications. Set the error integration on the root task, pointing to a notification integration. It pushes to Amazon SNS, Azure Event Grid or Google Pub/Sub, on your account's own cloud, not to email or webhooks.

task_alerts.sql

-- notification integration: SNS, Event Grid, Pub/Sub
ALTER TASK root SUSPEND;
ALTER TASK root SET ERROR_INTEGRATION = task_errors;
ALTER TASK root RESUME;

For email, use a finalizer task that sends the message at the end of the graph run, or a task alert built on event table logs.

Then make failures recoverable. TASK_AUTO_RETRY_ATTEMPTS retries the graph, and it's off by default. SUSPEND_TASK_AFTER_NUM_FAILURES stops a task that keeps failing. And EXECUTE TASK ... RETRY LAST reruns the graph from the failure.

task_recovery.sql

ALTER TASK root SUSPEND;
ALTER TASK root SET
  TASK_AUTO_RETRY_ATTEMPTS = 2
  SUSPEND_TASK_AFTER_NUM_FAILURES = 3;
ALTER TASK root RESUME;

-- rerun the graph from the failed task
EXECUTE TASK root RETRY LAST;

The mistake I see

Alerts only on failure. If the root task is suspended, nothing fails, and nothing runs either. So also alert when a task hasn't run on time.

Question 5: Your team wants to stop writing streams and tasks by hand. What are dynamic tables, and how does target lag work?

A dynamic table is declarative. You write the SELECT, set a target lag and a warehouse, and Snowflake keeps the result fresh. No stream, no task, and no MERGE to write.

Target lag is how far behind the source the data may get. The minimum is sixty seconds, and it's a target, not a guarantee, so the actual lag can be higher.

Snowflake works out the pipeline from the queries and refreshes in dependency order, with consistent snapshots downstream. For an intermediate table, set the target lag to DOWNSTREAM: it refreshes only when a dependent table needs it.

Let's build two layers. Silver cleans raw.orders with a downstream lag, and gold aggregates it with a ten-minute lag:

dynamic_tables.sql

CREATE DYNAMIC TABLE silver.orders_clean
  TARGET_LAG = DOWNSTREAM
  WAREHOUSE = transform_wh
AS SELECT order_id, customer_id, order_date, amount
   FROM raw.orders
   WHERE status != 'CANCELLED';

CREATE DYNAMIC TABLE gold.daily_sales
  TARGET_LAG = '10 minutes'
  WAREHOUSE = transform_wh
AS SELECT order_date, SUM(amount) AS revenue
   FROM silver.orders_clean GROUP BY order_date;

Now the cost. Refreshes run on the warehouse you name, plus cloud services for scheduling, and a shorter lag means more scheduling overhead. So pick the lag the business really needs, not the smallest number.

Know the limits too:

• No MERGE into it: a dynamic table is read-only.
• No stored procedures or Snowpark: in the definition.

For a manual refresh, use ALTER DYNAMIC TABLE ... REFRESH.

Question 6: A dynamic table does a full refresh every time and costs too much. Why, and how do you make it incremental?

Check the refresh mode first. SHOW DYNAMIC TABLES gives the refresh_mode, and the refresh_mode_reason column explains why incremental wasn't possible.

check_mode.sql

SHOW DYNAMIC TABLES LIKE 'DAILY%' IN SCHEMA gold;
-- read refresh_mode and refresh_mode_reason

SHOW DYNAMIC TABLES1 row

name	refresh_mode
DAILY_SALES	FULL

There it is: DAILY_SALES refreshes in FULL mode. Let's see how the modes work:

Refresh mode	What it does
AUTO (default)	decided once, at creation: INCREMENTAL or FULL, then fixed
INCREMENTAL	processes only the changes; creation fails if it cannot
FULL	recomputes everything, every refresh

The default mode, AUTO, is decided once, when the table is created. If Snowflake can't refresh the query incrementally, it picks FULL, and that choice is fixed.

Typical causes are constructs incremental refresh doesn't support, like INTERSECT and EXCEPT, or exact percentiles like PERCENTILE_CONT. Support keeps growing, so read the reason instead of memorizing a list.

To fix it, rewrite the query without those constructs, often by splitting it into two dynamic tables, and set the refresh mode to INCREMENTAL on purpose. Then creation fails, instead of quietly falling back to full.

daily_sales.sql

CREATE OR REPLACE DYNAMIC TABLE gold.daily_sales
  TARGET_LAG = '10 minutes' WAREHOUSE = transform_wh
  REFRESH_MODE = INCREMENTAL
AS SELECT order_date, SUM(amount) AS revenue
   FROM silver.orders_clean GROUP BY order_date;

Remember

The refresh mode can't be changed with ALTER. You recreate the table with CREATE OR REPLACE or CREATE OR ALTER, and some changes reinitialize it. Then check SHOW DYNAMIC TABLES again to confirm the mode.

Scenario 7: The silver and gold layers must refresh every ten minutes from raw data. Do you build streams and tasks or dynamic tables, and why?

My default here is dynamic tables. It's a multi-step SQL pipeline with joins and aggregates, exactly where Snowflake's decision guide prefers them, and ten minutes is well above the sixty second minimum.

Let's put the two options side by side:

	Dynamic tables	Streams and tasks
JOINS AND AGGREGATES	preferred	possible
MULTI-STEP SILVER AND GOLD	preferred	possible
MERGE, PROCEDURAL LOGIC	no	preferred
CRON, STRICT ORCHESTRATION	possible (manual trigger)	preferred

So the design is simple. Silver is a dynamic table with a downstream target lag, and gold has a target lag of ten minutes. Snowflake refreshes silver only when gold needs it, in the right order, with consistent snapshots.

When would I choose streams and tasks instead? When the logic is procedural: a MERGE with custom rules, calls to stored procedures or external services, or strict cron orchestration with other jobs.

They also mix well. A common pattern is a stream and a task that MERGE raw changes into silver, with dynamic tables on top for the gold aggregates.

Either way, three things:

• Set REFRESH_MODE = INCREMENTAL on purpose.
• Keep an eye on refresh_mode_reason.
• Alert on failures.

Tell the business

Ten minutes is a target lag, not a guarantee.

Key points to remember

• A stream is an offset: net changes, consumed by DML
• Stale when the offset leaves retention: consume and alert
• Tasks: schedule plus WHEN, triggered tasks, task graphs
• Alerts: error integration to a cloud queue, retries, RETRY LAST
• Dynamic tables: target lag, and set REFRESH_MODE on purpose

Next in this series

Part 6: Time Travel, Cloning and Recovery

Part of the Real Time Snowflake Interview Questions and Answers series on TechBrothersIT. Features, limits and their status (GA or preview) are current as of October 2026; check the Snowflake documentation for anything you quote in an interview.