View Types

Overview

A view is a database object that lets you see the result of a query as if it were a table. Snowflake has three types: standard, secure and materialized.

The split is cleaner than the list suggests. Standard and secure views both store nothing and differ only in what they reveal. A materialized view stores its results — which is why it is the only one that costs storage, the only one gated by edition, and the only one that is not really a view at all.

The three types at a glance

The three columns are the original comparison. The two bands below them are added here, because they are the facts that decide whether a materialized view is available to you at all and what it costs once it is.

Standard views

• The default view type — a CREATE VIEW with no keyword produces one.
• A named definition of a query — a SELECT statement, and nothing more. It is a virtual table: the query is executed dynamically each time the view is referenced, and the results are not stored for later use.
• Executes as the executing role.
• The underlying DDL is available to any role with access to the view — anyone who can query it can read its definition.

The everyday value is simplifying complex queries and removing redundancy: write the join once, name it, and let everything else select from the name.

Secure views

• The definition and details are visible only to authorized users. A role that can query the view still cannot read the SELECT behind it — which is the difference from a standard view.
• Executes as the owning role.
• The Snowflake query optimizer bypasses optimizations it would normally apply to a view.

That third bullet is the one worth understanding rather than memorising, because it is the actual mechanism. Ordinary view optimizations — pushing a predicate down into the base table, reordering a join — can leak information about rows the view was written to hide: a query that errors on one input and not another tells the caller something about data it cannot see. A secure view gives that up. So the security is real, and it is bought with query performance.

A secure view is not row-level security. In the walkthrough, SECURE VIEW HR_EMPLOYEES filters with WHERE DEPARTMENT = 'HR' and is described as "restricting the data". It does — but it restricts it identically for everyone. The predicate is hard-coded, not per-user. Filtering by who is asking needs CURRENT_ROLE() in the definition, or a row access policy.

Materialized views

• Behaves more like a table than like a view.
• The results of the underlying query are stored — precomputed once and read from there afterwards.
• Auto-refreshed: Snowflake maintains it in the background as the base data changes. There is nothing to schedule.
• A secure materialized view is also supported — the two properties compose.

The case for one is a fixed, expensive computation that many queries repeat. A daily total by department, recomputed on every dashboard load, is the archetype: identical query, identical answer, paid for again each time.

It is not a cache

The source describes a materialized view as storing results "in a cache" throughout. That word is worth replacing, because Snowflake has a genuine cache and it is a different thing:

A materialized view is a stored, maintained, billed object — closer to a table that keeps itself up to date than to anything cached. That difference is the whole reason it has to be chosen deliberately.

Three limits the source does not mention

• Enterprise Edition and above. On Standard you cannot create one at all. The demo account is Enterprise, which is why nothing in the walkthrough hints at this.
• One table, no joins. A materialized view may query exactly one table. No joins, no window functions, no ORDER BY, no nesting on another view, and only a restricted set of aggregates. The walkthrough's example — one table, GROUP BY with SUM — happens to sit inside those limits, which is what makes it look easy. Most real reporting queries do not.
• Refresh is background, but reads are always correct. "Auto-refreshed as soon as the base data changes" is close but not exact: maintenance runs asynchronously, and if a query arrives before it has caught up, Snowflake reads the stored rows and merges the outstanding changes from the base table at query time. You never see stale data — you pay for the merge instead. A materialized view over a table that changes constantly can therefore cost more than the query it replaced.

Setting up the walkthrough

Context first — role, warehouse, and the schema the views will be created in — then a base table with data in it.

USE ROLE ACCOUNTADMIN;
USE WAREHOUSE COMPUTE_WH;
USE SCHEMA MY_DB.MY_SCHEMA;

CREATE OR REPLACE TABLE EMPLOYEES (
  ID         INT,
  NAME       VARCHAR(50),
  DEPARTMENT VARCHAR(50),
  SALARY     NUMBER(10,2)
);

INSERT INTO EMPLOYEES (ID, NAME, DEPARTMENT, SALARY) VALUES
  (1, 'Alice',   'Finance', 72000),
  (2, 'Bhavesh', 'IT',      88000),
  (3, 'Chen',    'HR',      64000),
  (4, 'Dara',    'IT',      91000),
  (5, 'Elif',    'Sales',   70000);

SELECT * FROM EMPLOYEES;   -- five rows

Creating each type

-- Standard: no keyword. This is the default.
CREATE OR REPLACE VIEW IT_EMPLOYEES AS
  SELECT ID, NAME, SALARY
  FROM EMPLOYEES
  WHERE DEPARTMENT = 'IT';

SELECT * FROM IT_EMPLOYEES;   -- two rows, out of the base table's five

-- Secure: the SECURE keyword goes before VIEW.
CREATE OR REPLACE SECURE VIEW HR_EMPLOYEES AS
  SELECT ID, NAME, SALARY
  FROM EMPLOYEES
  WHERE DEPARTMENT = 'HR';

