We’re taking a little departure away from Microsoft technology in this blog post as we’re looking at Snowflake, a SQL-based Cloud Data Warehouse platform that’s been gaining plenty of attention and plaudits. Often the best way to understand a vendor product is to get hands-on and make use of any learning material available. Snowflake offer a learning platform and several free courses to allow you to work through the basics of the Snowflake platform. This is a very useful way to gain hands-on experience with the platform via guided learning.
Accessing Snowflake
To work through the learning materials you must sign-up for a 30 day free trial of Snowflake, you are not prompted to enter in any payment card information. The information you are required to enter is you name, email, and company name. You will then be sent details via email on connecting to the Snowflake service.
Links
The Hands-On Essentials – Data Warehouse is found via the START BADGE ONE WORKSHOP link, this will launch the Snowflake University portal.
Credential Overview
Hands On Essentials – Data Warehouse
The credential overview is as follows:
“Issued by Snowflake
The Data Warehouse badge is the first badge in Snowflake’s Hands On Essentials Series. Users earning this credential have completed coursework, passed a written exam and scored 90% or better on submitted project work. Topics covered: User Roles, Navigation, Creating Database Objects, Virtual Warehouse creation and configuration, loading and querying CSV and JSON data.”
Snowflake Interface
You can access Snowflake via a web interface and there is also support for connecting to Snowflake using Visual Studio Code as shown here.
Learning Overview
In terms of what you’ll actually be learning as you work through the Snowflake Hands On Essentials – Data Warehouse tutorials, it’s split into 12 modules including an overall course exam module with 59 questions. Each module has a set of questions at the end to test your knowledge.
Intro & User Roles
You will learn the various roles that a user can be assigned to and what each role has access to and the available functionality of that role. You will see how to switch roles using the Snowflake interface.
DB Navigation
You will learn how to use the navigation ribbon to browse the available Snowflake databases and the properties of the database including permissions.
Data Exploration
You will learn how to browse the available tables within a Snowflake database, see the columns and data type information, and how to select data using SQL syntax.
Warehouses
You will learn how to create a “warehouse” in Snowflake, which is an instance of compute. You will also see options for clusters and scaling policies.
Creating DB Objects
You will learn how to create a new database, create a table, create a file format to enable parsing of a flat-file, then finally how to load the flat-file data into the Snowflake database table.
Transformations
In this module you’ll learn how to use SQL transformation syntax to replace source data values with desired destination values when inserting into a table.
WH Concepts
This module explains the concept of a Snowflake Warehouse in terms of usage, available compute sizes and the associated cost (per credit). The different editions are explained with features that exist in each edition.
Staging Data
This module explains how to connect to a remote cloud storage account such as AWS S3 and Azure Blob Storage, browse the remote storage, and list the available files. The module then concludes with showing how to query and load file data from a “stage” (remote cloud storage) into a Snowflake table.
Data Storage Structures
We now look at how to identify what entities, attributes, and values are in relation to an ERD (Entity Relationship Diagram) and objects within Snowflake. Then the Sequence object is discussed and how to generate a sequence number when selecting/inserting data.
Intro to Semi-Structured Data
There are 5 semi-structured file formats that Snowflake support, this module goes through these formats and the supported Snowflake data type/column type when inserting this data.
Semi-Structured Nested Data
Once semi-structured data has been inserted into Snowflake, we can use the FLATTEN command in SQL to select this nested data. This module also shows how to select specific levels and values in a nested hierarchy in XML and JSON.
Module Exam
The final module is a set of 59 questions testing your knowledge from the previous 11 modules.
Submitting for the Certification
After you have completed the modules including the lab exercises, you can submit a short video to Snowflake to complete the process of certification. This process is documented within the learning materials and is straight-forward to follow. It includes allowing a plugin to record a short video of you showing your work within Snowflake.
Conclusion
If you are interested in getting started with Snowflake or are just curious about its offering, then this Essentials course is a great introduction.