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
- Working knowledge of Snowflake.
- A general idea about Schemas.
- Snowflake account and database.
- A general idea about using the command line.
Introduction to Snowflake
Image Source
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
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.
Hevo Data, a No-code Data Pipeline helps to transfer data from 100+ sources to Snowflake. Hevo is fully-managed and completely automates the process of not only exporting data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.
It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination. It allows you to focus on key business needs and perform insightful analyses.
Get Started with Hevo for Free
Check out some amazing features of Hevo:
- Completely Managed Platform: Hevo is fully managed. You need not invest time and effort to maintain or monitor the infrastructure involved in executing codes.
- Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.
- Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to export.
- Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
- Schema Management: Hevo takes away the tedious task of Schema management & automatically detects the Schema of incoming data and maps it to the destination Schema.
- Minimal Learning: Hevo with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
Sign up here for a 14-Day Free Trial!
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
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.
Vishal has a passion towards the data realm and applies analytical thinking and a problem-solving approach to untangle the intricacies of data integration and analysis. He delivers in-depth researched content ideal for solving problems pertaining to modern data stack.