CREATE OR ALTER VERSIONED SCHEMA¶
Creates a new versioned schema or modifies an existing versioned schema. This command is only supported for application instances in the Native Apps Framework.
CREATE OR ALTER VERSIONED SCHEMA <name> [ WITH MANAGED ACCESS ] [ DATA_RETENTION_TIME_IN_DAYS = ] [ MAX_DATA_EXTENSION_TIME_IN_DAYS = ] [ DEFAULT_DDL_COLLATION = '<collation_specification>' ] [ COMMENT = '<string_literal>' ]
Specifies the identifier for the schema; must be unique for the application instance 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.
WITH MANAGED ACCESS
Specifies a managed versioned schema. Managed access versioned schemas centralize privilege management with the schema owner.
In regular versioned 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, refer Understanding & Using Time Travel.
For a detailed description of this object-level parameter, as well as more information about object parameters, refer to Parameters. For more information about table-level retention time, refer to CREATE TABLE and Understanding & Using Time Travel.
90for permanent schemas
1for transient schemas
Enterprise Edition (or higher):
1(unless a different default value was specified at the database or account level)
A value of
0effectively 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.
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.
COMMENT = 'string_literal'
Specifies a comment for the schema.
Default: No value
Access Control Requirements¶
If the schema already exists and you want to modify the schema, the OWNERSHIP privilege on the application is required.
For instructions on creating a custom role with a specified set of privileges, see Creating Custom Roles.
While you typically create a versioned schema in the set up script, a versioned schema can be created:
From an owner’s rights stored procedure.
In the consumer account using an application role that has the CREATE SCHEMA privilege on the application.
If the schema does not exist, Snowflake creates a versioned schema.
If the schema exists and already matches command, Snowflake views this as a no-operation.
If the schema exists and does not match the command, Snowflake modifies the versioned schema to match the command.