Enhance Data Management with Snowflake Create Schema

A schema is a logical representation of how data is organized into tables. Before importing any dataset into a data warehouse platform like Snowflake, it is necessary to understand how to create a schema in Snowflake. This allows better organization and accessibility of your data in the Snowflake environment.

This article discusses the methods for creating a schema in the Snowflake database. It covers the use of the GUI and the Snowflake CREATE SCHEMA function in addition to examples demonstrating the creation of a schema.

What Are the Methods to Create a Schema in Snowflake?

Snowflake is a prominently used data warehousing platform that can help you manage and analyze your data. By creating a schema in Snowflake, you can organize your data and ensure controlled access to it for security purposes. This section will discuss the methods that you can follow for Snowflake create schema.

There are two methods to create a schema in Snowflake: the user interface and the SQL interface. 

Method 1: Using Snowflake User Interface to Create Schema

This section discusses how to create, modify, and view your database schema with the help of the Snowflake user interface.

Create a Schema

To create a Snowflake schema using the user interface, you must log in to your Snowflake account. On the database view screen, click on the database name under the Database column where you want to create a schema.

You will see a new database properties page containing information about the selected database’s tables, schemas, and views. Click on the Schema option on this page to proceed to the next step.

After clicking on the Schema option, all the available schemas in this database will appear on the screen. You can click on the + Create button above the Schema column. A Create Schema dialog box will appear, where you can enter the name of the schema and click Finish.

After clicking the Finish button, the new schema name will appear under the Schema column.

Modify Your Database Schema

You can easily modify the name of your database schema in Snowflake. To do this, you must select the Databases icon from the top ribbon and choose the name of the database containing your schema. You can click on the Schemas option to view a schema list and select the schema that requires modification. Then, click on the Alter icon.

The Alter Schema dialog box will pop up where you can alter the schema name. Finally, click Finish to apply the changes.

The updated schema name appears on the database schema list.

Clone and Drop Database Schema

The Snowflake interface allows you to clone and delete schemas from the database. To do this, click on the Databases icon on the top ribbon and select a database from the View panel. Then, click on the Schemas option; a list of schemas will appear in the database. Select the schema name you want to clone and click the Clone button.

A Clone Schema dialog box will appear. You can select the name of the new schema to which you want to clone the data. Finally, click Finish to apply the changes.

A new cloned schema will appear on the view schema page.

Note: Following the best practices for maintaining a database is crucial. Reviewing your schemas and deleting unnecessary ones can help organize your database and reduce storage space. To achieve this, select the name of a schema from the schema list and click on the Drop icon.

Method 2: Using Snowflake SQL Interface to Create Schema

Another method to manage schemas in Snowflake involves using the Snowflake SQL interface and the CREATE SCHEMA function. This section highlights how you can create, modify, clone, and drop a database schema using SQL commands.

Snowflake CREATE SCHEMA Function

To use the Snowflake CREATE SCHEMA function, you must log in to Snowflake and navigate to Worksheets. You can run the following code to create a new schema named TEST_SCHEMA in the TEST_DB database.

CREATE SCHEMA "TEST_DB"."TEST_SCHEMA";

Click the Run button in order to execute this query. A new schema with the name TEST_SCHEMA will be created in TEST_DB.

Modify Database Schema

Snowflake’s ALTER SCHEMA command allows you to modify the schema name. Here’s a sample command to change the name of the schema from TEST_SCHEMA to TEST_SCHEMA_RENAME.

ALTER SCHEMA "TEST_DB"."TEST_SCHEMA" RENAME TO "TEST_DB"."TEST_SCHEMA_RENAME";

Clone and Drop Schema

To clone a database schema, you can use the following SQL command.

CREATE SCHEMA "TEST_DB"."TEST_CLONE" CLONE "TEST_DB"."TEST_SCHEMA_RENAME";

This command creates a new schema, TEST_CLONE, inside the TEST_DB database, which is a clone of the TEST_SCHEMA_RENAME schema.

Note: To delete or drop a schema, you can use the DROP SCHEMA command in the Snowflake SQL interface.

DROP SCHEMA "TEST_DB"."TEST2";

After dropping the schema, you can use the following command to view all the schemas present in the database TEST_DB.

SHOW SCHEMAS;

Examples of Creating Different Schema

Here are some examples demonstrating how to create different types of schemas using the Snowflake SQL interface.

Creating a Permanent Schema

To create a permanent schema, use the following command, which is typically used to create schema in Snowflake.

CREATE SCHEMA myschema;
SHOW SCHEMAS;

This code will create a permanent schema named myschema and list all the schemas present in the database.

Creating a Transient Schema

You can also specify the schema to be transient in nature, i.e., a schema with no fail-safe period. While it does not incur additional storage costs, a transient schema is more susceptible to data loss. The following command creates a transient schema:

CREATE TRANSIENT SCHEMA tschema;

SHOW SCHEMAS;

This will create a transient schema named tschema and display all the schemas.

Creating a Managed Access Schema

Another form of schema is managed access schema. It centralizes permission management with schema owners. In a managed access schema, only the schema owner can grant privileges on schema objects.

Here’s a code snippet to create a managed access schema:

CREATE SCHEMA mschema WITH MANAGED ACCESS;
SHOW SCHEMAS;

This command sets up a managed access schema named mschema and then lists out all the schemas in your database.

Integrate Data Directly into Snowflake Schema Using Hevo

After using the Snowflake CREATE SCHEMA function, you can load data into the Snowflake environment, especially if your database is set up in another environment. However, managing data types and schemas during the import process can be a hassle. This is where Hevo can be beneficial.

Hevo is a no-code, real-time ELT data integration platform that cost-effectively automates the data pipeline for your specific needs. It provides 150+ data source connectors, enabling easy data integration between platforms of your choice.

Here are some of the major features of Hevo:

• Data Transformation: Hevo enhances analytical workflows by providing analyst-friendly data transformation options. With its Python-based and drag-and-drop features, you can clean and prepare your data before loading it into your required destination.

• Automated Schema Mapping: Hevo simplifies schema management by automatically detecting the incoming data format and replicating it according to the destination schema. You can choose between Full or Incremental Mappings based on your data replication needs.

• Incremental Data Load: Hevo ensures efficient bandwidth utilization at the source and the destination ends by allowing real-time transfer of modified data.

Conclusion

This article highlights how to create a schema in Snowflake; you can use the Snowflake user interface or the Snowflake CREATE SCHEMA function. Based on your schema creation preference, you can choose from permanent, transient, and managed access schema.

After creating the schema, you can move your in-house data into Snowflake. Consider using SaaS-based data integration tools like Hevo Data to create flexible data pipelines. Hevo offers 150+ data source connectors to extract your data and eventually load it into Snowflake or any other destination of your choice. Sign up to Hevo for a 14-day free trial.

If you are looking to integrate data from a database into Snowflake, here are some of the interesting reads:

• DynamoDB to Snowflake
• MongoDB to Snowflake ETL
• Aurora to Snowflake

Frequently Asked Questions (FAQs)

Q1. What is a Snowflake schema?

Snowflake schema is a logical way of representing tables in a multidimensional database often employed for online analytical processing (OLAP) tasks. This schema is an extension of the star schema, where each dimension is stored in a single table. However, a Snowflake schema breaks down dimensions into multiple related tables to form a Snowflake-like structure.

Q2. How to create a Snowflake schema from Python?

To create a Snowflake schema using Python, you can install the Snowflake Python connector. Then, you can use the following Python code to create schema Snowflake.

pip install --upgrade snowflake-connector-python

conn.cursor().execute("CREATE SCHEMA IF NOT EXISTS testschema")