Snowsight Dashboards, Custom Filters, Worksheets and Notebooks for Descriptive Analysis

1.Summarizing data on a dashboard: tiles built from worksheet queries

Descriptive analysis means summarizing a large data set so other people can read it at a glance. In Snowsight the tool for that is the dashboard: a set of charts, laid out as tiles, that are generated from query results and can be customized. You can start from an empty dashboard (Projects » Dashboards, then + Dashboard) or turn an existing worksheet into one. To do that, hover over the worksheet name, choose Move to and then New dashboard.

The word *move* matters. When you create a dashboard from a worksheet, the worksheet is no longer a worksheet. It drops out of the worksheet list and can only be reached through the dashboard. If you had shared that worksheet with colleagues, they lose access at that point: their permissions are revoked and their links stop working. Moving a worksheet onto an existing dashboard does the same thing.

After the dashboard exists, you add more tiles with + and New Tile from Worksheet. A blank worksheet opens over the dashboard, and Return to <dashboard name> saves your query and places it as a tile. New tiles go to the bottom, and you rearrange them by dragging. The tile menu gives you two ways to edit. View Chart opens the chart for restyling, and Edit query opens the SQL.

In a worksheet, a query shows one chart type at a time. To show the same result two ways on a dashboard, for example as a table and as a chart, you add tiles rather than switching. If a tile shows a table, choose Edit Query, select Chart and return to the dashboard. Snowsight then adds a *new* tile with the chart view and leaves the table tile in place. Duplicate Tile gives you a second copy, and each copy can be styled differently.

Sources1

2.Sharing a dashboard: roles decide who can see the numbers

Once the dashboard is built, you share it. Editors and owners can invite named collaborators or turn on link sharing. The invite list only includes users who have signed in to Snowsight at least once, so anyone else has to get a link. Link sharing does nothing by default: people with the link cannot view the dashboard until you change that setting. You can also choose something in between, such as letting people view results without running the underlying queries.

Access to the dashboard and access to its data are separate. Dashboard queries run in their own sessions, each with an assigned role and warehouse that you set in the context selector. A viewer must use the same role as that session context to see the shared dashboard. Dashboards also ignore secondary roles. They run only with the user's primary role, whatever DEFAULT_SECONDARY_ROLES is set to. Finally, unlike worksheets, dashboards cannot be organized into folders.

Sources1

3.Creating a reusable custom filter

A dashboard that answers a single fixed question gets stale quickly. Filters let readers change the slice themselves. Snowsight offers *system filters*, which every role can use, and *custom filters*, which administrators create. A custom filter changes a query's results without anyone editing the query. It works as a special keyword in the SQL that resolves to a subquery or a list of values when the query runs. That is also why filters have some limitations inside SQL. Because a custom filter is defined once and then used across dashboards and SQL worksheets, it is the reusable filter the exam guide refers to.

SELECT
  COUNT(O_ORDERDATE) as orders, O_ORDERDATE as date
FROM
  SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS
WHERE
  O_ORDERDATE = :daterange
GROUP BY
  :datebucket(O_ORDERDATE), O_ORDERDATE
ORDER BY
  O_ORDERDATE
LIMIT 10;

Not every role can create filters. A user with ACCOUNTADMIN has to grant the ability to a role, and that grant can only be made in Snowsight, not in SQL. In a dashboard or worksheet, open the filter control (Manage Filters in a worksheet), choose Edit Permission, tick the roles and save.

To create the filter, choose + Filter and fill in these fields:

- Display Name: what users see. - SQL Keyword: a unique :<string> with no spaces, such as :page_path. - Description. - Role: the filter's associated role, which also runs any query that populates its values. - Warehouse: the owner role needs USAGE on it. - Options via: either Query or List.

A list is a set of name/value pairs. If you leave a name blank, the value is shown instead. A query must return columns named name and value. It can also return a description column, and any other columns are ignored. A filter runs one query, so you cannot build names from one query and values from another. You can also set a Value Type (Text or Number), allow multiple selections, and add an All or Other option.

Because every user in the account can see every custom filter, the values a filter query returns are visible to everyone. The documentation says to make sure the option list contains no protected or sensitive data. Editing works differently. Anyone with the filter's associated role can edit or delete it, and ACCOUNTADMIN can view and edit every filter.

Sources2

4.Keeping query-based filters fresh, and what that costs

A list filter stays the same until someone edits it. A query-based filter has a refresh frequency: hourly (the default), daily or never. The schedule starts from when you saved the filter and moves with how long each refresh takes. If you save an hourly filter at 10:07 AM, its first refresh runs at or after 11:07. If that refresh takes 20 minutes, the next one runs at or after 12:27. When many refreshes fall due together, they are queued.

There are two operational consequences. The first is cost: the warehouse runs the refresh query on schedule even when no one has the filter open in a browser, so a complex query on an hourly schedule keeps using credits. The second is identity: refreshes run as the user who created or last modified the filter, and they appear in Query History as queries executed by user tasks.

Running as that user is why refreshes can fail. A refresh fails if that user is dropped or disabled, or if they have not signed in for 3 months. Snowsight does not show who created or last modified a filter. One clue in SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY is a successful authentication by WORKSHEETS_APP_USER followed by a failed one, repeating at the filter's refresh interval. If a filter's associated role is dropped, the role that dropped it does not take ownership. An ACCOUNTADMIN has to assign the filter a new role.

