Snowflake – Databases, Schemas and Tables

Snowflake's object model is short enough to learn in one sitting, and two of its choices differ sharply from Postgres or Oracle in ways that cause real bugs. This lesson covers the hierarchy, the DDL you write daily, the three table types and what each one costs, and the constraints that are not constraints.

The hierarchy

Account
└── Database
    └── Schema
        ├── Tables
        ├── Views / Materialized views
        ├── Stages, File formats, Pipes
        ├── Streams, Tasks
        └── Functions, Procedures

Objects are addressed as database.schema.object. Unlike Postgres, a query can reference several databases at once without anything resembling a foreign data wrapper — a join across two databases is an ordinary join:

SELECT o.o_orderkey, c.c_name
FROM   snowflake_sample_data.tpch_sf1.orders   AS o
JOIN   learn_snowflake.staging.customer_notes  AS c
       ON c.customer_id = o.o_custkey;

That is why "database" in Snowflake is closer to a namespace than to a server. Splitting environments across databases in one account costs nothing and is the standard pattern (lesson 16).

Every database is created with two schemas you did not ask for. PUBLIC is a default place to put things. INFORMATION_SCHEMA is a set of views over metadata for that database — the same metadata as the account-level SNOWFLAKE.ACCOUNT_USAGE views, but current rather than delayed, and scoped to one database.

Creating databases and schemas

USE ROLE SYSADMIN;

CREATE DATABASE analytics
  DATA_RETENTION_TIME_IN_DAYS = 7          -- Time Travel window, inherited by objects
  COMMENT = 'Curated data for reporting';

CREATE SCHEMA analytics.raw     COMMENT = 'Landed, untransformed';
CREATE SCHEMA analytics.marts   COMMENT = 'Modelled, for consumption';

-- A transient schema: no Fail-safe, so cheaper storage for rebuildable data.
CREATE TRANSIENT SCHEMA analytics.scratch DATA_RETENTION_TIME_IN_DAYS = 0;

DATA_RETENTION_TIME_IN_DAYS set on a database is inherited by schemas and tables created inside it unless they override it. Getting it right at the database level is much easier than fixing it per table later.

Three kinds of table

This is the choice with the clearest cost consequence, and the one people leave on the default without thinking.

Fail-safe is a 7-day period after Time Travel expires during which Snowflake — not you — can recover data. You cannot query it, you cannot use it yourself, and you pay to store it. For a table you can rebuild from source in twenty minutes, that is money for nothing.

CREATE TABLE          dim_customer (id INT, name STRING);   -- permanent
CREATE TRANSIENT TABLE stg_orders    (id INT, total NUMBER(12,2));
CREATE TEMPORARY TABLE tmp_ids       (id INT);               -- gone at logout

The rule of thumb: permanent for anything that is a source of truth, transient for anything derived from something else you still have. Staging tables, intermediate models and scratch space are all transient.

One trap: a temporary table shadows a permanent one of the same name for the whole session. Create TEMPORARY TABLE orders and every unqualified reference to orders hits the temporary one until you disconnect — including in stored procedures you did not write.

Constraints that are not enforced

Snowflake accepts PRIMARY KEY, FOREIGN KEY and UNIQUE, and records them in the metadata. It does not check any of them. Only NOT NULL is enforced.

CREATE TABLE customer (
  customer_id  INT           NOT NULL,   -- enforced
  email        STRING        NOT NULL,   -- enforced
  segment      STRING,
  created_at   TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(),
  CONSTRAINT pk_customer PRIMARY KEY (customer_id)   -- NOT enforced
);

-- Both of these succeed. There is no error and no warning.
INSERT INTO customer (customer_id, email) VALUES (1, 'a@example.com');
INSERT INTO customer (customer_id, email) VALUES (1, 'b@example.com');

Declare them anyway. The optimiser uses them for join elimination, BI tools read them to infer relationships, and they document intent. But uniqueness is your loader's responsibility — usually a MERGE on the key rather than an INSERT, which lesson 13 uses throughout. If you need to know whether duplicates crept in, ask:

SELECT customer_id, COUNT(*) AS n
FROM   customer
GROUP  BY customer_id
HAVING COUNT(*) > 1;

Creating tables from other tables

Three forms, and the difference between them matters:

-- 1. CTAS: structure and data, from a query.
CREATE TABLE big_orders AS
SELECT * FROM snowflake_sample_data.tpch_sf1.orders WHERE o_totalprice > 400000;

-- 2. LIKE: structure only — columns, types, defaults, constraints. No rows.
CREATE TABLE orders_backup LIKE snowflake_sample_data.tpch_sf1.orders;

-- 3. CLONE: structure AND data, instantly, at no storage cost.
CREATE TABLE orders_snapshot CLONE snowflake_sample_data.tpch_sf1.orders;

The third is the one without an equivalent elsewhere. A clone copies metadata, not data — both tables point at the same micro-partitions, and storage is only consumed as one of them diverges. It is instant regardless of table size. Cloning a whole database to get a development copy of production is a single statement, and lesson 12 is largely about that.

Views and materialized views

-- A view is stored SQL. It runs every time it is queried.
CREATE OR REPLACE VIEW vw_order_summary AS
SELECT o_orderdate,
       COUNT(*)            AS orders,
       SUM(o_totalprice)   AS revenue
FROM   snowflake_sample_data.tpch_sf1.orders
GROUP  BY o_orderdate;

-- A secure view hides its definition and blocks optimisations that could
-- leak rows. Use it whenever a view is the security boundary.
CREATE OR REPLACE SECURE VIEW vw_my_orders AS
SELECT * FROM orders WHERE owner = CURRENT_USER();

A materialized view stores its results and keeps them up to date automatically as the base table changes. That maintenance is serverless and billed, so it is worth it only when a view is expensive and queried far more often than its base table changes. They also come with real restrictions — one table only, no joins, no window functions — and require Enterprise edition.

Reach for a materialized view when an expensive aggregate over a slow-changing table is queried constantly. Reach for a regular view, or a table refreshed by a task, for everything else.

Altering tables

ALTER TABLE customer ADD COLUMN country STRING;
ALTER TABLE customer RENAME COLUMN segment TO market_segment;
ALTER TABLE customer DROP COLUMN market_segment;

-- Widening is free. Narrowing a type is not allowed.
ALTER TABLE customer ALTER COLUMN email SET DATA TYPE STRING;

ALTER TABLE customer SET DATA_RETENTION_TIME_IN_DAYS = 30;

Adding a column is a metadata change — instant, on a table of any size, because existing micro-partitions simply have no value for it. Dropping a column is likewise metadata. This is one of the pleasant surprises of the architecture: schema changes that would be an overnight maintenance window on a row-store are a statement here.

A schema layout that survives

Since databases and schemas are free, the useful question is not how few you can get away with but what division makes permissions and dependencies obvious. The layout that most teams converge on separates data by how trustworthy it is:

The reason it works is that the read boundary and the rebuild boundary fall in the same place. Everything before MARTS can be dropped and rebuilt from source, so it is transient and nobody outside the pipeline depends on it. Everything in MARTS is what other people build on, so it is permanent and it is where Time Travel is worth paying for. Lesson 14 grants against exactly these three names.

Two other table kinds exist and are worth knowing by name rather than by heart. External tables put a schema over files sitting in your own cloud storage without loading them, which is useful for data you query rarely. Dynamic tables declare a query and let Snowflake keep the result fresh on a lag you specify, which is a simpler alternative to the streams-and-tasks pipeline in lesson 13 when the transformation is expressible as one query.

Finding out what exists

SHOW DATABASES;
SHOW SCHEMAS IN DATABASE analytics;
SHOW TABLES IN SCHEMA analytics.marts;
DESCRIBE TABLE customer;

-- Size and row counts, from metadata — no scanning.
SELECT table_name, row_count, ROUND(bytes / POWER(1024, 3), 2) AS gb
FROM   analytics.information_schema.tables
WHERE  table_schema = 'MARTS'
ORDER  BY bytes DESC;

Next: data types, and the three that cause real bugs.