CREATE SCHEMA command syntax in Snowflake

The schema is a logical grouping of tables/views in Snowflake. To create a new schema, navigate to the correct database and execute the below command,

Example:

create schema myschema;

Syntax:

CREATE [ OR REPLACE ] [ TRANSIENT ] SCHEMA [ IF NOT EXISTS ] <name>
  [ CLONE <source_schema>
        [ { AT | BEFORE } ( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> } ) ] ]
  [ DATA_RETENTION_TIME_IN_DAYS= <num> ]
  [ COMMENT = '<string_literal>' ]

• Transient schemas do not have a Fail-safe period so they do not incur additional storage costs once they leave Time Travel; however, this means they are also not protected by Fail-safe in the event of a data loss
  
• DATA_RETENTION_TIME_IN_DAYS : Specifies the number of days for which Time Travel actions (CLONE and UNDROP) can be performed on the schema, as well as specifying the default Time Travel retention time for all tables created in the schema
  

Once the schema is created you can use the below command to verify if the schema is created properly

show schemas;

To start creating tables or views in the new schema simply use the "USE SCHEMA" command to navigate to the new schema. Creating a new schema also sets it as the active schema.

USE SCHEMA myschema;

Just to be sure, use the below command:

select current_warehouse(), current_database(), current_schema();

We are all set to create tables in the new schema now!

DataFreak

posted on 19 Oct 18