Incremental Pipelines in Snowflake: Dynamic Tables vs Streams & Tasks (Hands-On)

Rebuilding a whole table every hour is the easiest pipeline to write and the most expensive one to run. Snowflake gives you two native ways to process only what changed: Streams & Tasks, and Dynamic Tables.

In this tutorial we build the same small pipeline both ways: raw orders in, clean orders and a daily revenue summary out. Then we compare them so you know which to use.

The setup

Create a database, a small warehouse and a raw table that new orders land in, for example from Snowpipe or Openflow:

CREATE DATABASE IF NOT EXISTS shop;
CREATE SCHEMA  IF NOT EXISTS shop.pipeline;
USE SCHEMA shop.pipeline;

CREATE WAREHOUSE IF NOT EXISTS transform_wh
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND   = 60
  AUTO_RESUME    = TRUE;

CREATE OR REPLACE TABLE raw_orders (
  order_id     NUMBER,
  customer_id  NUMBER,
  status       STRING,
  amount       NUMBER(12, 2),
  updated_at   TIMESTAMP_NTZ
);

INSERT INTO raw_orders VALUES
  (1, 101, 'PLACED',    120.00, '2026-10-18 09:00:00'),
  (2, 102, 'PLACED',     75.50, '2026-10-18 09:05:00'),
  (1, 101, 'SHIPPED',   120.00, '2026-10-18 12:00:00');

Note that order 1 appears twice: once when placed, once when shipped. Our pipeline must keep only the latest version of each order.

Option 1: Streams & Tasks

A stream records which rows changed in a table since you last read it. A task runs SQL on a schedule. Together they give you change-driven, incremental processing where you control every step.

-- 1. Target table
CREATE OR REPLACE TABLE orders_clean (
  order_id     NUMBER PRIMARY KEY,
  customer_id  NUMBER,
  status       STRING,
  amount       NUMBER(12, 2),
  updated_at   TIMESTAMP_NTZ
);

-- 2. Stream on the raw table (only new rows are needed here)
CREATE OR REPLACE STREAM raw_orders_stream
  ON TABLE raw_orders
  APPEND_ONLY = TRUE;

-- 3. Task that merges the latest version of each order
CREATE OR REPLACE TASK merge_orders
  WAREHOUSE = transform_wh
  SCHEDULE  = '5 MINUTE'
  WHEN SYSTEM$STREAM_HAS_DATA('raw_orders_stream')
AS
MERGE INTO orders_clean t
USING (
  SELECT *
  FROM raw_orders_stream
  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 (order_id, customer_id, status, amount, updated_at)
  VALUES (s.order_id, s.customer_id, s.status, s.amount, s.updated_at);

-- Tasks are created suspended
ALTER TASK merge_orders RESUME;

-- Run it once now instead of waiting for the schedule
EXECUTE TASK merge_orders;

A few things worth knowing:

The WHEN clause saves money. SYSTEM$STREAM_HAS_DATA is checked without starting the warehouse, so the task only runs when there is something to process.
The stream advances only when a DML statement that reads it commits. If the MERGE fails, the same changes are still there for the next run.
Serverless tasks are an option: leave out WAREHOUSE and Snowflake manages the compute, which suits small, frequent jobs.
Chain tasks with AFTER merge_orders to build a DAG, for example a second task that rebuilds the daily summary.

Check task runs and errors:

SELECT name, state, scheduled_time, completed_time, error_message
FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY(TASK_NAME => 'MERGE_ORDERS'))
ORDER BY scheduled_time DESC
LIMIT 10;

Option 2: Dynamic Tables

A dynamic table is defined by a query and a freshness target. You declare what the result should be, and Snowflake works out how to keep it up to date, incrementally where it can. No streams, no MERGE, no schedule.

-- Latest version of each order
CREATE OR REPLACE DYNAMIC TABLE orders_current
  TARGET_LAG   = 'DOWNSTREAM'
  WAREHOUSE    = transform_wh
  REFRESH_MODE = INCREMENTAL
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;

