What's New with Dynamic Tables — Faster Refresh

What's New with Dynamic Tables — Faster Refresh

Skip to content

Blog / Product and Technology / What's New with Dynamic Tables: Faster and More Flexible

JUN 15, 2026 / 7 min read Product and Technology What's New with Dynamic Tables: Faster and More Flexible

PD Dutta +1

Thousands of customers turn to Dynamic Tables for a modern and fast approach to data transformations. With end-to-end pipeline latency in minutes and an efficient incremental processing engine, Dynamic Tables provide a modern and scalable approach to autonomous pipelines. Over the past year, Snowflake has shipped a wave of updates that make Dynamic Tables faster, more expressive and more interoperable with the tools you already use.

At Summit, Sergey Labetsik, a senior data engineer at Wind Creek Hospitality, demonstrated how his team was able to deliver food vouchers to guests within a minute of eligibility. By migrating a dbt batch job to a Dynamic Tables pipeline, they cut end-to-end latency to under a minute, a vast improvement from the 30-minute schedule that the job had been running on.

Here's what's new with Dynamic Tables and why it matters for your pipelines.

Benchmarks of up to 2.8x faster refresh performance

Figure 1: Benchmarks showing up to 2.8x faster refresh performance on Dynamic Tables.

Speed is the foundation on which everything else builds. We benchmarked the most popular Dynamic Table patterns from May 2025 to May 2026 and measured up to 2.8x faster refresh performance. This reflects updates we made under the hood to accelerate performance on Dynamic Tables, including top-level aggregate functions, QUALIFY row/rank = 1 (SCD-1), cluster-by operations and joins — all measured on Gen2 warehouses.

These gains come from performance optimizations specifically built for Dynamic Tables paired with Gen2 warehouses. Incremental refreshes process less data, finish sooner and free up compute for the rest of your workload. If you haven't moved your Dynamic Table workloads to Gen2 warehouses yet, now is the time.

Design patterns: How to build well with Dynamic Tables

Let's go back to the basics and review best practices to build efficient pipelines with Dynamic Tables :

Use multiple Dynamic Tables chained together: Break complex pipelines into a chain of two or more Dynamic Tables, each handling one logical step. Many teams use medallion vocabulary: bronze (raw landing), silver ( TARGET_LAG = DOWNSTREAM ), gold (aggregated with a time-based lag). In the world of Dynamic Tables, the bronze layer represents the raw landing table, silver layer is the Dynamic Table where you clean the data, and gold layer is where you enrich the data, ready to be served for your downstream applications.

Decompose joins and aggregations across separate Dynamic Tables: Place joins first, aggregations next. Each step then refreshes incrementally, compounding the efficiency gains and improving the manageability.

Use a dual warehouse strategy: Set INITIALIZATION_WAREHOUSE for reinitializations (full scans, resource-intensive) and a smaller warehouse for ongoing incremental refreshes.

Never use REFRESH_MODE = AUTO in production: Use the auto refresh mode in development to discover whether your pipeline runs incrementally or requires a full refresh. Then set the refresh_mode explicitly in production.

New updates make refreshes even faster

SCD-1 deduplication with QUALIFY row/rank = 1

For CDC pipelines where your base table receives append-only records, QUALIFY ROW_NUMBER() = 1 (generally available) is the cleanest way to incrementally keep only the latest row per business key. The window function picks the correct row irrespective of the ingestion order, handling out-of-order arrival without additional logic.

On top of that, if you use the SELECT * EXCLUDE pattern, it also gives you automatic schema evolution: Columns added to or dropped from the base table automatically propagate downstream without touching the Dynamic Table definition.

SELECT * EXCLUDE _metadata_cols FROM raw_events QUALIFY ROW_NUMBER() OVER ( PARTITION BY id ORDER BY updated_at DESC ) = 1

Primary keys: Elegantly address insert-overwrites

Here's a common story: You have an elegantly running incremental pipeline. Then an INSERT OVERWRITE hits a base table, and it resets the change-tracking metadata. Or, there is a complex aggregation that requires a full refresh. Now every downstream table is reprocessing everything from scratch. You can now address this with a simple PRIMARY KEY RELY constraint (generally available) on the base table.

ALTER TABLE dim_customers ADD PRIMARY KEY (customer_id) RELY;

This tells Snowflake: "Trust this key for change detection. Don't rely on change-tracking columns."

Benefit Details

Pipeline-wide propagation Declare once on the base table; the benefit flows to all downstream Dynamic Tables. Note: Primary Key RELY doesn't apply retroactively. After adding it, run CREATE OR REPLACE on downstream Dynamic Tables to activate the benefit.

Derived keys Snowflake reads your SELECT, GROUP BY columns and QUALIFY ROW_NUMBER() partitions automatically that become the unique key.

Apache Iceberg™ v2 sources Significantly improves update/delete performance for tables in cloud storage.

Breaks cascade dependency Downstream Dynamic Tables stay INCREMENTAL even when reading from a FULL refresh parent.

Adaptive refresh mode

What if you can't use primary keys, but your incremental pipeline occasionally hits conditions where a full recompute would actually be cheaper? That's the problem adaptive refresh mode (public preview) solves.

Think of it as incremental but smarter. Snowflake has built-in heuristics that evaluate at each refresh whether to process incrementally or reinitialize, based on what will give better price-performance at that moment.

CREATE DYNAMIC TABLE my_table REFRESH_MODE = ADAPTIVE TARGET_LAG = '10 minutes' WAREHOUSE = my_warehouse AS SELECT ... FROM source_table;

Guardrails built in for adaptive refresh mode: Expensive functions (Cortex AI Functions, user-defined functions) are never reinitialized unexpectedly. If a Dynamic Table definition can't support incremental refresh, creation fails proactively. This helps reduce unexpected runtime behavior.

Masking and row access policies in incremental Dynamic Tables

If you're in financial services, healthcare or any regulated industry, you're likely using masking or row-access policies. Previously, certain policy functions would...