Identify Which Tableau Dashboard Queried Snowflake
This article was originally written in Japanese and published here.
Using Snowflake as a live-connection data source enables scalable data visualization in Tableau. However, as this approach becomes more widespread within your organization, it’s essential to understand the source of each query, including the workbooks or dashboards, for effective cost management and governance.
With this in mind, this article introduces a method for identifying which Tableau resources — Workbooks, Dashboards, or Worksheets — are executing queries in Snowflake, specifically within the ACCOUNT_USAGE.QUERY_HISTORY, at the query-ID level.
Tableau Query Tagging and Snowflake Query Tag
The method in this article uses Query Tagging of Tableau Cloud, which is enabled by default for Snowflake live-connection. This feature allows you to attach metadata such as the source workbook, dashboard, and worksheet to Snowflake’s Query Tag.
Therefore, if the requirements are met, we can find Snowflake’s Query Tags containing LUIDs (Locally Unique Identifiers) in ACCOUNT_USAGE.QUERY_HISTORY.
-- Query executed when a worksheet is opened
ALTER SESSION SET QUERY_TAG = $${
"tableau-query-origins": {
"site-luid": "315b7bd0-c6a2-42f8-a5fb-72366c88115c",
"user-luid": "73fe2467-66fd-47bc-907e-fe7033438751",
"workbook-luid": "5c327ef1-da96-4c74-be98-9aae3dce0b29",
"worksheet-luid": "a3359961-3c6e-4ed8-afa4-5aeef73da16f"
}
}$$
-- Query executed when a worksheet within a dashboard is opened
ALTER SESSION SET QUERY_TAG = $${
"tableau-query-origins": {
"site-luid": "315b7bd0-c6a2-42f8-a5fb-72366c88115c",
"workbook-luid": "5c327ef1-da96-4c74-be98-9aae3dce0b29",
"dashboard-luid": "a3db65a8-4586-4cad-b492-51bb2e995e39",
"worksheet-luid": "a3359961-3c6e-4ed8-afa4-5aeef73da16f",
"user-luid": "73fe2467-66fd-47bc-907e-fe7033438751"
}
}$$However, since the query tag only contains the LUIDs, it is necessary to create a master table like the one below to map them to their respective resource names.
Creating the Master Table for the LUIDs
To create the master table, we can use the Tableau Metadata API, which allows us to retrieve metadata from Tableau Cloud using GraphQL.
Below are the steps for using the Tableau Metadata API through Snowflake’s External Network Access:
Step 1: Creating a Personal Access Token for Tableau Cloud
Create a personal access token required for authentication with the Tableau Metadata API.
Step 2: Enabling External Network Access to Tableau Cloud
Enable External Network Access to your Tableau Cloud region from Snowflake to access the Tableau Metadata API.
-- Creation of Network Rule for your Tableau Cloud
CREATE NETWORK RULE tableau_cloud_nw_rule
TYPE = HOST_PORT,
MODE = EGRESS,
VALUE_LIST = ('**********.online.tableau.com'); -- Your Tableau Cloud Region
-- Registration of Personal Access Token
CREATE SECRET tableau_cloud_token
TYPE = GENERIC_STRING,
SECRET_STRING = '{
"personalAccessTokenName": "**********",
"personalAccessTokenSecret": "**********",
"site": {"contentUrl": "**********"}
}';
-- Creation of External Access Integration
CREATE EXTERNAL ACCESS INTEGRATION tableau_cloud_api_access_integration
ALLOWED_NETWORK_RULES = (tableau_cloud_nw_rule),
ALLOWED_AUTHENTICATION_SECRETS = (tableau_cloud_token),
ENABLED = true;Step 3: Creating a Python UDF to Fetch Metadata of Tableau Views
Create a Python UDF that retrieves data from the Tableau Metadata API.
CREATE FUNCTION fetch_view_metadata()
RETURNS VARIANT
LANGUAGE PYTHON
RUNTIME_VERSION = 3.8
HANDLER = 'main'
EXTERNAL_ACCESS_INTEGRATIONS = (tableau_cloud_api_access_integration)
PACKAGES = ('requests')
SECRETS = ('cred' = tableau_cloud_token)
AS
$$
import requests
import _snowflake
import json
TABLEAU_CLOUD_URL = "https://**********.online.tableau.com/"
API_VERSION = "3.21"
def main():
# Authentication with Tableau Cloud API
auth_url = f"{TABLEAU_CLOUD_URL}/api/{API_VERSION}/auth/signin"
auth_creds = _snowflake.get_generic_secret_string('cred')
auth_payload = {"credentials": json.loads(auth_creds)}
auth_headers = {"Content-Type": "application/json", "Accept": "application/json"}
auth_response = requests.post(auth_url, headers=auth_headers, json=auth_payload).json()
auth_token = auth_response['credentials']['token']
# Executing Query
graphql_query = """
query getViewsOnMySite {
views {
luid
name
workbook {
vizportalUrlId
luid
name
projectVizportalUrlId
projectName
}
}
}
"""
query_url = f"{TABLEAU_CLOUD_URL}/api/metadata/graphql"
query_headers = {
"Content-Type": "application/json",
"X-Tableau-Auth": auth_token,
"Accept": "application/json",
}
return requests.post(query_url, headers=query_headers, json={'query': graphql_query}).json()
$$;Step 4: Executing the Python UDF and Writing the Result into a Table
Run the Python UDF, flatten the received JSON, and write the data into the metadataapi_data table.
CREATE OR REPLACE TABLE metadataapi_data AS
WITH base AS (
-- Retrieving JSON response from Tableau Metadata API
SELECT fetch_view_metadata() AS response
), json_flatten AS (
SELECT -- Flattening JSON
f2.value:luid::VARCHAR AS item_luid,
f2.value:name::VARCHAR AS item_name,
f2.value:workbook:luid::VARCHAR AS workbook_luid,
f2.value:workbook:vizportalUrlId::VARCHAR AS workbook_id,
f2.value:workbook:name::VARCHAR AS workbook_name,
f2.value:workbook:projectVizportalUrlId::VARCHAR AS project_id,
f2.value:workbook:projectName::VARCHAR AS project_name
FROM
base,
LATERAL FLATTEN(input => base.response:data) AS f1,
LATERAL FLATTEN(input => f1.value) AS f2
WHERE -- Excluding records without item LUID
f2.value:luid::VARCHAR != ''
)
SELECT -- Information on worksheets and dashboards
item_luid,
item_name,
workbook_id,
workbook_name,
project_id,
project_name
FROM
json_flatten
UNION ALL
SELECT DISTINCT -- Information on workbooks
workbook_luid AS item_luid,
null AS item_name,
workbook_id,
workbook_name,
project_id,
project_name
FROM
json_flatten;Associating with ACCOUNT_USAGE.QUERY_HISTORY
We will create a view that joins the master table with ACCOUNT_USAGE.QUERY_HISTORY. This SQL statement will create a view containing only the query records related to Tableau Cloud.
CREATE OR REPLACE VIEW query_history_with_tableau_metadata AS
WITH base AS (
-- Retrieving luids from QUERY_TAG for join keys
SELECT
TRY_PARSE_JSON(query_tag)['tableau-query-origins']['workbook-luid']::VARCHAR AS workbook_luid,
TRY_PARSE_JSON(query_tag)['tableau-query-origins']['worksheet-luid']::VARCHAR AS worksheet_luid,
TRY_PARSE_JSON(query_tag)['tableau-query-origins']['dashboard-luid']::VARCHAR AS dashboard_luid,
*
FROM
snowflake.account_usage.query_history
WHERE
query_type = 'SELECT'
AND query_tag LIKE '%tableau-query-origins%'
AND start_time >= DATEADD('day', -28, CURRENT_DATE()) -- Any date range
)
SELECT
-- Associating Tableau metadata with QUERY_HISTORY
base.* EXCLUDE (worksheet_luid, dashboard_luid, workbook_luid),
meta_ws.item_name AS worksheet_name,
meta_db.item_name AS dashboard_name,
meta_wb.workbook_id,
meta_wb.workbook_name,
meta_wb.project_id,
meta_wb.project_name
FROM
base
INNER JOIN
metadataapi_data AS meta_wb
ON base.workbook_luid = meta_wb.item_luid
LEFT JOIN
metadataapi_data AS meta_ws
ON base.worksheet_luid = meta_ws.item_luid
LEFT JOIN
metadataapi_data AS meta_db
ON base.dashboard_luid = meta_db.item_luid;Querying the View
Now we can analyze the metadata at the query ID level. The view we’ve created has the same columns of ACCOUNT_USAGE.QUERY_HISTORY, so you can query it with the same familiarity.
For example, you can check the number of queries and execution times by project, workbook, and warehouse as follows:
SELECT
workbook_id,
workbook_name,
project_id,
project_name,
warehouse_name,
COUNT(1) AS num_of_queries,
SUM(total_elapsed_time) AS total_elapsed_time
FROM
query_history_with_tableau_metadata
GROUP BY ALL
ORDER BY 1The result will look something like this:
