An Intro to Snowflake Dynamic Tables

This post was written by an engineer at QueryPlane. QueryPlane is an app builder for your database: bring your own postgres db and you can create interactive applications to share with other developers, coworkers or even your customers. If you’re interested in trying it out, get started here.

---

Snowflake dynamic tables are one of the clearest signals of where Snowflake wants data engineering to go: less orchestration code, more declarative pipeline definitions. You define the result you want and the freshness target you can tolerate, and Snowflake takes care of refresh scheduling and dependency handling.

That is appealing, but it also creates confusion. Are dynamic tables a replacement for streams and tasks? Are they just materialized views with better marketing? And when should you not use them?

This guide covers the practical answers.

In this post, we’ll cover:

• What dynamic tables are
• Why teams use them
• How they compare with streams and tasks
• How they compare with materialized views
• Where they fit in real pipelines

What dynamic tables are

Snowflake describes dynamic tables as tables that automatically refresh based on a defined query and target freshness. In practice, you write a query that defines the transformed dataset you want, then Snowflake keeps that table up to date.

The key idea is outcome over orchestration.

Instead of saying:

• run this task every 5 minutes
• consume from this stream
• load this intermediate table
• then run the next task

you say:

• here is the transformed table I want
• here is how fresh it should be

That is why dynamic tables are appealing for teams with complicated warehouse DAGs. They reduce the amount of pipeline plumbing you have to own directly.

Why teams are adopting dynamic tables

The official docs highlight several use cases, and they line up closely with what teams actually struggle with:

• chaining transformations together
• incrementally precomputing joins and aggregations
• simplifying slowly changing dimension workflows
• moving from batch-style logic toward fresher pipelines

Dynamic tables are attractive because they let analysts and data engineers focus more on transformation logic and less on scheduling glue.

For small teams, this can remove a surprising amount of operational burden. For larger teams, it can also reduce the number of hand-maintained task graphs that become fragile over time.

How dynamic tables differ from streams and tasks

Snowflake’s own comparison doc on dynamic tables versus streams and tasks is the right mental model: they solve related problems, but not the exact same way.

Use streams and tasks when you need:

• fine-grained execution control
• procedural logic
• task graphs with explicit scheduling behavior
• exact control over how change data is consumed

Use dynamic tables when you need:

• declarative refresh behavior
• lower orchestration overhead
• clear freshness targets instead of hand-managed schedules
• straightforward transformations built from SQL

The practical tradeoff is control versus simplicity.

Streams and tasks give you more knobs. Dynamic tables remove knobs you may not want to manage anymore.

A simple example

Imagine you ingest raw order events into raw.orders_events and want a cleaner analytics table with the latest state per order.

With a dynamic table, the pattern looks like:

CREATE OR REPLACE DYNAMIC TABLE analytics.current_orders
  TARGET_LAG = '5 minutes'
  WAREHOUSE = transform_wh
AS
SELECT
  order_id,
  ANY_VALUE(customer_id) AS customer_id,
  MAX_BY(status, event_ts) AS status,
  MAX(event_ts) AS updated_at
FROM raw.orders_events
GROUP BY order_id;

You define the table and the freshness target. Snowflake handles the refresh lifecycle.

That is much easier to read than a pipeline spread across streams, tasks, and staging objects, especially for teams where maintainability matters as much as raw flexibility.

Dynamic tables vs materialized views

Dynamic tables and materialized views can look similar from far away because both keep derived data around. The difference is mostly about intent and workflow.

Dynamic tables are better when you want:

• a table-shaped transformation outcome
• more complex pipeline-style logic
• freshness-oriented refresh behavior
• something that behaves like a normal table downstream

Materialized views are better when you want:

• query acceleration for a repeated access pattern
• a simpler optimization-oriented abstraction
• the optimizer to work with a smaller derived structure

Snowflake’s comparison docs make this distinction pretty clear. Dynamic tables are closer to declarative pipelines. Materialized views are closer to performance optimization.

Where dynamic tables work best

Dynamic tables are a good fit for:

• medallion-style warehouse layers
• incrementally refreshed aggregations
• dimensional modeling where freshness matters
• pipelines that currently use a lot of task scheduling boilerplate

They are especially useful when your current orchestration is hard to reason about. If your team has to read three tasks, two streams, and a hand-maintained refresh order just to understand one derived dataset, that is a strong sign dynamic tables may simplify the system.

Where they are not the best fit

Dynamic tables are not always the answer.

You may want to stay with streams and tasks when:

• refresh timing must be controlled very precisely
• downstream actions are not just SQL transformations
• you need procedural branching or complex orchestration behavior
• your team already has mature task-based patterns that are working well

In other words, dynamic tables are a simplification tool. If your workload genuinely needs more control, removing that control is not progress.

Target lag and the cost math behind it

TARGET_LAG is the most consequential setting on a dynamic table. It’s the maximum staleness Snowflake will allow between the source data and the dynamic table — the time between when a row changes upstream and when Snowflake guarantees that change is reflected. The cost implication is direct: a shorter target lag forces more frequent refreshes, and every refresh runs on a warehouse you specify, billed per second.

A worked example. Suppose analytics.current_orders is a 200 GB dynamic table aggregating from a 2 TB base table, refreshed incrementally. Incremental refreshes against this shape typically run for around 30 seconds on an XSMALL warehouse. The relationship between TARGET_LAG and credits-per-day looks roughly like:

TARGET_LAG	Refreshes per day	Approx. warehouse seconds	Approx. credits per day (XSMALL @ 1 credit/hr)
1 minute	1,440	43,200	12.0
5 minutes	288	8,640	2.4
15 minutes	96	2,880	0.8
1 hour	24	720	0.2

A 1-minute lag costs roughly 60x more than a 1-hour lag on the same pipeline, before any warehouse cold-start or minimum billing overhead. The right starting point is to ask the consumers of the table what staleness they actually need — most BI dashboards tolerate 15-30 minutes, most operational reports tolerate hours, and only a thin slice of true-streaming use cases need single-digit minutes. When in doubt, start at 1 hour and lower it when there’s a concrete complaint.

TARGET_LAG = DOWNSTREAM is the other lever worth knowing. Setting an upstream dynamic table’s lag to DOWNSTREAM tells Snowflake to refresh it only when something downstream asks for fresher data, which avoids paying refresh cost on intermediate tables whose only consumer is another dynamic table. In a chain of three or four dynamic tables, DOWNSTREAM on the middle steps can cut total warehouse seconds nearly in half because the chain refreshes top-down on a single schedule instead of each level refreshing independently.

Incremental vs full refresh

Dynamic tables refresh in one of two modes per run, and Snowflake picks which automatically based on the query shape and the available change-tracking metadata.

Incremental refresh (docs) reads only the rows that changed in the source since the last refresh, recomputes the affected rows in the dynamic table, and writes a delta. This is the cheap path. It works on most aggregations, filters, projections, inner joins, and outer joins where both sides have change-tracking, and it’s what makes dynamic tables economical at low lag.

Full refresh reads the entire source table and recomputes the dynamic table from scratch. This happens when the query uses constructs incremental refresh doesn’t support: non-deterministic functions like CURRENT_TIMESTAMP() or RANDOM(), window functions whose frame depends on the whole table, OVER clauses without partition keys, QUALIFY over the full table, or sources that don’t have change-tracking metadata. Full refresh on a large source is expensive — a 2 TB scan every 5 minutes is the kind of thing that shows up in next month’s bill in the worst way.

The way to find out which mode your table uses is to run the table once and check INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY:

SELECT
  name,
  state,
  refresh_action,           -- 'INCREMENTAL' or 'FULL'
  refresh_trigger,
  data_timestamp,
  refresh_start_time,
  refresh_end_time,
  DATEDIFF('second', refresh_start_time, refresh_end_time) AS duration_sec,
  statistics:numInsertedRows::INT  AS inserted,
  statistics:numUpdatedRows::INT   AS updated,
  statistics:numDeletedRows::INT   AS deleted
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
  NAME => 'analytics.current_orders'
))
ORDER BY refresh_start_time DESC
LIMIT 20;

If refresh_action shows FULL when you expected INCREMENTAL, the next step is to find the part of the query that’s blocking incremental mode. The common culprits are CURRENT_TIMESTAMP() calls inside the SELECT list (replace with a refresh-time column from a metadata table), window functions without PARTITION BY (add one), and joins to non-tracked sources like external Iceberg tables not configured with change-tracking. The supported operations doc is the canonical list.

Monitoring refresh history and the dependency graph

Two Snowflake views answer “is my dynamic table healthy” questions, and both are worth knowing intimately.

INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY is the per-refresh log: timestamps, durations, success/failure, the action that ran, the row counts touched. Filter on a name argument to scope to one table, or call it without arguments for an account-wide view (in the ACCOUNT_USAGE schema with a longer retention window). The columns we look at most: state to find failures, refresh_action to confirm incremental mode, and the statistics VARIANT to see how many rows actually changed each refresh — a dynamic table where most refreshes inserted zero rows is a candidate for DOWNSTREAM lag.

