Dynamic Tables Just Got Faster and Far More Flexible — PART 1
This title could be clearer and more informative.Try out Clickbait Shieldfor free (5 uses left this month).
Snowflake's Dynamic Tables received a major update yielding up to 2.8x faster refreshes on Gen2 warehouses for common patterns like aggregates, SCD-1 deduplication, CLUSTER BY, and joins. Beyond speed, Dynamic Tables gained new capabilities: DELETE and MERGE support, FROZEN WHERE regions that skip recomputation on historical data, PRIMARY KEY RELY for trusted change detection (which also lifts the old restriction on incremental tables depending on full-refresh ones), and SELECT * EXCLUDE for automatic schema evolution. The piece walks through four production best practices (chaining tables, separating joins from aggregations, dual-warehouse initialization strategy, and always pinning REFRESH_MODE instead of using AUTO in production) and builds a full bronze-silver-gold medallion pipeline with live, executed SQL examples, including how to verify refresh health via DYNAMIC_TABLE_REFRESH_HISTORY statistics.
Table of contents
A hands-on guide to Snowflake’s 2026 Dynamic Tables updates — with every example run against a live account.Feature availability:Part 1 — Speed: move to Gen2 firstPart 2 — Four design rules worth internalisingPart 3 — Step-by-step: build a complete pipelinePart 4 — Frozen regionsWhat frozen regions buy youA naming detailPlease continue to part 2 hereQuestions this post answers
How much faster are Snowflake Dynamic Tables after the 2026 update, and what do I need to use to get it?
Refreshes are up to 2.8x faster on common patterns, but the gain is specifically tied to Gen2 warehouses. The optimized patterns are top-level aggregate functions, QUALIFY ROW_NUMBER()/RANK() = 1 deduplication, CLUSTER BY operations, and joins. Moving an existing Dynamic Table's warehouse to Gen2 requires no pipeline rewrite, just an ALTER DYNAMIC TABLE SET WAREHOUSE statement. Track platform performance changes like Snowflake's Gen2 Dynamic Tables boost on daily.dev before replatforming pipelines.
Why shouldn't I use REFRESH_MODE = AUTO for a Dynamic Table in production on Snowflake?
AUTO lets Snowflake silently choose between incremental and full refresh, which means a later query change can downgrade a table from incremental to full refresh without any failure or warning, quietly increasing compute cost. The recommended approach is to use AUTO only in development to discover the supported mode, then pin REFRESH_MODE explicitly (e.g. INCREMENTAL) in production so an unsupported construct fails loudly at deploy time. Developers pinning refresh modes to avoid silent cost regressions can follow platform changes like this on daily.dev.
Can a Dynamic Table use SELECT * without breaking on schema changes in Snowflake?
Yes, using SELECT * EXCLUDE (column_name) instead of a bare SELECT * gives automatic schema evolution: columns added to or dropped from the upstream table propagate downstream without redefining the Dynamic Table. This reverses the prior standing advice to never use SELECT * in a Dynamic Table, since naming only the columns to drop avoids the fragility of naming every column to keep. Engineers rethinking old Dynamic Tables guidance can track schema-evolution changes like this via daily.dev.