Snowflake Dynamic Tables: A Practical guide to building pipelines that run themselves

If you’ve ever spent a Saturday untangling a failed Airflow DAG, or babysat a multi-terabyte backfill while watching your compute budget evaporate, or hand-written yet another MERGE to fold in a trickle of CDC events — you know the shape of the problem. For most of its history, data engineering has been the work of managing state, wiring up dependencies, and quietly hoping an upstream table doesn't change its schema overnight. We've treated transformations as a list of chores to be scheduled and supervised.

Dynamic Tables change the deal. Instead of describing how and when to run a transformation, you describe what the result should look like and how fresh it needs to be. Snowflake works out the scheduling, the dependency graph, and the incremental micro-batching. You write ordinary SQL; the platform does the babysitting.

This guide walks the whole arc — the core model, the optimizations that matter in production, and the slate of features Snowflake shipped at Summit 2026. To keep it concrete, we’ll follow the data team at Slice & Dice Analytics, the (fictional) data arm of a global pizza chain, as they move their fragile legacy pipelines onto a self-healing Dynamic Table architecture, using Snowflake’s built-in TPC-H sample data so you can run everything yourself.

What actually shipped at Summit 2026

Before the code, some context, because the ground shifted this year.

At Summit 2026 in San Francisco, Snowflake leaned hard into autonomous, AI-ready pipelines, and Dynamic Tables sat near the center of it. The headline number was performance: refreshes are now up to 2.8x faster across aggregates, ranking/SCD-1 patterns, cluster-by operations, and joins, and that improvement is generally available.

The customer proof point that landed with the engineers in the room came from Wind Creek Hospitality. Senior data engineer Sergey Labetsik showed how his team moved a dbt batch job, previously stuck on a 30-minute schedule, onto a Dynamic Tables pipeline and cut end-to-end latency to under a minute. The payoff was operational: guests could receive food vouchers within about a minute of becoming eligible, with no separate streaming stack to run. That’s the part worth sitting with. Real-time operational analytics, without standing up Kafka next to your warehouse.

A few capability upgrades came with it:

• Adaptive Refresh (Public Preview) — pipelines that run incrementally by default but reinitialize automatically when an upstream system does a bulk load.
• Custom Incremental Dynamic Tables — write explicit MERGE or INSERT logic for CDC patterns a plain SELECTcan't express, while Snowflake still handles scheduling and retries.
• Frozen Regions — the feature formerly called immutability constraints, renamed in late May (the old IMMUTABLE WHERE syntax still works), now paired with Storage Lifecycle Policies for moving aged data to cheaper tiers.
• AI Agent Identity — cryptographically verified identities for autonomous agents querying your tables, which extends zero-trust thinking to the agents now reading your data.

We’ll get to all of them. First, the basics.

Picking the right tool

The team’s first real decision isn’t syntax — it’s choosing where Dynamic Tables fit. Snowflake gives you several ways to transform data, and the wrong choice is the difference between a clean pipeline and a maintenance headache.

The classic approach is Streams and Tasks. You land data in a table, attach a Stream to capture row-level changes, create a target table, and schedule a Task that runs a MERGE. That's four objects per pipeline minimum, and the scheduling and retry logic are yours to own.

Materialized Views solve a different problem. They exist to accelerate reads — the optimizer transparently routes BI queries to the precomputed view. Because the data has to stay synchronously current, they’re heavily restricted: no complex joins, no advanced aggregations, single base table only.

dbt incremental models are the analytics-engineering standard, and for good reason — version control, testing, a real development workflow. But dbt leans on an external orchestrator (dbt Cloud, Airflow), and you’re writing Jinja ({% if is_incremental() %}) to manage the merge yourself. If the orchestrator goes down, your data goes stale.

Dynamic Tables sit in the gap between these. You get the declarative feel of a view, the incremental efficiency of a Stream-and-Task, and orchestration with no external moving parts.

For Slice & Dice, the answer falls out of the requirements. They want to join raw orders and customers into a clean Silver layer, then aggregate into a Gold layer for executive dashboards. Complex joins, multi-table dependencies, no need for sub-second latency or outbound API calls. That’s Dynamic Tables.

Setting up: privileges and compute

A bit of foundation before any transformation logic. Dynamic Tables come with their own quirks around access control and cost.

Two warehouses, on purpose

Here’s a trap worth naming early. The first time you build a Dynamic Table over years of history, that initial population is a full scan — heavy memory, heavy I/O, the works. After that, the ongoing refreshes might touch a few thousand changed rows an hour.

Size for the initial build and you’ll bleed money provisioning a large warehouse for tiny hourly updates. Size for the updates and your historical load spills to remote storage and crawls.

