Snowflake is a cloud-based data warehouse solution we leveraged to manage our research database. Snowflake is a web application, so it is different from other desktop applications for relational database (e.g. sql developer). Instead of downloading an executable and install it on your desktop, you just simply go to a URL (universal resource link):

https://jl41563.us-east-2.aws.snowflakecomputing.com/

There are two Snowflake web interface available to use:

• Classic (default): when you first log in to snowflake, you may land on the Classic console:

• Snowsight (recommended): for a much improved coding experience (e.g., autocompletion, user dashboard, data visualization), we highly recommend using the Snowsight console for query development. Switching to Snowflake console is just a simple click of the Snowsight icon (you may also be prompted to log in again):

Once switching over, you will be asked to set Snowsight console as your default user interface (UI). Alternatively, you can also set it up from Snowsight/Profile. In near future, this classic console will be obsolete and replaced by the Snowsight interface.

An acctivation email should be sent to your registering email account with instructions on setting up password and multi-factor authentication (MFA). Snowflake allow any user to self-enroll in MFA, which can be completed through the classic web interface:

Or the Snowsight interface:

We will assume using Snowsight user interface for the remainder of this training manual.

Snowflake uses roles to manage user access to different application component, data, and worksheets. You can switch role at two different level: a) account level; b) worksheet level.

Switch Role at account level

Switch Role at worksheet level

All new worksheet will automatically inherit account role, but you can still change role at worksheet level but simply running the following DCL command:

use role PUBLIC; -- change to PUBLIC role

Snowflake provides the following classes of objects for managing storage and computing resources:

A virtual warehouse, often referred to simply as a “warehouse”, is a cluster of compute resources in Snowflake. A pre-defined standardized warehouse (GROUSE_WH) is usually assigned to all users at beginning. But we can always offer more powerful warehouse to accommodate escalating computing needs.

Databases and schemas are used to organize data stored in Snowflake:

• A database is a logical grouping of schemas. Each database belongs to a single Snowflake account.
• A schema is a logical grouping of database objects (tables, views, etc.). Each schema MUST belong to a single database (or, you won't be able to create schema without an existing database)
• Tables and views are the primary objects created and maintained in database schemas. Each table or view MUST belong to a single schema (or, you won't be able to create table without an existing schema).

Source data catalogs linked below contains more details on all data views contained in GROUSE:

GROUSE Source Data Catalog

However, you may only have access to a subset of them depending on which sites have agreed to provide data to support your study as summaried in corresponding DROC request.

Snowflake also provide a command line interface (CLI), SnowSQL, which run on the Snowflake Connector for Python. SnowSQL requires installation of an application on your host machine and running CLI scripts to start, configure and execute queries. However, for easy start, we recommend using the web interface. For more instructions on using SnowSQL, please go to the Snowflake Documentation - SnowSQL (CLI Client) for more information.

If you use Visual Studio Code (VS code) as developer IDE, snowflake also provides an extension for directly executing queries in VS code. Please go to Snowflake Documentation - Snowflake Extension for Visual Studio Code for more implementation details.