Snowflake Dynamic Tables: Faster Refreshes and Enhanced Features
Snowflake has released significant performance improvements for Dynamic Tables, achieving up to 2.8x faster refresh times on Gen2 warehouses. New features like QUALIFY row/rank = 1 and PRIMARY KEY RELY constraints enhance incremental processing and data stability. These updates benefit data engineers and architects managing autonomous data pipelines by reducing latency and compute costs, with some features now generally available and others in public preview.
- →SCD-1 deduplication with QUALIFY ROW_NUMBER() = 1
- →PRIMARY KEY RELY constraint for incremental refreshes
- →Adaptive refresh mode for cost-optimized recomputation
- →Frozen regions to skip unchanged historical data
- →BACKFILL FROM for zero-copy cloning into frozen regions
Features (5) ›
- SCD-1 deduplication with QUALIFY ROW_NUMBER() = 1
Dynamic Tables now support incremental deduplication for CDC pipelines using QUALIFY ROW_NUMBER() = 1, which handles out-of-order arrivals and supports automatic schema evolution with the SELECT * EXCLUDE pattern. This feature is generally available.
- PRIMARY KEY RELY constraint for incremental refreshes
A new PRIMARY KEY RELY constraint on base tables allows Dynamic Tables to maintain incremental processing even after an INSERT OVERWRITE or a full refresh of a parent table. This feature is generally available and propagates benefits to downstream tables.
- Adaptive refresh mode for cost-optimized recomputation
Adaptive refresh mode, currently in public preview, intelligently switches between incremental processing and full reinitialization based on cost-performance heuristics at each refresh. Guardrails are in place to prevent unexpected behavior with expensive functions.
- Frozen regions to skip unchanged historical data
Frozen regions, now generally available, allow users to declare historical data that will not change using a predicate. Snowflake skips these frozen rows during refreshes, reducing compute and cost by only recomputing the mutable window of new data.
- BACKFILL FROM for zero-copy cloning into frozen regions
The BACKFILL FROM feature, generally available, enables zero-copy cloning of existing historical data directly into the frozen region of a new Dynamic Table, eliminating the need for recomputation during migration.
Enhancements (1) ›
- Up to 2.8x faster refresh performance for Dynamic Tables
Snowflake has implemented under-the-hood optimizations for Dynamic Tables, including improvements for aggregate functions, QUALIFY row/rank = 1, cluster-by operations, and joins. These optimizations, measured on Gen2 warehouses, result in up to 2.8x faster refresh performance for popular patterns.
https://www.snowflake.com/content/snowflake-site/global/en/blog/whats-new-dynamic-tables-faster-flexible