What you will be able to do
- Explain how a dynamic table refreshes and what target lag does and does not guarantee
- Distinguish standard (non-materialized) views from materialized views by storage, cost and speed
- Recognize constructs that a materialized view definition cannot include
- Decide when a view should be secure, and what securing it costs
1.Dynamic tables: query results that Snowflake keeps current
A dynamic table stores the results of a SELECT query and keeps them up to date. You write the query and set a target lag, and Snowflake works out which base tables the query depends on and refreshes the results on schedule.
CREATE OR ALTER DYNAMIC TABLE dt_orders
TARGET_LAG = '10 minutes'
WAREHOUSE = transform_wh
REFRESH_MODE = INCREMENTAL
AS
SELECT
order_id,
customer_id,
order_date,
TRIM(UPPER(product_name)) AS product_name,
quantity,
unit_price,
quantity * unit_price AS line_total,
order_status
FROM raw_orders
WHERE order_status != 'returned';TARGET_LAG says how far behind the base tables the data is allowed to fall. It is a goal, not a guarantee. If refreshes take longer than expected, the actual lag can exceed the target. On intermediate tables you can set TARGET_LAG = DOWNSTREAM, so they refresh only when the tables that depend on them need fresh data.
REFRESH_MODE sets how each refresh works. INCREMENTAL processes only the rows that changed. FULL recomputes the entire result. AUTO lets Snowflake choose when the table is created. ADAPTIVE works incrementally but reinitializes after large upstream changes. CUSTOM_INCREMENTAL lets you write your own refresh logic in DML.
When dynamic tables read from each other, they form a pipeline. Snowflake infers the dependency graph from the queries and refreshes the tables in dependency order from a consistent snapshot, so you never have to script the execution order. If you want an external tool such as dbt or Airflow to trigger refreshes, set SCHEDULER = DISABLE.
Checkpoint 1 of 7· Put it in order
Put the steps of a dynamic table refresh in order
- 1.Snowflake computes either the changed rows (incremental) or the entire result set (full)
- 2.Snowflake applies the new results to the dynamic table atomically
- 3.Snowflake detects that the base tables have changed
A refresh starts by detecting changes, then computes results according to the refresh mode, then applies them atomically so readers never see a partially refreshed table.
“The new results are applied to the dynamic table atomically, so readers never see a partial refresh.”Source: docs.snowflake.com
Checkpoint 2 of 7· Exam question
An architect wants to explain how Snowflake physically stores a large fact table on disk. Which statement correctly describes the storage layer?
Correct answer: A — Snowflake automatically splits the table into micro-partitions of roughly 50-500 MB uncompressed, storing each column independently for compression and pruning.
- A. This is correct: Snowflake automatically divides table data into contiguous micro-partitions in the 50-500 MB uncompressed range, and within each partition columns are stored and compressed independently, which is what enables column pruning.
- B. This is incorrect because Snowflake never stores table data as one large row-oriented file; data is always divided into many small immutable micro-partitions rather than a single monolithic object.
- C. This is incorrect because micro-partition sizing is not a fixed 1 GB and partitions are not tied to a clustering key value or replicated per warehouse; storage is fully separated from compute.
- D. This is incorrect because table data persists durably in cloud storage independent of any virtual warehouse; warehouses only cache data locally for performance and never own the authoritative copy.
Sources1
2.Standard (non-materialized) views
A dynamic table stores results. A standard view stores only the query. Snowflake has two kinds of view: non-materialized views, usually just called views, and materialized views. A non-materialized view is a named query definition. Snowflake runs the query each time the view is referenced and doesn't keep the results. Any query that returns a valid result can define one: a subset of columns, a range of rows, or a join of several tables. This is the most common kind of view. Because nothing is stored, it costs no storage, but it is slower than a materialized view.
Checkpoint 3 of 7· Check yourself
What happens when a query references a standard (non-materialized) view?
A non-materialized view is only a named query. Its results are produced when the view is referenced and are never kept.
“The results are not stored for future use.”Source: docs.snowflake.com
Checkpoint 4 of 7· Exam question
A BI dashboard runs a query that filters an orders table on `order_date` and selects only three of its forty columns. Why does this query typically complete faster than a `SELECT *` with no filter, even though both touch the same table?
Correct answer: A — Micro-partition metadata lets Snowflake skip partitions whose min/max `order_date` range excludes the filter, and columnar storage lets it read only the three needed columns.
- A. This is correct: each micro-partition stores min/max metadata per column, so the date filter allows partition pruning, and because columns are stored independently within a partition, only the selected columns need to be read.
- B. This is incorrect because Snowflake does not silently rewrite ad hoc queries into materialized views; materialized views must be explicitly created and are refreshed by Snowflake, not conjured from a single query pattern.
- C. This is incorrect because warehouse size and node count are fixed by explicit configuration, not automatically increased because a query contains a `WHERE` clause; the speedup here comes from pruning, not extra compute.
- D. This is incorrect because the orders table is a regular managed table, not an external table, and Snowflake's optimizer is always involved in planning both filtered and unfiltered queries.
Sources2
3.Materialized views
A materialized view is called a view, but in many ways it behaves like a table. Its results are stored, which makes queries faster. In exchange, it needs storage space and ongoing maintenance, and both add cost. Materialized views can be clustered, and the clustering rules are much the same as for tables. A materialized view is also the documented way to speed up queries against an external table.
| View type | Results | Performance | Cost and restrictions |
|---|---|---|---|
| Non-materialized view | Created by running the query when the view is referenced; not stored | Slower than materialized views | No stored results; any valid query expression can define it |
| Materialized view | Stored, almost as though the results were a table | Faster access | Requires storage space and active maintenance; has restrictions that non-materialized views do not |
Because Snowflake has to keep stored results current, materialized views only allow a restricted set of query constructs. A materialized view can't include UDFs, window functions, HAVING, ORDER BY or LIMIT clauses, GROUP BY keys that aren't in the SELECT list, GROUPING SETS, ROLLUP or CUBE, nested subqueries, or the MINUS, EXCEPT and INTERSECT set operators. Only some aggregate functions are supported, such as COUNT, SUM, MIN, MAX and AVG, and aggregates can't be nested. A CREATE MATERIALIZED VIEW statement that uses any of these constructs fails validation.
Checkpoint 5 of 7· Check yourself
A CREATE MATERIALIZED VIEW statement fails validation. Which construct in its definition is a documented cause?
Window functions are on the list of constructs a materialized view can't include. SUM and COUNT are among the supported aggregates.
“A materialized view cannot include: UDFs (this limitation applies to all types of user-defined functions, including external functions). Window functions.”Source: docs.snowflake.com
4.Secure views
Standard and materialized views can both be made secure. Securing a view addresses two weaknesses of non-secure views. First, some internal optimizations for views need access to the base table data, and user code such as a UDF could use that access to reveal data the view is supposed to hide. Secure views don't use those optimizations. Second, anyone can normally see a view's definition, including the underlying table names. A secure view shows its definition only to authorized users, meaning those who have been granted the role that owns the view. For anyone else, the definition doesn't appear in SHOW VIEWS, SHOW MATERIALIZED VIEWS, GET_DDL or the Information Schema VIEWS view.
To create a secure view, add the SECURE keyword to CREATE VIEW or CREATE MATERIALIZED VIEW. To convert an existing view in either direction, set or unset SECURE with ALTER. The protection has a cost: without those optimizations, secure views can run more slowly. Use them for views built for data privacy, not for views that exist only to make queries more convenient.
Checkpoint 6 of 7· Check yourself
Which statement about secure views is correct?
Secure views give up some optimizations to avoid exposing data, which can make them slower. Materialized views can also be secure, and unauthorized users can't see the definition through GET_DDL.
“Secure views do not utilize these optimizations, ensuring that users have no access to the underlying data.”Source: docs.snowflake.com
Checkpoint 7 of 7· Exam question
A 50 TB events table is loaded continuously and most analytical queries filter on `event_timestamp`, but new rows no longer arrive in timestamp order because of a change in the upstream pipeline. Query pruning has degraded noticeably. What should the team do?
Correct answer: A — Define a clustering key on `event_timestamp` so Snowflake's automatic clustering service reorganizes micro-partitions to reduce overlap and restore pruning effectiveness.
- A. This is correct: a clustering key on the filtered column lets Snowflake's automatic clustering service incrementally reorganize micro-partitions to reduce overlapping value ranges, which restores effective pruning on that column.
- B. This is incorrect because temporary tables are dropped at the end of a session rather than being periodically reloaded, and their existence has nothing to do with how rows are ordered within micro-partitions.
- C. This is incorrect because external tables are pruned using partition columns derived from file paths, not by reorganizing data, and converting a loaded table to an external table would abandon Snowflake-managed storage and DML support entirely.
- D. This is incorrect because warehouse size controls compute parallelism for query and load performance, not the physical ordering of rows written into micro-partitions; ordering is a data layout property, not a compute property.
Sources5
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.A dynamic table's target lag guarantees that data is never more stale than the configured value.Why is that wrong?
Target lag is what Snowflake aims for. Actual lag can exceed it when refreshes take longer than expected.
Covered in Dynamic tables: query results that Snowflake keeps current
2.A materialized view is just a view, so like a standard view it costs nothing to store.Why is that wrong?
A materialized view stores its results like a table, and that storage and maintenance add cost.
Covered in Materialized views
3.Making every view secure is good practice, even views that exist only to simplify queries.Why is that wrong?
Secure views are meant for data privacy. They can run more slowly, so they shouldn't be used for views that exist only for convenience.
Covered in Secure views
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“A dynamic table materializes the results of a SELECT query and keeps them up to date.”
↩︎ Dynamic tables: query results that Snowflake keeps current“INCREMENTAL processes only the rows that changed since the last refresh. FULL refreshes the entire result set.”
↩︎ Dynamic tables: query results that Snowflake keeps current“You don’t need to declare dependencies or script the execution order.”
↩︎ Dynamic tables: query results that Snowflake keeps current“actual lag can exceed the target when refreshes take longer than expected.”
↩︎ Exam trap 1“The new results are applied to the dynamic table atomically, so readers never see a partial refresh.”
↩︎ Checkpoint - 2.
“A view is basically a named definition of a query.”
↩︎ Standard (non-materialized) views“Non-materialized views are the most common type of view.”
↩︎ Standard (non-materialized) views“A materialized view’s results are stored, almost as though the results were a table.”
↩︎ Materialized views“This allows faster access, but requires storage space and active maintenance, both of which incur additional costs.”
↩︎ Exam trap 2“The results are not stored for future use.”
↩︎ Checkpoint - 3.
“You can cluster materialized views, as well as tables.”
↩︎ Materialized views - 4.
“Nesting of subqueries within a materialized view. The MINUS, EXCEPT, or INTERSECT set operators.”
↩︎ Materialized views“A materialized view cannot include: UDFs (this limitation applies to all types of user-defined functions, including external functions). Window functions.”
↩︎ Checkpoint - 5.
“the view definition and details are visible only to authorized users”
↩︎ Secure views“To create a secure view, specify the SECURE keyword in the CREATE VIEW or CREATE MATERIALIZED VIEW command.”
↩︎ Secure views“Secure views can execute more slowly than non-secure”
↩︎ Secure views“Secure views should not be used for views that are defined solely for query convenience”
↩︎ Exam trap 3“Secure views do not utilize these optimizations, ensuring that users have no access to the underlying data.”
↩︎ Checkpoint