Snowflake Create Schema Command: 2 Comprehensive Aspects

Snowflake schemas are ideal for one-to-many and many-to-many relationships among dimension levels and are typically associated with data marts and dimensional data warehouses, in which data retrieval speed is more critical than data manipulation efficiency. Using a snowflake schema offers many benefits such as easy maintenance due to zero redundancy, minimal disk storage, and joins tables with smaller sizes, etc.

This article is aimed at providing you with a step-by-step solution to help you create Schemas in Snowflake using the Snowflake Create Schema command. A complete walkthrough of the article will help you master the skills required for creating a Snowflake Schema.

Table of Contents

• Prerequisites
• Introduction to Snowflake
• Introduction to Schema
  • Star Schema
  • Snowflake Schema
  • Galaxy Schema
• Using the Snowflake Create Schema command
  • Creating Schemas in Snowflake
  • Limitations of Snowflake Schema
• Conclusion

Prerequisites

• Working knowledge of Snowflake.
• A general idea about Schemas.
• Snowflake account and database.
• A general idea about using the command line.

Introduction to Snowflake

Snowflake is a fully managed cloud-hosted data warehouse available as Software-as-a-Service (SaaS). Snowflake provides an agile and easy-to-use data warehouse service and possesses many similarities to traditional data warehouses with additional capabilities. Snowflake uses a new SQL database engine and AWS Cloud services for storage (S3) and computation (EMR). It performances exceptionally well even with enormous volumes of data.

Key features of Snowflake

• Low Maintenance: All the maintenance, management, and performance tuning are handled by Snowflake, thereby providing a fully managed service.
• Managed Cloud: Snowflake runs entirely on public cloud instances, and uses virtual compute instances and storage services.
• Highly Secure: Snowflake provides top-notch security for data at rest and on-flight to ensure user information is always safe.
• No Hardware: There is no requirement to set up, configure, install, or manage the hardware.
• Speed: Snowflake uses a concept of MPP (Massive Parallel Processing) to process queries on a virtual warehouse. Each warehouse has its cluster with exceptional scaling capability.
• Time Travel: Snowflake has an extraordinary Time-Travel concept that allows you to access the historical data (the data that has been changed or deleted) at any point within the defined period.
• Separate Storage and Compute Layer: Snowflake uses different storage and computes layers that can scale up or down without affecting each other.

For further information on Snowflake, you can check the official site here.

Introduction to Schema

Schema is the logical description and grouping of database objects like tables and views. A standard relational database uses a relational model to store data, whereas a data warehouse uses a Schema to store the data. Schema can include databases, tables, and views of all record types. In layman’s terms, a data warehouse Schema is a description or a way to explain the database in its entirety.

There are three kinds of Schema:

• Star Schema
• Snowflake Schema
• Galaxy Schema

Star Schema

The Star Schema is the simplest among all the three Schemas. In a Star Schema, the fact table is in the center and is surrounded by a series of dimensions tables. Each dimension table contains a set of attributes describing the dimension. The dimension table contains a foreign key that connects it with the fact table and fetches results for various queries.

Snowflake Schema

The Snowflake Schema is an extension of the Star Schema. It contains a large number of dimensions as compared to a Star Schema and stores data in a normalized format. Normalization, not only reduces redundancy but also saves a lot of disk space.
It is easy to add a new dimension to Snowflake Schema as it is an extension of an existing Star Schema.

Galaxy Schema

The Galaxy Schema is a collection of Star Schemas. Here the dimension tables are shared between two or more fact tables. It splits the dimension tables into multiple dimensions based on hierarchy and provides a much better understanding of the data by aggregating the fact tables. You can create a Galaxy Schema by simply splitting a Star Schema into multiple parts.

Using the Snowflake Create Schema command

You can create a Schema in Snowflake using the following syntax:

Fill the following parameters carefully to create a Schema in Snowflake:

• <name>: Provide a unique name for the Schema you want to create.
• Transient: It represents a temporary Schema. Such schemas are volatile and hence the data gets deleted automatically once the session is terminated.
• Clone: It is used to create a clone of an existing Schema. You can simply provide a name for the Schema you want to clone.
• At|Before: This field is to used to provide a time-stamp to clone a Schema. It specifies a particular period in time from where you want to clone the data.
• With Managed Access: The field defines a managed Schema. It helps the user to monitor the access controls given to the user.
• Data_Retention: This is used to specify the number of days the object is will remain retained in the memory. It has a value of 1 by default but can be altered as per need.
• Comments: These provide a small description of the Schema thus created.

You can perform the following operations on a Snowflake Schema:

• Creating a Permanent Schema
• Creating a Transient Schema
• Creating a Managed Access Schema
• Dropping a Schema in Snowflake
• Altering a Schema in Snowflake

Creating a Permanent Schema

You can create a Permanent Schema using the following syntax:

CREATE SCHEMA <SCHEMA_NAME>;

Example query:

create schema myschema;
show schemas;

You can use the Show Schemas command to display the created Schema. This will provide a tabular representation of the data present in the Schema.

This is how you can use the Snowflake Create Schema command to create a Permanent Schema in Snowflake. Also read Snowflake create table.

Creating a Transient Schema

You can create a Transient Schema using the following syntax:

CREATE TRANSIENT SCHEMA <SCHEMA_NAME>;

Example query:

create transient schema tschema;
show schemas;

You can use the Show Schemas command to display the created Schema. This will provide a tabular representation of the data present in the Schema.

This is how you can use the Snowflake Create Schema command to create a Transient Schema in Snowflake.

Creating a Managed Access Schema

To create a new Schema with Managed Access, use the managed access keyword along with the name of the Schema as follows:

CREATE TRANSIENT SCHEMA <SCHEMA_NAME> WITH MANAGED ACCESS;

Example query:

create schema mschema with managed access;
show schemas;

You can use the Show Schemas command to display the created Schema. This will provide a tabular representation of the data present in the Schema.

This is how you can use the Snowflake Create Schema command to create a Schema with Managed Access in Snowflake.

Dropping a Schema in Snowflake

To remove a Schema from the current database, you can use the Drop Schema command as follows:

DROP SCHEMA [ IF EXISTS ] <name> [ CASCADE | RESTRICT ]

Example query:

drop schema myschema;
show schemas;

You will receive an output stating that the desired Schema was dropped successfully. Use the Show Schemas command to display the created Schema. This will provide a tabular representation of the data present in the Schema.

Altering a Schema in Snowflake

To alter the Schema you have created, you can use the Alter Schema command. This command allows you to modify various properties for the created Schema such as renaming the Schema, swapping it with another one, etc.

You can use the following syntax:

Example queries:

To rename a Schema, you can use the following command:

alter schema if exists schema1 rename to schema2;

To convert a Regular Schema to Managed Access Schema, you can use the following command:

alter schema schema2 enable managed access;

This is how you can use the Snowflake Create Schema command. For further information on using Schemas in Snowflake, you can check the official documentation here.

Limitations of Snowflake Schema

Snowflake Schemas offer managed & high-quality data models for data warehousing, however, there are a few limitations associated with Snowflake and its Schemas:

• To get the desired results, you might need to write complex queries using joins, aggregate functions, etc.
• Data models are hard to understand at times, especially for individuals who don’t have a technical background.
• Schemas require a lot of maintenance.

Conclusion

This article teaches you how to successfully create Schemas using the Snowflake Create Schema command. It provides in-depth knowledge about the concepts behind every step to help you understand and implement them efficiently. You can have a look at Star and Snowflake Schema Analytics.

Visit our Website to Explore Hevo

These methods, however, can be challenging, especially for a beginner & this is where Hevo saves the day. Hevo Data, a No-code Data Pipeline helps to transfer data from multiple sources to Snowflake without having to write the code repeatedly. Hevo with its strong integration with 100+ sources, allows you to not only export & load data but also transform & enrich your data to make it analysis-ready in a jiff.

Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand.

Tell us about your experience of using the Snowflake Create Schema command! Share your thoughts in the comments section below.