Introducing Frozen Regions for Snowflake Dynamic Tables
Snowflake Dynamic Tables provide a simple, declarative way to express data pipelines. You define what the table should contain, and Snowflake automatically keeps it up to date, handling incremental updates and refreshes so you can focus on insights instead of maintenance.
Dynamic Tables operate with delayed view semantics: their contents match those of a view with the same definition, but possibly at an earlier point in time as configured by the target_lag parameter. This model is a powerful way to express complex transformation pipelines, but sometimes you need more control over which rows are updated or recomputed.
For example, you might want to:
Delete data from base tables without affecting downstream Dynamic Tables
Update a Dynamic Table’s definition without updating previously computed rows to match the new definition
Avoid reprocessing old records when a dimension table changes
Migrate an existing pipeline to Dynamic Tables without recomputing existing rows
To address these use cases, we are excited to introduce a new constraint for Dynamic Tables. With the FROZEN WHERE (previously IMMUTABLE WHERE) constraint, you can now lock specific portions of your Dynamic Tables so they never change during refreshes. No rows in this marked portion can be updated or deleted, and no new rows can be added to the marked portion. This means less recomputation, which can significantly lower the cost of refreshes.
For example, here’s a simple pipeline that enriches transaction data with a join and then computes time-windowed aggregates. Using this new constraint, aggregates older than a day should be frozen:
CREATE TABLE transactions (customer_id INT, txn_time TIMESTAMP, txn_id INT, amount INT);
CREATE TABLE customer_info (customer_id INT, location TEXT);
CREATE DYNAMIC TABLE location_statistics TARGET_LAG='1 minute' WAREHOUSE=my_wh
FROZEN WHERE (hour < CURRENT_TIMESTAMP() - INTERVAL '1 day')
AS
SELECT location, DATE_TRUNC(HOUR, txn_time) as hour, SUM(amount) as total_amount
FROM transactions JOIN customer_info USING (customer_id)
GROUP BY location, hour;If customer_info is updated, we don’t need to go back and recompute old aggregates, which can significantly reduce the cost of refreshing the Dynamic Table. Additionally, we can now safely delete old data from the transactions table if we have the following Task defined as an example:
CREATE TASK delete_old_txns WAREHOUSE=my_wh SCHEDULE='12 hours' AS
DELETE FROM transactions WHERE txn_time < CURRENT_TIMESTAMP() - INTERVAL '1 week';We no longer will need to pay for storage of the old raw data but can still maintain the historical aggregates in the Dynamic Table.
Building on this concept, Dynamic Tables with FROZEN WHERE specified can also be backfilled from an existing table. This unlocks two important scenarios: migrating existing pipelines to Dynamic Tables and changing definitions of Dynamic Tables without expensive recomputation.
For the first scenario, let’s say we have an existing table containing historical aggregates that we want to include in our location_statistics Dynamic Table, but we no longer have the corresponding raw transaction data. We can create our Dynamic Table with the following:
CREATE DYNAMIC TABLE location_statistics TARGET_LAG='1 minute' WAREHOUSE=my_wh
FROZEN WHERE (hour < CURRENT_TIMESTAMP() - INTERVAL '1 day')
BACKFILL FROM historical_statistics
AS
SELECT location, DATE_TRUNC(HOUR, txn_time) as hour, SUM(amount) as total_amount, AVG(amount) as avg_amount
FROM
transactions JOIN customer_info USING (customer_id)
GROUP BY location, hour;For the second scenario, we want to start computing average transaction amounts in addition to sums. Without the immutability provided in frozen and backfill, we would have had to create an entirely new Dynamic Table. With these new features, we can instead copy forward the data:
-- Clone our dynamic table into a new regular table
CREATE TABLE cloned_location_statistics CLONE location_statistics;
-- Add the new column that we want
ALTER TABLE cloned_location_statistics ADD COLUMN avg_amount FLOAT;
-- If we need to do any updating, such as manually backfilling the average for some time range, do that here by mutating `cloned_location_statistics`
-- Update the definition
CREATE OR REPLACE DYNAMIC TABLE location_statistics
TARGET_LAG='1 minute' WAREHOUSE=my_wh
FROZEN WHERE (hour < CURRENT_TIMESTAMP() - INTERVAL '1 day')
BACKFILL FROM cloned_location_statistics
AS
SELECT location,
DATE_TRUNC(HOUR, txn_time) as hour,
SUM(amount) as total_amount,
AVG(amount) as avg_amount
FROM transactions JOIN customer_info USING (customer_id)
GROUP BY location, hour;In addition to changes to the Dynamic Table, upstream changes can also be made without having to fully reinitialize. In the previous example, without frozen, create-or-replaceing the customer_info table would result in recomputing all of location_statistics, but with frozen, only the mutable region would need to be recomputed.
Finally, frozen can also be used to avoid full recomputation for full refresh Dynamic Tables. In the following example, we enrich a timestamped transaction table (stock_trades) with a changing market data table (market_quotes) using an ASOF JOIN. Because ASOF JOIN is currently not supported for incremental Dynamic Tables, we must specify a full refresh (REFRESH_MODE = FULL). However, by applying a FROZEN WHERE clause for only the recent day of data, we drastically limit the processing scope, allowing the full refresh to perform significantly faster than recomputing the entire history.
CREATE DYNAMIC TABLE enriched_stock_trades
TARGET_LAG = '12 hours'
WAREHOUSE = my_wh
REFRESH_MODE = FULL
FROZEN WHERE (trade_timestamp < CURRENT_TIMESTAMP() - INTERVAL '1 day')
AS
SELECT
t.*,
q.bid_price
FROM stock_trades t
ASOF JOIN market_quotes q
MATCH_CONDITION (t.trade_timestamp >= q.quote_timestamp)
ON t.ticker = q.ticker
WHERE t.trade_timestamp < CURRENT_TIMESTAMP() - INTERVAL '1 day';With this definition, each refresh only needs to compute the most recent day’s worth of data, rather than the entire contents of the Dynamic Table.
In conclusion, Snowflake Dynamic Tables with frozen constraints offer a powerful and flexible solution for managing data pipelines. Now that users can lock specific portions of their tables, they can reduce refresh and recomputation costs, manage evolution of pipelines and easily migrate existing pipelines to Dynamic Tables. We are working on further improvements in this space, such as the ability to modify Dynamic Table definitions in place and additional ways to express immutability with frozen, so stay tuned for future announcements.
Managing Partial Updates with an SCD-1 Pipeline on Snowflake
From SSIS to Snowflake: Modernizing Complex ETL Orchestration with Snowflake AIM
Beyond RAG: Enterprise Search Meets Analytics Over Unstructured Data
