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
In a nutshell
  • 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 AT or BEFORE, 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.

Needhide logic or shareSecure viewNeedspeed on stable dataMaterialized viewNeedtest copy or backupZero copy clone

How it works

Comparing views

PropertyStandardSecureMaterialized
Definition visible to othersYes, to roles that can see itOnly to the owner roleYes, unless also secure
Results storedNoNoYes
Use in a shareNot allowedRequiredSecure materialized view allowed
EditionAllAllEnterprise or higher
Extra costNonePossible slower queriesStorage 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 as MINUS.
  • 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.

Watch out

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 GRANTS copies grants on the object (all except OWNERSHIP) for commands that support it.
  • AT or BEFORE clones 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.
Production databaseCLONEDev cloneshares storagechanges divergeSeparate storage grows

Use cases for cloning

Use caseWhy a clone fits
Dev and test on production sized dataInstant, no extra storage at first
Backup before a risky changeClone first, then drop it when satisfied
Recover a table to a past pointClone with AT or BEFORE
Point in time analysis or reporting snapshotFrozen view of data
Fix after a bad loadClone 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 AT or BEFORE, 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.