Snowflake in 20 Minutes via Web-UI

5 min readJul 13, 2019

Snowflake provided a great tutorial to introduce some key concepts and tasks as the followings:

Create objects ->Load data to table -> Query data

How to create required Snowflake objects (databases, tables, etc.) for storing and querying data.
Loading a small amount of sample data from CSV files into a table.
Querying data from the table.

However, the tutorial is presented by the Snowflake command line client, SnowSQL. Of course, it is a very convenient tool if you are familiar with command line interface. Otherwise, you can try the web interface. Snowflake Web UI is very easy to use and quite powerful.

Therefore, I will show you the same steps via Web-UI to complete the tutorial. Hope it will help you to begin your journal in Snowflake ().

Before You Begin

An activated Snowflake user to access Snowflake (sign up for a free trial if needed) and granted to create databases, tables, and virtual warehouses
(Nice to have) Basic information about Snowflake
(Nice to have) Basic knowledge about SQL
Download sample data files from Snowflake (link)

Getting Started

1. Connect and login into Snowflake web interface

To log into the Snowflake web interface:

Go to the URL provided by Snowflake for your account.
Login with your credentials

If you login via SnowSQL, you will need to know what if you Full Account Name related to your Snowflake region. (link)

2.Create Snowflake Objects

After you login, you need to create a database, a table and virtual warehouse.

Create the sf_tuts database using the CREATE DATABASE command
Create a table named emp_basic in sf_tuts.public using the CREATE TABLE command:

Create a table named emp_basic in sf_tuts.public using the CREATE TABLE command.

3.Load data the Target Table

There are many ways to import data into Snowflake. For example, to execute PUT or COPY to upload local data files to the table via SnowSQL. The alternative way is loading data by Snowflake Wizard of the Web UI. Simple, effective, but with some limitations (file size limit: 50 MB).

This step is combined step 3 and step 4 of the tutorial at official site. Upladed data will be load into target.

If you have downloaded and checked the sample files (link), they include dummy employee data in CSV format with five records each with comma (,) character delimiter.

#An example record
Lem,Boissier,lboissier@sf_tuts.com,3002 Ruskin Trail,Shikārpur,8/25/2017

In Snowflake, when loading CSV data with a default setting, no blank spaces is expected to exist before or after the commas separating the fields in each record.

In addition, if the entire field contain delimiter (ex, comma characters in the following employees 03.csv), they must be must be escaped or enclosed (double quotes (" ")).

# employees03.csv of sample data
Althea,Featherstone,afeatherstona@sf_tuts.com,"8172 Browning Street, Apt B",Calatrava,7/12/2017

When you in “Create File Format” step, you need to choose a correct enclosed characters (Fig 3–6).

Note: The uploaded files will be removed after the data loading to table is finish. You can not see the list of the uploaded files in stage by executing a LIST command.

# Corresponding SQL statement
# internal stage name was created automatically by SnowflakePUT file://<file_path>/employees02.csv @EMP_BASIC/ui1562857962069
PUT file://<file_path>/employees04.csv @EMP_BASIC/ui1562857962069
PUT file://<file_path>/employees01.csv @EMP_BASIC/ui1562857962069
PUT file://<file_path>/employees03.csv @EMP_BASIC/ui1562857962069
PUT file://<file_path>/employees05.csv @EMP_BASIC/ui1562857962069COPY INTO “SF_TUTS”.”PUBLIC”.”EMP_BASIC” FROM @/ui1562857962069 FILE_FORMAT = ‘“SF_TUTS”.”PUBLIC”.”EMPLOYEE”’ ON_ERROR = ‘ABORT_STATEMENT’ PURGE = TRUE;

Please see more details in Loading Using the Web UI

Step 4. Query the Loaded Data

After you loaded data into the target table “em_basic”, you can use SQL statement to query data.

Four examples introduced in the tutorial as the followings.

Return all rows and columns from the table
Insert two additional rows into the table
Return a list of email addresses with United Kingdom domain names using the LIKE function
Filter the list by employees whose start date occurred earlier than “January 1, 2017"

# Query the Loaded DataSELECT count(*) FROM emp_basic;SELECT * FROM emp_basic;INSERT INTO emp_basic VALUES
  ('Clementine','Adamou','cadamou@sf_tuts.com','10510 Sachs Road','Klenak','2017-9-22') ,
  ('Marlowe','De Anesy','madamouc@sf_tuts.co.uk','36768 Northfield Plaza','Fangshan','2017-1-26');SELECT email FROM emp_basic WHERE email LIKE '%.uk';SELECT first_name, last_name, DATEADD('day',90,start_date) FROM emp_basic WHERE start_date <= '2017-01-01';

Step 5. Clean Up

It is not necessary to drop database and warehouse. You should make sure what your are going to do before you execute it.

# Clean Up DB and WarehouseDROP DATABASE IF EXISTS sf_tuts;

DROP WAREHOUSE IF EXISTS sf_tuts_wh;

Summary

In this article, I introduced how to use very basic functions of Snowflake step by step. Most commands and steps are from the tutorials, Snowflake in 20 Minutes.

Some concepts are as the followings.

Data loading is performed in two steps in Snowflake: Stage data and Copy data into a target table.
A file format for loading data is important and should be aligned with your input data. Web UI provides a simple interface to create it.
There are no much difference between SnowSql and WebUI, but user interface, uploading file limitation, and query profiling (I didn’t show here). I use Web UI all the time, but loading data mechanism is more systematical and programmed.

There are more resources you can learn about Snowflake. You can refer the reference in the bottom.

本文分享了如何透過Web UI進行基本的Snowflake 應用與操作：

主要步驟為：

登入
建立Snowflake Objects
Upload data
Query data
清理database 和 warehouse

內容參考自Snowflake 官方網站的教學 Snowflake in 20 Minutes，官網的教學是以Snowflake command line client, SnowSQL, 作為示範，但是本文完全以Web UI來操作。

請務必參考延伸參考文件來學習更進階的Snowflake的操作和資料庫應用邏輯。