Data and Object Management · Topic 3 of 11
Databases, Tables and Table Types - Design and Management
Manage databases, tables and views and choose table types: permanent, transient, temporary, dynamic, hybrid and event tables, with retention rules.
- 5 min
- Permanent: Time Travel 1 day (Standard) or up to 90 days (Enterprise and higher), plus 7 days Fail-safe.
- Transient and temporary: 0 or 1 day, no Fail-safe. Temporary ends with the session.
UNDROPworks within the retention period and fails on a name clash.
What it is
Data in Snowflake sits in a hierarchy: account, database, schema, then tables and views. Most administrator decisions come down to which kind of table to create and how long its history is kept, because that sets cost and recoverability.
Tables store data in micro-partitions, which Snowflake creates and manages automatically. You do not manage files or indexes. Design means choosing the table type, retention, clustering and security, not tuning storage layout by hand.
How it works
Managing databases, schemas and tables
| Task | Command | Note |
|---|---|---|
| Create | CREATE DATABASE, CREATE TABLE | Also CREATE TABLE ... AS SELECT, LIKE and CLONE |
| Remove and recover | DROP, then UNDROP | Works within the Time Travel retention period |
| Replace a table atomically | ALTER TABLE ... SWAP WITH | Swaps names of two tables in one step |
| Empty a table | TRUNCATE TABLE | Keeps the structure |
| Change history length | DATA_RETENTION_TIME_IN_DAYS | Set on account, database, schema or table |
UNDROP fails if an object with the same name now exists, so rename the new one first. A dropped object that has passed Time Travel is held in Fail-safe (permanent tables only) and can only be recovered by Snowflake support.
A database created from a share is read-only for the consumer. A table is owned by one role, and OWNERSHIP is what allows altering, dropping and granting on it.
Table types
| Type | Lives | Time Travel | Fail-safe | Use for |
|---|---|---|---|---|
| Permanent | Until dropped | 1 day on Standard Edition; up to 90 days on Enterprise or higher | 7 days | Core business data |
| Transient | Until dropped | 0 or 1 day | None | Staging, ETL scratch data that can be rebuilt |
| Temporary | Current session only | 0 or 1 day | None | Session scratch work; dropped at session end |
Other table types serve special jobs:
| Type | Use for |
|---|---|
| External | Querying files in a stage in place, read-only |
| Dynamic | Declarative pipelines: the table refreshes toward a target lag from its defining query |
| Hybrid | Row level, low latency operations, with enforced primary and foreign keys |
| Event | Collecting logs, traces and events from code running in Snowflake |
| Iceberg | Open table format with Parquet data in your own storage |
The cost angle matters: Time Travel and Fail-safe data count as storage. A big transient staging table avoids Fail-safe storage entirely. A temporary table vanishes with the session and cannot be seen by other sessions.
Transient and temporary tables have no Fail-safe. If data cannot be rebuilt, make the table permanent.
Transient can also be applied to a whole schema or database (CREATE TRANSIENT DATABASE), and every table created in it is transient.
Table design considerations
- Retention versus cost. Longer Time Travel means more storage for every change. Set long retention on critical tables and 0 or 1 on staging data.
- Clustering. Natural load order often clusters well. For very large tables with a selective filter on a column that is not the load order, define a clustering key. Automatic Clustering then maintains it with serverless compute, which costs credits.
- Search optimization service speeds up selective lookups and substring searches on large tables. It needs Enterprise Edition or higher and adds storage and compute cost.
- Constraints. Primary key, unique and foreign key constraints are informational on standard tables; only
NOT NULLis enforced. Hybrid tables do enforce keys. - Data types. Use
VARIANTfor semi-structured data, and keep the types the data really has so that pruning works. - Governance hooks. Tags, masking policies and row access policies attach to tables and columns. Plan them with the schema.
Views at a glance
Views are saved queries, and they come in three kinds: standard, secure and materialized. The later page on views covers when to use each. For this topic remember that a view needs SELECT on its base tables for the owner role, and users need SELECT on the view.
Common mistakes
Making every staging table permanent. It pays for Time Travel and Fail-safe on data you can reload.
Using transient tables for the only copy of data. There is no Fail-safe, and Time Travel is at most 1 day.
Treating primary keys as enforced. On standard tables they document intent but do not block duplicates.
Adding a clustering key by default. It has an ongoing credit cost. Add it only for large tables where pruning is poor.
Expecting a temporary table in another session. It exists only in the session that made it.
What to remember for the exam
- Permanent: Time Travel 1 day (Standard) or up to 90 days (Enterprise and higher), plus 7 days Fail-safe.
- Transient and temporary: 0 or 1 day, no Fail-safe. Temporary ends with the session.
UNDROPworks within the retention period and fails on a name clash.- Only
NOT NULLis enforced on standard tables; hybrid tables enforce keys. - Clustering and search optimization both cost credits; use them only when queries justify it.
Check yourself
A table holds data reloaded nightly from a source. Which type saves storage cost?
A transient table, because it has no Fail-safe and at most 1 day of Time Travel.
A table was dropped yesterday and a new table with the same name was created. What blocks
UNDROP?The name clash; rename or drop the new table first.
Does a
PRIMARY KEYon a standard table stop duplicate rows?No, it is informational; only
NOT NULLis enforced.
Topic 3 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.