Snowflake 101: Setting Up Environment and Database (2/4)

Create your Snowflake Account

Start discovering for free! Snowflake offers a 30-days free account with $400 worth of credits that you can use to play around to query your data.

Make sure to choose the Enterprise or Business Critical edition for this trial to take advantage of the most features. Next, for the purpose of this tutorial, we are going to choose AWS as our cloud platform, but you can definitely use Azure or Google Cloud Platform (GCP) too if you are already utilising those.

Once signed in, you’ll have a unique URL containing your account number, followed by the zone in which Snowflake has been installed. Using this link, you will be directed to this web-interface login prompt, and voila! You are ready to conquer the Snowflake world.

Setting up your Snowflake Environment

The first thing that you will need to do is to create an environment for your specific project. By default, Snowflake generates a warehouse called compute_wh when you sign in. But we are going to create our own environment.

This virtual warehouse environment is required to query data from Snowflake as well as loading data into Snowflake. A warehouse is a cluster of servers that provides compute resources. We use warehouses to execute compute queries and perform all the operations including bulk data loading. Multiple warehouses allow us to distribute resource-intensive tasks such as querying and data loading. We can also isolate our query workloads from others and resize our warehouse at any time to improve performance.

There are two ways to create a warehouse, from the UI or with SQL Code. Both will give the same output, except that you might want to consider using code in case you need to create multiple objects all at once.

• From the UI:
  Warehouses -> Create

• Via worksheet,
  Run this code:

CREATE WAREHOUSE SNOWFLAKE_WH
WITH
WAREHOUSE_SIZE = XSMALL
AUTO_SUSPEND = 60
AUTO_RESUME = TRUE
INITIALLY_SUSPENDED = TRUE
STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = 300
STATEMENT_TIMEOUT_IN_SECONDS = 600;

Here we are creating a SNOWFLAKE_WH virtual environment with the smallest size, auto suspends after 60 seconds of idle time, Automatic Resume once you again execute any query on the Snowflake warehouse and the last 3 lines of codes for further idle timeout setting.

It is important to configure these options wisely based on your needs. In this tutorial, performance is not our main concern since we are going to use rather simple data. Therefore we are carrying this set-up to optimize cost instead.

In case you have big data and want to focus on performance, you can resize the warehouse size to, for example, Large to 4X-Large. Other scenarios would include considering both cost and performance or prioritising one of them, regardless, any choice that you make here depends on the specific use cases.

Create your first database

Similar to creating a warehouse, you can also create a database via the user interface as well as using code. It is important to note that using a number as a first character or space within the name is prohibited.

• From the UI:

  Databases -> Create

  

• Via worksheet:

  Run this code:

Create schemas within a database

Inside a database, we can create multiple schemas and each schema can contain multiple tables and views. To check the existing schema inside of your database, you can simply click the database name -> Schemas.

Each time you create a new database, PUBLIC schema (that can be used to create other objects) and INFORMATION_SCHEMA (that contains the database’s metadata) are automatically generated. To create a new schema, you can use both the UI and the code provided below:

• From the UI:
  • Databases -> Click Retail_DB_Sales -> Schemas -> Create -> Check Management Access
  • In case you want the security objects to be managed by the owner of the individual object instead of the schema owner.
    

• Via worksheet:
  Run this code.
  

CREATE SCHEMA Sales_Data;
CREATE SCHEMA Sales_Views;
CREATE SCHEMA Sales_Stage;

USE WAREHOUSE SNOWFLAKE_WH;
USE DATABASE Retail_DB_Sales;
USE SCHEMA Sales_Data;

The second batch of codes is used to make sure that we correctly set up the context to the warehouse, database, and schema we made before. You can also set this up using the console on the top right corner.

Your environment is all set, now you can start to play around with your data and are ready to discover the Snowflake interface even further.

Read the other Snowflake 101 blog posts:

• Blog | Snowflake 101: Why is Snowflake Great? (1/4)
• Blog | Snowflake 101: Setting Up Environment and Database (2/4)
• Blog | Snowflake 101: Loading Data from Local (3/4)
• Blog | Snowflake 101: Loading Data from Cloud using AWS (4/4)

Build a data-driven organization with Snowflake. 

A powerful data cloud thanks to an architecture and technology that enables today’s data-driven organizations. 

Want to try out Snowflake? We got you covered! Sign up for a Snowflake trial today and receive $400 worth of free usage when you test drive Snowflake. Don't hesitate to reach out to us if you need some assistance with you setting up your Snowflake trial. We'll get one of our bright minds to help you with it.

Issye Margaretha
Analytics Consultant
Biztory 

Discover other Snowflake content

• Technologies - Snowflake
• Blog | Snowflake 101: Why is Snowflake Great? (1/4)
• Blog | Snowflake 101: Setting Up Environment and Database (2/4)
• Blog | Snowflake 101: Loading Data from Local (3/4)
• Blog | Snowflake 101: Loading Data from Cloud using AWS (4/4)
• Blog | What is Snowflake?
• Blog | What are the different Snowflake components?
• Blog | Is Snowflake difficult to learn
• Blog | The Power of Snowflake's Data Sharing
• Blog | Snowflake & Security - A quick overview
• Blog | How to pass the SnowPro Core certification exam