Overview

Work smarter with Cortex Code (CoCo): Snowflake's coding agent ships a /dynamic-tables skill that scaffolds, optimizes, and debugs Dynamic Table pipelines for you. Describe the pipeline you want and let CoCo drive — it's the fastest way to go from idea to a running Dynamic Table.

What You'll Learn

Prerequisites

Load Sample Data

-- =============================================================================
-- LOAD DATA
-- =============================================================================
USE ROLE ACCOUNTADMIN;
USE WAREHOUSE COMPUTE_WH;

-- Create a dedicated database and schema for this guide
CREATE DATABASE IF NOT EXISTS DYNAMIC_TABLES_GUIDE;
CREATE SCHEMA IF NOT EXISTS DYNAMIC_TABLES_GUIDE.DEMO;
USE SCHEMA DYNAMIC_TABLES_GUIDE.DEMO;

CREATE OR REPLACE FILE FORMAT csv_ff TYPE = 'CSV';

CREATE OR REPLACE STAGE tasty_bytes_stage
  URL = 's3://sfquickstarts/tastybytes/'
  FILE_FORMAT = csv_ff;

CREATE OR REPLACE TABLE menu_raw (
  menu_id NUMBER(19,0), menu_type_id NUMBER(38,0), menu_type VARCHAR, truck_brand_name VARCHAR,
  menu_item_id NUMBER(38,0), menu_item_name VARCHAR, item_category VARCHAR, item_subcategory VARCHAR,
  cost_of_goods_usd NUMBER(38,4), sale_price_usd NUMBER(38,4), menu_item_health_metrics_obj VARIANT
);

COPY INTO menu_raw FROM @tasty_bytes_stage/raw_pos/menu/;

SELECT * FROM menu_raw LIMIT 10;

Your First Dynamic Table

CREATE OR REPLACE DYNAMIC TABLE menu_profitability
  TARGET_LAG = '3 hours'
  WAREHOUSE = COMPUTE_WH
  REFRESH_MODE = ADAPTIVE
  AS
  SELECT
    menu_item_id, menu_item_name, truck_brand_name, menu_type, item_category, item_subcategory,
    cost_of_goods_usd, sale_price_usd,
    (sale_price_usd - cost_of_goods_usd) AS profit_usd,
    ROUND(((sale_price_usd - cost_of_goods_usd) / NULLIF(sale_price_usd, 0)) * 100, 2) AS profit_margin_pct,
    CASE
      WHEN sale_price_usd < 5 THEN 'Budget'
      WHEN sale_price_usd BETWEEN 5 AND 10 THEN 'Mid-Range'
      ELSE 'Premium'
    END AS price_tier
  FROM menu_raw
  WHERE sale_price_usd IS NOT NULL AND cost_of_goods_usd IS NOT NULL;

SELECT truck_brand_name, menu_item_name, price_tier, profit_usd, profit_margin_pct
FROM menu_profitability
ORDER BY profit_margin_pct DESC
LIMIT 10;

Building a Pipeline: Layered Dynamic Tables

CREATE OR REPLACE DYNAMIC TABLE dt_category_benchmarks
  TARGET_LAG = 'DOWNSTREAM'
  WAREHOUSE = COMPUTE_WH
  AS
  SELECT item_category, ROUND(AVG(sale_price_usd), 2) AS avg_category_price
  FROM menu_raw
  WHERE sale_price_usd IS NOT NULL
  GROUP BY item_category;

CREATE OR REPLACE DYNAMIC TABLE dt_menu_vs_benchmark
  TARGET_LAG = '1 hour'
  WAREHOUSE = COMPUTE_WH
  AS
  SELECT m.menu_item_name, m.item_category, m.sale_price_usd,
         b.avg_category_price,
         ROUND(m.sale_price_usd - b.avg_category_price, 2) AS price_vs_category_avg
  FROM menu_raw m
  JOIN dt_category_benchmarks b ON m.item_category = b.item_category
  WHERE m.sale_price_usd IS NOT NULL;

SELECT menu_item_name, item_category, sale_price_usd, avg_category_price, price_vs_category_avg
FROM dt_menu_vs_benchmark
ORDER BY price_vs_category_avg DESC
LIMIT 5;