The fix is INITIALIZATION_WAREHOUSE. You point a big cluster at the initial build (and any full reinitializations) and a small, cheap cluster at routine incremental maintenance.

-- Establish role and database context
USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS SLICE_ANALYTICS;
CREATE SCHEMA IF NOT EXISTS SLICE_ANALYTICS.PIPELINES;
USE SCHEMA SLICE_ANALYTICS.PIPELINES;

-- Small warehouse for routine incremental refreshes
CREATE WAREHOUSE IF NOT EXISTS WH_INCREMENTAL_XS
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE;

-- Large warehouse for the heavy lifting (initialization)
CREATE WAREHOUSE IF NOT EXISTS WH_INITIALIZATION_XL
  WAREHOUSE_SIZE = 'XLARGE'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE;

The aggressive AUTO_SUSPEND = 60 matters more than it looks. The XL initialization warehouse shuts off a minute after the backfill finishes, so you're not paying for an idle giant.

The owner-role refresh model

Refreshes don’t run as the user who triggered them. They run as the role that owns the table. That has a consequence people learn the hard way: the owner role has to keep USAGE on the warehouses and SELECT on every upstream base table, permanently. Revoke access to a base table from the owner role and the background refreshes start failing — and after five consecutive errors, the table suspends itself.

USE ROLE SECURITYADMIN;
CREATE ROLE IF NOT EXISTS DATA_ENGINEER_PIPELINE;

-- Schema and warehouse access
GRANT USAGE ON DATABASE SLICE_ANALYTICS TO ROLE DATA_ENGINEER_PIPELINE;
GRANT USAGE, CREATE DYNAMIC TABLE
  ON SCHEMA SLICE_ANALYTICS.PIPELINES TO ROLE DATA_ENGINEER_PIPELINE;

-- Usage on both warehouses
GRANT USAGE ON WAREHOUSE WH_INCREMENTAL_XS TO ROLE DATA_ENGINEER_PIPELINE;
GRANT USAGE ON WAREHOUSE WH_INITIALIZATION_XL TO ROLE DATA_ENGINEER_PIPELINE;

-- Source data from the built-in sample dataset
GRANT IMPORTED PRIVILEGES
  ON DATABASE SNOWFLAKE_SAMPLE_DATA TO ROLE DATA_ENGINEER_PIPELINE;

GRANT ROLE DATA_ENGINEER_PIPELINE TO USER CURRENT_USER();
USE ROLE DATA_ENGINEER_PIPELINE;

If you want your ops team to watch the pipeline without being able to change it, grant MONITOR. They get refresh history and scheduling state; they don't get the ability to alter or drop anything.

Building the pipeline: From raw data to real-time insights

Warehouses configured, privileges sorted. Now the Medallion architecture, fed from SNOWFLAKE_SAMPLE_DATA.TPCH_SF10 (roughly 10 GB).

Bronze: earn the incremental merge with RELY

Dynamic Tables lean on primary keys to figure out the cheapest incremental merge path. When you join tables, if Snowflake can’t prove a row is unique, it gives up on the incremental plan and does a full refresh instead — which is exactly what you’re trying to avoid.

So we declare the primary key and tag it RELY. That keyword is you telling the compiler: trust me, this is unique. It's a promise, not a check — Snowflake won't validate it, so if you lie, you'll get wrong results. Used honestly, it unlocks the optimized incremental plan.

-- Bronze tables
CREATE OR REPLACE TABLE raw_orders (
    o_orderkey   NUMBER(38,0),
    o_custkey    NUMBER(38,0),
    o_orderstatus VARCHAR(1),
    o_totalprice NUMBER(12,2),
    o_orderdate  DATE,
    CONSTRAINT pk_raw_orders PRIMARY KEY (o_orderkey) RELY
);

CREATE OR REPLACE TABLE raw_customers (
    c_custkey    NUMBER(38,0),
    c_name       VARCHAR(25),
    c_mktsegment VARCHAR(10),
    CONSTRAINT pk_raw_customers PRIMARY KEY (c_custkey) RELY
);

-- Ingest the historical sample data
INSERT INTO raw_orders
SELECT o_orderkey, o_custkey, o_orderstatus, o_totalprice, o_orderdate
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF10.ORDERS;

INSERT INTO raw_customers
SELECT c_custkey, c_name, c_mktsegment
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF10.CUSTOMER;

Silver: your first Dynamic Table

The team wants a clean, denormalized view of active orders joined to each customer’s market segment, fresh enough for delivery routing — no more than 15 minutes stale.

