Create tables

Opening your lesson

Two ways to make a table:

From a query: CREATE TABLE sandbox.returns AS SELECT ... (CTAS) takes its columns and types from the query.
From a column list: CREATE TABLE sandbox.targets (region VARCHAR NOT NULL, month DATE, target NUMBER(10,2)).

CREATE OR REPLACE rebuilds a table that exists, and IF NOT EXISTS skips one that does.

Snowflake has three kinds of table. Permanent is the default, with time travel (a day by default) and then seven days of Fail-safe. Transient tables have no Fail-safe and at most a day of time travel, so they cost less to keep: use them for staging data you can rebuild. Temporary tables last only for your session.

Constraints surprise people: Snowflake records PRIMARY KEY, UNIQUE and FOREIGN KEY but doesn't enforce them. Only NOT NULL is enforced. Your role, ANALYST, can create tables in RIDGELINE.SANDBOX.

CREATE OR REPLACE TABLE sandbox.returns AS
SELECT order_id, customer_id, order_total
FROM sales.orders
WHERE status = 'returned';

A new table built from a query, its columns named after the query's.

Choose your character

Your Work Gym account