-- Daily revenue, at most 15 minutes behind the source
CREATE OR REPLACE DYNAMIC TABLE revenue_daily
  TARGET_LAG   = '15 minutes'
  WAREHOUSE    = transform_wh
  REFRESH_MODE = INCREMENTAL
AS
SELECT DATE_TRUNC('day', updated_at) AS order_date,
       COUNT(*)                      AS orders,
       SUM(amount)                   AS revenue
FROM orders_current
WHERE status <> 'CANCELLED'
GROUP BY order_date;

How the two lag settings work together:

TARGET_LAG = '15 minutes' on the final table says: never let this be more than 15 minutes behind the source.
TARGET_LAG = 'DOWNSTREAM' on the intermediate table says: refresh only when something downstream needs it. Snowflake refreshes the whole chain together, so you do not pay for extra refreshes.
REFRESH_MODE = INCREMENTAL makes creation fail if the query cannot be refreshed incrementally, so you find out straight away rather than silently paying for full refreshes. Use AUTO to let Snowflake choose.

Monitor refreshes and confirm they really are incremental:

SELECT name, state, refresh_action, refresh_trigger,
       refresh_start_time, refresh_end_time, state_message
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(NAME => 'REVENUE_DAILY'))
ORDER BY refresh_start_time DESC
LIMIT 10;

-- Which mode did Snowflake pick, and why?
SHOW DYNAMIC TABLES IN SCHEMA shop.pipeline;

-- Force a refresh, for example after a backfill
ALTER DYNAMIC TABLE revenue_daily REFRESH;

-- Pause and resume
ALTER DYNAMIC TABLE revenue_daily SUSPEND;
ALTER DYNAMIC TABLE revenue_daily RESUME;

In SHOW DYNAMIC TABLES, check the refresh_mode and refresh_mode_reason columns. In the refresh history, refresh_action tells you whether each run was INCREMENTAL, FULL or NO_DATA.

What’s new for Dynamic Tables in 2026

More queries refresh incrementally: since September 2026, incremental refresh supports non-deterministic aggregate functions, so fewer tables fall back to full refreshes.
Refreshes survive failover: after a failover to another region, dynamic tables keep refreshing incrementally instead of starting again from scratch.
Frozen period: stop a dynamic table from refreshing data before a set timestamp, which helps when you backfill history separately.

Which should you use?

	Dynamic Tables	Streams & Tasks
Style	Declarative: say what the result is	Procedural: say how to update it
Code to maintain	One SELECT per table	Stream, MERGE logic and task per table
Scheduling	Freshness target (TARGET_LAG)	Cron or interval schedule
Dependencies	Worked out automatically	You chain tasks yourself
Custom logic	SQL query only	Any SQL, stored procedures, external calls
Side effects	No	Yes: notifications, writes to many tables, API calls
Best for	Transformations and medallion layers	Complex upserts, SCD Type 2, orchestration with side effects

My rule of thumb: start with Dynamic Tables for bronze-to-silver-to-gold transformations. Reach for Streams & Tasks when you need control the declarative model cannot give you, such as slowly changing dimensions, deletes with special rules, or calling a procedure after a load. The two mix well: a task can feed a raw table that dynamic tables then transform.

Cost tips for incremental pipelines

Set TARGET_LAG to what the business actually needs. A 1-minute lag on a dashboard that is checked once a day burns credits for nothing.
Use DOWNSTREAM on intermediate tables so they refresh only when a consumer needs them.
Give pipelines their own small warehouse with a 60-second auto-suspend, so you can see and cap their cost separately.
Watch for full refreshes. An unexpected FULL in the refresh history usually means the query changed or uses a construct that cannot be refreshed incrementally.

For more ways to cut credits, see my Snowflake cost optimisation playbook, and for the bigger picture of what Snowflake shipped this year, read what’s new in Snowflake in 2026.