[2026] Snowflake Dynamic Tables: Zero-Copy Backfill + Dual Virtual Warehouses + Adaptive Virtual Warehouses

AI Generated Image

I’m writing this blog right now — before these Snowflake features become the default talking point in every data engineering Slack channel. Dynamic tables already automate your ELT pipelines like magic: declare your transformation logic in SQL, and Snowflake handles the incremental refreshes, dependency graphs, and scheduling. No more cron jobs, Airflow DAGs, or manual orchestration headaches.

But here’s the catch: historical backfills. Want to populate a dynamic table with years of past data? Those “one-time” loads turn your warehouse into a credit blackhole. Full scans on massive datasets, endless compute cycles, and skyrocketing costs — I’ve seen teams burn through $10K+ in a single weekend retrying failed backfills.

Source from snowflake documents-https://docs.snowflake.com/en/user-guide/dynamic-tables-create

Now lets start some basic dynamic tables creation

-- =====================================================
-- COMPLETE DYNAMIC TABLE SETUP WITH DUAL WAREHOUSES & BACKFILL
-- Database: DYNAMIC_DB | Schema: VCTR_SCHMA_DY_TBL
-- =====================================================

-- =====================================================
-- STEP 1: CREATE DATABASE AND SCHEMA
-- =====================================================
CREATE DATABASE DYNAMIC_DB;
-- Creates new database named DYNAMIC_DB for dynamic table demo

USE DATABASE DYNAMIC_DB;
-- Sets DYNAMIC_DB as active database context 

CREATE SCHEMA VCTR_SCHMA_DY_TBL;
-- Creates schema VCTR_SCHMA_DY_TBL to organize all demo objects 

USE SCHEMA VCTR_SCHMA_DY_TBL;
-- Sets VCTR_SCHMA_DY_TBL as active schema context

-- =====================================================
-- STEP 2: CREATE DUAL WAREHOUSES (COST OPTIMIZED)
-- =====================================================
CREATE WAREHOUSE IF NOT EXISTS init_wh 
    WAREHOUSE_SIZE = 'XSMALL' 
    AUTO_SUSPEND = 60 
    AUTO_RESUME = TRUE;
-- Creates small warehouse for initial loads/backfills (fast startup, low cost)

CREATE WAREHOUSE IF NOT EXISTS refresh_wh 
    WAREHOUSE_SIZE = 'SMALL' 
    AUTO_SUSPEND = 300 
    AUTO_RESUME = TRUE;
-- Creates medium warehouse for ongoing refreshes (handles production load)

-- =====================================================
-- STEP 3: CREATE BASE TABLES WITH SAMPLE DATA
-- =====================================================
CREATE OR REPLACE TABLE sales (
    item_id INT,
    ts TIMESTAMP,
    sales_price FLOAT
);

INSERT INTO sales VALUES 
    (1, '2025-12-01 01:00:00', 10.0),
    (1, '2025-12-01 02:00:00', 15.0),  -- This will be corrected in backfill
    (1, '2025-12-01 03:00:00', 11.0),
    (1, '2025-12-02 00:00:00', 11.0),
    (1, '2025-12-02 05:00:00', 13.0);
-- Populates 5 sales records across 2 days for testing

-- =====================================================
-- STEP 4: CREATE BACKFILL TABLE (EXACT SCHEMA MATCH REQUIRED)
-- =====================================================
CREATE OR REPLACE TABLE sales_backfill AS
SELECT 
    item_id, 
    DATE_TRUNC('DAY', ts) AS day,      -- Must match dynamic table column #2
    COUNT(sales_price) AS sales_count, -- Must match dynamic table column #3
    AVG(sales_price) AS sales_avg      -- Must match dynamic table column #4
FROM sales
GROUP BY item_id, DATE_TRUNC('DAY', ts);
-- Creates backfill source with EXACT same columns/order as dynamic table output

UPDATE sales_backfill 
SET sales_count = 3, 
    sales_avg = 12.0 
WHERE item_id = 1 AND day = '2025-12-01 00:00:00';

-- =====================================================
-- STEP 5: CREATE DYNAMIC TABLE #1 - BASIC (DUAL WAREHOUSES)
-- =====================================================
CREATE OR REPLACE DYNAMIC TABLE sales_daily_basic
    TARGET_LAG = '20 minutes'                    -- Max 20min behind source data
    WAREHOUSE = refresh_wh                       -- Production refresh warehouse
    INITIALIZATION_WAREHOUSE = init_wh           -- Initial load uses small/fast WH
    REFRESH_MODE = AUTO                         -- Snowflake auto-selects incremental/full
    INITIALIZE = ON_CREATE                      -- Populate immediately on creation
AS
SELECT 
    item_id, 
    DATE_TRUNC('DAY', ts) AS day, 
    COUNT(*) AS sales_count
FROM sales
GROUP BY item_id, day;
-- Basic dynamic table - dual warehouses optimize cost/performance

-- =====================================================
-- STEP 6: CREATE DYNAMIC TABLE #2 - BACKFILL + IMMUTABLE (ADVANCED)
-- =====================================================
CREATE OR REPLACE DYNAMIC TABLE sales_daily_backfill
    IMMUTABLE WHERE (day <= '2025-12-01 00:00:00')  -- Dec 1 data never changes
    BACKFILL FROM sales_backfill                    -- Zero-copy load from corrected table
    TARGET_LAG = '1 minute'                         -- Near real-time updates
    WAREHOUSE = refresh_wh                          -- Ongoing refresh warehouse
    INITIALIZATION_WAREHOUSE = init_wh              -- Initial/backfill warehouse
    REFRESH_MODE = INCREMENTAL                     -- Optimizes for change tracking
    INITIALIZE = ON_CREATE                         -- Refresh immediately
