Data and Object Management · Topic 5 of 11
Views, Secure Views, Materialized Views and Cloning
Choose between standard, secure and materialized views, know their limits, and identify when zero copy cloning of databases and tables is the right tool.
- 5 min
- Secure views hide the definition and are required for sharing; they can be slower.
- Materialized views: Enterprise Edition, single table, serverless maintenance, no joins or UDFs.
- Cloning is zero copy, supports
ATorBEFORE, and suspends cloned tasks.
What it is
A view is a saved query that behaves like a table. Snowflake has three kinds:
- Standard view: a stored query; results are computed on every use.
- Secure view: a view whose definition and internals are hidden from non-owners.
- Materialized view: a view whose results are stored and kept up to date by Snowflake.
Cloning creates a copy of a database, schema or table using metadata only. The clone shares the original storage until either side changes.
How it works
Comparing views
| Property | Standard | Secure | Materialized |
|---|---|---|---|
| Definition visible to others | Yes, to roles that can see it | Only to the owner role | Yes, unless also secure |
| Results stored | No | No | Yes |
| Use in a share | Not allowed | Required | Secure materialized view allowed |
| Edition | All | All | Enterprise or higher |
| Extra cost | None | Possible slower queries | Storage and background maintenance |
Secure views
Create one with CREATE SECURE VIEW or ALTER VIEW ... SET SECURE. The view definition is not shown to roles that do not own it, and the optimizer does not use shortcuts that could leak data through error messages or timing.
That makes secure views the right choice for data sharing and for views that apply row or column filtering by CURRENT_ROLE() or a mapping table. The trade-off is that the same protection can make queries slower. Do not use secure views when you only want a convenience view with no sensitivity.
Check whether a view is secure with the is_secure column of SHOW VIEWS.
Materialized views
A materialized view stores its result. A background serverless service updates it after the base table changes, and queries always see current data (stale parts are read from the base table).
Limits you must know:
- It queries one table only: no joins and no self joins.
- It cannot use window functions,
HAVING,ORDER BY,LIMIT, nested subqueries or set operators such asMINUS. - It cannot contain a UDF or non-deterministic function such as
CURRENT_TIMESTAMP. - Only a defined set of aggregates is allowed, for example
COUNT,SUM,AVG,MIN,MAX. - No direct DML, and it cannot be queried with Time Travel.
Costs: stored results add storage, and maintenance uses serverless credits. Resource monitors cannot control that maintenance.
A materialized view pays off when results are small compared to the base table, queries are expensive, the base table changes rarely and results are read much more often than they change. It also helps over external tables.
Effects of base table changes: adding a column does not change the view. Changing or dropping an existing base table column suspends the view, and it usually must be recreated. Monitor it with SHOW MATERIALIZED VIEWS (BEHIND_BY, REFRESHED_ON) and the MATERIALIZED_VIEW_REFRESH_HISTORY view.
Avoid SELECT * in a materialized view definition. New base table columns never appear in it.
Cloning
CREATE TABLE orders_test CLONE orders;
CREATE DATABASE dev_db CLONE prod_db AT (OFFSET => -3600);
- A clone is zero copy: no extra storage at creation. Storage grows as source or clone data changes.
- Cloning a database or schema also clones its child objects, and those children keep their grants. The cloned container itself does not inherit the source's grants.
COPY GRANTScopies grants on the object (all exceptOWNERSHIP) for commands that support it. ATorBEFOREclones data as of a past point within retention.- Tasks and alerts in a clone are suspended; you must resume them.
- Streams: unconsumed records in a cloned stream are not available.
- Pipes that point to internal stages are not cloned. Table stages are cloned empty. Named internal stages are cloned only with
INCLUDE INTERNAL STAGES. - Automatic Clustering is suspended on a cloned table until you run
ALTER TABLE ... RESUME RECLUSTER. - A materialized view cannot be cloned directly, but a schema or database clone includes it.
Use cases for cloning
| Use case | Why a clone fits |
|---|---|
| Dev and test on production sized data | Instant, no extra storage at first |
| Backup before a risky change | Clone first, then drop it when satisfied |
| Recover a table to a past point | Clone with AT or BEFORE |
| Point in time analysis or reporting snapshot | Frozen view of data |
| Fix after a bad load | Clone the earlier state, then swap names |
A clone is not a replacement for replication or a backup in another region; it lives in the same account and region.
Common mistakes
Using a standard view in a share. Shares require secure views.
Building a materialized view over a join. Not allowed. Consider a dynamic table or a table loaded by a task.
Assuming a clone has the source's grants on itself. Grants are not copied to the clone unless COPY GRANTS is used (and for containers, only children keep theirs).
Forgetting to resume cloned tasks. They stay suspended.
Cloning to keep history cheaply for a long time. Clone storage grows with changes and retention still applies.
What to remember for the exam
- Secure views hide the definition and are required for sharing; they can be slower.
- Materialized views: Enterprise Edition, single table, serverless maintenance, no joins or UDFs.
- Cloning is zero copy, supports
ATorBEFORE, and suspends cloned tasks. - Clone use cases: dev and test, safe change, point in time recovery.
Check yourself
Which view type must a provider use when sharing filtered data?
A secure view (or secure materialized view).
Why does a materialized view on two joined tables fail?
Materialized views may query only one table, with no joins.
After cloning a database that has tasks, what must you do?
Resume the tasks, because they are cloned in a suspended state.
Topic 5 of 11 in Data and Object Management
Test yourself on Data and Object Management
The full Snowflake ADA-C02 question bank has 814 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.