CREATE OR REPLACE DYNAMIC TABLE dt_silver_active_orders
  TARGET_LAG = '15 minutes'
  WAREHOUSE = WH_INCREMENTAL_XS
  INITIALIZATION_WAREHOUSE = WH_INITIALIZATION_XL
  REFRESH_MODE = AUTO
  INITIALIZE = ON_CREATE
AS
SELECT
    o.o_orderkey    AS order_id,
    c.c_name        AS customer_name,
    c.c_mktsegment  AS market_segment,
    o.o_totalprice  AS order_value,
    o.o_orderdate   AS order_date,
    o.o_orderstatus AS status
FROM raw_orders o
INNER JOIN raw_customers c
    ON o.o_custkey = c.c_custkey
WHERE o.o_orderstatus IN ('O', 'P');  -- Open or Pending

Reading the DDL line by line:

• **TARGET_LAG = '15 minutes'** is your freshness contract. Snowflake watches raw_orders and raw_customers; when either changes, it schedules a refresh to land before the 15-minute window closes.
• **WAREHOUSE / INITIALIZATION_WAREHOUSE** split the work — the big historical join runs on the XL, every micro-batch after that on the XS.
• **REFRESH_MODE = AUTO** lets Snowflake decide. Because this is a plain inner join on declared primary keys, it resolves to incremental and processes only the rows that changed.
• **INITIALIZE = ON_CREATE** builds it immediately instead of waiting for the next scheduled interval.

Gold: scheduling by dependency, not by clock

The executive dashboard sums potential revenue by market segment, but leadership only looks at it in the morning standup. Running a 15-minute compute cycle to keep it perpetually fresh would be money set on fire.

Rather than write a CRON expression, use TARGET_LAG = DOWNSTREAM.

CREATE OR REPLACE DYNAMIC TABLE dt_gold_segment_revenue
  TARGET_LAG = DOWNSTREAM
  WAREHOUSE = WH_INCREMENTAL_XS
  REFRESH_MODE = INCREMENTAL
AS
SELECT
    market_segment,
    COUNT(order_id)  AS total_active_orders,
    SUM(order_value) AS total_pipeline_revenue
FROM dt_silver_active_orders
GROUP BY market_segment;

DOWNSTREAM puts the Gold table to sleep. It burns no scheduled compute. It only wakes up when someone runs ALTER DYNAMIC TABLE dt_gold_segment_revenue REFRESH, or when a downstream table demands fresh data from it.

And Snowflake understands the lineage for free. Because Gold reads from Silver, it builds the DAG behind the scenes and guarantees Silver finishes applying its latest snapshot before Gold starts aggregating. You don’t get torn reads across the layers.

Summit 2026, applied: the messy-data features

The textbook Medallion flow assumes clean, append-only data. Production rarely cooperates. What happens when a CRM dumps a full overwrite? When you need an audit log of deleted rows? Most of the Summit 2026 work on Dynamic Tables exists to handle exactly these cases.

Bulk loads, survived: Adaptive Refresh

Picture the Slice & Dice inventory system. Most of the time it sends a steady drip of row-level updates as ingredients get used — incremental handles that beautifully. But on the first of every month, the supply-chain vendor truncates and does a massive INSERT OVERWRITE.

Lock that table to REFRESH_MODE = INCREMENTAL and Snowflake will dutifully try to diff millions of swapped rows. Computing that differential often costs more memory and time than just rebuilding the table from scratch.

CREATE OR REPLACE DYNAMIC TABLE dt_silver_adaptive_inventory
  TARGET_LAG = '1 hour'
  WAREHOUSE = WH_INCREMENTAL_XS
  REFRESH_MODE = ADAPTIVE
AS
SELECT * FROM raw_inventory;

ADAPTIVE runs cheap incremental batches by default, but checks the size of the incoming changeset before each run. If it spots a bulk overwrite or large delete that makes incremental processing a bad deal, it does a fast full reinitialization for that one cycle, then drops back to incremental for the next hour. (If you've set an INITIALIZATION_WAREHOUSE, the reinit uses it.) No pager goes off. The pipeline sorts itself out.

When SELECT isn’t enough: Custom Incremental logic

Some patterns simply can’t be expressed as a declarative SELECT. An append-only audit trail of historical changes. A CDC feed with soft deletes. Because a Dynamic Table behaves like a delayed view, a row deleted upstream just disappearsdownstream — which is the opposite of what an audit log needs.

CUSTOM_INCREMENTAL is the escape hatch. You write explicit DML — MERGE or INSERT — and Snowflake still owns the scheduling, transactions, and DAG.

Say Slice & Dice wants a permanent log of every canceled order:

CREATE OR REPLACE DYNAMIC TABLE dt_audit_canceled_orders (
    order_id NUMBER(38,0),
    cancellation_timestamp TIMESTAMP_NTZ
)
  TARGET_LAG = '1 day'
  WAREHOUSE = WH_INCREMENTAL_XS
  INITIALIZE = ON_SCHEDULE
REFRESH USING (
    INSERT INTO SELF
    SELECT
        o_orderkey,
        CURRENT_TIMESTAMP()
    FROM raw_orders
    CHANGES(INFORMATION => DEFAULT)
    WHERE METADATA$ACTION = 'DELETE'
);

Worth verifying before you ship: the feature is real and in Public Preview, and the docs describe REFRESH USING with MERGE/INSERT. Confirm the exact INSERT INTO SELF ... CHANGES(...) form against the current syntax for your account before relying on it in production.

Here we’ve swapped the usual AS SELECT for REFRESH USING (INSERT INTO SELF ...). When the scheduler fires, it reads the base table's change-tracking metadata, finds the physically deleted rows, and appends them. That's the procedural control of a Stream and Task with the operating overhead of a single Dynamic Table. Note that without a SELECT to infer from, you have to declare the columns explicitly in the DDL.

Reaching outside Snowflake: Streams on Dynamic Tables

Sometimes the pipeline needs to do something beyond the warehouse — fire a push notification when a VIP cancels, say. Dynamic Tables can’t call external functions; the engine needs deterministic behavior. So you put a Stream on top of the Dynamic Table.

-- Capture net changes from the Silver table
CREATE OR REPLACE STREAM stream_vip_cancellations
ON DYNAMIC TABLE dt_silver_active_orders;

The Stream captures the net inserts, updates, and deletes from each refresh cycle. A regular Task reads from it, filters for VIPs, and calls a stored procedure that hits your alerting API. The shape that works in practice: build the bulk of the pipeline in declarative SQL, and drop into imperative tooling only at the edges where you genuinely need side effects.

Where FinOps and DataOps finally meet

Mature platforms accumulate. A table doing 50 million transactions a day eventually hits a scale where scanning all of history during a failure or schema change is a real cost. Summit 2026 handled this from two directions: maturing immutability constraints into Frozen Regions, and pairing them with Storage Lifecycle Policies.

Freezing compute with Frozen Regions

A Frozen Region tells the engine which historical rows to ignore during refresh. Set the boundary with FROZEN WHEREand you shrink the micro-partitions the warehouse has to scan.

If Slice & Dice knows orders older than six months are legally immutable, freeze them:

ALTER DYNAMIC TABLE dt_silver_active_orders
SET FROZEN WHERE (order_date < DATEADD('month', -6, CURRENT_DATE()));

After that, refreshes only touch the active region — the last six months — and skip the frozen data entirely. There’s a useful asymmetry here, and it’s easy to get burned by the wrong direction: expanding the region (say, to 12 months) updates instantly, but shrinking it exposes previously frozen rows to the engine and forces a full reinitialization. Grow freely; shrink carefully.

(One backward-compat note, since you’ll see both in older code: FROZEN WHERE was called IMMUTABLE WHERE until the late-May rename, and the old keyword still works. SHOW DYNAMIC TABLES even still reports an immutable_where column.)

Cutting storage with Lifecycle Policies

Frozen Regions save compute, but that old data still sits in premium active storage. Storage Lifecycle Policies — now GA and supported on dynamic tables — physically move aged rows into cheaper archive tiers.

Define the rule at the schema level:

CREATE STORAGE LIFECYCLE POLICY slp_archive_cold_orders
AS (ts DATE) RETURNS BOOLEAN -> ts < DATEADD('month', -6, CURRENT_DATE())
ARCHIVE_FOR_DAYS = 365 * 5   -- keep in cold storage for 5 years
ARCHIVE_TIER = COLD;

Then attach it:

ALTER DYNAMIC TABLE dt_silver_active_orders
ADD STORAGE LIFECYCLE POLICY slp_archive_cold_orders ON (order_date);

This runs on its own schedule, asynchronous to the pipeline. Roughly every 24 hours a Snowflake-managed process checks the table and moves matching rows to the COLD tier, where they stay for five years before expiring. Two things to keep in mind: COLD requires an archival period of at least 180 days, and COLD retrieval isn't instant — pulling archived data back can take up to 48 hours, so reserve it for data you genuinely won't query.

The reason this composes cleanly with Frozen Regions: because the archived rows fall inside the FROZEN WHEREboundary, the refresh engine already knows not to look for them. The archive move doesn't break anything. Together they retire the manual purge cron job most teams are still running.

Integrating with dbt

dbt is the reigning champion of version control and testing, and plenty of teams aren’t giving it up. But its model of the world clashes with Dynamic Tables. dbt assumes it owns time — it decides when models rebuild, via dbt run. Dynamic Tables rely on Snowflake's own background scheduler.

Deploy a Dynamic Table with a TARGET_LAG from dbt and here's what happens: dbt issues the DDL, Snowflake registers it, dbt moves straight on to tests — and the actual refresh might land five minutes later. Your tests run against stale data and fail. It's a confusing first failure because nothing is technically broken; the two schedulers just aren't talking.

The fix is to take Snowflake’s scheduler out of the loop and let dbt drive the refresh synchronously, using the dbt-snowflakeadapter:

{{ config(
    materialized='dynamic_table',
    snowflake_warehouse='WH_INCREMENTAL_XS',
    snowflake_initialization_warehouse='WH_INITIALIZATION_XL',
    scheduler='DISABLE',
    refresh_mode='AUTO'
) }}

SELECT
    o.o_orderkey   AS order_id,
    c.c_name       AS customer_name,
    o.o_totalprice AS delivery_revenue
FROM {{ ref('raw_orders') }} o
INNER JOIN {{ ref('raw_customers') }} c
    ON o.o_custkey = c.c_custkey

With scheduler='DISABLE', you cut the table off from Snowflake's background polling. Now dbt run issues a synchronous ALTER DYNAMIC TABLE ... REFRESH, and the DAG waits for Snowflake to finish the merge before moving on.

This is the combination a lot of teams are landing on: dbt for version control, cross-platform dependencies, and data-quality tests; Dynamic Tables as the execution engine underneath, giving you incremental state tracking without a single {% is_incremental() %} macro.

Observability and debugging

Abstract away the procedural steps and debugging changes shape. There’s no explicit MERGE sitting in query history to inspect, so you rely on the native table functions instead. The one to know is DYNAMIC_TABLE_REFRESH_HISTORY.

SELECT
    name,
    state,
    refresh_action,
    data_timestamp,
    DATEDIFF('millisecond', refresh_start_time, refresh_end_time) / 1000.0
        AS duration_seconds
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
    NAME_PREFIX => 'SLICE_ANALYTICS.PIPELINES.DT_',
    ERROR_ONLY => FALSE
))
ORDER BY refresh_start_time DESC;

