PowderInsights | Dynamic Tables vs Streams & Tasks: Modern Incremental Pipelines

For most of Snowflake's history, "incremental pipeline" meant a stream on the source table, a task on a schedule, and a MERGE statement you wrote and maintained yourself. That pattern still works, and there are cases where it is the right answer. But since Dynamic Tables went GA, the default for new transformation pipelines has flipped: declare the result you want and a target freshness, and let Snowflake work out the incremental refresh.

This tutorial walks the two approaches side by side, shows the migration path, and covers the gotchas that bite teams on their first Dynamic Table project.

The streams & tasks version

Suppose raw orders land in RAW.ORDERS and we want a clean, deduplicated ANALYTICS.ORDERS_CLEAN. The classic implementation:

CREATE OR REPLACE STREAM raw.orders_stream ON TABLE raw.orders;

CREATE OR REPLACE TASK analytics.orders_clean_task
  WAREHOUSE = etl_wh
  SCHEDULE = '5 MINUTE'
  WHEN SYSTEM$STREAM_HAS_DATA('raw.orders_stream')
AS
MERGE INTO analytics.orders_clean t
USING (
  SELECT order_id, customer_id, status, amount, updated_at
  FROM raw.orders_stream
  WHERE metadata$action = 'INSERT'
  QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) = 1
) s
ON t.order_id = s.order_id
WHEN MATCHED AND s.updated_at > t.updated_at THEN UPDATE SET
  customer_id = s.customer_id, status = s.status, amount = s.amount, updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT VALUES (s.order_id, s.customer_id, s.status, s.amount, s.updated_at);

ALTER TASK analytics.orders_clean_task RESUME;

You own: the stream's offset semantics, the MERGE logic, the schedule, the warehouse sizing, the DAG if there is a downstream task, and the monitoring. Multiply by thirty tables.

The Dynamic Table version

CREATE OR REPLACE DYNAMIC TABLE analytics.orders_clean
  TARGET_LAG = '5 minutes'
  WAREHOUSE = etl_wh
  REFRESH_MODE = INCREMENTAL
  INITIALIZE = ON_CREATE
AS
SELECT order_id, customer_id, status, amount, updated_at
FROM raw.orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) = 1;

That is the whole pipeline. Snowflake tracks changes on RAW.ORDERS, computes the incremental delta for the query, and refreshes the table often enough to keep it within five minutes of the source. Downstream Dynamic Tables that select from ORDERS_CLEAN form a DAG automatically; set TARGET_LAG = DOWNSTREAM on intermediate tables and only the leaf's lag matters.

Migration path from streams & tasks

Inventory the task DAG. SELECT * FROM TABLE(INFORMATION_SCHEMA.TASK_DEPENDENTS(TASK_NAME => 'ROOT_TASK')) gives you the tree. Group by target table.
Classify each task's SQL. Pure SELECT/JOIN/aggregate/window logic converts directly. Tasks that call stored procedures, write to multiple targets, or have side effects (sending notifications, calling external functions) stay as tasks.
Rewrite the MERGE as a query. A Dynamic Table is defined by what the result should be, so you express the dedupe / SCD / aggregate as a SELECT over the source rather than as a mutation.
Create the Dynamic Table with a new name, run both side by side for a few refresh cycles, and diff: SELECT * FROM old EXCEPT SELECT * FROM new in both directions.
Swap and suspend. ALTER TABLE ... SWAP WITH is not supported for Dynamic Tables, so point consumers at the new name (or use a view as the stable interface), then ALTER TASK ... SUSPEND and drop the stream.

Refresh-mode gotchas

This is where first projects go wrong.

REFRESH_MODE = AUTO can choose FULL. If the query uses constructs that are not incrementalizable, Snowflake silently falls back to full refresh, and your "incremental" pipeline rescans the source every cycle. Always check after creating:

SHOW DYNAMIC TABLES LIKE 'ORDERS_CLEAN' IN SCHEMA analytics;
-- inspect the "refresh_mode" and "refresh_mode_reason" columns

Set REFRESH_MODE = INCREMENTAL explicitly during development so creation fails if the query cannot be incrementalized, instead of degrading quietly.

Constructs that block incremental refresh (as of early 2026; the list shrinks every release): non-deterministic functions (CURRENT_TIMESTAMP, RANDOM), most UDFs, LATERAL FLATTEN in some positions, some OUTER JOIN shapes with aggregation, and UNION with duplicates (UNION ALL is fine). The documentation's support table is the authority.

Change tracking must be on for the sources. Dynamic Tables enable it automatically when you own the source table; if the source is shared or owned by another role, ALTER TABLE ... SET CHANGE_TRACKING = TRUE is required first.

TARGET_LAG is a ceiling, not a schedule. Snowflake refreshes when needed to meet the lag, and skips refreshes when the source has not changed — which is why idle Dynamic Tables cost nothing. Do not set a one-minute lag on a table nobody reads in under an hour.

Incremental refresh of window functions is supported but expensive when the partition key is coarse. A ROW_NUMBER() partitioned by order_id is cheap; one partitioned by customer_id re-evaluates every customer with any change.

Monitoring

The table function DYNAMIC_TABLE_REFRESH_HISTORY is the primary tool:

SELECT name, state, refresh_action, refresh_trigger,
       data_timestamp, refresh_start_time, refresh_end_time,
       DATEDIFF('second', refresh_start_time, refresh_end_time) AS secs,
       statistics:numInsertedRows::int AS inserted,
       statistics:numDeletedRows::int  AS deleted
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(NAME => 'ANALYTICS.ORDERS_CLEAN'))
ORDER BY refresh_start_time DESC
LIMIT 20;

refresh_action tells you whether each cycle was INCREMENTAL, FULL, or NO_DATA; a run of FULL values on a table you declared incremental means a schema change or upstream reinitialization, and is worth an alert. DYNAMIC_TABLE_GRAPH_HISTORY shows the DAG and each node's configured versus actual lag. Snowsight's Dynamic Tables page visualizes both.

Cost comparison on a sample pipeline

Take a representative pipeline — an orders table of tens of millions of rows receiving a few hundred thousand changes per hour, a dedupe step, and two downstream aggregates — and consider where the credits go on a small warehouse over a day:

Streams & tasks: the task fires on its schedule whether or not the change volume justifies it. At a five-minute cadence that is close to 300 warehouse resumes a day, and each short MERGE pays the 60-second minimum billing floor. The MERGE also has to probe the full target table to find matches, so cost scales with the target, not with the delta. Warehouse time dominates.
Dynamic Tables: refreshes are skipped entirely when the source has not changed, consecutive refreshes across the DAG run in the same warehouse session, and the incremental delta is computed by the engine from change-tracking metadata rather than by a MERGE against the whole target. The gap widens overnight, when change volume drops and the task-based version keeps firing.

Measure it on your own data: compare WAREHOUSE_METERING_HISTORY for the ETL warehouse across a week before and after the switch, and add the Dynamic Table refresh credits from DYNAMIC_TABLE_REFRESH_HISTORY. The pattern is consistent across the migrations we have done: for standard transformation logic, Dynamic Tables are cheaper to run and much cheaper to maintain.

When streams & tasks still win

Procedural logic, multi-target writes, or external calls
Pipelines that need an exact schedule (a nightly batch at 02:00, not "within an hour")
Sources without change tracking (external tables, some shares)
Workloads where you need full control of the MERGE for auditability

Everything else: declare it.

Planning a pipeline modernization? Contact us — our senior Snowflake consultants have migrated task DAGs to Dynamic Tables for teams of every size.