Data Movement · Topic 7 of 17

Dynamic Tables and Materialized Views

Compare dynamic tables, materialized views and streams with tasks: TARGET_LAG, refresh modes, DOWNSTREAM lag, materialized view limits and cost.

  • 5 min
  • Includes a diagram
In a nutshell
  • Dynamic table: TARGET_LAG (60 seconds minimum or DOWNSTREAM), required WAREHOUSE, REFRESH_MODE default AUTO, no manual DML.
  • Materialized view: one table, no joins, no window functions, no UDFs, Enterprise Edition, serverless maintenance, always current.
  • Dynamic tables suit multi step pipelines with joins; materialized views speed up repeated single table queries.

What it is

A dynamic table is a table defined by a SELECT query that Snowflake keeps up to date automatically. You declare the result and the freshness you need; Snowflake plans the refreshes.

A materialized view stores the precomputed result of a query on one table, and Snowflake maintains it in the background so queries always see current data. It is a performance tool for repeated, expensive queries, whereas a dynamic table is a pipeline tool for transformation.

Source tablesSELECT definitionDynamic tableTARGET_LAG 10 minutesrefresh on warehouseResultwithin lagBase tablebackground maintenanceMaterialized viewalways currentservesFast queries

How it works

Dynamic tables

Two base tables feed a dynamic table with TARGET_LAG DOWNSTREAM, which feeds a dynamic table with a 10 minute target lag; Snowflake schedules refreshes in dependency order, incremental when possible.

CREATE DYNAMIC TABLE daily_sales
  TARGET_LAG = '10 minutes'
  WAREHOUSE = transform_wh
  REFRESH_MODE = AUTO
AS
  SELECT o.order_date, c.region, SUM(o.amount) AS total
  FROM orders o JOIN customers c ON o.customer_id = c.id
  GROUP BY o.order_date, c.region;
PropertyMeaning
TARGET_LAGThe maximum staleness you accept, from 60 seconds up, or DOWNSTREAM. It is a goal, not a cron schedule, and actual lag can exceed it if a refresh runs long
WAREHOUSEThe warehouse that runs the refreshes; required
REFRESH_MODEAUTO (default) lets Snowflake choose at creation; INCREMENTAL processes only changes; FULL recomputes everything
INITIALIZEON_CREATE (default) fills the table at creation; ON_SCHEDULE waits for the first scheduled refresh

Key behavior:

  • TARGET_LAG = DOWNSTREAM on an intermediate table refreshes it only when a dynamic table that reads from it needs fresh data. Set a real lag on the final tables and DOWNSTREAM on the middle ones.
  • Chains refresh together. Dynamic tables that read other dynamic tables form a graph, refreshed in dependency order from a consistent snapshot, with each refresh applied atomically.
  • No manual DML. You cannot insert, update or delete rows; only refreshes change data.
  • Control. ALTER DYNAMIC TABLE ... SUSPEND, RESUME and REFRESH stop, restart and force a refresh. ALTER ... SET TARGET_LAG changes the freshness.
  • Incremental limits. Some query constructs cannot refresh incrementally. With AUTO, Snowflake falls back to full refresh and records the reason; check SHOW DYNAMIC TABLES for refresh_mode and refresh_mode_reason.
  • Monitoring. DYNAMIC_TABLE_REFRESH_HISTORY shows each refresh, its state and its lag; the DYNAMIC_TABLES function shows lag status.
  • Cost. Warehouse compute for refreshes, Cloud Services for scheduling, and storage including Time Travel.
Exam tip

If a requirement says "a joined, aggregated table must be no more than 15 minutes behind its sources, with the least code", answer dynamic table with TARGET_LAG = '15 minutes'.

Choose full refresh deliberately: when most rows change each cycle, an incremental refresh has no savings, so a long lag or a full refresh is fine.

Materialized views

CREATE MATERIALIZED VIEW sales_by_day AS
  SELECT order_date, SUM(amount) AS total, COUNT(*) AS n
  FROM orders
  GROUP BY order_date;

Facts:

  • Enterprise Edition or higher is required.
  • Maintained by Snowflake in the background with serverless compute. You pay for the stored result and for the maintenance credits, which grow with the volume of base table changes.
  • Always current. A query combines the stored result with any changes not yet processed, so results match the base table.
  • A clustering key can be set on the view independently of the base table.
  • Cost tracking is in MATERIALIZED_VIEW_REFRESH_HISTORY.

Limits are heavily tested.

AllowedNot allowed
One base table (native, external or shared)Joins, including self joins
Filters, projectionsAnother materialized view, a regular view, a dynamic table or a hybrid table as source
GROUP BY where every key is in the SELECT listHAVING, ORDER BY, LIMIT, window functions, nested subqueries
Aggregates such as SUM, COUNT, AVG, MIN, MAX, APPROX_COUNT_DISTINCTUDFs, non deterministic functions such as CURRENT_TIMESTAMP, DISTINCT inside aggregates, nested aggregates
Querying the viewINSERT, UPDATE, DELETE, MERGE, TRUNCATE on the view, and Time Travel queries
Watch out

A materialized view over a table that changes constantly can cost more in maintenance than it saves in query time. Use it when data changes rarely and the same expensive query runs often.

Choosing among the options

NeedStreams and tasksDynamic tableMaterialized view
StyleYou write MERGE logic and schedulesDeclare the SELECT and a lagDeclare a single table query
Joins and complex SQLAnything SQL or procedures allowJoins, many aggregatesOne table, limited SQL
FreshnessYou set the scheduleWithin the target lagAlways current
ComputeTask warehouse or serverlessWarehouse you nameServerless background
EditionAllAllEnterprise or higher
Best forCustom CDC, procedures, external callsMulti step transformation pipelinesSpeeding up repeated aggregates on one table
Need freshness within minutes on joined data?yesDynamic tableNeed custom logic or procedure calls?yesStreams and tasksNeed faster repeated single table aggregates?yesMaterialized view

Common mistakes

Treating target lag as a schedule. Snowflake chooses refresh times to meet the lag; it is not a cron interval.

Setting a short lag on every table in a chain. Use DOWNSTREAM on intermediate tables so only the final table drives refresh frequency.

Joining tables in a materialized view. Joins are not allowed; use a dynamic table instead.

Trying to write to a dynamic table. Only refreshes change it.

Expecting a materialized view on a standard edition account. It needs Enterprise Edition.

What to remember for the exam

  • Dynamic table: TARGET_LAG (60 seconds minimum or DOWNSTREAM), required WAREHOUSE, REFRESH_MODE default AUTO, no manual DML.
  • Materialized view: one table, no joins, no window functions, no UDFs, Enterprise Edition, serverless maintenance, always current.
  • Dynamic tables suit multi step pipelines with joins; materialized views speed up repeated single table queries.
  • Streams and tasks give the most control; dynamic tables give the least code.
  • Refresh and maintenance both cost credits, so lag and change volume drive cost.

Check yourself

  • Which object can maintain a joined and aggregated result with no custom orchestration?

    A dynamic table, with a TARGET_LAG and a warehouse.

  • Why is a join query not valid as a materialized view?

    Materialized views support a single base table and do not allow joins.

  • What does TARGET_LAG = DOWNSTREAM do?

    The table refreshes only when a dynamic table that depends on it needs fresh data.

Topic 7 of 17 in Data Movement

Test yourself on Data Movement

The full Snowflake DEA-C02 question bank has 709 questions, with timed practice exams and an explanation for every answer.

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.