Every refresh sees a single, consistent snapshot. Snowflake evaluates each refresh at one transactional point in time — its data timestamp — so every input reflects all committed base data up to that instant and nothing after. A table that joins several sources can never read a half-updated picture, and across a layered pipeline the whole graph refreshes against a consistent snapshot. Each new result is swapped in atomically: your queries always see the fully-old or fully-new table, never a partial one.

Incremental Refresh

CREATE OR REPLACE PROCEDURE generate_menu_items(num_rows INTEGER)
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
  items_before INTEGER;
  items_after INTEGER;
BEGIN
  SELECT COUNT(*) INTO :items_before FROM menu_raw;
  INSERT INTO menu_raw
  SELECT
    (SELECT COALESCE(MAX(menu_id), 0) FROM menu_raw) + ROW_NUMBER() OVER (ORDER BY RANDOM()),
    menu_type_id, menu_type, truck_brand_name,
    (SELECT COALESCE(MAX(menu_item_id), 0) FROM menu_raw) + ROW_NUMBER() OVER (ORDER BY RANDOM()),
    'New Menu Item ' || ((SELECT COALESCE(MAX(menu_item_id), 0) FROM menu_raw) + ROW_NUMBER() OVER (ORDER BY RANDOM())),
    item_category, item_subcategory,
    cost_of_goods_usd * (0.8 + UNIFORM(0, 0.4, RANDOM())),
    sale_price_usd * (0.8 + UNIFORM(0, 0.4, RANDOM())),
    menu_item_health_metrics_obj
  FROM menu_raw WHERE menu_item_id IS NOT NULL
  ORDER BY RANDOM() LIMIT :num_rows;
  SELECT COUNT(*) INTO :items_after FROM menu_raw;
  RETURN 'Inserted ' || (:items_after - :items_before)::STRING || ' items. Total: ' || :items_after::STRING;
END;
$$;

CALL generate_menu_items(100);

ALTER DYNAMIC TABLE menu_profitability REFRESH;

Refresh Modes

ADAPTIVE is the mode to reach for on messy, real-world data. It refreshes incrementally by default, but the moment a bulk load or large update would make incremental processing more expensive than a full rebuild, it reinitializes automatically — then drops right back to incremental on the next cycle. You get incremental economics on steady data and graceful handling of big swings, with no mode to babysit.

SHOW DYNAMIC TABLES LIKE 'menu_profitability';
-- Inspect the refresh_mode and refresh_mode_reason columns

Monitoring Refreshes

SELECT name, refresh_action, state,
       DATEDIFF('second', refresh_start_time, refresh_end_time) AS duration_seconds
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY())
WHERE name = 'MENU_PROFITABILITY'
ORDER BY refresh_start_time DESC
LIMIT 5;

SELECT name, target_lag_sec, mean_lag_sec, maximum_lag_sec,
       time_within_target_lag_ratio, last_completed_refresh_state, latest_data_timestamp
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLES())
ORDER BY time_within_target_lag_ratio;

SELECT name, state, refresh_action, refresh_end_time
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(ERROR_ONLY => TRUE))
ORDER BY refresh_end_time DESC;

Simplified Pipelines

Replacing a Materialized View

-- Materialized View:
CREATE OR REPLACE MATERIALIZED VIEW menu_summary_mv AS
SELECT
  truck_brand_name, menu_type, item_category,
  COUNT(*) AS item_count,
  ROUND(AVG(sale_price_usd - cost_of_goods_usd), 2) AS avg_profit_usd
FROM menu_raw
WHERE sale_price_usd IS NOT NULL AND cost_of_goods_usd IS NOT NULL
GROUP BY truck_brand_name, menu_type, item_category;

-- Becomes a Dynamic Table (same query, new header):
CREATE OR REPLACE DYNAMIC TABLE menu_summary_dt
  TARGET_LAG = '1 hour'
  WAREHOUSE = COMPUTE_WH
  AS
  SELECT
    truck_brand_name, menu_type, item_category,
    COUNT(*) AS item_count,
    ROUND(AVG(sale_price_usd - cost_of_goods_usd), 2) AS avg_profit_usd
  FROM menu_raw
  WHERE sale_price_usd IS NOT NULL AND cost_of_goods_usd IS NOT NULL
  GROUP BY truck_brand_name, menu_type, item_category;

