Snowflake Dynamic Tables更新：更快更灵活

A hands-on guide to Snowflake’s 2026 Dynamic Tables updates — with every example run against a live account.

Disclaimer: I am Principal Technical Architect at Snowflake with over 30 years of data strategy, architecture, and development experience. The views expressed here are mine alone and do not necessarily reflect the views of my current, former, or future employers

This document outlines a comprehensive set of platform enhancements and newly introduced capabilities. Given the breadth of updates, the content has been structured across three distinct sections to support focused reading and efficient knowledge transfer.

Part 9 — Custom incremental Dynamic Tables with MERGE

This is the headline capability: write the MERGE or INSERT logic yourself while Snowflake still owns scheduling, retries, dependency tracking, and transactional guarantees.

Row two is the trade. A declarative Dynamic Table is guaranteed to equal what its query would return. With custom incrementalization that guarantee becomes yours — if your MERGE logic is wrong, the table is wrong, and Snowflake will not notice.

Syntax

CREATE [ OR REPLACE ] DYNAMIC TABLE <name> (
 <col_name> <col_type> [ , … ] - REQUIRED
)
 TARGET_LAG = { '<time_spec>' | DOWNSTREAM }
 WAREHOUSE = <warehouse_name>
 [ REFRESH_MODE = { AUTO | CUSTOM_INCREMENTAL } ]
 [ INITIALIZE = ON_SCHEDULE ]
 [ BACKFILL FROM <table_name> ]
 [ START AT ({ STREAM => '<stream>' | TIMESTAMP => <ts>
 | STATEMENT => <query_id> | OFFSET => -<seconds> }) ]
 REFRESH USING ( <single_dml_statement> )

Hard requirements: an explicit column list (schema can’t be inferred from DML), exactly one DML statement per REFRESH USING, and no multi-statement transactions or stored procedures. REFRESH_MODE = AUTO resolves to CUSTOM_INCREMENTAL when REFRESH USING is present.

SELF is how you reference the table being defined — as write target (MERGE INTO SELF) and as read source (FROM SELF AS cur inside the USING subquery, to read current contents). You cannot use the table’s own object name inside REFRESH USING, and CHANGES() on SELF is rejected.

CHANGES() replaces stream semantics. Snowflake binds the interval to refresh boundaries, so you must not specify AT, BEFORE, or END.

Two metadata columns come with it:

Working example: CDC enrichment with delete propagation

CREATE OR REPLACE TABLE ci_orders_cdc (
 order_id NUMBER,
 customer_id NUMBER,
 amount NUMBER(12,2),
 updated_at TIMESTAMP_NTZ
) CHANGE_TRACKING = TRUE;
CREATE OR REPLACE DYNAMIC TABLE ci_dt_orders_enriched (
 order_id NUMBER,
 customer_id NUMBER,
 customer_nm VARCHAR,
 region VARCHAR,
 amount NUMBER(12,2),
 updated_at TIMESTAMP_NTZ
)
 TARGET_LAG = '1 minute'
 WAREHOUSE = transform_wh
 REFRESH_MODE = CUSTOM_INCREMENTAL
 REFRESH USING (
 MERGE INTO SELF AS tgt
 USING (
 SELECT c.order_id, c.customer_id, d.customer_nm, d.region,
 c.amount, c.updated_at,
 c.METADATA$ACTION AS chg_action,
 c.METADATA$ISUPDATE AS chg_isupdate
 FROM ci_orders_cdc CHANGES(INFORMATION => DEFAULT) AS c
 LEFT OUTER JOIN ci_dim_customers AS d
 ON c.customer_id = d.customer_id
 QUALIFY ROW_NUMBER() OVER (
 PARTITION BY c.order_id
 ORDER BY c.updated_at DESC,
 IFF(c.METADATA$ACTION = 'INSERT', 0, 1)
 ) = 1
 ) AS src
 ON tgt.order_id = src.order_id
 WHEN MATCHED AND src.chg_action = 'DELETE' THEN DELETE
 WHEN MATCHED THEN UPDATE SET
 tgt.customer_id = src.customer_id,
 tgt.customer_nm = src.customer_nm,
 tgt.region = src.region,
 tgt.amount = src.amount,
 tgt.updated_at = src.updated_at
 WHEN NOT MATCHED AND src.chg_action = 'INSERT' THEN INSERT
 (order_id, customer_id, customer_nm, region, amount, updated_at)
 VALUES (src.order_id, src.customer_id, src.customer_nm,
 src.region, src.amount, src.updated_at)
 );

