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
In a nutshell
  • 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.
  • UNDROP works 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.

AccountDatabaseSchemaTablepermanent, transient, temporaryMicro partitionsmanaged by Snowflake

How it works

Managing databases, schemas and tables

TaskCommandNote
CreateCREATE DATABASE, CREATE TABLEAlso CREATE TABLE ... AS SELECT, LIKE and CLONE
Remove and recoverDROP, then UNDROPWorks within the Time Travel retention period
Replace a table atomicallyALTER TABLE ... SWAP WITHSwaps names of two tables in one step
Empty a tableTRUNCATE TABLEKeeps the structure
Change history lengthDATA_RETENTION_TIME_IN_DAYSSet 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

TypeLivesTime TravelFail-safeUse for
PermanentUntil dropped1 day on Standard Edition; up to 90 days on Enterprise or higher7 daysCore business data
TransientUntil dropped0 or 1 dayNoneStaging, ETL scratch data that can be rebuilt
TemporaryCurrent session only0 or 1 dayNoneSession scratch work; dropped at session end

Other table types serve special jobs:

TypeUse for
ExternalQuerying files in a stage in place, read-only
DynamicDeclarative pipelines: the table refreshes toward a target lag from its defining query
HybridRow level, low latency operations, with enforced primary and foreign keys
EventCollecting logs, traces and events from code running in Snowflake
IcebergOpen 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.

Watch out

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 NULL is enforced. Hybrid tables do enforce keys.
  • Data types. Use VARIANT for 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.
Requirementhistory, cost, speedPick table typeSet retentionClustering or search optimizationonly if proven neededAttach policies and tags

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.
  • UNDROP works within the retention period and fails on a name clash.
  • Only NOT NULL is 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 KEY on a standard table stop duplicate rows?

    No, it is informational; only NOT NULL is 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.