9.4 Views, Materialized Views & Transformation Table Design

Why This Decision Matters

Objective 3.3 of the blueprint lists views and tables — their benefits, limitations, and properties; the relationship between views and data types; the impact on costs; dynamic tables; and staging layers and tables. The same business logic can be delivered as a view, a materialized view, a dynamic table, or a table built by tasks, and each choice trades freshness, cost, and flexibility differently.

Standard Views

A view stores a named query; each query against it runs that query.

• No storage cost; every read pays compute on the querying warehouse.
• Read-only — you cannot INSERT into a view.
• Schema is captured at creation. Changes to a table are not automatically propagated to views on it: a view created with SELECT * does not gain columns added later, and dropping or changing a referenced column can make the view invalid. Recreate views as part of schema migrations (repeatable R__ scripts in migration tools are designed for this).
• Data types: a view's columns take their types from its query. Explicit casts in the view (for example payload:amount::NUMBER(12,2)) turn VARIANT paths into typed, stable columns for consumers; if an upstream type changes, the cast can start failing, so the view is also where type contracts are enforced.

Secure Views

CREATE SECURE VIEW (or ALTER VIEW ... SET SECURE):

• Hides the view definition from roles that do not own it.
• Disables optimizations that could let predicates run before the view's own filters and reveal rows the user should not see.
• Is the usual choice for a share when consumers must not see the definition; regular views can also be added to a share.
• Can be slower than an equivalent standard view because some optimizations are unavailable, so use them where the protection is needed rather than everywhere.

Materialized Views

A materialized view (MV) is a precomputed result of a query, stored and kept current automatically. It is an Enterprise Edition feature.

• Maintenance: a background process applies base-table inserts, updates, and deletes (and reclustering) to the MV. Costs appear as serverless compute (tracked under a Snowflake-provided warehouse named MATERIALIZED_VIEW_MAINTENANCE and in MATERIALIZED_VIEW_REFRESH_HISTORY) plus storage for the MV's own data.
• Always current: if maintenance is behind, Snowflake combines the MV with the newest base-table data, so results are never stale.
• Automatic query rewrite: the optimizer can use an MV for queries against the base table without the query naming the MV.
• Good fits: repeated, expensive aggregations or selective filters on a large, relatively stable table; accelerating queries on external tables (the MV stores data inside Snowflake).

Materialized View Limitations (Frequently Tested)

An MV can query only a single table and cannot:

• Use joins (including self-joins) or query another view, a materialized view, a hybrid table, a dynamic table, or a UDTF.
• Include UDFs (including external functions), window functions, HAVING, ORDER BY, LIMIT, GROUP BY ROLLUP/CUBE/GROUPING SETS, nested subqueries, or MINUS/EXCEPT/INTERSECT.
• Group by keys that are not in the SELECT list.

When logic needs joins or window functions, use a dynamic table (Section 9.3) or a task-built table instead.

Comparing the Options

Staging Layers and Table Types

A common layered design:

Cost rules of thumb:

• High-churn staging data in permanent tables accumulates Time Travel and 7 days of Fail-safe for every rewritten micro-partition; transient tables avoid the Fail-safe portion.
• Transient databases or schemas make every table created in them transient by default.
• Views save storage but spend compute on every read; MVs and dynamic tables spend storage and background compute to make reads cheap. Choose based on how often results are read versus how often the base data changes.

Architect Tip: Put secure views (or dynamic tables) at the consumption boundary even when internal layers use plain tables. It gives BI tools and shares a stable column contract while you keep freedom to refactor the layers underneath.