Tableau & Snowflake: SQL UDFs and Workdays Example

Doing functions like looping and dynamic sql functions in Tableau is hard or impossible. Things like counting a number of workdays between 2 dates with a Tableau Function is incredibly difficult, but in SQL it is very simple. You would just say count(holidays) between date start and date end. Fortunately Tableau allows passthrough SQL to do this exact type of operation. This is something I first explored in 2012 for a Tableau Customer Conference 2012 session in San Diego. The session was called “Join Us at the Custom SQL Table”

Raw SQL Pass Through
Tableau has a special class of functions called pass through. I would recommend a browse through the documentation, but this is my general interpretation.

RAWSQLAGG_RETURNTYPE(“dbo.function(%1,%N)”,[arg1], [argN])

• AGG will Aggregate leaving it out will execute at the row level
• RETURNTYPE must be specified and match the data type of the SVF
• %1, %N are aliases that will be replaced by the contents of the arguments
• [arg1], [argN] are fields in Tableau, Constants or Parameters

Creating Dates and Holidays Tables in Snowflake
We will create a tbl_dates with month start and end for 2019 and 2020. We will create a table of all 16 Federal holidays in the US for 2019 and 2020.

Creating Holidays Count Functions
Now we can test counting holidays with a simple between statement and a SQL UDF.

--Test Select Between
SET DATE_START = TO_DATE('1/1/2019');
SET DATE_END = TO_DATE('3/7/2020');
SELECT COUNT(*) FROM TBL_HOLIDAYS WHERE DATE_HOLIDAY BETWEEN $DATE_START AND $DATE_END;

Creating a SQL UDF to return the count from a callable function and testing it.

Creating Raw Snow SQL in Tableau
Let’s start putting it all together. Open a new Tableau Workbook and connect to your Snowflake Account. If you do not have a Snowflake Account you can sign up for a free trial here: https://trial.snowflake.com/. Connect to the tbl_dates you create earlier and create a RAWSQLAGG_INT function to run the Between SQL.

RAWSQLAGG_INT("
     SELECT COUNT(*)
     FROM TBL_HOLIDAYS
     WHERE DATE_HOLIDAY BETWEEN %1 AND %2",
     [Date Start], [Date End])

We can see the resulting nested SQL in the SELECT clause that Tableau has written from our RAW SQL function by looking at the Query History in Snowflake: https://docs.snowflake.net/manuals/user-guide/ui-history.html , or by looking at a Tableau Performance Recording: https://help.tableau.com/current/pro/desktop/en-us/perf_record_create_desktop.htm .

SELECT "TBL_DATES"."DATE_END" AS "DATE_END",
"TBL_DATES"."DATE_START" AS "DATE_START",
"TBL_DATES"."ID" AS "ID",
SUM(1) AS "sum:Number of Records:ok",
(SELECT COUNT(*) FROM TBL_HOLIDAYS
WHERE DATE_HOLIDAY BETWEEN "TBL_DATES"."DATE_START"
AND "TBL_DATES"."DATE_END") AS "usr:Calculation_6013712904378720256:ok"
FROM "PUBLIC"."TBL_DATES" "TBL_DATES"
GROUP BY 1,
2,
3

Connect Raw SQL in Tableau to UDFs in Snowflake
Create another RAWSQLAGG_INT function to run the SQL UDF_HOLIDAYS.

RAWSQLAGG_INT(
     "SELECT UDF_HOLIDAYS(%1, %2)",
     [Date Start], [Date End])

We can see the resulting nested SQL in the SELECT clause that Tableau has written from our RAW SQL UDF function by looking at the Query History in Snowflake: https://docs.snowflake.net/manuals/user-guide/ui-history.html , or by looking at a Tableau Performance Recording: https://help.tableau.com/current/pro/desktop/en-us/perf_record_create_desktop.htm .

SELECT "TBL_DATES"."DATE_END" AS "DATE_END",
"TBL_DATES"."DATE_START" AS "DATE_START",
"TBL_DATES"."ID" AS "ID",
SUM(1) AS "sum:Number of Records:ok",
(SELECT COUNT(*) FROM TBL_HOLIDAYS
WHERE DATE_HOLIDAY BETWEEN "TBL_DATES"."DATE_START"
AND "TBL_DATES"."DATE_END") AS "usr:Calculation_6013712904378720256:ok",
(SELECT UDF_HOLIDAYS("TBL_DATES"."DATE_START", "TBL_DATES"."DATE_END")) AS "usr:Calculation_6013712904379215873:ok"
FROM "PUBLIC"."TBL_DATES" "TBL_DATES"
GROUP BY 1,
2,
3

Conclusion
Now you can leverage this custom function framework to do just about anything in SQL. Pull in real time stock quotes, prices, inventory or any other dynamic sql function for random numbers to HLL to hitting restful web service and other functions that do not exist in Tableau. Happy Querying!

Originally published at http://bigdatadave.com on March 8, 2020.