What is snowflake?

Snowflake is a data warehouse platform that pitches the native cloud ( or cloud-first ) approach as its primary value proposition. let’s learn about snowflake by going hands-on in few basic operations in snowflake.

 

Creating Database in snowflake

Creating a database in snowflake is fairly easy and standard. There are two approaches of doing it.

Graphical Interface

Clicking on the Databases tabs from the main menu loads the database panel. Click on create to create a database.

SQL Command line

The other way of creating a database in snowflake is via the command line. For that, click on the Worksheets tab. Worksheet in snowflake is an interface for viewing all data objects. Click on + sign to create a new worksheet if you prefer, or continue to work in the default worksheet.

1.  The left side of worksheet lists all the database objects.

2. SQL space is where we can write SQL queries ( in this case to create a database)

3. On top right, it gives basic information about role, instance type and the current database selected.

Coming back to query for creating database.

1. Write query in the worksheet.
2. Results show up at the bottom pane
3. Refresh the left pane and we have our database ready.

[su_box title=”Creating Database with SQL” style=”glass” radius=”1″]CREATE DATABASE “ANALYTICS_DEMO_DATA_2”;[/su_box]

Creating Schema in snowflake

A schema is a logical grouping of database objects ( tables, views, etc. ). In contrast, a database is a logical grouping of schemas. Each schema belongs to a single database.

To create schema in snowflake, go to the Databases tab. Click on the database name for which you want to create the schema. Then click on Schemas sub-menu.

1. Select database for which you want to create the schema
2. Click on Schemas sub-menu
3. Click on Create button
4. Enter schema name

SQL alternative for schema creation will be.

[su_box title=”Creating Schema with SQL” style=”glass” radius=”1″]CREATE SCHEMA “ANALYTICS_DEMO_DATA”.”DEMO_SCHEMA”;[/su_box]

 

Creating a table in snowflake database

To create a table in snowflake via Graphical interface.

1. Select the database you want to create a table in,  from the Databases menu.
2. Tables sub-menu is selected by default. Click on + Create.
3. Enter the table name.
4. Add a column ( you need atleast 1 column to create the table ).

The SQL way of creating a table will be.

[su_box title=”Creating Table with SQL” style=”glass” radius=”1″]CREATE TABLE “ANALYTICS_DEMO_DATA_2″.”PUBLIC”.”MY_FIRST_TABLE” (“C1” STRING) COMMENT = ‘Hello World Table’;[/su_box]

Creating views in snowflake database

To create a view in snowflake via Graphical interface.

1. Select the database you want to create a table in,  from the Databases menu.
2. Select the views sub-menu. Click on + Create icon.
3. Enter the view name.
4. Enter the view definition.

SQL way of creating a way in snowflake would be :

[su_box title=”Creating views with SQL” style=”glass” radius=”1″]CREATE VIEW “ANALYTICS_DEMO_DATA_2″.”PUBLIC”.My_First_view AS Select C1 from My_First_Table;[/su_box]

Previewing data in snowflake

We can preview the data in snowflake from the worksheet menu.

1. From the left side database object menu, select the table to preview
2. Click on the preview data button
3. Data preview will show in bottom pane

I have selected a sample Citibank database and trips table in it for preview.

 

This was a high-level overview of getting started with snowflake. We’ll look into how to load data in snowflake and the concept of stages.