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
- Dynamic table:
TARGET_LAG(60 seconds minimum orDOWNSTREAM), requiredWAREHOUSE,REFRESH_MODEdefaultAUTO, 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.
How it works
Dynamic tables

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;
| Property | Meaning |
|---|---|
TARGET_LAG | The 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 |
WAREHOUSE | The warehouse that runs the refreshes; required |
REFRESH_MODE | AUTO (default) lets Snowflake choose at creation; INCREMENTAL processes only changes; FULL recomputes everything |
INITIALIZE | ON_CREATE (default) fills the table at creation; ON_SCHEDULE waits for the first scheduled refresh |
Key behavior:
TARGET_LAG = DOWNSTREAMon 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 andDOWNSTREAMon 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,RESUMEandREFRESHstop, restart and force a refresh.ALTER ... SET TARGET_LAGchanges the freshness. - Incremental limits. Some query constructs cannot refresh incrementally. With
AUTO, Snowflake falls back to full refresh and records the reason; checkSHOW DYNAMIC TABLESforrefresh_modeandrefresh_mode_reason. - Monitoring.
DYNAMIC_TABLE_REFRESH_HISTORYshows each refresh, its state and its lag; theDYNAMIC_TABLESfunction shows lag status. - Cost. Warehouse compute for refreshes, Cloud Services for scheduling, and storage including Time Travel.
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.
| Allowed | Not allowed |
|---|---|
| One base table (native, external or shared) | Joins, including self joins |
| Filters, projections | Another materialized view, a regular view, a dynamic table or a hybrid table as source |
GROUP BY where every key is in the SELECT list | HAVING, ORDER BY, LIMIT, window functions, nested subqueries |
Aggregates such as SUM, COUNT, AVG, MIN, MAX, APPROX_COUNT_DISTINCT | UDFs, non deterministic functions such as CURRENT_TIMESTAMP, DISTINCT inside aggregates, nested aggregates |
| Querying the view | INSERT, UPDATE, DELETE, MERGE, TRUNCATE on the view, and Time Travel queries |
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
| Need | Streams and tasks | Dynamic table | Materialized view |
|---|---|---|---|
| Style | You write MERGE logic and schedules | Declare the SELECT and a lag | Declare a single table query |
| Joins and complex SQL | Anything SQL or procedures allow | Joins, many aggregates | One table, limited SQL |
| Freshness | You set the schedule | Within the target lag | Always current |
| Compute | Task warehouse or serverless | Warehouse you name | Serverless background |
| Edition | All | All | Enterprise or higher |
| Best for | Custom CDC, procedures, external calls | Multi step transformation pipelines | Speeding up repeated aggregates on one table |
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 orDOWNSTREAM), requiredWAREHOUSE,REFRESH_MODEdefaultAUTO, 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.