CREATE SCHEMA | Snowflake Documentation (2024)

Syntax¶

CREATE [ OR REPLACE ] [ TRANSIENT ] SCHEMA [ IF NOT EXISTS ] <name> [ CLONE <source_schema> [ { AT | BEFORE } ( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> } ) ] ] [ 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>' , ... ] ) ]

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 entireidentifier string is enclosed in double quotes (e.g. "My object"). Identifiers enclosed in double quotes are alsocase-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 oncethey 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, seeCREATE 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 orbefore a specific point in the past.

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 privilegeson their objects to other roles. In managed schemas, the schema owner manages all privilege grants, includingfuture grants, on objects in the schema. Object owners retain the OWNERSHIPprivileges 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 thedefault Time Travel retention time for all tables created in the schema. For more details, see .

For a detailed description of this object-level parameter, as well as more information about object parameters, seeParameters. For more information about table-level retention time, seeCREATE TABLE and .

Values:

• Standard Edition: 0 or 1

  See Also

  Snowflake Schema in Data Warehouse Model - GeeksforGeeksWas ist das Snowflake Schema? | Data BasecampSnowflake Information Schema | Snowflake Documentation

• 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 inthe 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 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 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 anIceberg 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-8characters 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 defaultcan 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 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 followingprivileges at a minimum:

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 onsecurable 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 theUSE 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 optionalOR 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 withthe 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, seeStorage 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 otherprivileges (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