Replacing Streams and Tasks

The traditional Streams + Tasks pattern

CREATE OR REPLACE STREAM menu_changes_stream ON TABLE menu_raw;

CREATE OR REPLACE TABLE menu_profitability_cdc (
  menu_item_id NUMBER(38,0), menu_item_name VARCHAR, truck_brand_name VARCHAR,
  profit_usd NUMBER(38,4), profit_margin_pct NUMBER(38,2), updated_at TIMESTAMP_NTZ
);

CREATE OR REPLACE TASK update_menu_profitability
  WAREHOUSE = COMPUTE_WH
  SCHEDULE = '3 HOURS'
  WHEN SYSTEM$STREAM_HAS_DATA('menu_changes_stream')
  AS
  MERGE INTO menu_profitability_cdc t
  USING (
    SELECT menu_item_id, menu_item_name, truck_brand_name,
           sale_price_usd - cost_of_goods_usd AS profit_usd,
           ROUND(((sale_price_usd - cost_of_goods_usd) / NULLIF(sale_price_usd, 0)) * 100, 2) AS profit_margin_pct,
           METADATA$ACTION
    FROM menu_changes_stream
  ) s ON t.menu_item_id = s.menu_item_id
  WHEN MATCHED AND s.METADATA$ACTION = 'DELETE' THEN DELETE
  WHEN MATCHED THEN UPDATE SET t.profit_usd = s.profit_usd, t.profit_margin_pct = s.profit_margin_pct, t.updated_at = CURRENT_TIMESTAMP()
  WHEN NOT MATCHED THEN INSERT (menu_item_id, menu_item_name, truck_brand_name, profit_usd, profit_margin_pct, updated_at)
                        VALUES (s.menu_item_id, s.menu_item_name, s.truck_brand_name, s.profit_usd, s.profit_margin_pct, CURRENT_TIMESTAMP());

Approach A: a declarative Dynamic Table

CREATE OR REPLACE DYNAMIC TABLE menu_profitability_dt
  TARGET_LAG = '3 hours'
  WAREHOUSE = COMPUTE_WH
  AS
  SELECT menu_item_id, menu_item_name, truck_brand_name,
         (sale_price_usd - cost_of_goods_usd) AS profit_usd,
         ROUND(((sale_price_usd - cost_of_goods_usd) / NULLIF(sale_price_usd, 0)) * 100, 2) AS profit_margin_pct
  FROM menu_raw;

Approach B: a Custom Incremental Dynamic Table (REFRESH USING)

The refresh logic is a single MERGE or INSERT statement inside REFRESH USING. SELF refers to the dynamic table itself, and CHANGES(...) streams the base table's changes automatically — no offsets to manage.

ALTER TABLE menu_raw SET CHANGE_TRACKING = TRUE;

CREATE OR REPLACE DYNAMIC TABLE menu_profitability_cidt (
  menu_item_id NUMBER(38,0), menu_item_name VARCHAR, truck_brand_name VARCHAR,
  profit_usd NUMBER(38,4), profit_margin_pct NUMBER(38,2), updated_at TIMESTAMP_NTZ
)
  TARGET_LAG = '3 hours'
  WAREHOUSE = COMPUTE_WH
  REFRESH USING (
    MERGE INTO SELF t USING (
      SELECT menu_item_id, menu_item_name, truck_brand_name,
             (sale_price_usd - cost_of_goods_usd) AS profit_usd,
             ROUND(((sale_price_usd - cost_of_goods_usd) / NULLIF(sale_price_usd, 0)) * 100, 2) AS profit_margin_pct,
             act
      FROM (
        SELECT menu_item_id, menu_item_name, truck_brand_name, sale_price_usd, cost_of_goods_usd,
               METADATA$ACTION AS act,
               ROW_NUMBER() OVER (PARTITION BY menu_item_id ORDER BY CASE WHEN METADATA$ACTION = 'INSERT' THEN 1 ELSE 0 END DESC) AS rn
        FROM menu_raw CHANGES(INFORMATION => DEFAULT)
      ) WHERE rn = 1
    ) s ON t.menu_item_id = s.menu_item_id
    WHEN MATCHED AND s.act = 'DELETE' THEN DELETE
    WHEN MATCHED THEN UPDATE SET menu_item_name = s.menu_item_name, truck_brand_name = s.truck_brand_name,
                                 profit_usd = s.profit_usd, profit_margin_pct = s.profit_margin_pct, updated_at = CURRENT_TIMESTAMP()
    WHEN NOT MATCHED AND s.act = 'INSERT' THEN
      INSERT (menu_item_id, menu_item_name, truck_brand_name, profit_usd, profit_margin_pct, updated_at)
      VALUES (s.menu_item_id, s.menu_item_name, s.truck_brand_name, s.profit_usd, s.profit_margin_pct, CURRENT_TIMESTAMP())
  );

