ALTER SCHEMA

Modifies the properties for an existing schema, including renaming the schema or swapping it with another schema, and changing the Time Travel data retention period (if you are using Snowflake Enterprise Edition or higher).

See also:

CREATE SCHEMA , DESCRIBE SCHEMA , DROP SCHEMA , SHOW SCHEMAS , UNDROP SCHEMA

Syntax

ALTER SCHEMA [ IF EXISTS ] <name> RENAME TO <new_schema_name>

ALTER SCHEMA [ IF EXISTS ] <name> SWAP WITH <target_schema_name>

ALTER SCHEMA [ IF EXISTS ] <name> SET {
                                      [ 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 } ]
                                      [ CLASSIFICATION_PROFILE = '<classification_profile>' ]
                                      [ COMMENT = '<string_literal>' ]
                                      }

ALTER SCHEMA [ IF EXISTS ] <name> SET TAG <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' ... ]

ALTER SCHEMA [ IF EXISTS ] <name> UNSET TAG <tag_name> [ , <tag_name> ... ]

ALTER SCHEMA [ IF EXISTS ] <name> UNSET {
                                        DATA_RETENTION_TIME_IN_DAYS         |
                                        MAX_DATA_EXTENSION_TIME_IN_DAYS     |
                                        EXTERNAL_VOLUME                     |
                                        CATALOG                             |
                                        REPLACE_INVALID_CHARACTERS          |
                                        DEFAULT_DDL_COLLATION               |
                                        LOG_LEVEL                           |
                                        TRACE_LEVEL                         |
                                        STORAGE_SERIALIZATION_POLICY        |
                                        COMMENT
                                        }
                                        [ , ... ]

ALTER SCHEMA [ IF EXISTS ] <name> { ENABLE | DISABLE } MANAGED ACCESS
Copy

Parameters

name

Specifies the identifier for the schema to alter. If the identifier contains spaces, special characters, or mixed-case characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive.

RENAME TO new_schema_name

Specifies the new identifier for the schema; must be unique for the database.

For more details, see Identifier requirements.

You can move the object to a different database while optionally renaming the schema. To do so, specify a qualified new_schema_name value that includes the new database name in the form db_name.new_schema_name.

Note

The destination database must already exist. In addition, a schema with the same name cannot already exist in the new location; otherwise, the statement returns an error.

When an object is renamed, other objects that reference it must be updated with the new name.

SWAP WITH target_schema_name

Swaps all objects (tables, views, etc.) and metadata, including identifiers, between the two specified schemas. Also swaps all access control privileges granted on the schemas and objects they contain. SWAP WITH essentially performs a rename of both schemas as a single operation.

SET ...

Specifies one (or more) properties to set for the schema (separated by blank spaces, commas, or new lines):

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.

The value you can specify depends on the Snowflake Edition you are using:

  • Standard Edition: 0 or 1

  • Enterprise Edition (or higher): 0 to 90

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:

  • Any new columns added to existing tables in the schema.

  • All columns in new tables added to the schema.

Setting the parameter does not change the collation specification for any existing columns.

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

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.

CLASSIFICATION_PROFILE = 'classification_profile'

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

COMMENT = 'string_literal'

Adds a comment or overwrites an existing comment for the schema.

UNSET ...

Specifies one (or more) properties and/or parameters to unset for the database, which resets them to the defaults:

  • DATA_RETENTION_TIME_IN_DAYS

  • MAX_DATA_EXTENSION_TIME_IN_DAYS

  • EXTERNAL_VOLUME

  • CATALOG

  • DEFAULT_DDL_COLLATION

  • TAG tag_name [ , tag_name ... ]

  • COMMENT

You can reset multiple properties/parameters with a single ALTER statement; however, each property/parameter must be separated by a comma. When resetting a property/parameter, specify only the name; specifying a value for the property will return an error.

ENABLE | DISABLE MANAGED ACCESS

Enable managed access for a schema, or disable to convert a managed access schema to a regular 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 access 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.

Usage notes

  • To rename a schema, the role used to perform the operation must have the CREATE SCHEMA privilege on the database for the schema and OWNERSHIP privileges on the schema.

  • To swap two schemas, the role used to perform the operation must have OWNERSHIP privileges on both schemas.

  • To convert a regular schema to a managed access schema:

    • The schema owner (i.e. the role that has the OWNERSHIP privileges on the schema) must also have the global MANAGE GRANTS privilege. The MANAGE GRANTS privilege is required because another role with this privilege could have defined future grants on objects of a specified type in the schema. After a regular schema becomes a managed access schema, the schema owner could revoke the future grants without understanding why a role with the MANAGE GRANTS privilege granted them.

    • All open future grants must be revoked using REVOKE <privileges> with the FUTURE keyword.

    After a regular schema is converted to a managed access schema, all privileges previously granted on individual objects are retained; however, the object owners cannot grant further privileges on those objects.

  • To convert a managed access schema to a regular schema, the schema owner must also have the global MANAGE GRANTS privilege only if the current schema has future privilege grants defined.

  • 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

Rename schema schema1 to schema2:

ALTER SCHEMA IF EXISTS schema1 RENAME TO schema2;
Copy

Convert a regular schema to a managed access schema:

ALTER SCHEMA schema2 ENABLE MANAGED ACCESS;
Copy
Language: English