Snowflake Dynamic Tables

What's new with Snowflake Dynamic Tables.

1. Smarter incremental refreshes

Snowflake has fine-tuned the incremental engine driving Dynamic Tables. Updates now apply only the changes since the last refresh, making it ideal for large datasets with frequent small updates. Less data is reprocessed, so refreshes are quicker and don't hammer your warehouse as hard.

Why it matters: app devs get fresher data for UIs without lag spikes. Data scientists see faster updates for models. Data engineers save on compute costs.

2. Enhanced downstream lag

The TARGET_LAG = 'DOWNSTREAM' option — where refreshes kick off based on downstream needs — got an upgrade. It's now sharper at handling chained Dynamic Tables (like one feeding another). Updates ripple through more smoothly when you have dependencies.

CREATE OR REPLACE DYNAMIC TABLE downstream_sales
  TARGET_LAG = 'DOWNSTREAM'
  WAREHOUSE  = my_warehouse
AS
  SELECT *
    FROM daily_sales_by_region
   WHERE total_sales > 100;

3. Better monitoring tools

The INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY() view picked up new tricks, including REFRESH_TRIGGER columns. Pair that with tighter Resource Monitor integration and you've got a clearer read on what's eating your credits and why refreshes fire.

SELECT NAME, REFRESH_TRIGGER, STATISTICS, REFRESH_END_TIME
  FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY())
 WHERE NAME = 'DAILY_SALES_BY_REGION';

Why it matters: debug faster — spot whether a refresh tanked from bad SQL or heavy data — and keep your budget in line.

4. Expanded transformation support

You can now use more complex SQL in Dynamic Tables, including CTEs and subqueries. Recent updates (Q4 2024) loosened the old limits, so nested logic doesn't trip you up as much.

CREATE OR REPLACE DYNAMIC TABLE sales_trends
  TARGET_LAG = '1 hour'
  WAREHOUSE  = my_warehouse
AS
  WITH ranked_sales AS (
    SELECT
      region,
      sale_amount,
      ROW_NUMBER() OVER (
        PARTITION BY region
        ORDER BY event_timestamp DESC
      ) AS rn
    FROM sales_events
  )
  SELECT region, sale_amount
    FROM ranked_sales
   WHERE rn = 1;

Why it matters: data scientists can bake in ranking or trend logic. App devs get richer data without extra processing steps.

5. Warehouse swaps on the fly

No need to rebuild a table to change its warehouse anymore. A quick ALTER lets you swap compute power as loads shift — handy when refreshes start dragging.

ALTER DYNAMIC TABLE daily_sales_by_region
  SET WAREHOUSE = bigger_warehouse;

Why it matters: data engineers can tweak performance without downtime — keeps things humming.