CREATE SCHEMA¶

Creates a new schema in the current database.

In addition, this command can be used to clone an existing schema, either at its current state or at a specific time/point in the past (using Time Travel). For more information about cloning a schema, see Cloning considerations.

See also:: ALTER SCHEMA , DESCRIBE SCHEMA , DROP SCHEMA , SHOW SCHEMAS , UNDROP SCHEMA

Syntax¶

CREATE [ OR REPLACE ] [ TRANSIENT ] SCHEMA [ IF NOT EXISTS ] <name>
  [ CLONE <source_schema>
      [ { AT | BEFORE } ( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> } ) ]
      [ IGNORE TABLES WITH INSUFFICIENT DATA RETENTION ]
      [ IGNORE HYBRID TABLES ] ]
  [ WITH MANAGED ACCESS ]
  [ DATA_RETENTION_TIME_IN_DAYS = <integer> ]
  [ MAX_DATA_EXTENSION_TIME_IN_DAYS = <integer> ]
  [ EXTERNAL_VOLUME = <external_volume_name> ]
  [ CATALOG = <catalog_integration_name> ]
  [ REPLACE_INVALID_CHARACTERS = { TRUE | FALSE } ]
  [ DEFAULT_DDL_COLLATION = '<collation_specification>' ]
  [ STORAGE_SERIALIZATION_POLICY = { COMPATIBLE | OPTIMIZED } ]
  [ COMMENT = '<string_literal>' ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]

Copy

Required parameters¶

name

Specifies the identifier for the schema; must be unique for the database in which the schema is created.

In addition, the identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (e.g. "My object"). Identifiers enclosed in double quotes are also case-sensitive.

For more details, see Identifier requirements.

Optional parameters¶

TRANSIENT

Specifies a schema as transient. 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. For more information, see Understanding and viewing Fail-safe.

In addition, by definition, all tables created in a transient schema are transient. For more information about transient tables, see CREATE TABLE.

Default: No value (i.e. schema is permanent)

CLONE source_schema

Specifies to create a clone of the specified source schema. For more details about cloning a schema, see CREATE <object> … CLONE.

AT | BEFORE ( TIMESTAMP => timestamp | OFFSET => time_difference | STATEMENT => id )

When cloning a schema, the AT | BEFORE clause specifies to use Time Travel to clone the schema at or before a specific point in the past.

IGNORE TABLES WITH INSUFFICIENT DATA RETENTION

Ignore tables that no longer have historical data available in Time Travel to clone. If the time in the past specified in the AT | BEFORE clause is beyond the data retention period for any child table in a database or schema, skip the cloning operation for the child table. For more information, see Child Objects and Data Retention Time.

IGNORE HYBRID TABLES

Ignore hybrid tables, which will not be cloned. Use this option to clone a schema that contains hybrid tables. The cloned schema includes other objects but skips hybrid tables.

If you don’t use this option and your schema contains one or more hybrid tables, the command ignores hybrid tables silently. However, the error handling for schemas that contain hybrid tables will change in an upcoming release; therefore, you may want to add this parameter to your commands preemptively.

WITH MANAGED ACCESS

Specifies a managed schema. Managed access schemas centralize privilege management with the schema owner.

In regular schemas, the owner of an object (i.e. the role that has the OWNERSHIP privilege on the object) can grant further privileges on their objects to other roles. In managed schemas, the schema owner manages all privilege grants, including future grants, on objects in the schema. Object owners retain the OWNERSHIP privileges on the objects; however, only the schema owner can manage privilege grants on the objects.

DATA_RETENTION_TIME_IN_DAYS = integer

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. For more details, see Understanding & using Time Travel.

For a detailed description of this object-level parameter, as well as more information about object parameters, see Parameters. For more information about table-level retention time, see CREATE TABLE and Understanding & using Time Travel.

Values:

Standard Edition: 0 or 1

Enterprise Edition:

0 to 90 for permanent schemas

0 or 1 for transient schemas

Default:

Standard Edition: 1

Enterprise Edition (or higher): 1 (unless a different default value was specified at the database or account level)

Note

