CertSafari
    Snowflake SnowPro Advanced: Data Analyst (DAA-C01)· Lessons

    Domain 3 · Lesson 13/19

    Regular, secure and materialized views in Snowflake

    Use SQL extensibility features.

    6 min read
    8% of exam
    3 sources
    Published 20 Oct 2026
    Docs as of 4 Oct 2026

    What you will be able to do

    • Choose between a regular, secure and materialized view for a given privacy and performance need
    • Explain how a secure view stops definition exposure and optimizer-based data leaks
    • Predict the ongoing costs of a materialized view and what suspends one

    1.Regular views: a stored query

    A view lets you query the result of a query as if it were a table, including joins across several tables or other views. A regular view stores nothing. Every query against it reruns the definition on a warehouse. That keeps it cheap to create and always current, but it is only as fast as the query underneath. A materialized view makes the opposite trade.

    Regular versus materialized views
    Object typeProsCons
    ViewSimple, easily defined, consumes no storageInflexible, slow, requires compute to generate results
    Materialized ViewFast results retrieval; relatively simple definition; always up to dateIncurs compute to keep up to date; consumes storage

    A regular view also has two openings that matter when it exists to restrict data. Its definition text is visible to other users, which reveals the base tables and the filter logic. And some of the optimizer's internal shortcuts touch base-table data before the view's filter applies, so user code such as a UDF can sometimes see rows the view was supposed to hide.

    Checkpoint 1 of 3· Check yourself

    A team builds a regular (non-secure) view that filters a sales table down to one region. They grant it to analysts who must not see any other region's rows. What is true about this setup?

    Sources12

    2.Secure views: hiding the definition and the data

    A probe that infers hidden rows from a division-by-zero errorsql
    SELECT *
        FROM widgets_view
        WHERE 1/iff(color = 'Purple', 0, 1) = 1;

    If a purple row exists anywhere underneath, IFF returns 0 and the query fails with division by zero, which tells the user something they were never meant to see. A secure view closes this hole by giving up the optimizations that touch hidden data. You create one with CREATE SECURE VIEW (or CREATE SECURE MATERIALIZED VIEW), and you can switch an existing view either way with ALTER VIEW SET or UNSET SECURE.

    Only users with the role that owns a secure view can see its definition. Other users get nothing from SHOW VIEWS, GET_DDL or the Information Schema VIEWS view. Roles with IMPORTED PRIVILEGES on the SNOWFLAKE database, ACCOUNTADMIN or the SNOWFLAKE.OBJECT_VIEWER database role can still read it through the Account Usage VIEWS view. Query Profile hides a secure view's internals even from the owner, because non-owners may be able to see the owner's profiles. The IS_SECURE column shows whether a view is secure.

    Security costs speed: secure views can run more slowly. Use them for views built to protect sensitive data. For views that only make queries easier to write, they slow things down and protect nothing.

    Checkpoint 2 of 3· Check yourself

    An architect proposes marking every view in the account SECURE as a blanket best practice, including views built only to simplify queries for analysts. What is wrong with this plan?

    Sources2

    3.Materialized views: stored results with a running bill

    A materialized view computes its result in advance and stores it, so reading it is faster than querying the base table. The gain is largest for expensive aggregation, projection and selection over large tables that run often. Where that fits among other tuning options is covered in query performance optimization.

    The speed has an ongoing cost. Stored results add to monthly storage. Whenever the base table changes, a Snowflake-managed background service updates every materialized view on it, and that compute is billed per second against a warehouse called MATERIALIZED_VIEW_MAINTENANCE. No tool estimates this cost ahead of time. It grows with the number of materialized views on each table, the amount of data that changes, and clustering: a view clustered differently from its base table may rewrite far more micro-partitions than the table did. Resource monitors cannot cap that warehouse. You can suspend a view yourself and resume it later, but that only defers maintenance, and the backlog grows the longer it waits. Snowflake advises creating a few views first and watching MATERIALIZED_VIEW_REFRESH_HISTORY.

    Changes to the base table can also suspend a materialized view, and this is a different situation from a manual suspend:

    • Adding a column to the base table is safe.
    • Changing or dropping an existing column suspends every materialized view on the table, even views that never used that column. Unlike a manual suspend, this one cannot be undone: the view can be neither used nor maintained, and RESUME is not allowed. Recreate it with CREATE OR REPLACE MATERIALIZED VIEW ... COPY GRANTS.
    • Dropping, renaming or swapping the base table also suspends the view without dropping it.

    This is why Snowflake advises against SELECT * in a materialized view definition.

    Checkpoint 3 of 3· Check yourself

    Requirement: a dashboard runs an expensive aggregation over a large sales table many times an hour. The result includes sensitive margin data, and analysts must not see the definition or reach base-table rows outside it. Which object fits?

    Sources13

    Sources

    (3)

    Every claim above is drawn from one of these pages, quoted as it was written on the date shown.

    1. 1.
      “a view allows the result of a query to be accessed as if it were a table”
      ↩︎ Regular views: a stored query
      “querying a materialized view is faster than executing a query against the base table of the view.”
      ↩︎ Materialized views: stored results with a running bill
    2. 2.
      “This access might allow data that is hidden from users of the view to be exposed through user code, such as user-defined functions”
      ↩︎ Regular views: a stored query
      “Secure views do not utilize these optimizations, ensuring that users have no access to the underlying data.”
      ↩︎ Secure views: hiding the definition and the data
      “specify the SECURE keyword in the CREATE VIEW or CREATE MATERIALIZED VIEW command.”
      ↩︎ Secure views: hiding the definition and the data
      “If an unauthorized user uses any of the following commands or interfaces, the view definition is not displayed”
      ↩︎ Secure views: hiding the definition and the data
      “Secure views can execute more slowly than non-secure views.”
      ↩︎ Secure views: hiding the definition and the data
      “Secure views should not be used for views that are defined solely for query convenience”
      ↩︎ Exam trap 2
      “For a non-secure view, internal optimizations can indirectly expose data.”
      ↩︎ Checkpoint
      “To create a secure view, specify the SECURE keyword in the CREATE VIEW or CREATE MATERIALIZED VIEW command.”
      ↩︎ Checkpoint
    3. 3.
      “all materialized views defined on the table are updated by a background service”
      ↩︎ Materialized views: stored results with a running bill
      “you cannot use them to control credit usage for the Snowflake-provided warehouses, including the MATERIALIZED_VIEW_MAINTENANCE warehouse”
      ↩︎ Materialized views: stored results with a running bill
      “You can also control costs by suspending or resuming the materialized view”
      ↩︎ Materialized views: stored results with a running bill
      “If a base table is altered so that existing columns are changed or dropped, then all materialized views on that base table are suspended”
      ↩︎ Materialized views: stored results with a running bill
      “the materialized views cannot be used or maintained”
      ↩︎ Materialized views: stored results with a running bill
      “You cannot RESUME that materialized view. If you want to use it again, you must recreate it.”
      ↩︎ Materialized views: stored results with a running bill
      “Snowflake recommends not using SELECT * in the definition of a materialized view.”
      ↩︎ Materialized views: stored results with a running bill
      “suspending maintenance typically only defers costs, rather than reducing them”
      ↩︎ Exam trap 1

    Ready to test yourself?

    Practise the 29 questions on this subdomain.

    Spotted a mistake, or was something unclear? Tell us.