Skip to content
Tutorials Center
My learning

Topics

Lesson 4 of 38 ยท Getting Started

Databases, Schemas and Objects

Organize Snowflake data with databases and schemas. Compare permanent, transient and temporary tables, views, materialized views, and use SHOW and DESCRIBE.

Snowflake organizes everything in a two-level hierarchy: databases contain schemas, and schemas contain tables, views, stages, file formats, sequences, functions, streams, tasks and other objects. Good organization makes security and cost control simple, so it is worth learning the rules early.

The namespace

Every object has a fully qualified name: database.schema.object. When you set a database and schema with USE, you can leave off the prefix.

SQL
-- Snowflake SQL (read-only)
CREATE DATABASE IF NOT EXISTS shop;
CREATE SCHEMA   IF NOT EXISTS shop.raw;
CREATE SCHEMA   IF NOT EXISTS shop.analytics;

USE SCHEMA shop.raw;
CREATE TABLE orders (id NUMBER, total NUMBER(10,2));   -- shop.raw.orders
SELECT * FROM shop.analytics.daily_sales;               -- fully qualified

A new database automatically gets a schema named PUBLIC. A common pattern is to separate layers: RAW (data as loaded), STAGING (cleaned), ANALYTICS (ready for reports). Each can have different access rules.

flowchart TD
  A["Account"] --> B["Database SHOP"]
  B --> C["Schema RAW"]
  B --> D["Schema ANALYTICS"]
  C --> E["Table ORDERS"]
  C --> F["Stage LANDING"]
  D --> G["View DAILY_SALES"]

Schemas group related objects; databases group schemas.

Table types

Snowflake has several table types, and the difference is mainly about how long data is protected (Time Travel and Fail-safe, explained in Time Travel, Fail-safe and Cloning) and therefore storage cost.

Type Lasts Time Travel Fail-safe Typical use
Permanent (default) Until dropped 1 day (up to 90 on Enterprise) 7 days Real data
Transient Until dropped 0 or 1 day None Reproducible staging data
Temporary The session 0 or 1 day None Scratch work
External Files stay in your own cloud storage None None Query files in place
SQL
-- Snowflake SQL (read-only)
CREATE TABLE sales (id NUMBER, amount NUMBER(10,2));              -- permanent
CREATE TRANSIENT TABLE sales_stage (id NUMBER, amount NUMBER);    -- cheaper
CREATE TEMPORARY TABLE scratch (id NUMBER);                       -- gone at logout

Rule of thumb: if you can regenerate it, make it transient; if it is your only copy, keep it permanent.

Views

A view stores a query, not data. Snowflake supports three kinds:

  • Standard view: runs the query each time.
  • Materialized view (Enterprise+): Snowflake stores and automatically refreshes the result for a single table query. Good for expensive, repeated aggregations on slowly changing data; it costs background compute.
  • Secure view: hides its definition from non-owners and prevents some optimizer tricks that could leak data, used for sharing and security (a later chapter covers secure views and sharing).
SQL
-- Snowflake SQL (read-only)
CREATE VIEW v_big_orders AS
  SELECT * FROM shop.raw.orders WHERE total > 1000;

CREATE SECURE VIEW v_regional AS
  SELECT region, SUM(total) AS revenue FROM shop.raw.orders GROUP BY region;

The standard view idea runs anywhere, so try it:

SQL A view stores a query, not rows ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Exploring with SHOW and DESCRIBE

Snowflake gives you commands to look around. They return metadata rather than data:

SQL
-- Snowflake SQL (read-only)
SHOW DATABASES;
SHOW SCHEMAS IN DATABASE shop;
SHOW TABLES  IN SCHEMA shop.raw;
SHOW VIEWS   LIKE 'V_%';
DESCRIBE TABLE shop.raw.orders;      -- columns and types
SELECT GET_DDL('TABLE', 'shop.raw.orders');   -- the CREATE statement

Each account also has INFORMATION_SCHEMA in every database, a set of views describing tables, columns and usage. The same idea exists in standard SQL, and SQLite's version is sqlite_master. You can see the equivalent here:

SQL Stand-in: listing objects like SHOW TABLES ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

And the stand-in for DESCRIBE TABLE:

SQL Stand-in: DESCRIBE TABLE ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Cloning, dropping and undropping

Because data is stored in immutable micro-partitions, copying is cheap. CREATE ... CLONE makes a new table, schema or whole database that shares storage with the original until one of them changes. DROP is not instant destruction either: while the retention period lasts you can bring an object back.

SQL
-- Snowflake SQL (read-only)
CREATE DATABASE shop_dev CLONE shop;      -- zero-copy clone
DROP TABLE shop.raw.orders;
UNDROP TABLE shop.raw.orders;             -- restored

You will use this in Time Travel, Fail-safe and Cloning.

Comments, naming and good habits

  • Use COMMENT = '...' on objects so people find out what they hold.
  • Name things consistently: singular or plural, but not both. Prefer uppercase-friendly names without spaces so you never need quotes.
  • Put raw and curated data in separate schemas and give them different grants.
  • Prefer transient tables for staging to save storage cost.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the fully qualified name format for a table?

    Snowflake uses database.schema.object. Setting USE DATABASE and USE SCHEMA lets you omit the prefix.
  2. 2. You need a scratch table that disappears at the end of your session. Which type?

    A temporary table exists only for the session. It has no Fail-safe.
  3. 3. Which table type has no Fail-safe period and is cheaper to store?

    Transient tables have no Fail-safe and at most 1 day of Time Travel, so they cost less to keep.
  4. 4. What does a standard view store?

    A standard view stores the query. The result is computed each time you select from it.

Summary

  • Objects live in database.schema.object namespaces; each database starts with a PUBLIC schema.
  • Permanent, transient, temporary and external tables differ in how long they keep data and how much protection they carry.
  • Standard, secure and materialized views serve different needs.
  • SHOW, DESCRIBE, GET_DDL and INFORMATION_SCHEMA let you explore metadata.
  • Cloning and undropping are cheap because storage is shared and immutable.

Next: The Three-Layer Architecture