Incremental Load
Snowflake · Views, Pipelines & Automation
Theory
An incremental load processes only new or changed rows instead of reloading everything. In Snowflake the standard pattern is a stream plus a task: the stream on a source table captures changes, and a task runs on a schedule with WHEN SYSTEM$STREAM_HAS_DATA('orders_stream'), so it starts only when there is something to process. The task's SQL is usually a MERGE that updates matching rows in the target and inserts new ones.
Because the MERGE reads from the stream inside a committed DML statement, the stream's offset moves forward, and the next run sees only later changes. On a table that receives a few thousand changes per hour, each run touches only those rows rather than millions.
Why we use it
Full reloads waste compute and time as tables grow. Incremental processing keeps runtimes and cost proportional to the amount of change, which also makes frequent, near-real-time refreshes affordable.
When to use it
Use stream plus task plus MERGE when you need control over the change logic (deletes, soft deletes, SCD history). For straightforward transformations where a target lag is enough, a dynamic table is often simpler.
The Stream + Task Pattern
Pairing a Stream (which knows what changed) with a Task (which runs on a schedule) creates a standard incremental-load pattern for pipelines.
How It Runs
The task wakes up, reads the stream's captured changes, and applies them to a target table — typically with a single MERGE statement that inserts new rows and updates changed ones.
Why It's Faster at Scale
Processing only what changed instead of reloading the entire source table every run dramatically cuts compute cost and runtime on large datasets.
How it works
- Source table receives inserts and updates
- Stream captures the changes
- Task wakes on schedule
- Does SYSTEM$STREAM_HAS_DATA return true? Yes: Run MERGE from the stream into the target. No: Skip the run, no warehouse used.
- Transaction commits; stream offset advances
- Target is up to date with only changed rows processed
Example
CREATE OR REPLACE TASK process_order_changes
WAREHOUSE = analytics_wh
SCHEDULE = '5 MINUTE'
WHEN SYSTEM$STREAM_HAS_DATA('orders_stream')
AS
MERGE INTO orders_final AS target
USING (SELECT * FROM orders_stream WHERE METADATA$ACTION = 'INSERT') AS source
ON target.order_id = source.order_id
WHEN MATCHED THEN UPDATE SET target.amount = source.amount
WHEN NOT MATCHED THEN INSERT (order_id, amount) VALUES (source.order_id, source.amount);
ALTER TASK process_order_changes RESUME;Key points
- Processes only changed rows, cutting compute versus full reloads
- MERGE applies inserts and updates in one statement
- WHEN SYSTEM$STREAM_HAS_DATA avoids empty runs
- Filter METADATA$ACTION carefully so deletes are handled as intended
- Dynamic tables can replace this pattern for simpler cases