Snowflake View Types – Standard, Materialized & Secure
Snowflake has three view types: standard, materialized, and secure. The exam tests which one solves which problem, plus the edition and sharing rules attached to each type.
| Term | Plain meaning |
|---|---|
| Standard view | A saved SELECT. No data stored. Every query runs the underlying SQL fresh. |
| Materialized view (MV) | A view whose result is stored. Snowflake refreshes it in the background when the base table changes. Costs serverless credits. |
| Secure view | A SECURE flag added to a standard or materialized view. Hides the DDL and disables certain optimizer paths to prevent indirect data leaks. |
| Predicate pushdown | An optimization that pushes outer-query filters down into the view’s base scan. Standard views allow it. Secure views block it. |
| Serverless refresh | Background compute that Snowflake runs to keep an MV in sync with its base table. Billed separately from your warehouse. |
Today’s Concept
Micro-Concept 1: The Three-View Comparison
One table to internalise. Most Day 13 exam questions can be answered by walking these rows in your head.
| Property | Standard view | Materialized view | Secure view |
|---|---|---|---|
| Edition required | All editions | Enterprise+ | All editions (the SECURE flag is on top of either of the other two) |
| Stores data? | No. Recomputes every query | Yes. Physical result set | Inherits from base type |
| Refresh | n/a (live) | Automatic on base-table change, serverless credits | n/a (if standard) / serverless (if materialized) |
| Predicate pushdown / optimizer | Yes | Yes | No. Disabled to protect base data |
| DDL visible to non-owners? | Yes (via SHOW, GET_DDL, INFORMATION_SCHEMA) | Yes | No. Only the owner sees the text |
| Allowed in shares? | No by default (non-secure views can’t be shared; this is what the exam tests) | No unless made SECURE | Yes |
| Multi-table joins in definition? | Yes | No. Single base table only | Inherits |
| HAVING / ORDER BY in definition? | Yes | No | Inherits (Yes if base is standard; No if base is MV) |
| Aggregates allowed? | Yes | Limited subset of aggregate functions | Inherits |
Micro-Concept 2: Standard Views (Live Queries with Saved Names)
This is the default. A standard view stores no data. You define a SELECT once, then reference the view by name. Each reference re-runs the underlying SQL against the current base data.
Use it for three jobs:
- Hiding a complex join behind a simple name end users can query
- Applying consistent calculated columns and renames in one place
- Exposing a narrow column or row subset so consumers never touch the underlying table
Zero extra storage. Compute is paid at query time, every time. Standard views also support recursion: a view can reference itself. Useful for hierarchies and rarely tested directly on COF-C03.
Micro-Concept 3: Materialized Views (Pre-Computed Results)
A materialized view physically stores its result set. When you query the MV, Snowflake reads the stored copy. When the base table changes, Snowflake refreshes the MV in the background using serverless compute. Your warehouse is not involved in the maintenance.
The textbook MV pattern is narrow: the same expensive aggregation, run many times against a base table that changes infrequently. Get all three conditions and an MV pays for itself. Miss any one and you’re better off with a standard view, a Dynamic Table, or just a bigger warehouse.
When to use:
- Same expensive aggregation runs many times per day
- Base table changes infrequently relative to read frequency
- The query pattern is narrow enough to fit MV constraints
Hard restrictions. Every one of these is exam-testable:
- Enterprise Edition or higher required. Standard Edition cannot create them at all.
- One base table only. No joins, including self-joins.
- No
HAVING, noORDER BY, noLIMITin the definition. - No window functions. No UDFs.
- Limited aggregate subset (SUM, COUNT, MIN, MAX, AVG are fine; nested aggregates and DISTINCT-with-aggregates are not).
- No subqueries in some positions, no GROUPING SETS / ROLLUP / CUBE.
Refresh and cost: Maintenance runs on Snowflake-managed serverless compute, not your warehouse. The credits show up in SNOWFLAKE.ACCOUNT_USAGE.MATERIALIZED_VIEW_REFRESH_HISTORY and as a separate line on your bill. Heavy DML on a base table translates directly to a bigger MV refresh bill, because every change triggers maintenance.
In training sessions I have run, the question often raised is whether the refresh can be throttled. It cannot. The trigger is the base-table change, not a schedule.
“Always current” guarantee: If a refresh is behind when you query the MV, Snowflake combines the stored result with the newer base-table partitions on the fly. The query result is always current. You never see stale data. The only effect of a lagging refresh is slightly more compute on the query.
Micro-Concept 4: Secure Views (Privacy at the Cost of Speed)
Add the SECURE keyword to a standard or materialized view and Snowflake changes two behaviours:
- The view definition is hidden from anyone other than the owner.
SHOW VIEWSreturns null for the text column.GET_DDLis blocked.INFORMATION_SCHEMA.VIEWShides the body. - Certain optimizer paths are disabled so query behaviour cannot indirectly reveal data the user is not allowed to see. Predicate pushdown into the base scan is the standard example.
The optimizer change is what makes secure views slower. With pushdown disabled, your WHERE filter does not reach the base table early. Snowflake reads more partitions than it would for an equivalent standard view.
That extra cost is the price of the privacy guarantee. Use secure views when you need that guarantee, not as a default.
Snowflake docs make the same point directly: do not use secure views for queries created purely for convenience. Use them when the view sits between the user and data the user should not be able to infer.
Micro-Concept 5: When Secure Views Are Required
Two situations force the SECURE flag:
- Cross-database sharing. A share is restricted to objects in a single database. To share data that spans more than one database, the provider creates a secure view in the shared database. The SELECT inside that view references the cross-database tables. This is the verbatim Q1 below and shows up on the live exam in roughly this form.
- Any view in a share, by default. The default behaviour of
GRANT SELECT ON VIEW … TO SHAREonly accepts secure views. The same applies to secure materialized views and secure UDFs. Snowflake added an opt-inSECURE_OBJECTS_ONLY = FALSEproperty on shares for cases where you want the query optimization gains of a non-secure view. The C03 exam tests the default. If a question asks what is needed to share a view, the answer is “make it secure.”
Micro-Concept 6: Pick the Right View (Decision Tree)
Walk these questions in order. Most exam scenarios answer at step 1 or 2.
- Sharing this with another account? → Secure view (or secure MV).
- Same expensive aggregate, queried many times, against slow-changing data? → Materialized view (Enterprise+). Check the restrictions fit.
- Neither of the above? → Standard view.
- Need a multi-step pipeline or joined aggregates an MV can’t express? → Dynamic Table (Day 12) or a regular table maintained by a Task.
Micro-Concept 7: Views Are Read-Only
No INSERT, UPDATE, DELETE, or TRUNCATE directly against any view. To change the data, change the base table. The exam sometimes drops this in as a single-clause distractor on a longer scenario. Easy point if you remember it.
Cheat Sheet
| Concept | What to remember |
|---|---|
| Standard view | No data stored. Live recompute. All editions. Allows joins, ORDER BY, HAVING. |
| Materialized view | Enterprise+. Stored result. Auto-refreshed with serverless credits. Single base table, no joins, no HAVING/ORDER BY. |
| Secure view | The SECURE flag on top of either. Hides DDL. Blocks optimizer pushdown → slower. |
| Sharing | By default, only secure views (or secure MVs / UDFs) can be shared (exam default). Newer SECURE_OBJECTS_ONLY=FALSE opt-in exists but is not what C03 tests. |
| Cross-database share | Requires a secure view in the shared database that references the cross-DB objects. |
| MV refresh | Serverless compute, separate credits. Track via MATERIALIZED_VIEW_REFRESH_HISTORY. |
| MV correctness | Always current. Snowflake combines stored MV with newer base data on the fly if refresh is behind. |
| Views are read-only | No DML against any view type. Update the base table. |
| Secure view = slower | Predicate pushdown disabled. Use only when privacy / share requirements demand it. |
| MV vs Dynamic Table | MV = narrow SQL subset, single-table aggregates. Dynamic Table = full SQL, multi-step pipelines, declarative. |
Three traps to know. (1) “Materialized views are available on any edition” is FALSE. Enterprise+ only. If a scenario mentions Standard Edition and asks for an MV-based answer, the correct option is always “upgrade the edition” or “use a different approach”. (2) “Secure views are faster because they store results” is FALSE. Secure views are usually slower than the equivalent standard view because the optimizer cannot see through them. They store nothing on their own. Materialized views store. Secure protects. Two different flags doing two different jobs. The distractor conflates them. (3) “A standard view can be added to a share” is FALSE by default on COF-C03. Only secure views are shareable in the default case. Cross-database sharing specifically requires a secure view in the shared database. The SECURE_OBJECTS_ONLY=FALSE opt-in exists in current Snowflake but the exam still tests the default.
Hands-On Lab
lab_xs warehouse; day10_orders from Day 10.Create a standard view on day10_orders.
USE ROLE SYSADMIN;
USE WAREHOUSE lab_xs;
ALTER WAREHOUSE lab_xs RESUME IF SUSPENDED;
CREATE OR REPLACE VIEW vw_standard AS
SELECT O_ORDERDATE, O_ORDERSTATUS, COUNT(*) AS order_count
FROM day10_orders
GROUP BY O_ORDERDATE, O_ORDERSTATUS;
SELECT * FROM vw_standard
WHERE O_ORDERDATE = '1995-03-15';Try a materialized view with a JOIN, and watch it fail.
-- This will ERROR — MVs cannot reference multiple base tables
CREATE OR REPLACE MATERIALIZED VIEW mv_bad AS
SELECT o.O_ORDERDATE, c.C_NAME
FROM day10_orders o
JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER c ON o.O_CUSTKEY = c.C_CUSTKEY;Create a valid materialized view. Single-table aggregate, no banned clauses.
CREATE OR REPLACE MATERIALIZED VIEW mv_orders AS
SELECT O_ORDERDATE, O_ORDERSTATUS, COUNT(*) AS order_count
FROM day10_orders
GROUP BY O_ORDERDATE, O_ORDERSTATUS;
-- Query the MV. The result is read from the stored MV, not the base table.
SELECT * FROM mv_orders
WHERE O_ORDERDATE = '1995-03-15';
-- Inspect refresh history
SELECT NAME, STATE, REFRESH_TRIGGER, REFRESH_START_TIME, REFRESH_END_TIME, CREDITS_USED
FROM TABLE(INFORMATION_SCHEMA.MATERIALIZED_VIEW_REFRESH_HISTORY(
DATE_RANGE_START => DATEADD('hour', -1, CURRENT_TIMESTAMP())))
WHERE NAME = 'MV_ORDERS'
ORDER BY REFRESH_START_TIME DESC;lab_xs. In Query Profile, the MV query scans the MV’s stored partitions, not the full day10_orders set. That partition reduction is where the speed-up comes from.Make a secure view and prove its DDL is hidden from a different role.
CREATE OR REPLACE SECURE VIEW vw_secure AS
SELECT O_ORDERDATE, O_ORDERSTATUS, SUM(O_TOTALPRICE) AS revenue
FROM day10_orders
GROUP BY O_ORDERDATE, O_ORDERSTATUS;
-- As the OWNER (SYSADMIN), DDL is visible:
SELECT GET_DDL('view', 'vw_secure');
-- As a different role, the secure-view definition is hidden.
-- (Set up a quick test role for the demo.)
USE ROLE ACCOUNTADMIN;
CREATE ROLE IF NOT EXISTS day13_reader;
GRANT USAGE ON DATABASE CURRENT_DATABASE() TO ROLE day13_reader;
GRANT USAGE ON SCHEMA CURRENT_SCHEMA() TO ROLE day13_reader;
GRANT SELECT ON VIEW vw_secure TO ROLE day13_reader;
GRANT ROLE day13_reader TO USER CURRENT_USER();
USE ROLE day13_reader;
SELECT * FROM vw_secure LIMIT 5; -- works
SELECT GET_DDL('view', 'vw_secure'); -- returns NULL / hidden body
SHOW VIEWS LIKE 'vw_secure'; -- text column blank for non-ownerday13_reader, but the DDL is hidden. Results visible, logic invisible. That separation is the whole reason secure views are the only views you can put in a share by default.Inspect IS_SECURE. This is the metadata column the exam may name directly.
USE ROLE SYSADMIN;
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, IS_SECURE
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME IN ('VW_STANDARD','VW_SECURE');
-- vw_standard: IS_SECURE = NO
-- vw_secure: IS_SECURE = YESINFORMATION_SCHEMA.VIEWS. They sit in INFORMATION_SCHEMA.TABLES with TABLE_TYPE = 'MATERIALIZED VIEW'. For MV secure status, use SHOW MATERIALIZED VIEWS.)Cleanup. Drop today’s three views and the test role. Keep day10_orders. The dependency map still needs it.
DROP VIEW IF EXISTS vw_standard;
DROP VIEW IF EXISTS vw_secure;
DROP MATERIALIZED VIEW IF EXISTS mv_orders;
DROP ROLE IF EXISTS day13_reader;
ALTER WAREHOUSE lab_xs SUSPEND;
-- KEEP day10_orders — needed for Days 26, 31, 32, 35, 39MATERIALIZED_VIEW_REFRESH_HISTORY against your active MV list each month.Snowflake Documentation
External References
CREATE syntax and refresh-history monitoring.
Practice Questions
Options:
A. External tables
B. Secure views
C. Materialized views
D. User-Defined Functions (UDFs)
Why B: A share is restricted to a single database. To expose data spanning more than one database, the provider creates a secure view inside the shared database. Its SELECT references objects in the other databases. By default, non-secure views cannot even be granted to a share. The secure view is the only mechanism for cross-database sharing on the exam.
Why not A: External tables can be shared, but they don’t solve the cross-database problem. Each external table still lives in one database.
Why not C: An MV is restricted to a single base table and cannot span databases via joins. Even a secure MV doesn’t fix the cross-database scenario.
Why not D: Secure UDFs can be shared, but they are functions, not the standard mechanism for sharing cross-database table data.
Options:
A. Materialized views require Enterprise Edition or higher
B. Materialized views can join two or more base tables
C. Refresh of materialized views uses serverless credits, separate from warehouse credits
D. Materialized views never need to be refreshed because they are pre-computed at creation
E. Standard Edition supports materialized views with a 1-day refresh limit
Why A: CREATE MATERIALIZED VIEW is gated to Enterprise Edition and above. Standard Edition cannot create one.
Why C: MV maintenance runs on Snowflake-managed serverless compute. The credits show up as a separate line and are tracked in MATERIALIZED_VIEW_REFRESH_HISTORY, not in your warehouse credit usage.
Why not B: MVs are limited to a single base table. No joins, including self-joins. This is the most common MV trap on COF-C03.
Why not D: Snowflake refreshes MVs in the background as the base table changes. They are not a one-time snapshot.
Why not E: Standard Edition has no MV support at all, with or without a refresh limit. The “1-day” wording echoes Time Travel limits from Day 11 to make the option look familiar. Re-read Day 3 if missed.
Options:
A. Standard view: the cheapest option
B. Materialized view: performance is best
C. Secure view: DDL is hidden from non-owners
D. External table: restricts access by file path
Why C: A secure view is the only view type that hides its definition from non-owner roles. SHOW VIEWS, GET_DDL, and the body columns in INFORMATION_SCHEMA all return null. The optimizer is also blocked from leaking base data via predicate-pushdown side effects.
Why not A: Standard views expose their full SELECT text to anyone with USAGE on the schema. “Cheapest” is true but irrelevant. The requirement is privacy.
Why not B: A plain MV exposes its definition the same way a standard view does. Only when the SECURE flag is added does the DDL get hidden.
Why not D: External tables are a storage abstraction over cloud files. They have nothing to do with hiding logic from a downstream team.
Options:
A. Secure views run on a smaller compute cluster reserved for security workloads
B. Snowflake disables internal optimizations such as predicate pushdown to prevent indirect data exposure
C. Secure views recompute the underlying data three times for consistency
D. Secure views require encryption-key rotation on every query
Why B: The optimizer can’t see through a secure view, so filter predicates in the outer query don’t get pushed down to the base scan. More partitions get read than would otherwise be needed. Predicate pushdown is the most-cited example in the Snowflake docs. The same principle applies to any optimization that could indirectly reveal base-table contents.
Why not A: Snowflake doesn’t reserve separate compute clusters for any view type. Secure views run on the same virtual warehouse as everything else.
Why not C: No “triple computation” exists. This is a fabricated mechanism designed to sound plausible.
Why not D: Encryption keys aren’t rotated per query. Same trap as C: invented-sounding internals.
Options:
A. Create a standard view over the aggregation logic
B. Create a materialized view of the aggregation
C. Resize the warehouse to 4X-Large
D. Recreate the table as transient
Why B: Textbook MV scenario. Same expensive aggregate, single base table, slow-changing data, Enterprise Edition already in place. Serverless refresh keeps the stored result current. Every query hits the pre-computed partitions instead of re-aggregating 2 TB.
Why not A: Standard views re-run the aggregation on every reference. Same compute, same latency, no improvement.
Why not C: A bigger warehouse makes each individual run faster but doesn’t avoid the redundant work across dozens of identical queries. It also costs more per run. Re-read Day 8 if missed.
Why not D: Table type has no effect on query performance. Transient only changes Time Travel and Fail-safe behaviour. Re-read Day 11 if missed.
Today you learned: Three view types. Standard view = saved SELECT, live recompute, all editions, full SQL expressiveness. Materialized view = stored result, auto-refreshed using serverless credits. Enterprise+ only, single base table, no joins, no HAVING, no ORDER BY. Secure view = the SECURE flag on top of either type. Hides DDL from non-owners and disables optimizer pushdown (slower). By default, only secure views (and secure MVs / UDFs) can be added to shares. Cross-database sharing always requires a secure view in the shared database.
Key takeaway: Standard for cheap abstraction. Materialized for repeated heavy aggregates on slow-changing single-table data. Secure when you must hide the SQL or include the view in a share.
Tomorrow (Day 14): Week 2 Recap. 30-minute closed-book drill plus exam gotchas across Days 8–13 (warehouses, multi-cluster, micro-partitions, table types, Iceberg + Dynamic Tables, view types). Includes a 10-question mixed Practice Test. Same two-file pattern as Day 7.
