Tableau & Snowflake: Pass-Through SQL with Session Variables and Parameters

With pass-through SQL in Tableau and Snowflake you can set session variables in Snowflake to deliver on a variety of use cases. You can create dynamic derived tables, set database security contexts, route queries or expand database connectivity beyond your imagination. I already went over passing in data elements from Tableau to a Snowflake UDF (here). You can set session variables in Snowflake or just pass in parameter values from Tableau to do something in the database or to the data returned from Snowflake to Tableau.

In this example we will cover basic methods to pass in a session variable or dynamically alter a where clause in Snowflake from Tableau. These are basic examples, but they show how to get and set values from Tableau into Snowflake. With the examples you will be able to apply these methods to your use case, and bend Tableau with Snowflake to your will.

The Demo SQL
We will be using the warehouse DEMO_WH and the Snowflake Sample Data database. TLDR, just give me the workbook (here). Below is some SQL that I used to set this up:

Method 1: Use Initial SQL in Tableau to Set a Session Variable in Snowflake
Now that we have the SQL to get this started, open Tableau and connect to your Snowflake account. Edit the Initial SQL dialogue to Include the following. You could set multiple session variables here to your heart’s content. We will show how to do this with Method 3 below.

SET VARIABLE_MANAGER = 'William Ward'

After that connect to the SNOWFLAKE_SAMPLE_DATA database and the TPCDS_SF10TCL schema. Then drag out a Custom SQL Query and paste the following.

SELECT
*
FROM
"SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF10TCL"."STORE_SALES" SS JOIN
"SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF10TCL"."STORE" S ON S.S_STORE_SK = SS.SS_STORE_SK
WHERE S.S_MANAGER = $VARIABLE_MANAGER

Now you can query in Snowflake with the context set by Initial SQL in Tableau. You could use, size, or alter a warehouse. This method can call stored procedures or do many other things inside your database.

Method 2: Use a Tableau Parameter with In-Line SQL
Besides setting contexts with a set initial SQL statement a user may want to interact with a UDF, data output or filter state in Tableau. You can set up Tableau to do this example easier with a Quick FIlter, but this example shows the plumbing, and it can be extended to your needs.

Open a new connection to Snowflake and connect to the SNOWFLAKE_SAMPLE_DATA database and the TPCDS_SF10TCL schema. Then drag out a Custom SQL Query and paste the following. We will come back and add in the Tableau parameter later.

SELECT
*
FROM
"SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF10TCL"."STORE_SALES" SS JOIN
"SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF10TCL"."STORE" S ON S.S_STORE_SK = SS.SS_STORE_SK

Now go to a sheet and create a parameter from S_MANAGER for later use.

Now go back to the Custom SQL Query and edit it to add in the Tableau parameter on the where clause.

SELECT
*
FROM
"SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF10TCL"."STORE_SALES" SS JOIN
"SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF10TCL"."STORE" S ON S.S_STORE_SK = SS.SS_STORE_SK
WHERE S.S_MANAGER = <Parameters.PARAMETER_S_MANAGER>

When you change the parameter in Tableau the SQL sent to Snowflake includes the chosen parameter value.

Method 3: Extending the Initial SQL to a Session Variables Table
There are many aspects that can be set with session variables other than a filter context. Snowflake things like Account, User, Region, Role, Session, Session Timestamp or System Timestamp can be set. Tableau things like Application, Workbook, Version, Server User, and Server Full Name can be set. With these contexts much richer database experiences can be derived.

I used this SQL to setup the Session Variables table, and it uses an extensible JSON payload instead of a rigid schema.

Now we can insert this Initial SQL statement into Tableau to get access to all these variables at runtime. Notice you can add any name value pairs you desire along with the system functions.

Now you can call the Variables you want from the Session Variables table in-line with SQL for the current session. We are just giving the basic plumbing here, but you can extend this much deeper to meet your use case.

--Use It in Custom SQL
SELECT
*
FROM
"SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF10TCL"."STORE_SALES" SS JOIN
"SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF10TCL"."STORE" S ON S.S_STORE_SK = SS.SS_STORE_SK
WHERE S.S_MANAGER = (SELECT TOP 1 V:S_MANAGER::STRING
FROM DEMO.PUBLIC.SESSION_VARIABLES
WHERE CURRENT_SESSION() = V:"Session ID"::INTEGER
ORDER BY V:S_MANAGER::STRING
)
;

Conclusion
We are just exploring the tip of iceberg here. So many more things can be done with this connectivity from using warehouses, to altering warehouse sizes to completely controlling the security context of the user experience on Tableau Server. Happy (custom SQL with custom parameters and variables) Queuring!

Originally published at http://bigdatadave.com on September 22, 2020.