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
- 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?
How it works
Views compared
| Object | Stores data | Refreshed by | Key limit | Edition |
|---|---|---|---|---|
| Standard view | No, only the query | Not needed, always current | Cost is paid at every query | All |
| Secure view | No | Not needed | Hides the definition; some optimizations are skipped | All |
| Materialized view | Yes | Background service | One table only, no joins | Enterprise or higher |
| Dynamic table | Yes | Refresh on a target lag | Needs a warehouse | See 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.
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 type | Time Travel | Fail safe | Typical use |
|---|---|---|---|
| Permanent | 1 day by default, up to 90 on Enterprise or higher | 7 days | Curated and consumption data |
| Transient | 0 or 1 day | None | Staging and rebuildable data |
| Temporary | 0 or 1 day, ends with the session | None | Session scratch work |
| External | Not applicable | Not applicable | Query 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.
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 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.