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.
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.
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.
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.
By default, Dynamic Tables in a pipeline share snapshot isolation where all refreshes are synchronized and read from the same point in time. That’s great for consistency. But sometimes you need independence.
Refresh boundaries (generally available) let you insert a soft break into your directed acyclic graph (DAG), including the Dynamic Table → View → Dynamic Table pattern, which allows subpipelines to operate on their own freshness schedules.
This tells Snowflake: “Trust this key for change detection. Don’t rely on change-tracking columns.”
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.
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.
| 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. |
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 require a full refresh even when the query itself was eligible for incremental processing.
Upon general availability (coming soon), any row access or masking policies that grant full data access to the Dynamic Table owner role are expected to support incremental refreshes so there are no more unnecessary full scans.
Imagine five years of historical order data in your pipeline. On every refresh, Snowflake reprocesses all of it, including the rows from 2020 that will never change. Frozen regions (generally available) let you declare which rows can be frozen with a simple predicate. Snowflake skips frozen rows on every refresh. Only the mutable window with new data gets recomputed.
Building a new pipeline, but already have years of clean historical data? BACKFILL FROM zero-copy clones the existing data directly into the frozen region with no recomputation (generally available).
| Benefit | What it means |
|---|---|
| Pay only for change | Frozen rows are skipped on every refresh. |
| Deletes are ignored | Deleting source rows won’t propagate to frozen output. |
| Dimension stability | Frozen join results don’t recompute even if dimensions change. |
| Full-refresh savings | Full-refresh Dynamic Tables with frozen regions behave like incremental refreshes for historical data. |
| Query evolution | Only new rows are recomputed when you evolve the query. |
Migrate existing pipelines to Dynamic Tables in minutes, not hours, without reprocessing years of data. So if you have historical data, we highly recommend that you use frozen regions from the get-go. We also hear from customers that they occasionally want to catch up with any change to the historical data or have a GDPR use case of deleting specific rows. Now, you can use DML commands into these frozen regions to keep your Dynamic Table up to date without paying the cost of recomputation.
Raw event data has a shelf life. You don’t need three years of raw logs sitting in a hot pipeline table. But purging without breaking downstream aggregates can turn into its own engineering project.
Storage Lifecycle Policies (generally available) let you attach a retention rule with a single clause. Rows expire automatically on their own schedule, independent of refresh. No custom jobs. No DELETE statements. No risk of accidentally reprocessing purged data.
| Use case | Why it helps |
|---|---|
| Slow-moving dimensions | Product catalog or geo lookups don’t block fast-moving orders pipelines. |
| Cross-team pipelines | Team A’s failures don’t cascade to Team B. |
| Data sharing | You control your own refresh schedule, independent of your data provider that you might not have control over. |
Custom incremental Dynamic Tables (generally available soon) move you from a SELECT-only strategy to SELECT / MERGE / INSERT strategy. You get full expressive power while Snowflake still controls execution (managed scheduling, dependency tracking, observability and replication) without sacrificing the performance and control of imperative batch processing.
What if your pipeline needs complex MERGE logic, a stream-static join, soft deletes or running accumulators? Standard SELECT-based Dynamic Tables can’t always express this elegantly, but you still want managed scheduling, retries and monitoring.
Some teams don’t need continuous refresh. They need data ready at 5 a.m., or they’re driving pipelines from dbt or Airflow. TARGET_LAG doesn’t work well for these cases.
New scheduler capabilities:
dbt handles your engineering workflow. Dynamic Tables handle your data freshness. Together, you eliminate the trade-off between pipeline discipline and pipeline performance.
The integration is a drop-in replacement: Most dbt models are already CTAS statements. Convert them to Dynamic Tables with zero logic rewrites. You keep your full dbt lineage graph, test suites and documentation, while you gain automatic skip-on-unchanged-source behavior:
You can read more about this powerful combination in this blog.
These updates add up to a clear message: Dynamic Tables give you more control without more complexity. You can now:
MERGE and INSERT for transformations that need complex logicTry the new capabilities today:
If you’re already running Dynamic Tables, upgrade your warehouse to Gen2 and measure the difference. If you’ve been holding off because of orchestration complexity or expressibility gaps, those blockers are gone.
Forward-Looking Statement
This content contains forward-looking statements, including about our future product offerings, and are not commitments to deliver any product offerings. Actual results and offerings may differ and are subject to known and unknown risks and uncertainties. See our latest 10-Q for more information.
Same refresh modes, same scheduling, same pipeline semantics with an open format output.
Debugging pipelines shouldn’t require reading through refresh history logs line by line. The new Dynamic Tables skill in Snowflake CoCo gives you expert guidance on creating, optimizing, monitoring and troubleshooting Dynamic Tables directly in your IDE.
Describe your goal, mention Dynamic Tables, and the skill gets invoked. Get help with refresh failures, lag tuning, pipeline design and performance optimization fast.
Dynamic Tables are fully compatible with Apache Iceberg on both ends of your pipeline.
| Use case | What you can do with Dynamic Tables |
|---|---|
| Read from Apache Iceberg | Use externally managed Iceberg v2 tables as sources. PRIMARY KEY RELY constraints significantly improve update/delete performance here. |
| Write to Apache Iceberg | Create Dynamic Iceberg Tables with an output in Iceberg format on Snowflake-managed external storage, readable directly by Apache Spark™, Trino and other engines. |
| Pattern | CI-DT Type |
|---|---|
| CDC enrichment with dimension lookup and delete propagation | MERGE |
| Maintain a Top-K leaderboard | MERGE |
| Append-only stream-static join enrichment | INSERT |
| Migrate from Tasks and Streams | MERGE or INSERT |
Dynamic Tables with custom incrementalization can be composed with standard SELECT-based Dynamic Tables in the same pipeline. Common use cases involve:
The foundation for an agentic control plane in life sciences includes four levels of capabilities working together: Infrastructure provides the […]
How teams are building on Snowflake Postgres The Snowflake Online Feature Store is just one example. Companies across the globe […]
Before the spark In early 2025, Snowflake’s marketing organization probably looked pretty similar to its peers in the tech space […]
Our services help both the big and small organisations in the identification of security breaches and provide outclass solutions to prevent any potential harm.