A value of 0 effectively disables Time Travel for the schema.

MAX_DATA_EXTENSION_TIME_IN_DAYS = integer

Object parameter that specifies the maximum number of days for which Snowflake can extend the data retention period for tables in the schema to prevent streams on the tables from becoming stale.

For a detailed description of this parameter, see MAX_DATA_EXTENSION_TIME_IN_DAYS.

EXTERNAL_VOLUME = external_volume_name

Object parameter that specifies the default external volume to use for Apache Iceberg™ tables.

For more information about this parameter, see EXTERNAL_VOLUME.

CATALOG = catalog_integration_name

Object parameter that specifies the default catalog integration to use for Apache Iceberg™ tables.

For more information about this parameter, see CATALOG.

REPLACE_INVALID_CHARACTERS = { TRUE | FALSE }

Specifies whether to replace invalid UTF-8 characters with the Unicode replacement character (�) in query results for an Iceberg table. You can only set this parameter for tables that use an external Iceberg catalog.

TRUE replaces invalid UTF-8 characters with the Unicode replacement character.
FALSE leaves invalid UTF-8 characters unchanged. Snowflake returns a user error message when it encounters invalid UTF-8 characters in a Parquet data file.

Default: FALSE

DEFAULT_DDL_COLLATION = 'collation_specification'

Specifies a default collation specification for all tables added to the schema. The default can be overridden at the individual table level.

For more details about the parameter, see DEFAULT_DDL_COLLATION.

STORAGE_SERIALIZATION_POLICY = { COMPATIBLE | OPTIMIZED }

Specifies the storage serialization policy for Apache Iceberg™ tables that use Snowflake as the catalog.

COMPATIBLE: Snowflake performs encoding and compression of data files that ensures interoperability with third-party compute engines.
OPTIMIZED: Snowflake performs encoding and compression of data files that ensures the best table performance within Snowflake.

Default: OPTIMIZED

COMMENT = 'string_literal'

Specifies a comment for the schema.

Default: No value

TAG ( tag_name = 'tag_value' [ , tag_name = 'tag_value' , ... ] )

Specifies the tag name and the tag string value.

The tag value is always a string, and the maximum number of characters for the tag value is 256.

For information about specifying tags in a statement, see Tag quotas for objects and columns.

Access control requirements¶

A role used to execute this SQL command must have the following privileges at a minimum:

Privilege	Object	Notes
CREATE SCHEMA	Database	Can create both regular and managed access schemas.
CREATE SCHEMA … CLONE … WITH MANAGED ACCESS	Options	The required privileges depends on whether the source schema is managed or unmanaged: Managed: OWNERSHIP on the source schema. Unmanaged: MANAGE GRANTS ON ACCOUNT and USAGE on the source schema.
USAGE	External volume, catalog integration	Required if setting the `EXTERNAL_VOLUME` or `CATALOG` object parameters, respectively.

Privilege

Object

Notes

CREATE SCHEMA

Database

Can create both regular and managed access schemas.

CREATE SCHEMA … CLONE … WITH MANAGED ACCESS

Options

The required privileges depends on whether the source schema is managed or unmanaged:

Managed: OWNERSHIP on the source schema.
Unmanaged: MANAGE GRANTS ON ACCOUNT and USAGE on the source schema.

USAGE

External volume, catalog integration

Required if setting the EXTERNAL_VOLUME or CATALOG object parameters, respectively.

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

Usage notes¶

Creating a schema automatically sets it as the active/current schema for the current session (equivalent to using the USE SCHEMA command for the schema).
If a schema with the same name already exists in the database, an error is returned and the schema is not created, unless the optional OR REPLACE keyword is specified in the command.

Important

Using OR REPLACE is the equivalent of using DROP SCHEMA on the existing schema and then creating a new schema with the same name; however, the dropped schema is not permanently removed from the system. Instead, it is retained in Time Travel. This is important because dropped schemas in Time Travel contribute to data storage for your account. For more information, see Storage Costs for Time Travel and Fail-safe.
CREATE OR REPLACE <object> statements are atomic. That is, when an object is replaced, the old object is deleted and the new object is created in a single transaction.
In a managed access schema, the schema owner manages grants on the contained objects (e.g. tables or views) but has no other privileges (USAGE, SELECT, DROP, etc.) on the objects.
Regarding metadata:

Attention

Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata fields in Snowflake.

Examples¶

Create a permanent schema:

CREATE SCHEMA myschema;

SHOW SCHEMAS;

+-------------------------------+--------------------+------------+------------+---------------+--------------+-----------------------------------------------------------+---------+----------------+
| created_on                    | name               | is_default | is_current | database_name | owner        | comment                                                   | options | retention_time |
|-------------------------------+--------------------+------------+------------+---------------+--------------+-----------------------------------------------------------+---------+----------------|
| 2018-12-10 09:34:02.127 -0800 | INFORMATION_SCHEMA | N          | N          | MYDB          |              | Views describing the contents of schemas in this database |         | 1              |
| 2018-12-10 09:33:56.793 -0800 | MYSCHEMA           | N          | Y          | MYDB          | PUBLIC       |                                                           |         | 1              |
| 2018-11-26 06:08:24.263 -0800 | PUBLIC             | N          | N          | MYDB          | PUBLIC       |                                                           |         | 1              |
+-------------------------------+--------------------+------------+------------+---------------+--------------+-----------------------------------------------------------+---------+----------------+

Copy

Create a transient schema:

CREATE TRANSIENT SCHEMA tschema;

SHOW SCHEMAS;

+-------------------------------+--------------------+------------+------------+---------------+--------------+-----------------------------------------------------------+-----------+----------------+
| created_on                    | name               | is_default | is_current | database_name | owner        | comment                                                   | options   | retention_time |
|-------------------------------+--------------------+------------+------------+---------------+--------------+-----------------------------------------------------------+-----------+----------------|
| 2018-12-10 09:34:02.127 -0800 | INFORMATION_SCHEMA | N          | N          | MYDB          |              | Views describing the contents of schemas in this database |           | 1              |
| 2018-12-10 09:33:56.793 -0800 | MYSCHEMA           | N          | Y          | MYDB          | PUBLIC       |                                                           |           | 1              |
| 2018-11-26 06:08:24.263 -0800 | PUBLIC             | N          | N          | MYDB          | PUBLIC       |                                                           |           | 1              |
| 2018-12-10 09:35:32.326 -0800 | TSCHEMA            | N          | Y          | MYDB          | PUBLIC       |                                                           | TRANSIENT | 1              |
+-------------------------------+--------------------+------------+------------+---------------+--------------+-----------------------------------------------------------+-----------+----------------+

Copy

Create a managed access schema:

CREATE SCHEMA mschema WITH MANAGED ACCESS;

SHOW SCHEMAS;

+-------------------------------+--------------------+------------+------------+---------------+--------------+-----------------------------------------------------------+----------------+----------------+
| created_on                    | name               | is_default | is_current | database_name | owner        | comment                                                   | options        | retention_time |
|-------------------------------+--------------------+------------+------------+---------------+--------------+-----------------------------------------------------------+----------------+----------------|
| 2018-12-10 09:34:02.127 -0800 | INFORMATION_SCHEMA | N          | N          | MYDB          |              | Views describing the contents of schemas in this database |                | 1              |
| 2018-12-10 09:36:47.738 -0800 | MSCHEMA            | N          | Y          | MYDB          | ROLE1        |                                                           | MANAGED ACCESS | 1              |
| 2018-12-10 09:33:56.793 -0800 | MYSCHEMA           | N          | Y          | MYDB          | PUBLIC       |                                                           |                | 1              |
| 2018-11-26 06:08:24.263 -0800 | PUBLIC             | N          | N          | MYDB          | PUBLIC       |                                                           |                | 1              |
| 2018-12-10 09:35:32.326 -0800 | TSCHEMA            | N          | Y          | MYDB          | PUBLIC       |                                                           | TRANSIENT      | 1              |
+-------------------------------+--------------------+------------+------------+---------------+--------------+-----------------------------------------------------------+----------------+----------------+

Copy