Snowflake CREATE SCHEMA: Organizing Your Database Objects
CREATE SCHEMA in Snowflake is a command used to create a new schema within the current database. A schema is a logical grouping of database objects such as tables, views, and stored procedures. This command can also clone existing schemas, including their state at a specific point in the past using Snowflake’s Time Travel feature.
Basic Syntax
_10CREATE [OR REPLACE] [TRANSIENT] SCHEMA [IF NOT EXISTS] <schema_name>_10 [CLONE <source_schema_name>];
Options:
- OR REPLACE: Optionally replace an existing schema with the same name.
- TRANSIENT: Create a transient schema, which is not covered by Fail-safe.
- IF NOT EXISTS: Only create the schema if it doesn’t already exist.
- CLONE: Create a duplicate of an existing schema, optionally from a past state.
Best Practices
- Transient Schemas: These schemas do not incur storage costs after they exit the Time Travel period, but they also lack Fail-safe protection.
- Cloning: You can clone a schema to quickly set up environments for testing or development without affecting the production data.
- Managed Access: By using
WITH MANAGED ACCESS, you centralize the privilege management, allowing only the schema owner to manage privileges on contained objects.
Examples
Creating a Basic Schema
Snowflake SQL Query
_10CREATE SCHEMA my_new_schema;
Creating a Schema from a Clone
Snowflake SQL Query
_10CREATE SCHEMA my_clone_schema CLONE existing_schema;
Creating a Transient Schema
Snowflake SQL Query
_10CREATE TRANSIENT SCHEMA temp_schema;
Related Commands
ALTER SCHEMA
Modify existing schemas.
_10ALTER SCHEMA my_schema RENAME TO my_renamed_schema;
DROP SCHEMA
Remove a schema.
_10DROP SCHEMA my_schema;
DESCRIBE SCHEMA
View schema details.
_10DESCRIBE SCHEMA my_schema;
SHOW SCHEMAS
List all schemas in the database.
_10SHOW SCHEMAS;
UNDROP SCHEMA
Recover a dropped schema within the Time Travel retention period.
_10UNDROP SCHEMA my_dropped_schema;