SELECT * FROM HR_EMPLOYEES;   -- one row

-- Materialized: the MATERIALIZED keyword goes before VIEW.
CREATE OR REPLACE MATERIALIZED VIEW MV_EMPLOYEE_SALARIES AS
  SELECT DEPARTMENT, SUM(SALARY) AS TOTAL_SALARY
  FROM EMPLOYEES
  GROUP BY DEPARTMENT;

-- And the same query as a standard view, for the comparison below.
CREATE OR REPLACE VIEW EMPLOYEE_SALARIES AS
  SELECT DEPARTMENT, SUM(SALARY) AS TOTAL_SALARY
  FROM EMPLOYEES
  GROUP BY DEPARTMENT;

The object browser shows each view under the schema's Views folder after a page reload, and labels HR_EMPLOYEES as a secure view. As with tables, the type is a property rather than something you can read off the name — SHOW VIEWS reports it in its is_secure column, and materialized views are listed separately by SHOW MATERIALIZED VIEWS.

Proving the difference in the query profile

This is the most useful part of the walkthrough, because it does not take the claim on trust. Run the same SELECT * against both aggregating views — both return the same four rows — then open Query History and compare the two Query Profile trees:

The SQL is character-for-character identical in both cases. Only the object it names differs — and that changes the plan. That is the whole argument for a materialized view, and the query profile is where you check it rather than assume it.

Choosing a type

What this means for a migration

Views are one of the few object classes that translate almost intact, which is exactly why the interesting problems here are about classification rather than translation.

The keyword that breaks the classifier

Code translation decides what a statement is with a set of regexes. The one for views, in app/ai/agents/code_translation/nodes/classify_node.py, requires CREATE or ALTER to sit adjacent to VIEW:

_VIEW_RE = re.compile(
    r"^(?:CREATE\s+(?:OR\s+(?:ALTER|REPLACE)\s+)?VIEW|ALTER\s+VIEW)\b",
    re.IGNORECASE,
)

Any keyword between the verb and VIEW defeats it. So both of the non-standard forms on this page fall through to generic DDL and are routed to DdlStrategy — the create-only path — instead of ViewStrategy:

For materialized views this is recorded deliberately. ledger_variances.py marks the V segment unobservable for the kind rather than not-applicable, and says why: the platform already has the primitive the check needs, since ViewStrategy's body diff applies verbatim to a materialized view's defining query — nothing points it at one. The fix is a classifier change, not a ledger change, and a test named test_a_materialized_view_never_reaches_the_route_that_would_validate_it is there to fail on the day it lands.

The secure-view case is the same mechanical gap and is not written down anywhere — worth knowing if a source dialect's secure equivalents ever reach the translator.

Where each kind is measured

The ledger treats a materialized view as a hybrid, and the segment lists say so exactly:

It is the only object class that appears in both lists, and the reason is the subject of this page: a materialized view is a definition and a stored result, so it has both a view's question and a table's.

One more thing follows from the capability matrix on the Snowflake page: materialized_view is recorded as ENFORCED — one of only three features that are. Most of what the matrix says about Snowflake is about what it will accept and not enforce; this is one of the places where the target genuinely delivers the source construct.

Four slips in the source

Recorded rather than repeated. The first is the one that would actually mislead.

• "Stored in a cache" — said throughout, about materialized views. It is not a cache. It is a persistent object with billed storage and background maintenance credits. Snowflake's actual result cache is a separate mechanism with different rules, and conflating them makes a materialized view sound free.
• "Whoever has access to a secure view can see the definition" — the narration states this both ways within a minute. The rule is the reverse of a standard view: with a secure view, the definition is hidden from roles that are not authorized for it, even while they can query the view.
• Materialized views are presented as universally available. They are Enterprise Edition and above, and carry substantial restrictions on what the defining query may do.
• Views are repeatedly called tables — "my IT employees table is created", "the employee salary table". Harmless for the materialized one, which really does behave like a table, but the standard and secure views store nothing at all, and the distinction is the point of the page.

Where this page comes from

The three types, their bullet properties and the walkthrough — including the query-profile comparison, which is the strongest thing in it — are Snowflake's own introductory material on view types, with the four misstatements above corrected. The base table's five rows are reconstructed to match the demo's shape; the original values are not shown on screen.

The migration section is this platform's own: the regex is in app/ai/agents/code_translation/nodes/classify_node.py, the unobservability ruling in app/ai/agents/migration_project/service/ledger_variances.py, and the segment lists in app/ai/agents/migration_project/constants/ledger_stages.py.

See also

• Table Types — the same shape of question one level down: what is stored, and what it costs to keep.
• Editions — why materialized views are an Enterprise line item.
• Databases and Schemas — the context a view is created in, and the privilege chain behind "authorized users".
• Official Docs — the verified link set, by topic.