Data Engineering · Topic 11 of 16

Views and Tables - Choosing a Transformation Solution

Choose between views, secure and materialized views, table types and CTAS: properties, limits, data type effects, and storage and compute cost.

  • 5 min
In a nutshell
  • Standard view: no storage, always current. Secure view: hides the definition, required for sharing by default, can be slower.
  • Materialized view: Enterprise or higher, single table, no joins, window functions, ORDER BY, LIMIT or UDFs, serverless maintenance cost.
  • Transient and temporary tables have no Fail safe and at most 1 day of Time Travel; permanent tables have 7 days of Fail safe.

What it is

An Architect transforms data in one of a few ways: a query that runs on demand (a view), a stored result that is refreshed for you (a materialized view or dynamic table), or a stored result that you load yourself (a table built with CTAS, tasks or procedures). The exam describes freshness, cost and security needs and asks which object fits.

Start from three questions. How fresh must the result be? How often is it read compared with how often the source changes? Who may see the logic or the underlying rows?

Requirementfreshness, reads, securitypickViewcomputed at query timeorMaterialized viewsingle table, auto maintainedorTableloaded by tasks or CTAS

How it works

Views compared

ObjectStores dataRefreshed byKey limitEdition
Standard viewNo, only the queryNot needed, always currentCost is paid at every queryAll
Secure viewNoNot neededHides the definition; some optimizations are skippedAll
Materialized viewYesBackground serviceOne table only, no joinsEnterprise or higher
Dynamic tableYesRefresh on a target lagNeeds a warehouseSee next page

A standard view is a saved SELECT. It adds no storage and always shows current data, so it is the default for simple reshaping, renaming and access control. Its weakness is that a heavy join or aggregate is recomputed every time someone queries it.

A secure view hides its definition from anyone but the owner and is required for sharing a view through Secure Data Sharing by default (only a share created with SECURE_OBJECTS_ONLY = FALSE accepts non secure views). The trade-off is that the optimizer will not push some user filters below the view in ways that could leak data, so queries can run slower.

A materialized view stores the result of a query on a single table and keeps it current with a background service. The optimizer can use it transparently, even when the query names the base table. It cannot contain joins, window functions, HAVING, ORDER BY, LIMIT, UDFs or non deterministic functions, and only a fixed set of aggregates is allowed.

Exam tip

When a question needs a join, a window function or a chain of transformations kept fresh, a materialized view is the wrong answer. Think dynamic table or a stream and task pipeline.

Table properties that drive design

Table typeTime TravelFail safeTypical use
Permanent1 day by default, up to 90 on Enterprise or higher7 daysCurated and consumption data
Transient0 or 1 dayNoneStaging and rebuildable data
Temporary0 or 1 day, ends with the sessionNoneSession scratch work
ExternalNot applicableNot applicableQuery files in place

Transient tables exist to save storage cost, because there is no Fail safe copy. They persist after the session ends, unlike temporary tables. Schemas and databases can also be transient.

Views and data types

A view has no data types of its own; each column takes the type of the expression behind it. Casting inside the view, such as value:amount::NUMBER(12,2), is how you turn VARIANT into typed columns for downstream tools.

A view binds to its sources by name. If an underlying column is dropped or renamed, the view fails when queried rather than when the table is altered. Adding a column to a table does not add it to a view that was created earlier, because the column list is fixed at creation, even with SELECT *. Materialized views behave differently: when any base table column is changed or dropped, even one the view does not use, they are suspended and must be recreated.

Watch out

Do not rely on SELECT * in view definitions. Name the columns so schema changes cannot silently change or break the view.

Data type choices also shape cost and pruning. Native types such as DATE and NUMBER give better partition pruning and smaller storage than the same values kept inside a VARIANT or as strings. Comparing a string column to a number forces implicit casts that can defeat pruning.

Cost impact

Standard viewno storage, compute per queryvsMaterialized viewstorage plus serverless maintenancevsTablestorage plus your load compute
  • Standard and secure views: no storage, but compute is spent at every query.
  • Materialized views: storage for the stored result, plus serverless background maintenance credits whenever the base table changes. Many views on a busy table, or a clustering key on the view, raise the cost.
  • Tables built with CTAS: storage, Time Travel and Fail safe storage on permanent tables, and warehouse compute when you load them.
  • Transient tables: remove Fail safe storage, so use them for staging layers that can be rebuilt.

A materialized view pays off when the base table changes slowly and the view is read often. If the base table changes constantly and the view is read rarely, a standard view or a periodic rebuild is cheaper.

Common mistakes

Choosing a materialized view for a join. It supports one table only.

Using permanent tables for every layer. Rebuildable staging data pays for Time Travel and Fail safe storage it does not need.

Assuming a view stays valid after schema changes. Dropped columns break it, and new columns do not appear.

What to remember for the exam

  • Standard view: no storage, always current. Secure view: hides the definition, required for sharing by default, can be slower.
  • Materialized view: Enterprise or higher, single table, no joins, window functions, ORDER BY, LIMIT or UDFs, serverless maintenance cost.
  • Transient and temporary tables have no Fail safe and at most 1 day of Time Travel; permanent tables have 7 days of Fail safe.
  • Column lists of views are fixed at creation; changing a base column suspends a materialized view.
  • Match the object to freshness and read to change ratio, then compare storage against compute.

Check yourself

  • A report needs a join of two large tables kept fresh and read often. Can a materialized view do it?

    No, a materialized view covers one table only. Use a dynamic table or a pipeline with streams and tasks.

  • Why use transient tables for a staging layer?

    They have no Fail safe and at most 1 day of Time Travel, which lowers storage cost for data that can be reloaded.

  • A column is dropped from a base table. What happens to a view over it?

    The standard view fails when it is queried, and a materialized view is suspended and must be recreated.

Topic 11 of 16 in Data Engineering

Test yourself on Data Engineering

The full Snowflake ARA-C01 question bank has 687 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.