AS
SELECT 
    item_id, 
    DATE_TRUNC('DAY', ts) AS day, 
    COUNT(sales_price) AS sales_count,
    AVG(sales_price) AS sales_avg
FROM sales
GROUP BY item_id, day;
-- Advanced: Backfills immutable historical data, live computes recent data

-- =====================================================
-- STEP 7: VERIFICATION QUERIES
-- =====================================================
SELECT item_id, 
       TO_CHAR(day, 'YYYY-MM-DD') AS day, 
       sales_count, 
       ROUND(sales_avg, 2) AS sales_avg,
       METADATA$IS_IMMUTABLE                    -- TRUE = backfilled/immutable
FROM sales_daily_backfill 
ORDER BY day;

VERIFICATION QUERIES

SHOW DYNAMIC TABLES IN SCHEMA VCTR_SCHMA_DY_TBL;
-- Lists all dynamic tables with refresh status, lag, warehouse info 

This setup turns what used to be a multi-day nightmare into a fire-and-forget operation. Pipelines stay lean, costs plummet, and your data team’s sanity? Restored.

Dynamic tables were cool; this makes them enterprise-ready. If you’re still hand-rolling backfills, it’s time to upgrade.

Dynamic tables with Adaptive Virtual Warehouse

Adaptive Warehouses Kill Dual Warehouse Complexity — Snowflake Dynamic Tables 2025–26

Adaptive Virtual Warehouse & Dynamic Tables

It is a serverless-like feature that eliminates the difficulty of choosing multi-clustering or dual warehouse configurations for dynamic tables. Use a single adaptive warehouse — it handles both workloads: 1) full initialization loads for historical data, and 2) incremental refreshes like CDC or Snowflake streams. No more guessing cluster counts or warehouse sizes!​

2nd Gen warehouse-

create WAREHOUSE IDENTIFIER('"TEST_GEN2_WH"') COMMENT = '' 
WAREHOUSE_SIZE = 'X-Small' 
AUTO_RESUME = true 
AUTO_SUSPEND = 300 
ENABLE_QUERY_ACCELERATION = true 
QUERY_ACCELERATION_MAX_SCALE_FACTOR = 2 
WAREHOUSE_TYPE = 'STANDARD' 
RESOURCE_CONSTRAINT = 'STANDARD_GEN_2' 
MIN_CLUSTER_COUNT = 1 
MAX_CLUSTER_COUNT = 2 
SCALING_POLICY = 'STANDARD'

-- =====================================================
-- CONVERT TO ADAPTIVE WAREHOUSE (SIMPLE ALTER COMMAND)
-- =====================================================
ALTER WAREHOUSE "TEST_GEN2_WH" 
    SET WAREHOUSE_TYPE = 'ADAPTIVE';
-- Converts existing warehouse to Adaptive Compute - NO DOWNTIME, preserves name/permissions 

As Adaptive Virtual Warehouse is still in private preview , we cant use with snowflake trial version

Adaptive Virtual Warehouse

For more information , please check -https://www.snowflake.com/en/blog/adaptive-compute-smarter-warehouses/

[embed]Snowflake Unveils Next Wave of Compute Innovations For Faster, More Efficient Warehouses and… SAN FRANCISCO - June 3, 2025 - Snowflake (NYSE: SNOW), the AI Data Cloud company, today announced at its annual user…www.snowflake.com

-- =====================================================
-- VERIFY WAREHOUSE TYPE CONVERSION
-- =====================================================
SHOW WAREHOUSES LIKE '%TEST%';
-- Confirms WAREHOUSE_TYPE changed to 'ADAPTIVE' 

DESC WAREHOUSE "TEST_GEN2_WH";
-- Shows detailed properties including WAREHOUSE_TYPE = 'ADAPTIVE' 

-- =====================================================
-- UPDATE DYNAMIC TABLES TO USE ADAPTIVE WAREHOUSE
-- =====================================================
ALTER DYNAMIC TABLE sales_daily_basic 
    SET WAREHOUSE = "TEST_GEN2_WH";     -- Now uses adaptive warehouse
-- Routes queries to Adaptive Compute pool 

ALTER DYNAMIC TABLE sales_daily_backfill 
    SET WAREHOUSE = "TEST_GEN2_WH",
    INITIALIZATION_WAREHOUSE = "TEST_GEN2_WH";
-- Both init and refresh use same adaptive warehouse 

-- =====================================================
-- TEST ADAPTIVE BEHAVIOR
-- =====================================================
SELECT SYSTEM$WAITING_QUERIES();
-- Monitors adaptive scaling in real-time 

ALTER DYNAMIC TABLE sales_daily_backfill REFRESH;
-- Triggers refresh - Adaptive auto-selects optimal cluster/size 

If you have private preview access, try the queries and features above with your dynamic tables right now.

So ditch the ETL drudgery. Let Snowflake handle the heavy lifting.

So that’s all for now — thanks for reading! Stay tuned for my next post with more practical Snowflake tips and the latest features.