Dynamic Tables Just Got Faster and Far More Flexible — PART 2
This title could be clearer and more informative.Try out Clickbait Shieldfor free (5 uses left this month).
A deep technical walkthrough of Snowflake's Dynamic Tables platform updates, covering DML support (DELETE/UPDATE/MERGE) within frozen regions, adaptive refresh mode (public preview) that lets Snowflake choose between incremental and full recompute, BACKFILL FROM for zero-copy migration of historical data into frozen regions, refresh boundaries (GA) enabling independent freshness schedules and a Dynamic Table → View → Dynamic Table pattern, storage lifecycle policies (GA) for declarative data retention, Apache Iceberg read/write support, and new dbt/Airflow orchestration options that let external schedulers replace TARGET_LAG. Every example was tested against a live Snowflake account, with exact SQL, error messages, and output states shown.
Table of contents
A hands-on guide to Snowflake’s 2026 Dynamic Tables updates — with every example run against a live account.Part 5 — DML on a Dynamic Table (yes, really)Part 6 — Adaptive refresh modeGet Guhaarindam’s stories in your inboxPart 7 — Migrating without reprocessing: BACKFILL FROMPart 8 — The rest of the releasePlease continue to part 3Questions this post answers
Can I run DELETE or MERGE statements directly on a Snowflake Dynamic Table?
Yes, but only on rows inside the table's frozen region. Running DELETE, UPDATE, or MERGE on rows within the mutable (non-frozen) region still fails, and on a Dynamic Table with no frozen region at all, the operation raises a SQL compilation error such as 'Invalid Operation MERGE on Dynamic Table'. This is because the non-frozen region is derived and would be overwritten on the next refresh. See how frozen-region DML changes GDPR deletion and historical corrections on daily.dev.
What does REFRESH_MODE = ADAPTIVE do for a Snowflake Dynamic Table?
Adaptive refresh mode, in public preview, lets Snowflake's heuristics choose between incremental processing and full reinitialization at each refresh based on which is cheaper at that moment. It guards against unexpectedly re-executing expensive functions like Cortex AI Functions or UDFs across full history, and creation fails proactively if the query definition cannot support incremental refresh at all. Compare adaptive versus auto refresh trade-offs before picking a mode on daily.dev.
How does BACKFILL FROM let you migrate historical data into a Snowflake Dynamic Table without reprocessing it?
BACKFILL FROM zero-copy clones existing historical data directly into a Dynamic Table's frozen region, avoiding a full recomputation of years of clean data. It requires a frozen region to be defined on the table, and the resulting table reports refresh_mode = INCREMENTAL, backfill_from set to the source table, and insert_only_inputs = true. Retrofitting frozen regions later still forces the recomputation you were trying to avoid. Track pipeline migration features like this one for Snowflake on daily.dev.