Snowflake Snowsight: 7 refreshing features
With the acquisition of Numeracy in March 2019, Snowflake took a step towards enhancing the Snowflake Cloud Data Platform web user experience. This is in the form of a new offering called Snowflake Snowsight.
Not only does Snowsight provide features such as automatic contextual statistics, script version history, and custom filtering, but also data visualizations.
Table of Contents
- To access Snowsight, when you log into Snowflake through the browser, click on the “Preview App” button in the top right hand corner. You will then be prompted to re-enter your credentials.
While Snowsight is currently in preview, this blog will walk you through seven refreshing features of this new user interface (UI).
SQL queries are executed in Snowsight using worksheets. When a query is run, in addition to viewing results, charts can also be displayed. Clicking on the “Chart” tab within a worksheet will automatically display a chart based on query results. Chart types include:
Furthermore, chart appearance can be configured using options such as orientation (horizontal or vertical), grouping, axis labelling, chart axis, bucketing, and types of aggregation. Charts can also be downloaded as .png files. The below screenshot illustrates the maximum order price per sales clerk in a Snowflake sample dataset.
Snowsight supports dashboard creation using new or existing Snowsight worksheets. Dashboards display charts as tiles and can be rearranged via drag and drop.
- Tip: To include a worksheet in a dashboard:
- Open that worksheet in Snowsight
- Click on the dropdown at the top left of the dialog
- Roll the mouse over “Move to”
- Select the required dashboard name, or click on “+ New dashboard”
- If a tile is deleted from a dashboard, this action will permanently delete the underlying worksheet
The following diagram shows a Snowsight dashboard which includes the worksheet chart prepared in the previous section.
Each time a SQL script is run using a Snowsight worksheet, that worksheet is automatically saved. Therefore if a script is refined and re-executed over time, a history of the various versions of that worksheet are available through the Snowsight interface.
By clicking on the “history” drop down menu at the top right hand side of the screen, earlier versions of worksheets can be accessed. The below screenshot displays a worksheet which has gone through five iterations, and the fourth iteration is currently being accessed.
Custom filters can be configured through the Admin section of the new UI. These filters can then be used in worksheets to populate dropdown menus which filter query outputs. The values presented in custom filters can be configured using a query or by manually inputting a list. When using the query option, the resultant look-up list can be refreshed hourly or daily, or not at all.
The configuration includes creating a custom SQL keyword which is then accessed when writing queries. In a Snowsight worksheet, when a colon (:) is typed, the autocomplete functionality will list all possible custom filter SQL keywords. Once a custom filter is selected from that list, the relevant filter appears at the top left of the screen.
In the below screenshot a custom filter based on line item status has been configured with SQL keyword “LineStatus”. The SQL keyword is shown in orange text. The possible filter values to be selected in this instance are “O” and “F”. Once a value is selected the query is automatically re-run with the new filter applied.
- Tip: Even if you are accessing Snowsight under the ACCOUNTADMIN role, permissions still need to be granted in order to create / edit filters. These permissions can be configured in the Admin → Settings → Privacy section of the new UI.
Also available in the Snowsight worksheet dialog is a schema browser. This is accessed by clicking on the “Schema” tab. Typing text into a search box on the right hand will return any table or column name matches. Tables can be ‘pinned’ which means that the table columns and datatypes are displayed as illustrated in the below screenshot.
Automatic Contextual Statistics
In the query results pane of a Snowsight worksheet, contextual statistics for rows, columns or data ranges are automatically displayed.
Going hand in hand with worksheet visualizations, these statistics contribute to the understanding of data being analysed. The below screenshot illustrates how statistics for a highlighted column such as average, sum (and corresponding histogram) ,and percentage of fields which contain data are automatically displayed.
Other examples of Snowsight contextual statistics include:
- Frequency distribution
- Applies to text columns used for categorizing data e.g. Product Type, Product Category
- Email domain distribution
- Displays the frequency distribution of domain names in email addresses
- Key distributions
- Displays key distribution for JSON columns whereby all relevant rows contain JSON object
Switching between Snowflake accounts
If you are working across more than one Snowflake account, Snowsight allows you to switch between those accounts with a few mouse clicks. Clicking on the dropdown menu on the top left hand corner of the main Snowsight dialog, followed by clicking on “Switch Account” will provide a list of available Snowflake accounts, as illustrated in the below screenshot.
- Note: The below Snowflake accounts have been configured separately
There are a variety of additional features which add to the end user experience such as:
- In Snowsight worksheets, when writing SQL, autocomplete will display contextual information in relation to database objects, custom filters, and Snowflake functions.
- Sharing dashboards and worksheets within your Snowflake account
- Worksheet SQL query formatting
- Snowflake Marketplace integration
- Organize worksheets into folders
- Snowsight keyboard shortcuts as illustrated below
The inclusion of both charts and dashboards follow the general industry trend of providing query editors and data visualization in the same user interface. Worksheet history, Custom Filters, Schema Browsing, Automatic Contextual Statistics, and ease of switching between Snowflake accounts all contribute to overall usability. Likewise general features such as autocomplete in Snowsight worksheets, sharing of dashboards and worksheets, SQL query formatting, and UI keyboard shortcuts all subscribe to a more user friendly UI.
- Tip: Snowsight’s own caching functionality means that using features such as Worksheet Charts or Automatic Contextual Statistics do not issue new queries and therefore do incur additional Snowflake compute usage costs.
Snowsight is available on the Snowflake Cloud Data Platform on Microsoft Azure and Amazon Web Services at present with release on Google Cloud in the pipeline. Administrative tasks such as virtual warehouse or role based access configuration are not yet available through the Snowsight UI, however these tasks can be carried out either via SQL or by reverting to the current Snowflake Web UI.
Snowsight is in preview, nonetheless it’s well worth checking out. As described in this blog, Snowsight makes the process of preparing SQL queries alongside understanding the associated data and schema on Snowflake a more efficient, cost effective, and productive experience.