This is the telemetry for your pipeline. refresh_action separates initial CREATION runs from automated SCHEDULED work from MANUAL triggers. Dig into the JSON for a successful refresh and you'll find the latency breakdown — queuedTimeMs, compilationTimeMs, executionTimeMs — alongside numInsertedRows and numDeletedRows.

The expensive mistake: accidental reinitialization

The costliest thing you can do to a Dynamic Table is trigger a full reinitialization you didn’t mean to. Because the lineage is strict, running CREATE OR REPLACE TABLE on an upstream landing table — depressingly common in CI/CD — breaks the object identifier graph. Snowflake notices, and to protect integrity it cascades a full rebuild across every downstream table. One careless drop, years of recompute.

Avoiding it comes down to schema-evolution discipline. Adding or dropping columns on base tables propagates incrementally through the DAG without a reinit, so prefer those over drop-and-recreate.

And if you do end up needing to rebuild a large pipeline, don’t make the system recompute history from zero. Use BACKFILL FROM, which stands up the new Dynamic Table from a zero-copy clone of already-materialized data and picks up where the old one left off — no compute penalty for the history you already paid for.

Where this leaves you

The shift from imperative orchestration to declarative pipelines is a real one, not just a rebrand. You stop writing CRON schedules, tracking watermark offsets, and untangling failed merges, and you start describing the result you want.

The Summit 2026 work closed most of the gaps that used to keep serious teams away. Adaptive Refresh handles the bulk loads that broke rigid incremental pipelines. Custom Incremental covers the CDC and audit patterns a SELECT can't reach. Frozen Regions and Storage Lifecycle Policies turn historical-data cost from a quarterly cleanup into a policy you set once.

None of this removes the need to understand your data — the owner-role permissions, the RELY promise, the shrink-vs-grow asymmetry on frozen regions, the dbt scheduler clash all still bite if you ignore them. But the busywork is mostly gone. Whether you're routing pizza deliveries in real time or serving an AI agent querying an enterprise lake, the pattern is the same: stop describing the steps, declare the destination, and let the platform do the driving.

Found this useful? Follow for more practical Snowflake content. Questions, corrections, or things I missed? Drop them in the comments — I read everything.