Applying an insert, an update and a delete in one batch:

INSERT INTO ci_orders_cdc VALUES (4,104,500.00,'2026-08-02 09:00:00');
UPDATE ci_orders_cdc SET amount = 999.99,
 updated_at = '2026-08-02 10:00:00'
 WHERE order_id = 2;
DELETE FROM ci_orders_cdc WHERE order_id = 3;
ALTER DYNAMIC TABLE ci_dt_orders_enriched REFRESH;

{"insertedRows":1, "copiedRows":1, "deletedRows":1, "updatedRows":1}

Note updatedRows — a statistic declarative Dynamic Tables never emit, because they express updates as delete-plus-insert. A custom incremental table performs a real UPDATE, so it appears. It’s a reliable signal your MERGE branches are firing as intended.

Order 3 is gone — the delete propagated.

The QUALIFY is not optional

MERGE is nondeterministic when multiple source rows match one target row, and Snowflake won’t protect you. With INFORMATION => DEFAULT, an update arrives as a DELETE row and an INSERT row for the same key — two rows matching one target.

The QUALIFY collapses them, and the tie-breaker decides the winner:

ORDER BY c.updated_at DESC,
 IFF(c.METADATA$ACTION = 'INSERT', 0, 1)

updated_at DESC normally picks the INSERT half, since the new version has a later timestamp. But when a column changes without updated_at changing, both halves share a timestamp — and the IFF ensures INSERT still wins instead of the row being spuriously deleted. Omit it and you get intermittent, silent corruption.

Working example: running accumulator

This pattern is impossible declaratively. It reads its own prior output, so history is never rescanned.

