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:
- Partner Connect
- User Manual
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:
- File Formats
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.
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
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.
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.
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:
You can also connect with the Software externally; it’s not necessary that the software should be listed on the partner connect.
Help menu give access to the
- Snowflake Documentation
- Support Portal
- Contextual help for each page
- Download Client software to connect snowflake to other interfaces and applications
Snowflake implements security through roles you can create roles to restrict access of the services to a user
In the user manual you can:
- Change password
- Log Out
- Switch Roles