Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

How to Create Schema in Snowflake: Syntax and Examples

To create a schema in Snowflake, use the CREATE SCHEMA command followed by the schema name. You can optionally specify the database and add comments or set permissions during creation.
📐

Syntax

The basic syntax to create a schema in Snowflake is:

  • CREATE SCHEMA <schema_name>; - creates a schema in the current database.
  • CREATE SCHEMA <database_name>.<schema_name>; - creates a schema in a specified database.
  • You can add COMMENT = 'text' to describe the schema.
sql
CREATE SCHEMA [IF NOT EXISTS] <schema_name> [COMMENT = '<comment_text>'];
💻

Example

This example creates a schema named sales_data in the current database with a comment describing its purpose.

sql
CREATE SCHEMA IF NOT EXISTS sales_data COMMENT = 'Schema for sales related tables';
Output
Schema SALES_DATA successfully created.
⚠️

Common Pitfalls

Common mistakes when creating schemas in Snowflake include:

  • Not specifying IF NOT EXISTS which causes an error if the schema already exists.
  • Trying to create a schema in a database without switching to it or specifying the database name.
  • Using invalid characters or reserved words in schema names.
sql
/* Wrong: causes error if schema exists */
CREATE SCHEMA sales_data;

/* Right: avoids error if schema exists */
CREATE SCHEMA IF NOT EXISTS sales_data;
📊

Quick Reference

CommandDescription
CREATE SCHEMA ;Creates a new schema in the current database.
CREATE SCHEMA IF NOT EXISTS ;Creates schema only if it does not exist.
CREATE SCHEMA .;Creates schema in specified database.
COMMENT = ''Adds a description to the schema.
✅

Key Takeaways

Use CREATE SCHEMA followed by the schema name to create a schema in Snowflake.
Add IF NOT EXISTS to avoid errors if the schema already exists.
Specify the database name if creating schema outside the current database.
Use comments to describe the schema purpose for clarity.
Avoid using reserved words or invalid characters in schema names.