INSERT INTO menu_raw (menu_id, menu_type_id, menu_type, truck_brand_name, menu_item_id, menu_item_name, item_category, item_subcategory, cost_of_goods_usd, sale_price_usd, menu_item_health_metrics_obj)
VALUES (9999, 1, 'Snack', 'Test Truck', 9999, 'CIDT Test Taco', 'Main', 'Taco', 2.00, 8.00, NULL);
UPDATE menu_raw SET sale_price_usd = 99.00 WHERE menu_item_id = 9999;

ALTER DYNAMIC TABLE menu_profitability_cidt REFRESH;

SELECT menu_item_id, menu_item_name, profit_usd, profit_margin_pct
FROM menu_profitability_cidt WHERE menu_item_id = 9999;

When to use which

From Python (Snowpark)

from snowflake.snowpark import Session
from snowflake.snowpark.functions import col

session = Session.builder.getOrCreate()

df = (
    session.table("menu_raw")
    .filter(col("sale_price_usd").is_not_null())
    .select(
        col("menu_item_id"),
        col("menu_item_name"),
        col("truck_brand_name"),
        (col("sale_price_usd") - col("cost_of_goods_usd")).alias("profit_usd"),
    )
)

df.create_or_replace_dynamic_table(
    "menu_profitability_snowpark",
    warehouse="COMPUTE_WH",
    lag="1 hour",
    refresh_mode="INCREMENTAL",
)

Common Pipeline Patterns

Stream processing: stream-static joins

CREATE OR REPLACE TABLE order_events (
  order_id NUMBER, menu_item_id NUMBER, quantity NUMBER, event_ts TIMESTAMP_NTZ
) CHANGE_TRACKING = TRUE;

-- Two order events land in the stream
INSERT INTO order_events
  SELECT 1, menu_item_id, 2, '2026-06-17 12:00:00' FROM menu_raw WHERE menu_item_name = 'Ice Tea' LIMIT 1;
INSERT INTO order_events
  SELECT 2, menu_item_id, 1, '2026-06-17 12:01:00' FROM menu_raw WHERE menu_item_name = 'Lemonade' LIMIT 1;

CREATE OR REPLACE DYNAMIC TABLE enriched_order_events (
  order_id NUMBER, menu_item_id NUMBER, menu_item_name VARCHAR,
  quantity NUMBER, revenue_usd NUMBER(38,4), event_ts TIMESTAMP_NTZ
)
  TARGET_LAG = '1 minute'
  WAREHOUSE = COMPUTE_WH
  REFRESH USING (
    INSERT INTO SELF
    SELECT e.order_id, e.menu_item_id, m.menu_item_name, e.quantity,
           e.quantity * m.sale_price_usd AS revenue_usd, e.event_ts
    FROM order_events CHANGES(INFORMATION => APPEND_ONLY) AS e
    JOIN menu_raw AS m ON e.menu_item_id = m.menu_item_id
  );

SELECT order_id, menu_item_name, quantity, revenue_usd
FROM enriched_order_events ORDER BY order_id;

Deduplication & history (SCD Types 1 and 2)

CREATE OR REPLACE TABLE menu_item_history (
  menu_item_id NUMBER, menu_item_name VARCHAR, sale_price_usd NUMBER(38,4), event_ts TIMESTAMP_NTZ
);
INSERT INTO menu_item_history VALUES
  (1, 'Ice Tea', 3.00, '2026-06-01 09:00:00'),
  (1, 'Ice Tea', 3.50, '2026-06-10 09:00:00'),
  (1, 'Ice Tea', 4.25, '2026-06-17 09:00:00'),
  (2, 'Lemonade', 3.50, '2026-06-01 09:05:00');

SCD Type 1 — latest row per key

CREATE OR REPLACE DYNAMIC TABLE dt_menu_scd1
  TARGET_LAG = '1 hour'
  WAREHOUSE = COMPUTE_WH
  AS
  SELECT menu_item_id, menu_item_name, sale_price_usd, event_ts AS last_updated
  FROM menu_item_history
  QUALIFY ROW_NUMBER() OVER (PARTITION BY menu_item_id ORDER BY event_ts DESC) = 1;

SCD Type 2 — full version history

CREATE OR REPLACE DYNAMIC TABLE dt_menu_scd2
  TARGET_LAG = '1 hour'
  WAREHOUSE = COMPUTE_WH
  AS
  SELECT menu_item_id, menu_item_name, sale_price_usd,
         event_ts AS valid_from,
         LEAD(event_ts) OVER (PARTITION BY menu_item_id ORDER BY event_ts) AS valid_to,
         LEAD(event_ts) OVER (PARTITION BY menu_item_id ORDER BY event_ts) IS NULL AS is_current
  FROM menu_item_history;

What about partial updates? The clean SCD Type 1 above assumes each change carries all columns. Real CDC feeds often send only the columns that changed, leaving the rest NULL — and a naive "latest row wins" would wipe good data. Custom incremental dynamic tables solve this by coalescing partial updates against the current state — for the full story, see Managing Partial Updates in an SCD-1 Pipeline on Snowflake.

Dynamic Iceberg Tables

Dynamic Iceberg Tables can read from both Iceberg v2 and v3 source tables, and materialize their output as either version — control it with ICEBERG_VERSION on the table (or the database-level ICEBERG_VERSION_DEFAULT).

CREATE OR REPLACE DYNAMIC ICEBERG TABLE dt_menu_profit_iceberg
  TARGET_LAG = '1 hour'
  WAREHOUSE = COMPUTE_WH
  REFRESH_MODE = INCREMENTAL
  ICEBERG_VERSION = 3
  AS
  SELECT menu_item_id, menu_item_name, truck_brand_name,
         (sale_price_usd - cost_of_goods_usd) AS profit_usd
  FROM menu_raw
  WHERE sale_price_usd IS NOT NULL AND cost_of_goods_usd IS NOT NULL;

SHOW DYNAMIC TABLES LIKE 'dt_menu_profit_iceberg';

Want to go deeper on Iceberg — v3 features, external-engine interop, and catalog options? See the Comprehensive Guide to Apache Iceberg™ v3 Tables.

AI Functions in Dynamic Tables

CREATE OR REPLACE DYNAMIC TABLE dt_menu_ai_classified
  TARGET_LAG = '1 hour'
  WAREHOUSE = COMPUTE_WH
  AS
  SELECT menu_item_id, menu_item_name, item_category AS source_category,
         AI_CLASSIFY(menu_item_name, ['Vegetarian','Meat','Seafood','Dessert','Beverage']):labels[0]::STRING AS ai_predicted_category
  FROM menu_raw
  WHERE menu_item_name IS NOT NULL;

SELECT ai_predicted_category, COUNT(*) AS item_cnt
FROM dt_menu_ai_classified
GROUP BY 1 ORDER BY 2 DESC;

AI functions are non-deterministic, but that doesn't stop you from using them in a Dynamic Table's SELECT. The table still refreshes incrementally — each new row is classified once as it arrives, and existing rows aren't re-scored on every refresh. You get AI enrichment that stays current while only paying to process new data.

Orchestration and DevOps

Coordinating with other work

CI/CD with DCM

DCM (and dbt) manage declarative, SELECT-based Dynamic Tables. Custom incremental dynamic tables (REFRESH USING) are not currently supported by DCM or dbt — manage those with direct SQL or CREATE OR ALTER.

Cleanup

DROP DATABASE IF EXISTS DYNAMIC_TABLES_GUIDE;

Conclusion and Resources

What You Learned

Additional Resources

---