Snowflake AI Data Cloud Features & Architecture · Topic 14 of 17

View Types - Standard, Materialized and Secure

Compare standard, materialized and secure views in Snowflake: storage, cost, edition, limits, automatic query rewrite and why shares need secure views.

  • 5 min
In a nutshell
  • Standard views store only SQL and run it each time; no storage cost.
  • Materialized views store results, need Enterprise Edition, are maintained by a serverless background service and support automatic query rewrite.
  • Materialized views read one table, with no joins, window functions, HAVING, ORDER BY, LIMIT or UDFs.

What it is

A view is a named query that you can use like a table. Snowflake has three view types the exam cares about: standard (non-materialized) views, materialized views and secure views. Secure is a property you can add to either of the other two.

The choice comes down to three questions. Should the result be computed on every query or stored in advance? Is it worth paying to keep a stored result current? Must the view's definition and the underlying data be protected from the people who query it?

View typeStores resultsExtra costEditionMain use
StandardNo, runs the query each timeNone beyond the queryAllSimplify SQL, limit columns or rows
MaterializedYes, precomputedStorage plus serverless maintenance creditsEnterprise or higherSpeed up expensive, frequent queries on one table
Secure (standard or materialized)As its base typePossibly slower queriesAll for secure views; Enterprise for secure materializedHide definition and protect data, share data

How it works

Standard views

CREATE VIEW stores only the query text. Each time someone selects from the view, Snowflake runs that query against the current base tables, so results are always up to date and the view uses no storage.

Standard views are good for hiding joins and complex logic, exposing only some columns or rows, and giving users a stable name while tables change underneath. They cost nothing until queried. The trade-off is that an expensive query stays expensive every time.

User querySELECT from viewexpands toView definitionstored SQLruns onBase tablescurrent datausesWarehouseeach time

Materialized views

CREATE MATERIALIZED VIEW stores the query result. A background service keeps it current after the base table changes, so queries read the precomputed data and are fast. Materialized views require Enterprise Edition or higher.

Costs come from two places: storage for the result, and serverless compute credits for maintenance. Maintenance does not use your warehouse. Resource monitors cannot cap these credits.

The optimizer can use a materialized view automatically. A query written against the base table may be rewritten to read the materialized view instead, and the query profile shows when this happens.

Materialized views have strict limits. The ones most often tested:

  • The query can read only one table. No joins, including self-joins.
  • No window functions, HAVING, ORDER BY, LIMIT, UDFs, or nested subqueries.
  • Only deterministic functions; CURRENT_TIMESTAMP is not allowed.
  • Only a limited set of aggregate functions, and no nested aggregates.
  • It cannot query another view, a dynamic table or a hybrid table.

Use a materialized view when the result changes rarely, is queried often and is expensive to compute. Use a standard view when the data changes often or the query is cheap. A common use is speeding up queries on an external table, whose files are otherwise read from cloud storage each time.

Base tablechangesbackground serviceMaterialized viewprecomputed resultservesFast queriesoptimizer may rewrite

Secure views

Adding SECURE (CREATE SECURE VIEW, or ALTER VIEW ... SET SECURE) changes two things:

  • The definition is hidden. Only users with the role that owns the view can see its SQL through SHOW VIEWS, GET_DDL or the query profile.
  • Some optimizations are turned off. The optimizer avoids plans that could let a user infer hidden rows, for example by evaluating a user's filter or function before the view's own filter. This can make secure views slower than standard ones.

Secure views matter for Secure Data Sharing: by default (SECURE_OBJECTS_ONLY = TRUE) a share can expose secure views and secure materialized views, but not standard ones. Only a share whose provider sets SECURE_OBJECTS_ONLY = FALSE, a one-way change, accepts non-secure views. They are also used for row-level filtering, for example a view that filters on CURRENT_ROLE() or a mapping table so each role sees only its rows.

You can check whether a view is secure with the IS_SECURE column in SHOW VIEWS or SHOW MATERIALIZED VIEWS output.

Providersecure viewadded toShareconsumer queriesSecure viewdefinition hiddenreturnsAllowed rows only
Exam tip

When a question says "share a view with another account", the expected answer is a secure view, the default requirement for shares. When it says "the view logic must not be visible to consumers", the answer is also a secure view.

How the exam asks about this

  • "A dashboard runs the same heavy aggregation on one large table many times a day, and the table changes rarely" -> materialized view, on Enterprise Edition or higher.
  • "Analysts should not see how a view filters sensitive rows" -> secure view.
  • "A view must join three tables and always return the latest data" -> standard view, since materialized views cannot join.

Common mistakes

Making every view secure. Secure views can run slower because some optimizations are disabled. Use them only when the definition or data needs protection, or for sharing.

Building a materialized view with a join. Materialized views read one table only. Use a standard view, a dynamic table, or a regular table built by a pipeline instead.

Expecting materialized view maintenance to run on your warehouse. It runs on Snowflake-managed serverless compute and is billed separately.

Forgetting the edition rule. Materialized views need Enterprise Edition or higher. Standard Edition can still use standard and secure views.

What to remember for the exam

  • Standard views store only SQL and run it each time; no storage cost.
  • Materialized views store results, need Enterprise Edition, are maintained by a serverless background service and support automatic query rewrite.
  • Materialized views read one table, with no joins, window functions, HAVING, ORDER BY, LIMIT or UDFs.
  • Secure views hide the definition from non-owners and may disable some optimizations.
  • By default, only secure views and secure materialized views can be shared; non-secure views need a share with SECURE_OBJECTS_ONLY = FALSE.
Watch out

Materialized views cannot be queried with Time Travel. If you need history, query the base table with Time Travel instead.

Check yourself

  • Which view type stores precomputed results and needs Enterprise Edition?

    A materialized view, kept current by a serverless background service.

  • Why can a secure view run slower than a standard view?

    Snowflake disables some optimizations that could expose hidden data, so the plan may be less efficient.

  • Can a standard view be added to a share?

    Not by default. Shares accept secure views and secure materialized views; a standard view is allowed only if the share sets SECURE_OBJECTS_ONLY = FALSE.

Topic 14 of 17 in Snowflake AI Data Cloud Features & Architecture

Test yourself on Snowflake AI Data Cloud Features & Architecture

The full Snowflake SnowPro Core COF-C03 question bank has 845 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.