Course contents

SnowPro Core COF-C03 Study GuidePart 6: Database objects and platformViews: Standard, Secure & Materialized

Views: Standard, Secure & Materialized

Views: Standard, Secure & Materialized

Definition

A view stores a named query. A standard view re-runs that query every time it's referenced. A secure view additionally hides its definition and disables certain optimizer shortcuts to protect filtered-out data. A materialized view (Part 2, revisited here for completeness) physically stores the result and refreshes it automatically.

CREATE SECURE VIEW eu_customers AS
  SELECT customer_id, name, region
  FROM customers
  WHERE region = 'EUROPE';
Standard viewSecure viewMaterialized view
Stores resultsNoNoYes
Definition hidden from non-ownersNoYesOnly if created SECURE
Joins across tablesYesYesSingle table only
Query-time computeEvery callEvery call, slower (fewer optimizations)Reads the stored result, plus any base-table rows newer than the last refresh
EditionStandardStandardEnterprise
  • A materialized view reads one table: no joins, not even self-joins.
  • Its functions must be deterministic, so CURRENT_TIMESTAMP isn't allowed.
  • Snowflake refreshes it on serverless compute and bills your account.
Alice runs the same SELECT on three kinds of view. A standard view re-runs its stored query on the base tables, and its definition is visible to users of the view. A secure view also re-runs its query, applies its own filter before Alice's predicates and hides its definition. A materialized view reads rows it already stored, which a serverless service keeps in sync with its single base table.STANDARD VIEWSECURE VIEWMATERIALIZED VIEWAlice's SELECTVIEWStored queryre-runs on every calldefinition visibleTABLEBase tablesjoins allowedevery query pays computeAlice's SELECTSECURE VIEWStored queryown filter applied firstdefinition hiddenTABLEBase tablesjoins allowedevery query, fewer optimizationsAlice's SELECTMATERIALIZED VIEWStored resultrows saved in advanceno re-run at query timeTABLEOne base tableno joinsstorage + refresh · Enterprisequeriesreads each timequeriesreads each timequeriesserverless refresh

STANDARD VIEW

Alice's SELECT

queries

VIEWStored queryre-runs on every calldefinition visible

reads each time

TABLEBase tablesjoins allowed

every query pays compute

SECURE VIEW

Alice's SELECT

queries

SECURE VIEWStored queryown filter applied firstdefinition hidden

reads each time

TABLEBase tablesjoins allowed

every query, fewer optimizations

MATERIALIZED VIEW

Alice's SELECT

queries

MATERIALIZED VIEWStored resultrows saved in advanceno re-run at query time

serverless refresh

TABLEOne base tableno joins

storage + refresh · Enterprise

Figure 6.4 All three answer the same SELECT; they differ in what runs at query time and who can see the definition. Read each column top to bottom: standard and secure views re-run their query on the base tables, a materialized view reads rows it stored earlier.

Choosing takes three questions in order. Does anyone untrusted query it, directly or through a share? Secure, full stop; shares take secure views by default, and curiosity leaks through standard views. Is the same expensive single-table aggregation read constantly but written rarely? Materialized, and let serverless maintenance carry it. Everything else, meaning most views at IsmaFrost, is a standard view: free to define, always current, costing only what its query costs each run. Thomas's team treats SECURE as a governance decision rather than a default, because the optimizer restrictions have a real price on big tables, and a secure view nobody needed to secure is just a slower view.

What SECURE costs you

  • Performance. The optimizer stops pushing user predicates and reordering expressions across the view boundary, and some of those skipped rewrites were doing real work. On a wide view over a large table the gap is measurable, sometimes dramatic.
  • Debuggability. Consumers can't see the definition, which is the point, but it also means Alice can't read the view to understand why her numbers look off; every question routes through the owning team.
  • Less optimizer transparency. The Query Profile hides the internals of a secure view from everyone, the owner included, so performance investigation hits a wall.
  • Easy to switch on, hard to review. Each view takes one statement (ALTER VIEW v SET SECURE, and UNSET SECURE to reverse it), but securing everything "to be safe" slows every dashboard on top, and undoing it later means re-reviewing every view.

The rule that falls out: SECURE where an untrusted audience or a share demands it, standard everywhere else, and never as a default.