CREATE SCHEMA¶

Creates a new schema in the current database.

This command supports the following variants:

CREATE OR ALTER SCHEMA: Creates a schema if it doesn’t exist or alters an existing schema.
CREATE SCHEMA … CLONE: Creates a clone of 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.

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 } ]
  [ CLASSIFICATION_PROFILE = '<classification_profile>' ]
  [ COMMENT = '<string_literal>' ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]

Copy

Variant syntax¶

CREATE OR ALTER SCHEMA¶

Creates a new schema if it doesn’t already exist, or transforms an existing schema into the schema defined in the statement. A CREATE OR ALTER SCHEMA statement follows the syntax rules of a CREATE SCHEMA statement and has the same limitations as an ALTER SCHEMA statement.

For more information, see CREATE OR ALTER SCHEMA usage notes.

CREATE OR ALTER [ TRANSIENT ] SCHEMA <name>
  [ 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>' ]
  [ LOG_LEVEL = '<log_level>' ]
  [ TRACE_LEVEL = '<trace_level>' ]
  [ STORAGE_SERIALIZATION_POLICY = { COMPATIBLE | OPTIMIZED } ]
  [ COMMENT = '<string_literal>' ]

Copy

CREATE SCHEMA … CLONE¶

Creates a new schema with the same parameter values:

CREATE [ OR REPLACE ] SCHEMA [ IF NOT EXISTS ] <name> CLONE <source_schema>
  [ ... ]

Copy

For more details, see CREATE <object> … CLONE.

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.

LOG_LEVEL = 'log_level'

Specifies the severity level of messages that should be ingested and made available in the active event table. Messages at the specified level (and at more severe levels) are ingested.

For more information about levels, see LOG_LEVEL. For information about setting log level, see Setting levels for logging, metrics, and tracing.

TRACE_LEVEL = 'trace_level'

Controls how trace events are ingested into the event table.

For information about levels, see TRACE_LEVEL. For information about setting trace level, see Setting levels for logging, metrics, and tracing.

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

CLASSIFICATION_PROFILE = 'classification_profile'

Associates the schema with a classification profile so that sensitive data in the schema is automatically classified.

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.
MODIFY LOG LEVEL	Account	Required to set the LOG_LEVEL for a schema.
MODIFY TRACE LEVEL	Account	Required to set the TRACE_LEVEL for a schema.
OWNERSHIP	Schema	Required only when executing a CREATE OR ALTER SCHEMA statement for an existing schema. OWNERSHIP is a special privilege on an object that is automatically granted to the role that created the object, but can also be transferred using the GRANT OWNERSHIP command to a different role by the owning role (or any role with the MANAGE GRANTS privilege).

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.

General 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.

CREATE OR ALTER SCHEMA usage notes¶

All limitations of the ALTER SCHEMA command apply.
This command does not support the following:
- Swapping schemas using the SWAP WITH parameter.
- Renaming a schema using the RENAME TO parameter.
- Creating a clone of a schema using the CLONE parameter.
- Adding or changing tags and policies. Any existing tags and policies are preserved.
- Converting a TRANSIENT schema to a non-TRANSIENT schema, or vice versa.

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

CREATE OR ALTER SCHEMA examples¶

Create a simple schema¶

Create a schema named s1:

CREATE OR ALTER SCHEMA s1;

Copy

Create or alter schema s1 and set properties and parameters:

CREATE OR ALTER SCHEMA s1
  WITH MANAGED ACCESS
  DATA_RETENTION_TIME_IN_DAYS = 5
  DEFAULT_DDL_COLLATION = 'de';

Copy

Unset a parameter previously set on schema¶

The absence of a previously set parameter in the modified schema definition results in unsetting it. In the following example, turn off managed access for the schema s1 created in the previous example:

CREATE OR ALTER SCHEMA s1
  DATA_RETENTION_TIME_IN_DAYS = 5
  DEFAULT_DDL_COLLATION = 'de';

Copy