How to Create a Table in Snowflake

Here's an example of creating a users table in Snowflake:

create table users (
  id integer autoincrement, -- auto incrementing IDs
  name varchar (100),  -- variable string column
  preferences variant, -- column used to store JSON type of data
  created_at timestamp
);

Within the parentheses are what's called column definitions, separated by commas. The minimum required fields for a column definition are column name and data type (shown above for columns name, preferences, and created_at). The id column has extra field to use an auto-incrementing feature to assign it values.

This is also a chance to specify not null constraints and default values:

create table users (
  id integer autoincrement,
  name varchar(100) not null,
  active boolean default true
);

You can also create temporary tables that will stick around for the duration of your session. This is helpful to break down your analysis into smaller pieces.

create temporary table inactive_users (
  id integer autoincrement,
  name varchar(100) not null,
  active boolean default false
);

Snowflake allows us to create transient tables which are a mix of permanent and temporary tables. They are used to store temporary data outside our session without having the need to implement a high level of data security and data recovery.

create transient table active_users (
  id integer autoincrement,
  name varchar(100) not null,
  active boolean default true
);