Snowflake Architecture · Topic 13 of 18
Object Hierarchy - Databases, Schemas, Tables and Views
Databases, schemas, table types and view types as design choices: transient and temporary tables, secure and materialized views, managed access and clones.
- 5 min
- Transient database or schema makes tables transient: no Fail-safe, Time Travel at most 1 day.
- Permanent table: Time Travel up to 90 days only on Enterprise and higher, Fail-safe 7 days.
- Temporary table: session only. External table: no Time Travel or Fail-safe.
What it is
Databases and schemas are the containers that organize data. Tables hold it and views present it. Each choice carries side effects: which data protection features apply, what a clone or replica copies, how much storage costs and what a data consumer can see.
The exam asks you to pick the right container and object type for a scenario, such as "staging data that can be rebuilt", "share only some columns" or "keep ten years of audit data".
How it works
Databases
A database is the unit for replication, failover and sharing, and a common unit for cloning and environment separation. A database can be permanent or transient. A transient database creates transient schemas and tables by default, with no Fail-safe and at most 1 day of Time Travel.
Databases can also be created from a share (read only, shared data), as a replica of another database, or as a clone. A database created from a share holds no copy of the data, so it cannot be replicated to other accounts.
Design guidance:
- One database per environment or data domain, with schemas for layers such as raw, curated and consumption.
- Group data that must be replicated or shared together in the same database.
- Set retention and protection at the database level, then override only where needed.
Schemas
A schema groups tables, views, stages, file formats, streams, tasks, functions and procedures. It is where most access control is applied and where you organize layers.
- Managed access schemas (
WITH MANAGED ACCESS) let only the schema owner or a role withMANAGE GRANTSgrant privileges on objects inside. Use them when access must be centrally controlled. - Transient schemas make new tables transient.
- Schema level
GRANT ... ON ALLandON FUTUREstatements make privileges apply to existing and new objects. - Cloning a schema copies its child objects, and a schema can be swapped with another using
ALTER SCHEMA ... SWAP WITH, which supports blue green releases.
Tables
| Table type | Time Travel | Fail-safe | Lifetime and use |
|---|---|---|---|
| Permanent | 0 to 1 day on Standard, 0 to 90 days on Enterprise and higher; default 1 | 7 days | Default type for important data |
| Transient | 0 or 1 day; default 1 | None | Persists until dropped; staging and reproducible data |
| Temporary | 0 or 1 day; default 1 | None | Lasts for the session only; scratch work |
| External | None | None | Reads files in cloud storage; data is not stored in Snowflake |
Other table kinds you must recognize: dynamic tables (a query result that Snowflake keeps refreshed to a target lag), Iceberg tables (open table format, with Snowflake or an external catalog managing metadata), hybrid tables (row store for transactional workloads) and event tables (telemetry and logs).
Decisions:
- Use transient tables for ETL staging and intermediate layers to avoid Fail-safe storage.
- Use temporary tables for work that must vanish with the session. They are not visible to other sessions and are not replicated.
- A transient or temporary table cannot be cloned into a permanent table.
- Choose a clustering key only for large tables with a proven pruning problem; clustering is covered in the performance chapter.
Views
| View type | What it is | Architectural use |
|---|---|---|
| Standard | A stored query; no data stored | Simplify queries, hide columns |
Secure (CREATE SECURE VIEW) | Definition is hidden and optimizer shortcuts that could leak data are disabled | The standard choice for sharing a view; use to protect row or column logic |
| Materialized | Precomputed results that Snowflake keeps current; Enterprise Edition or higher | Speed up repeated queries on one table with simple aggregates |
A secure view costs some query performance, because the optimizer cannot push user predicates through it in ways that might expose data. Accept that trade-off for shared or sensitive data.
A materialized view is billed for serverless maintenance and storage. It is limited to a single table source and supports a restricted set of query features, so it is not a general replacement for a standard view. If you need to join or transform, a dynamic table is usually the better choice.
A standard view is not secure. Its definition can be seen by anyone with the right privilege, and although non-secure views can now be shared, they do not protect the logic or rows. Share a secure view instead.
What a clone or replica copies
Cloning a database or schema copies its tables as zero copy clones, together with its views and most other child objects. Replication is narrower: temporary tables and databases created from shares are not replicated, and several other types (for example external, hybrid, event and externally managed Iceberg tables) are excluded or restricted, so check the replication considerations before relying on them.
Common mistakes
Making all tables permanent. Staging and rebuildable data on permanent tables pays for Time Travel and Fail-safe storage it does not need.
Sharing a standard view of sensitive data. Non-secure views can be shared, but their definition and data are not protected. Use a secure view.
Expecting temporary tables to persist or replicate. They end with the session and are not replicated.
Using a materialized view for joins. A materialized view reads one table. Use a dynamic table when you need joins or complex logic.
Forgetting managed access. Without it, every object owner can grant their own objects to anyone.
What to remember for the exam
- Transient database or schema makes tables transient: no Fail-safe, Time Travel at most 1 day.
- Permanent table: Time Travel up to 90 days only on Enterprise and higher, Fail-safe 7 days.
- Temporary table: session only. External table: no Time Travel or Fail-safe.
- Secure view for sharing and for hiding logic; materialized view needs Enterprise and a single source table.
- Managed access schema: schema owner or
MANAGE GRANTScontrols granting. - Dynamic table when you need refreshed, joined results.
Check yourself
Where should intermediate ETL tables that can be rebuilt be stored to cut storage cost?
In transient tables (or a transient schema), which have no Fail-safe and at most 1 day of Time Travel.
A provider wants to share a filtered subset of a table with a consumer. Which object type?
A secure view over the table, because it hides the definition and protects the filtered rows.
Which view type needs Enterprise Edition and can only read one table?
A materialized view.
Topic 13 of 18 in Snowflake Architecture
Test yourself on Snowflake Architecture
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.