CREATE DATABASE command syntax in Snowflake

The database holds all the Schemas that in turn contains all the tables and other objects. To create a new database,

Example:

CREATE DATABASE SALES_DB
DATA_RETENTION_TIME_IN_DAYS = 0
COMMENT = 'Ecommerce sales info' ;

Syntax:

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

NOTE: 

Transient databases 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 database, as well as specifying the default Time Travel retention time for all schemas created in the database

Use the below command to verify:

SHOW DATABASES;

OR 

show databases like 'SALES%';

Creating a new database automatically creates two schemas in the database:

• PUBLIC: Default schema for the database. The PUBLIC schema can be dropped
  
• INFORMATION_SCHEMA: Schema which contains views and table functions that can be used for querying metadata about the objects in the database, as well as across all objects in the account

dan-irving

posted on 19 Oct 18