INFORMATION_SCHEMA.DYNAMIC_TABLE_GRAPH_HISTORY is the dependency graph snapshot: which dynamic tables depend on which sources, in what order Snowflake plans to refresh them, and what the cumulative lag is. The view is essential for chains of dynamic tables — when downstream consumers complain about stale data, GRAPH_HISTORY shows whether the lag is concentrated in one slow step or distributed across the chain.

A monitoring query we deploy on every account that uses dynamic tables seriously:

SELECT
  name,
  COUNT(*)                                     AS refreshes,
  COUNT_IF(state = 'FAILED')                   AS failed_refreshes,
  AVG(DATEDIFF('second',
              refresh_start_time,
              refresh_end_time))               AS avg_duration_sec,
  MAX(DATEDIFF('second',
              refresh_start_time,
              refresh_end_time))               AS max_duration_sec,
  SUM(statistics:numInsertedRows::INT
      + statistics:numUpdatedRows::INT
      + statistics:numDeletedRows::INT)        AS total_rows_changed
FROM SNOWFLAKE.ACCOUNT_USAGE.DYNAMIC_TABLE_REFRESH_HISTORY
WHERE refresh_start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
GROUP BY name
ORDER BY avg_duration_sec DESC;

Sort by avg_duration_sec to find the dynamic tables eating the most warehouse time, by failed_refreshes to find the ones in trouble, and by total_rows_changed to find tables with high refresh cost relative to actual data change — the prime candidates for raising TARGET_LAG.

A multi-step chain example

A realistic production setup chains dynamic tables together for a medallion-style warehouse. Here’s the shape we see most often, end to end:

-- Bronze: deduplicate raw events into a current-state table
CREATE OR REPLACE DYNAMIC TABLE bronze.orders_current
  TARGET_LAG = 'DOWNSTREAM'
  WAREHOUSE  = transform_wh
AS
SELECT
  order_id,
  ANY_VALUE(customer_id)        AS customer_id,
  MAX_BY(status, event_ts)      AS status,
  MAX_BY(total_cents, event_ts) AS total_cents,
  MAX(event_ts)                 AS updated_at
FROM raw.orders_events
GROUP BY order_id;

-- Silver: enrich with customer attributes
CREATE OR REPLACE DYNAMIC TABLE silver.orders_enriched
  TARGET_LAG = 'DOWNSTREAM'
  WAREHOUSE  = transform_wh
AS
SELECT
  o.order_id,
  o.customer_id,
  c.country,
  c.segment,
  o.status,
  o.total_cents,
  o.updated_at
FROM bronze.orders_current o
JOIN bronze.customers c USING (customer_id);

-- Gold: 5-minute lag because this is what the dashboard reads
CREATE OR REPLACE DYNAMIC TABLE gold.orders_by_country_daily
  TARGET_LAG = '5 minutes'
  WAREHOUSE  = transform_wh
AS
SELECT
  DATE_TRUNC('day', updated_at) AS order_day,
  country,
  segment,
  COUNT(*)                AS order_count,
  SUM(total_cents) / 100  AS gross_revenue
FROM silver.orders_enriched
GROUP BY 1, 2, 3;

A few things to notice. The bronze and silver tables both use TARGET_LAG = 'DOWNSTREAM', which means they only refresh when gold.orders_by_country_daily needs to refresh — Snowflake plans the chain top-down on a single schedule rather than each layer refreshing independently. The gold table sets the lag policy for the whole chain; everything else inherits from it. If a second gold table is added with a 1-hour lag, the bronze and silver tables refresh on whichever cadence is tighter — they’re shared infrastructure.

Snowflake’s planner also collapses the join’s incremental refresh into a single pass when it can, instead of recomputing the enrichment for every row. The result is that the per-refresh cost of the chain is closer to “scan the changed rows of the leaf source once” than “rerun every step independently”.

Error recovery and refresh failures

A dynamic table can fail to refresh for the same reasons any query can fail: a permission revoked from the warehouse role, a source table dropped, a column type changed in a way the query can’t handle, a transient warehouse error. Failures show up as FAILED rows in DYNAMIC_TABLE_REFRESH_HISTORY and as a SUSPENDED state on the table itself after enough consecutive failures.

The recovery path depends on the failure type. For transient warehouse errors, Snowflake retries automatically — if the retry succeeds, the table self-recovers. For permanent errors (a dropped source column, a permission change), the table enters SUSPENDED state and stops trying. From SUSPENDED, you fix the underlying issue and resume:

ALTER DYNAMIC TABLE analytics.current_orders RESUME;

RESUME schedules an immediate refresh, which will be a full refresh if the table has been suspended long enough that the change-tracking metadata has aged out (configurable via MAX_DATA_EXTENSION_TIME_IN_DAYS). For very large tables, this means a paused-and-resumed table can cost significantly more on the resume run than a steadily refreshing table — worth knowing if you’re tempted to suspend pipelines during low-usage windows.

ALTER DYNAMIC TABLE ... REFRESH triggers an immediate one-off refresh outside the schedule. Useful for forcing a recompute after fixing a bug in the query definition or after a manual backfill into a source table. ALTER DYNAMIC TABLE ... SUSPEND is the opposite — pause refreshes without dropping the table. Suspend is the right tool when you’re rewriting a query and want to avoid spending warehouse time on a soon-to-be-replaced definition.

For chains of dynamic tables, a failure in an upstream table cascades — downstream tables won’t refresh past the data timestamp of the failed upstream, because their freshness guarantee depends on the upstream’s freshness. The DYNAMIC_TABLE_GRAPH_HISTORY view shows which step in the chain is the bottleneck, which is essential when you’re staring at a dashboard whose numbers are 4 hours stale and trying to figure out where the chain stopped advancing.

Cost optimization patterns

Five levers we pull on every dynamic table cost review, roughly in order of impact.

Raise TARGET_LAG first. The single biggest knob, and almost always the first thing to check when a dynamic table’s cost spikes. A pipeline that was acceptable at 5 minutes when it ran for 30 seconds per refresh becomes painful when the source grows and refreshes start taking 90 seconds. Doubling the lag halves the cost without any query changes.

Use DOWNSTREAM lag on intermediate tables. In any chain longer than two steps, the middle steps almost never need their own independent refresh schedule. DOWNSTREAM lag ties their refresh to the consumer’s schedule, eliminating redundant warehouse seconds.

Right-size the warehouse. Dynamic tables run on whatever warehouse you specify, but the warehouse size doesn’t change automatically with load. A MEDIUM warehouse refreshing a small dynamic table is paying for compute it doesn’t use. Conversely, an XSMALL refreshing a 2 TB join is taking longer per refresh than it would on a LARGE, and the per-second cost compounds. Look at avg_duration_sec in the monitoring query above — if it’s consistently below 10 seconds, the warehouse is oversized; if it’s consistently above 5 minutes with a tight lag, it’s undersized.

Investigate refreshes that read a lot but write nothing. A dynamic table whose refreshes consistently report inserted = 0, updated = 0, deleted = 0 is paying full read cost for zero useful work. Common causes: a tight lag on a slow-changing source, or a query that doesn’t actually depend on the source’s changes. Either raise the lag or split the table apart so the high-change and low-change pieces have different cadences.

Watch for full-refresh sneakiness. A query change can silently flip a table from incremental to full refresh, multiplying its cost by 10x or more on each run. The monitoring query above catches this if you alert on refresh_action != 'INCREMENTAL' for tables you’ve confirmed should be incremental.

Production pitfalls

Six things that bit teams in the first year of running dynamic tables in production, in rough order of frequency.

Forgetting that TARGET_LAG is the cost knob. Setting TARGET_LAG = '1 minute' because the dashboard “should be fresh” is the most common cause of dynamic table cost spikes. The right starting point is the loosest lag the consumers tolerate, with a written record of what that lag is and why.

Non-deterministic functions in the query. CURRENT_TIMESTAMP(), CURRENT_DATE(), RANDOM(), and most other non-deterministic functions force full refresh. If you need “when this row was processed”, store it as a column on the source row at ingest time rather than computing it in the dynamic table.

Joining to an external table without change-tracking. Joining a Snowflake-native source to an external table (Iceberg, S3 stage, shared inbound table) often disables incremental refresh because the external side has no change-tracking metadata. The fix is to materialize the external side into a Snowflake table on its own dynamic-table schedule, then join the materialized copy.

MAX_DATA_EXTENSION_TIME_IN_DAYS expiring change tracking. Snowflake keeps change-tracking metadata on a base table for a window controlled by MAX_DATA_EXTENSION_TIME_IN_DAYS. If a dynamic table is suspended for longer than that window, the next resume will be a full refresh because the incremental metadata aged out. For pipelines that can be suspended for days, raising the extension window prevents the surprise full refresh.

