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

  1. Source table receives inserts and updates
  2. Stream captures the changes
  3. Task wakes on schedule
  4. Does SYSTEM$STREAM_HAS_DATA return true? Yes: Run MERGE from the stream into the target. No: Skip the run, no warehouse used.
  5. Transaction commits; stream offset advances
  6. 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

Snowflake topics

Snowflake Fundamentals

Warehouses & Performance

Staging & SnowSQL

Tables & Data Handling

Cloud Integrations

Storage & Optimization

Security & Governance

Views, Pipelines & Automation

Data Types & SQL Fundamentals

Semi-Structured Data Deep Dive

Advanced Features

Data Sharing & Collaboration

Cost Management & Monitoring

Databases, Schemas & Objects

Performance Deep Dive

Advanced Security & Governance

Stored Procedures & UDFs

Ecosystem & Integrations

SnowPro Certification Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Mastering Snowflake

The modern data cloud, from architecture to SnowPro certification. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice Snowflake SQL Sharpen the SQL you use in Snowflake: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Snowflake  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Unlike the SQL and Python courses, Snowflake needs a real account — there's no in-browser engine for it. Click Copy above, then paste it into a free Snowflake trial (30 days, no card required) and run it in Snowsight.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic