Snowflake 101 : Cloud Native Data Warehouse

Snowflake is a cloud native data warehouse.

It is not built on an existing database or “big data” software platform such as Hadoop. It’s not a relational database so it does not enforce primary key and foreign key constraint but it offers Snowflake SQL command like DDL(Data Definition Language ), DML(Data Manipulation Language), and SQL functions.

Snowflake uses virtual compute instances for its computing needs and a storage service for persistent storage of data that means it has a real separation of computing and storage. The data in the table is stored in Amazon S3 and it will only consume storage cost and compute cost won’t be attached to it. Example If we will run DDL statements to create a database, schema, table, or other structural statements if all this takes 30 minutes then we will pay for 30 minutes of computing cost. Once our table structure is stabled and we need to load the data and it takes 1 hour per day then we only have to pay for that 1-hour rest of the time we will only pay for storage cost.

Snowflake supports multiple ways of connecting to the service:

A web-based user interface from which all aspects of managing and using Snowflake can be accessed.
Command-line clients (e.g. SnowSQL) which can also access all aspects of managing and using Snowflake.
ODBC and JDBC drivers that can be used by other applications (e.g. Tableau) to connect to Snowflake.
Native connectors (e.g. Python, Go, Node.js) that can be used to develop applications for connecting to Snowflake.
Third-party connectors that can be used to connect applications such as ETL tools (e.g. Informatica, Talend) and BI tools(e.g. Kafka, Spark) to Snowflake.

Snowflake Web Interface:

It has eight menu items in it’s menu bar:

Databases
Shares
Warehouses
Worksheets
History
Partner Connect
Help
User Manual

1.Databases

All data in Snowflake is stored in the Database. The database is a logical grouping of objects such as tables and views organised in the schema. The database page displays information about the database we have created and have the privilege to access it.

Create a database
Clone database
Drop the selected database
Transfer ownership of the selected database

In a Database you can create:

Tables
Views
Schemas
Stages
File Formats
Sequences
Pipes

2.Shares

Snowflake uses secure data sharing to share account usage data and sample data sets with all of Snowflake’s accounts. In this capacity, Snowflake acts as the provider of the data and all other accounts act as the consumers.

3.Warehouses

Warehouse is one or more clusters of servers that provide compute resources. It is used to execute queries and perform all other DML operations including bulk data loading

Warehouses page displays information about:

Existing warehouse
Create a warehouse
Delete a warehouse
Suspend and resume warehouse tab as snowflake only charges the cost of computation when we are using it
Transfer ownership
Configure warehouse which includes resizing the warehouse

By clicking on the warehouse we get the graphical view of the query load on the warehouse over the last 14 days

4.Worksheets

Worksheet page provides an interactive interface to execute SQL queries and perform other DDL and DML operations.

To get started:

Choose a Warehouse
Choose a Database and Schema(optional)

Worksheet maintains the result of each query executed in your current session and detailed information such as duration and starts time. Results can be downloaded in a text format.

5.History

History page displays all commands executed over 14 days including command executed outside the web interface. You can also filter the list and click on the query id to get the details of the query.

6.Partner Connect

Partner connect helps the user to quickly and easily integrate with third-party solutions like analysis tool, a visualization tool, Integration tool, etc. Only the account administrator can create a connection. Snowflake automatically generates the credentials for the third-party software and connects with it.

Snowflakes Partner connect: