Snowflake's Dynamic Tables are the tool of choice to simplify building data pipelines. Every node in a data pipeline can become a table with a simple SELECT query defining the table's contents. Snowflake automatically refreshes the tables to keep them up-to-date, so that you can focus on driving business value. Dynamic tables guarantee delayed view semantics, where the contents of a dynamic table are equivalent to a corresponding view evaluated at some point in the past — the timestamp at which the refresh is evaluated. While this powerful guarantee simplifies reasoning about application invariants, there are cases where delayed view semantics might not be what your application logic needs.
For example, you might want to:
- Perform stream-static joins where you process one base table (fact) incrementally, but the others (dimensions) at the current version
- Perform upserts from recent staging data
- Only process inserts into the base table, ignoring all updates and deletes. Conversely, you might only want to process deletes
- Access current state in the dynamic table to speed up the next refresh
- Perform data manipulation language (DMLs) into the dynamic table to correct/delete historical data, or process an out-of-band ETL event
Today, we introduce custom incremental dynamic tables (now generally available), a powerful new abstraction to enhance the expressibility and performance of dynamic tables. With SELECT-based incremental dynamic tables, Snowflake can automatically derive incremental plans to refresh your table based on the SELECT query you provided. With custom incremental dynamic tables, you get to specify exactly how to refresh your dynamic table, choosing a custom merge or insert strategy. You trade simplicity for flexibility — while custom incremental dynamic table definitions are more complex and require you to specify how the refresh should work, they are able to express transformations that SELECT-based dynamic tables can't. We now show you how.
Custom incremental dynamic tables define refresh logic with a REFRESH USING statement.
CREATE DYNAMIC TABLE dt (id int, val string) ... REFRESH USING (...);The statement writes to the dynamic table itself, referenced with the keyword SELF, using either MERGE INTO SELF or INSERT INTO SELF. The body of the merge or insert can use Snowflake's CHANGES clause for fine-grained control of incrementalization. If you are already familiar with CHANGES, feel free to skip ahead. If not, we provide a brief tour.
The CHANGES primitive
A CHANGES clause returns the rows that changed in a table, view, or dynamic table over a specified interval. For example, suppose the orders table (with change_tracking enabled) contains these rows at 2026-06-01 10:00:00:
| order_id | status | amount |
|---|---|---|
| 1001 | NEW | 25.00 |
| 1002 | NEW | 40.00 |
Between 10:00 and 10:05, one row is inserted and one row is updated:
INSERT INTO orders VALUES (1003, 'NEW', 15.00);
UPDATE orders SET status = 'SHIPPED' WHERE order_id = 1001;You can use CHANGES with timestamp markers to query only the rows that changed during that interval:
SELECT order_id, status, amount, METADATA$ACTION, METADATA$ISUPDATE
FROM orders
CHANGES()
AT (TIMESTAMP => '2026-06-01 10:00:00'::TIMESTAMP)
END (TIMESTAMP => '2026-06-01 10:05:00'::TIMESTAMP);which returns
| order_id | status | amount | METADATA$ACTION | METADATA$ISUPDATE |
|---|---|---|---|---|
| 1001 | SHIPPED | 25.00 | INSERT | TRUE |
| 1001 | NEW | 25.00 | DELETE | TRUE |
| 1003 | NEW | 15.00 | INSERT | FALSE |
In a custom incremental dynamic table, Snowflake manages these start and end offsets across refreshes. That means the REFRESH USING query can use CHANGES without specifying AT and END markers and each refresh automatically processes the next interval of base table changes. You can optionally specify CHANGES(INFORMATION => APPEND_ONLY) to ignore updates and deletes.
Putting custom incremental dynamic tables to work
A custom incremental dynamic table's definition could
- Combine CHANGES with static reads, for example, a stream-static join. Base tables referred to without CHANGES are processed as of the refresh timestamp
- Use standalone CHANGES logic, for example, to audit all deletes
- Not use changes at all, for example, to process data from the last n days
Stream-static joins
Let us look at a ubiquitous use case that custom incremental dynamic tables help unlock: stream-static joins. In the world of data engineering, a stream-static join is a process where a real-time stream of data (which is continuous and fast-moving) is combined with a static data set (which is stable and changes infrequently). In other words, high churn fact data needs to be enriched with slowly changing dimension data. As a data engineer, you would like to avoid dimension table changes causing a rewrite of all historical data to keep refreshes fast, provided your application semantics permit this.
CREATE OR REPLACE DYNAMIC TABLE enriched_clicks (
click_id INT, user_id INT, page_id INT, page_title STRING,
section STRING, click_ts TIMESTAMP
)
TARGET_LAG = DOWNSTREAM
WAREHOUSE = my_wh
REFRESH USING (
INSERT INTO SELF
SELECT c.click_id, c.user_id, p.page_id, p.page_title, p.section, c.click_ts
FROM clicks CHANGES(INFORMATION => APPEND_ONLY) AS c
LEFT OUTER JOIN pages AS p ON c.page_id = p.page_id
);The insert-only clicks base table serves as an immutable event stream (see this for an example where a base table is not insert-only). During a refresh cycle, the CHANGES(INFORMATION=>APPEND_ONLY) clause fetches only the newly arrived rows since the previous refresh. To enrich these fresh event records, you perform a LEFT JOIN against the pages dimension table, which is evaluated as a static snapshot at the current refresh timestamp. This mechanism is designed to ensure that while fact data is processed incrementally, it is joined against the most recent state of the dimension metadata before being persisted to the dynamic table. Only the initial refresh processes all the data in the fact table, and even this can be avoided with a backfill.
The key factor driving performance here is that dimension-only changes are not reprocessed. If a page title is renamed but no click row changes, the enriched view keeps the old name until a click change triggers a re-lookup. When these semantics are appropriate for your domain, this can be much more efficient than reprocessing all fact rows for a dimension change (as delayed view semantics would mandate).
For instance, consider the initial state of the pages table:
| page_id | page_title | section |
|---|---|---|
| 10 | Dynamic Tables | Docs |
| 20 | Pricing | Product |
Subsequently, the clicks stream receives a pair of new event records:
| click_id | user_id | page_id | click_ts |
|---|---|---|---|
| 1001 | 7 | 10 | 10:00 |
| 1002 | 8 | 20 | 10:01 |
During the initial refresh, the CHANGES(INFORMATION => APPEND_ONLY) expression on the fact table captures only these specific records. The logic performs a join with the dimension snapshot and appends the resulting enriched data to SELF:
| click_id | user_id | page_id | page_title | section | click_ts |
|---|---|---|---|---|---|
| 1001 | 7 | 10 | Dynamic Tables | Docs | 10:00 |
| 1002 | 8 | 20 | Pricing | Product | 10:01 |
If the dimension table is updated independently:
UPDATE pages
SET page_title = 'Dynamic Tables overview'
WHERE page_id = 10;Assuming no new event data arrives, the CHANGES clause for the clicks table will yield an empty set during the following refresh cycle. Consequently, the row associated with click_id 1001 remains unchanged in the dynamic table and does not reflect the modified page title:
| click_id | user_id | page_id | page_title | section | click_ts |
|---|---|---|---|---|---|
| 1001 | 7 | 10 | Dynamic Tables | Docs | 10:00 |
| 1002 | 8 | 20 | Pricing | Product | 10:01 |
Suppose a fresh event record enters the clicks stream after the modification of the page title:
| click_id | user_id | page_id | click_ts |
|---|---|---|---|
| 1003 | 9 | 10 | 10:05 |
The subsequent refresh cycle isolates this new click and performs a re-lookup against the now-updated pages snapshot:
| click_id | user_id | page_title | section | click_ts |
|---|---|---|---|---|
| 1003 | 9 | Dynamic Tables overview | Docs | 10:05 |
Consequently, the persistent state in the dynamic table reflects a mixture of historical snapshots and current enrichments:
| click_id | user_id | page_id | page_title | section | click_ts |
|---|---|---|---|---|---|
| 1001 | 7 | 10 | Dynamic Tables | Docs | 10:00 |
| 1002 | 8 | 20 | Pricing | Product | 10:01 |
| 1003 | 9 | 10 | Dynamic Tables overview | Docs | 10:05 |
Custom merge strategies
An architectural pattern from the dbt ecosystem that you can now implement is the merge strategy featuring an is_incremental() filter. In the scenario below, the model assumes no late-arriving records, utilizing a global high-watermark to filter any incoming data with an updated_at timestamp preceding the current MAX value stored in SELF. Only records satisfying this temporal constraint are then merged into the target state based on a designated unique key.
CREATE OR REPLACE DYNAMIC TABLE event_facts (
id NUMBER, event_date DATE, user_id NUMBER, event_type STRING, updated_at TIMESTAMP_NTZ
)
TARGET_LAG = '5 minutes'
WAREHOUSE = transform_wh
REFRESH USING (
MERGE INTO SELF t
USING (
SELECT id, event_date, user_id, event_type, updated_at
FROM stg_events
WHERE updated_at > COALESCE((SELECT MAX(updated_at) FROM SELF),
'1900-01-01'::TIMESTAMP_NTZ)
) s
ON t.id = s.id
WHEN MATCHED THEN UPDATE ALL BY NAME
WHEN NOT MATCHED THEN INSERT ALL BY NAME
);State reuse with custom incremental dynamic tables
Finally, custom incremental dynamic tables also unlock state reuse and memoization. A SELECT-based dynamic table's contents are a pure function of its inputs. The query can't look at the table's own current contents. However, custom incremental dynamic tables are stateful and their next state (after refresh) can be computed based on the current state before refresh. This allows us to express computations that might depend on the full history of changes rather than just the current batch of changes or the current snapshot. It also allows working with state that isn't in the source tables anymore (for example, because of data aging out), and is instead tracked only in the dynamic table itself.
Consider a scenario where you are processing a CDC stream of customer updates to maintain a dimension table. Say you require a last_changed_at column that precisely captures when the attributes of the customer were modified, rather than simply noting when the latest event record was received. This distinction is important in cases where CDC systems can emit snapshot rows, where a record could be re-emitted with a new commit timestamp despite no underlying change in data. A reliable last_changed_at timestamp must filter out these redundant no-op updates. To achieve this, we utilize a stateful approach via a custom incremental dynamic table:
CREATE OR REPLACE DYNAMIC TABLE dim_customer (
customer_id INT, name STRING, tier STRING, last_changed_at TIMESTAMP_NTZ
)
TARGET_LAG = '1 hour'
WAREHOUSE = wh
REFRESH USING (
MERGE INTO SELF AS tgt
USING (
SELECT customer_id, name, tier, _commit_time
FROM customers_cdc CHANGES(INFORMATION => APPEND_ONLY)
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY _commit_time DESC) = 1
) AS src
ON tgt.customer_id = src.customer_id
WHEN MATCHED AND (src.name, src.tier) IS DISTINCT FROM (tgt.name, tgt.tier)
THEN UPDATE SET tgt.name = src.name, tgt.tier = src.tier,
tgt.last_changed_at = src._commit_time
WHEN NOT MATCHED
THEN INSERT (customer_id, name, tier, last_changed_at)
VALUES (src.customer_id, src.name, src.tier, src._commit_time)
);The effectiveness of this pattern lies in the WHEN MATCHED AND (...) IS DISTINCT FROM (...) clause. By comparing the arriving change set against the state already persisted in SELF, the refresh logic ensures that the change timestamp is only progressed when the actual business values have evolved.
State reuse also unlocks opportunities to improve performance. Suppose we're working with a stream of match scores from a league for your favorite sport. We first need a player_scores table that holds each player's total score so far (try implementing it with a custom incremental dynamic table!). We'd like our dynamic table (dt_leaderboard) to maintain a top-three leaderboard. Here's how we'd do it:
CREATE OR REPLACE DYNAMIC TABLE player_scores (player_id INT, total_score INT)
TARGET_LAG = DOWNSTREAM
WAREHOUSE = transform_wh
REFRESH USING (
MERGE INTO SELF AS tgt
USING (
SELECT player_id, SUM(score) AS batch_score
FROM match_results CHANGES(INFORMATION => APPEND_ONLY) -- match_results is insert only
GROUP BY player_id
) AS src
ON tgt.player_id = src.player_id
WHEN MATCHED THEN
UPDATE SET tgt.total_score = tgt.total_score + src.batch_score
WHEN NOT MATCHED THEN
INSERT (player_id, total_score) VALUES (src.player_id, src.batch_score)
);
CREATE OR REPLACE DYNAMIC TABLE dt_leaderboard (player_id INT, total_score INT, rank INT)
TARGET_LAG = '30 minutes'
WAREHOUSE = transform_wh
REFRESH USING (
MERGE INTO SELF AS tgt
USING (
SELECT player_id, total_score,
ROW_NUMBER() OVER (ORDER BY total_score DESC, player_id ASC) AS new_rank
FROM (
SELECT COALESCE(ch.player_id, cur.player_id) AS player_id,
COALESCE(ch.total_score, cur.total_score) AS total_score
FROM SELF AS cur
FULL OUTER JOIN (
SELECT player_id, total_score
FROM player_scores CHANGES(INFORMATION => DEFAULT)
WHERE METADATA$ACTION = 'INSERT'
) AS ch
ON cur.player_id = ch.player_id
)
) AS src
ON tgt.player_id = src.player_id
WHEN MATCHED AND src.new_rank > 3 THEN DELETE
WHEN MATCHED THEN
UPDATE SET tgt.total_score = src.total_score, tgt.rank = src.new_rank
WHEN NOT MATCHED AND src.new_rank <= 3 THEN
INSERT (player_id, total_score, rank) VALUES (src.player_id, src.total_score, src.new_rank)
);The refresh first builds a candidate set by full-outer-joining the current leaderboard in SELF with the new player_scores changes. The full outer join keeps both kinds of candidates: new or updated players from the change feed, and existing leaders that had no new score but may still belong in the top three. For each joined row, COALESCE(ch.player_id, cur.player_id) and COALESCE(ch.total_score, cur.total_score) pick the changed value when present, otherwise pick the value already stored in SELF. The outer query then ranks those candidates with ROW_NUMBER() OVER (ORDER BY total_score DESC, player_id ASC), producing a deterministic new_rank where higher scores win and player_id breaks ties. The MERGE applies that ranking back to SELF: matched rows with new_rank > 3 are deleted, matched rows still in the top three are updated, and unmatched rows with new_rank <= 3 are inserted. Anything outside the top three and not already stored is ignored.
The trick to ensuring good performance here is to keep the amount of processing that each refresh needs to do proportional to the size of the incoming CHANGES, rather than all the historical score data. We know that for each batch of changes that comes in, the new leaders have to emerge either from the set of old leaders in SELF, or based on the changes. Therefore, all the refresh needs to do is to pull just those new rows via CHANGES, reconcile them against the current three-row leaderboard, and re-rank the resulting rows. Once we have the new top three, we can MERGE them back into the dynamic table, deleting anything that fell out and inserting anything new that broke in. The base table is never re-scanned and the dynamic table never grows beyond three, so the work per refresh depends only on the size of the incoming batch.
DML and schema evolution
Since custom incremental dynamic tables do not adhere to delayed view semantics, direct DML statements can be issued against them, for example, to correct or delete historical records. It is also possible to perform schema evolution on these tables without the need to perform an expensive reinitialization. You can refer to a companion blog post that demonstrates how to manage partial values in slowly changing dimensions (SCD) Type 1 scenarios to learn how. To enable periodic deletion of old data from a custom incremental dynamic table, consider defining a storage lifecycle policy on the table.
CREATE OR REPLACE DYNAMIC TABLE SLP_CIDT (
id INT, payload STRING, event_ts TIMESTAMP_NTZ
)
TARGET_LAG = '1 hour' WAREHOUSE = DT_TRIAGE_4XL
REFRESH USING (
MERGE INTO SELF AS tgt
USING (
SELECT id, payload, event_ts
FROM SLP_SRC CHANGES(INFORMATION => APPEND_ONLY)
QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY event_ts DESC) = 1
) AS src
ON tgt.id = src.id
WHEN MATCHED THEN UPDATE SET tgt.payload = src.payload, tgt.event_ts = src.event_ts
WHEN NOT MATCHED THEN INSERT (id, payload, event_ts) VALUES (src.id, src.payload, src.event_ts)
);
CREATE OR REPLACE STORAGE LIFECYCLE POLICY SLP_EXPIRE_OLD
AS (ts TIMESTAMP)
RETURNS BOOLEAN ->
TO_DATE(ts) < TO_DATE(DATEADD(DAY, -30, CURRENT_TIMESTAMP()));
ALTER TABLE SLP_CIDT
ADD STORAGE LIFECYCLE POLICY SLP_EXPIRE_OLD ON (event_ts);Conclusion
To summarize, custom incremental dynamic tables are a powerful new tool you should reach for your ETL toolkit when you have a use case that existing SELECT-based dynamic tables can't express. Many streams-and-task pipelines that only do INSERT/MERGE are also ideal candidates to use custom incremental dynamic tables instead to get the convenience and simplicity of dynamic tables. Custom incremental dynamic tables fit seamlessly into pipelines with SELECT-based dynamic tables. They can be either downstream or upstream of the latter. If you already have a pipeline that almost does what you want with SELECT-based dynamic tables and you need just one node that requires MERGE logic, you can now use custom incremental tables to implement that node. We are working on further improvements in this space, such as simplifying merge syntax and supporting other DML flavors, so stay tuned for future announcements.
To get started, visit our “Comprehensive Developer Guide” for Dynamic Tables or point your favorite coding agent to the dynamic tables documentation pages (hint: Snowflake CoCo is a great resource for dynamic tables).
Forward-looking statement
This blog post contains forward-looking statements, including statements about products, features and capabilities that are under development, not yet generally available or otherwise not yet available. These forward-looking statements are subject to risks and uncertainties that could cause actual results to differ materially. Please refer to Snowflake's SEC filings for a more detailed description of the risks that could cause actual results to differ.