CREATE OR REPLACE DYNAMIC TABLE ci_dt_player_scores (
 player_id NUMBER,
 total_score NUMBER
)
 TARGET_LAG = '1 minute'
 WAREHOUSE = transform_wh
 REFRESH_MODE = CUSTOM_INCREMENTAL
 REFRESH USING (
 MERGE INTO SELF AS tgt
 USING (
 SELECT player_id, SUM(score) AS batch_score
 FROM ci_match_results CHANGES(INFORMATION => APPEND_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 -- reuses prior state
 WHEN NOT MATCHED THEN INSERT
 (player_id, total_score) VALUES (src.player_id, src.batch_score)
 );

Across two batches (10 + 15 then + 5), player 1 finished at 30. The refresh summed only the two new rows and added them to the stored total. A declarative equivalent would re-aggregate the whole history every time.

APPEND_ONLY here means updates and deletes are ignored — usually right for a running total, since a retracted match shouldn’t silently rewrite history. For retractions, switch to DEFAULT and subtract on METADATA$ACTION = ‘DELETE’.

INSERT INTO SELF for append-only work

CREATE OR REPLACE DYNAMIC TABLE dt_deletions_log (
 id INT, name STRING, email STRING
)
 TARGET_LAG = '1 minute'
 WAREHOUSE = transform_wh
 INITIALIZE = ON_SCHEDULE
 REFRESH USING (
 INSERT INTO SELF
 SELECT * EXCLUDE (METADATA$ISUPDATE, METADATA$ACTION)
 FROM users CHANGES(INFORMATION => DEFAULT)
 WHERE NOT METADATA$ISUPDATE AND METADATA$ACTION = 'DELETE'
 );

NOT METADATA$ISUPDATE is what makes this an audit of genuine deletions rather than a log polluted by the delete half of every update.

Initialization and migration

Without BACKFILL FROM, the initial refresh replays every existing source row through your logic, because CHANGES() treats them all as INSERTs. On a large table that is slow and expensive.

START AT accepts TIMESTAMP, STATEMENT => <query_id>, STREAM => <stream_name>, or OFFSET => -<seconds>.

The STREAM option is the clean cutover from streams and tasks: point the new Dynamic Table at your existing stream’s offset so no changes are missed or double-counted. Both clauses are creation-time only — not settable via ALTER.

Limitations:

FROZEN WHERE and INSERT ONLY INPUTS cannot be combined with REFRESH USING. Frozen-region cost control and custom MERGE logic are mutually exclusive — pick one.

No dbt or DCM integration. Only CREATE OR ALTER can modify a REFRESH USING definition or its properties.

Primary keys are not derived. Declarative tables infer keys from GROUP BY and QUALIFY partitions; these don’t. Add one so downstream consumers stay efficient:

ALTER TABLE ci_dt_orders_enriched ADD PRIMARY KEY (order_id) RELY;

Retention must exceed your refresh gap. If retention expires on a base table read through CHANGES() before the next refresh, that refresh fails. Account for planned suspensions.

Upstream schema changes fail the next refresh. If the upstream was altered, recover with CREATE OR ALTER and an updated REFRESH USING; the refresh resumes from the last success. If it was CREATE OR REPLACEd, change tracking is broken and the downstream must be recreated.

Dimensions are read at snapshot time, not incrementally. Objects outside CHANGES() are read as-of the refresh, so editing a dimension does not re-enrich existing rows — only later change sets pick up new values. If you need that, it’s a declarative join.

Each refresh is one autocommit transaction. Any failure rolls back the whole refresh.

Type restrictions: no structured OBJECT/ARRAY/MAP, no INTERVAL, no geospatial columns in MERGE … ON. UDTFs unsupported; non-SQL scalar UDFs inside CHANGES() must be IMMUTABLE.

RELY changes CHANGES() semantics

If delete-then-insert of an identical row should be invisible to your merge logic — the usual result of INSERT OVERWRITE — add RELY so Snowflake compacts the pairs before they reach CHANGES(). APPEND_ONLY never uses primary keys for identity.

“No new data” is not a failure

With an active TARGET_LAG the scheduler may consume the change set before your manual ALTER … REFRESH, and CHANGES() is consumed once. During testing, two consecutive manual refreshes reported No new data while the accumulator was already correctly updated by the scheduler. Check contents before assuming a refresh failed.

When to use it

Use custom incrementalization for CDC with delete propagation, stream-static joins, state reuse (accumulators, Top-K leaderboards), audit trails, or migrating single-statement MERGE/INSERT logic off streams and tasks.

Stay declarative when a SELECT expresses the result. You keep delayed-view equivalence, automatic key derivation, FROZEN WHERE, dbt integration, and Snowflake’s correctness guarantee. Custom incrementalization trades all of that for expressive power — take the trade only when you need it.

Migration checklist

In dependency order:

1. Move to Gen2 warehouses. Largest gain, no rewrite.
2. Add PRIMARY KEY … RELY to base tables — then CREATE OR REPLACE downstream Dynamic Tables, since it isn’t retroactive.
3. Add frozen regions wherever history is immutable. Use DATEADD, not CURRENT_DATE() - n.
4. Use BACKFILL FROM for any migration with existing history.
5. Convert dedup logic to QUALIFY ROW_NUMBER() = 1, with SELECT * EXCLUDE if you want schema evolution.
6. Split monolithic tables — joins first, aggregations next.
7. Set INITIALIZATION_WAREHOUSE for a dual-warehouse strategy.
8. Pin REFRESH_MODE explicitly. Discover with AUTO, deploy with INCREMENTAL or ADAPTIVE.
9. Verify with SHOW DYNAMIC TABLES and refresh-history statistics.
10. Consider custom incrementalization only where a SELECT genuinely cannot express the transformation — CDC with delete propagation, stream-static joins, or accumulators. Remember it is incompatible with FROZEN WHERE, so decide which matters more for that table.

Closing

The through-line in this release is that Dynamic Tables gained expressive power without giving up the declarative model. You describe the result and the freshness you need; Snowflake handles incremental maintenance, scheduling, and dependencies.

What changed is that the edges are no longer walls. Immutable history can be frozen instead of endlessly recomputed. Existing data can be adopted instead of rebuilt. Refresh strategy can adapt per run. Where the declarative model genuinely needed an escape hatch — a GDPR deletion inside ten years of frozen history — there is now a narrow, well-defined one. And where a transformation simply cannot be written as a SELECT, custom incrementalization lets you supply the MERGE while Snowflake keeps the scheduling, retries, and dependency tracking.

That last one is the real shift. Dynamic Tables used to force a choice: declarative convenience or imperative control. You can now compose both in one pipeline — declarative tables for the transformations that fit, custom incremental tables for CDC with delete propagation or running accumulators that don’t. Just remember the trade: custom incrementalization hands you the correctness guarantee along with the control.

If you have been holding off on Dynamic Tables because of orchestration complexity or expressibility gaps, most of those blockers are gone. Start with Gen2 warehouses and frozen regions; they deliver the most value for the least change.

All examples verified against Snowflake on AWS us-east-1, August 2026. Feature availability varies by account and region — verify in your own environment before relying on preview features.

Dynamic Tables Just Got Faster and Far More Flexible — PART 3 (end) was originally published in Snowflake Builders Blog: Data Engineers, App Developers, AI, & Data Science on Medium, where people are continuing the conversation by highlighting and responding to this story.