Snowflake Dashboard Data: Context, Queries, Governance and BI Connections

1.Setting the context before you query

Before a dashboard shows anything useful, you have to decide where its queries run and as whom. In Snowsight, each dashboard has a context selector. You use it to choose the role and the warehouse for every query behind the tiles. The role is the security identity: it decides which databases, schemas and tables the queries can read. The warehouse is the compute that runs them. Running a query in a worksheet records both: its Query Details show the role used and the warehouse used to execute it.

The database and schema are the other half of the context. Once a worksheet's context is set to a database (and optionally a schema), you can refer to tables by their short names. Without that context, you have to fully qualify every object as DATABASE.SCHEMA.OBJECT. The Snowflake sample query used later in this lesson does this: it names SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS in full, so it runs no matter which database is currently selected.

This matters when you pick the data source. If a table is visible only through a secondary role, a dashboard won't see it. The dashboard's primary role needs the privileges itself. The role also affects who can use the dashboard later: to view a shared dashboard, a user must use the same role as the session context of its queries. So pick a role your audience actually has, not your most privileged personal role.

Sources12

2.Writing tile queries: naming, sorting and filtering

Each tile is a worksheet query. You write it like any other worksheet SQL. To run one statement, put the cursor in it and select Run. To run everything, use Run All. End each statement with a single semicolon, because Snowsight doesn't support multiple statements in a single API call.

Naming columns. Charts are bound to column names. If an alias changes, the chart can no longer find the column, and you have to point the chart at the new name. Column aliases are identifiers, so the identifier rules apply. A double-quoted alias such as "Net Revenue (USD)" is a delimited identifier: it's case-sensitive and can contain spaces and special characters. Any later reference must spell it exactly as created, including the double quotes. A CTE that refers to net_revenue_usd won't match it. A consistent convention that avoids quoted aliases with spaces or mixed case (for example net_revenue_usd) keeps names stable across tiles and wrapper queries.

Sorting and filtering. Put the sort order in the query with ORDER BY, and the row selection in a WHERE clause. You can also sort a result column through the column options in the results grid. That sort applies to the whole result, not just the first 10,000 rows, so it's a transformation that costs compute on the same warehouse that ran the query. Display-only formatting doesn't cost anything: thousands separators, percentages, decimal precision and date formatting. Filters such as :daterange are covered in the companion lesson on charts and filters.

Sources234

3.How row access policies and masking change what viewers see

Because the session role is part of every tile query, governance policies can make two people looking at the same tile see different results. Snowflake has two kinds of policy that act at query time:

A row access policy is a Boolean expression bound to columns. At runtime, Snowflake builds a secure inline view and returns only the rows for which the policy returns TRUE. The simplest policy checks the current role:

CREATE OR REPLACE ROW ACCESS POLICY rap_it
AS (empl_id varchar) RETURNS BOOLEAN ->
  'it_admin' = current_role()
;

If a dashboard on such a table runs as any role other than it_admin, it gets no rows. That isn't a broken dashboard; the policy is working. Policies can also use a mapping table, for example sales managers matched to regions, so each manager sees only their region's revenue. The policy expression is evaluated with the policy owner's role, so the viewer doesn't need access to the mapping table. Owning the table doesn't get you around the policy either: the object owner is also subject to it.

Dynamic Data Masking works on columns instead. A masking policy is applied wherever the column appears in a query. Depending on the policy conditions and the role, the viewer sees the plain-text value, a partially masked value or a fully masked value. Masking can even stop ACCOUNTADMIN or SECURITYADMIN from seeing data they have no need to see. For a dashboard, this means a tile's row count can stay the same while its values look different from one role to the next.

Sources56

4.Connecting external BI tools

Snowsight isn't the only place to build reports. External BI tools connect through Snowflake's drivers, for example the JDBC driver for tools that support JDBC and the ODBC driver for ODBC-based applications. Whatever the tool, it needs the same context you set in Snowsight. The one required setting is your Snowflake account identifier. You may also need to specify the warehouse, database, schema and role. Snowsight collects these for you: open the user menu and select Connect a tool to Snowflake to see the Account Details dialog.

Power BI is a useful example. With single sign-on, the Power BI service uses an embedded Snowflake driver and opens the session with the user's default role. So the default role decides what the report can read and which policies apply. By default, ACCOUNTADMIN, ORGADMIN, GLOBALORGADMIN and SECURITYADMIN are blocked from opening a Snowflake session through Power BI.

Sources789

Exam traps

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

1. 1.A dashboard can use privileges from my secondary roles, as a worksheet session can.Why is that wrong?

   Dashboards run only with the primary role. DEFAULT_SECONDARY_ROLES is ignored.

   Covered in Setting the context before you query

2. 2.A viewer whose role can't read a row access policy's mapping table will get an error on the dashboard.Why is that wrong?

   The policy is evaluated with the policy owner's role, so the viewer only sees the rows the policy allows and needs no access to the mapping table.

   Covered in How row access policies and masking change what viewers see

3. 3.Power BI SSO sessions use whatever role the report author chose in Snowsight.Why is that wrong?

   Snowflake creates the Power BI session with the user's default role.

   Covered in Connecting external BI tools