Sources2

5.Describing data ad hoc in a SQL worksheet

Dashboards are for presenting a summary you already understand. Worksheets are where you get to that understanding. Each worksheet has its own context: a role and warehouse, plus optionally a database and schema. That context is saved and shared with everyone who uses the worksheet. It is also independent of the active role in your user menu. Once a database and schema are set as context, you can refer to tables in that schema without fully qualifying their names. Each statement should end with a single semicolon. You can run the statement under the cursor with Run, or choose Run All from the menu next to it. Autocomplete suggests functions, table and column names, and aliases you have used before. The Databases explorer can insert a fully qualified object name, or a table's comma-separated column list, at your cursor.

The fastest descriptive analysis needs no extra SQL. Run SELECT * and look at the results. For result sets up to 1 million rows, Snowsight generates *contextual statistics* in the inspector pane, both for whatever you have selected and for the result as a whole. Each column gets a preview, and selecting a column opens its detailed statistics. You can also filter the results from the statistics pane. Accounts in U.S. government regions, VPS accounts and accounts using Private Connectivity still have results limited to 10,000 rows.

When a picture says more than a table, select Chart above the results. Snowsight generates a chart automatically, and you can switch it to a bar chart, line chart, scatterplot, heat grid or scorecard. In the Data section you can add or swap columns, or change how a time column is bucketed, for example from days to minutes. Each query shows one chart type at a time, which is why the dashboard section added tiles to show the same result two ways.

Watch the cost of exploring results. Some column transformations on results, such as sorting a column, apply to the full result set and use compute on the query's warehouse. Formatting changes do not: thousands separators, percentages, decimal precision and date formats are free. Transformations done by the recipient of a shared worksheet, working on a limited set of results, are also free, and so are transformations in accounts in U.S. government regions, VPS accounts and accounts using Private Connectivity. To find the transformations you were billed for, filter Query History for snowsight_transform_cte.

Sources345

6.Exploratory analysis in Snowflake Notebooks

A notebook suits exploration that mixes languages or needs a narrative. Snowflake Notebooks have SQL, Python and Markdown cells, and you can change a cell's language from its dropdown. Only one person can edit a cell at a time; it becomes available to others after 60 seconds of inactivity. Duplicating a cell lets you try variations of a query and compare outputs side by side without overwriting the working version. Each cell's output is capped at 20 MB, so split large results across cells.

Cells run one at a time, and other run requests wait in a queue. A coloured status bar shows each cell's state. A blue dot means modified but not yet run, green means it ran without errors, red means it hit an error, and gray means the results shown come from a previous session (kept for 7 days). SQL cells also report the warehouse used, the number of rows returned and a link to the query ID.

What makes a notebook worth using is that one cell can use another cell's result. A SQL cell's result is available to Python through the cell's name. to_df() returns a Snowpark DataFrame and to_pandas() returns a pandas DataFrame. In the other direction, a SQL cell can select from another cell with {{cell2}}. Python variables can be inserted into SQL the same way, but only if they are strings, not DataFrames. Cell names in references are case-sensitive and must match exactly.

my_df = cell1.to_pandas()

SELECT * FROM {{cell2}} where PRICE > 500

my_df = cell1. ? ()

Sources6

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Turning a shared worksheet into a dashboard leaves colleagues' access to the worksheet unchanged.Why is that wrong?

   The worksheet is moved into the dashboard. Its permissions are revoked and existing links stop working, so collaborators have to be given access to the dashboard instead.

   Covered in Summarizing data on a dashboard: tiles built from worksheet queries

2. 2.Deleting a tile only removes the visual, so the query can be recovered afterwards.Why is that wrong?

   Delete also removes the underlying query permanently, along with any other tile that uses that query. Use Unplace Tile to keep the query.

   Covered in Summarizing data on a dashboard: tiles built from worksheet queries

3. 3.A custom filter's associated role limits who can see and use the filter.Why is that wrong?

   Everyone in the account can see and use every custom filter. The associated role only controls who can edit it and which role runs its population query, so keep sensitive values out of the option list.

   Covered in Creating a reusable custom filter

4. 4.A query-based filter only uses warehouse credits while someone has a dashboard using it open.Why is that wrong?

   Refreshes run on their schedule regardless of whether anyone is viewing, so an hourly refresh of an expensive query keeps using credits.

   Covered in Keeping query-based filters fresh, and what that costs

Practise it for real

Explore the sample ORDERS data in a worksheet, chart it, and turn it into a dashboard tile.

1. 1.Open a new worksheet, set a role and warehouse in the context selector, and run the documentation's sample query against SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS.

   Why: The worksheet context decides which role and warehouse run the query.

   You should see: A results table showing order counts by date.

2. 2.Select the date column in the results and look at the inspector pane.

   Why: Contextual statistics describe the column without another query.

   You should see: A histogram for the date column, plus filled/empty counts.

3. 3.Select Chart above the results and change the chart type to Bar.

   Why: Snowsight generates a chart automatically, and you can change its type.

   You should see: A bar chart of orders by date.

4. 4.Hover over the worksheet name, choose Move to, then New dashboard, name it and select Create Dashboard.

   Why: This creates a dashboard from the worksheet, which moves the worksheet into it.

   You should see: A dashboard with one tile; the worksheet no longer appears in the worksheet list.