Warehouse auto-suspend interacting with tight lag. A warehouse that auto-suspends after 60 seconds of idleness will be cold-started by every refresh on a dynamic table with a 5-minute lag. Cold starts add 5-10 seconds per refresh, which on tight lags is a meaningful share of the per-refresh cost. Either set a higher auto-suspend (5 minutes is common for shared dynamic-table warehouses) or use a serverless dynamic table where Snowflake manages the compute.

Dynamic tables outliving their owners. Dynamic tables feel cheap to create — one DDL statement and you’ve got a maintained pipeline — and teams accumulate hundreds of them over a year. Without naming conventions, owner tags, and a periodic audit of which tables still serve a purpose, you end up paying warehouse credits to maintain dashboards no one looks at. Tag every dynamic table with an owner team via object tags, and re-review tables that haven’t been queried in 30 days against ACCOUNT_USAGE.ACCESS_HISTORY.

A good decision rule

Use dynamic tables when all three are true:

1. The output is naturally table-shaped.
2. Freshness matters more than exact scheduling mechanics.
3. You want less orchestration code.

If one of those is false, rethink before defaulting to the feature.

Frequently asked questions

What is a Snowflake dynamic table? A dynamic table is a table whose contents are defined by a SQL query and refreshed automatically by Snowflake to meet a TARGET_LAG you specify. You write the transformation as a single CREATE DYNAMIC TABLE ... AS SELECT ... statement, and Snowflake handles refresh scheduling, dependency ordering, and incremental computation. The result is a regular table downstream consumers can read, with no separate stream, task, or orchestrator wiring.

What is the difference between a dynamic table and a materialized view in Snowflake? Dynamic tables are pipeline-shaped abstractions that can chain into other dynamic tables, support complex SQL including joins and unions across multiple base tables, and refresh on a freshness target you control. Materialized views are query-acceleration abstractions tied to a single base table, automatically maintained on every base-table change, and used by the optimizer transparently. Use dynamic tables for ELT pipelines; use materialized views for hot query patterns against a single large table.

Are dynamic tables a replacement for streams and tasks? For most pipeline use cases, yes — and Snowflake’s own comparison documentation is the right reference. Streams and tasks are still the right tool when you need procedural logic between steps, fine-grained scheduling control, or actions other than SQL transformations (calling stored procedures, branching on results). For straight read-from-source, transform, write-to-target pipelines, dynamic tables remove a meaningful amount of operational code.

What is TARGET_LAG and how does it affect cost? TARGET_LAG is the maximum staleness you accept on the dynamic table — the time between when a row changes upstream and when that change must be visible. A 1-minute lag forces frequent refreshes and consumes more warehouse credits than a 1-hour lag against the same data, because the refresh runs on a warehouse you specify and bills per second. Pick lag based on actual user requirements; treating “fresher is always better” as the default routinely doubles or triples the cost of a pipeline.

Can dynamic tables refresh incrementally? Yes — Snowflake auto-detects which dynamic tables can use incremental refresh based on the query’s structure and the underlying tables’ change-tracking metadata. Most aggregations, filters, and inner joins refresh incrementally. Some patterns force a full refresh: non-deterministic functions, certain window functions over the full table, or joins that don’t have change-tracking on both sides. The INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY view tells you which refresh mode each run used.

Do dynamic tables work across schemas and databases? Yes. A dynamic table can read from base tables, views, or other dynamic tables in any schema or database the warehouse role has access to. Cross-database lineage is tracked automatically, so changing an upstream object will trigger downstream refreshes. The main constraint is that all dependencies must be in the same Snowflake account; cross-account dependencies require data sharing on the source side.

Wrapping up

Snowflake dynamic tables are valuable because they let you describe the transformed dataset you want instead of hand-wiring every refresh step yourself. For a lot of modern warehouse work, that is exactly the right abstraction.

They are not a universal replacement for streams, tasks, or materialized views. But they are a strong option when your team wants simpler, more declarative pipelines with predictable freshness.

If you are working through that decision in a day-to-day Snowflake workflow, our guide to the best Snowflake GUI tools is a useful companion. If you still need more explicit CDC-style orchestration, read our guide to Snowflake streams and tasks. And if cost is the next thing you want to tighten up, the Snowflake warehouse sizing guide covers the credit math, auto-suspend trade-offs, and resource monitors that pair with dynamic-table pipelines. For the upstream side — getting raw data into the source tables a dynamic table refreshes from — our guide to Snowflake stages, COPY INTO, and Snowpipe covers the ingestion shapes that feed declarative pipelines. And when the dynamic tables you’ve built become the canonical metrics surface for the business, our guide to Snowflake Cortex Analyst walks through wiring those tables into a semantic model so business users can query them in natural language.