Passthrough functions allow you to send SQL expressions directly to Google BigQuery without being interpreted by ThoughtSpot. If you have custom database functions that ThoughtSpot doesn’t support, you can use these new passthrough functions in the ThoughtSpot Formula Assistant to call your custom functions.

A passthrough function serves as a wrapper around the specific BigQuery SQL function. To use a specific BigQuery SQL function, match the data type that the function returns with the Embrace passthrough function that returns that data type.

Example

A company stores all data in varchar (dates, amounts, prices, and flags) in a history table and the only datetime field is a load date, which is not seen by the user.

This example only applies to quantities and amount fields, since ThoughtSpot’s to_date function automatically generates the correct BigQuery SQL for dates.

The original table items quantity, quantity_kg, and shipvalue are datatyped as varchar. Quantity is an integer, quantity_kg is a double (or float), and shipvalue is a double or float.

Formulas used in ThoughtSpot

The following passthrough functions were applied in a worksheet:

  • Shipped Quantity: sql_double_op ( “safe_cast({0} as numeric)” ,quantity )
  • Shipped Quantity (kg): sql_double_op ( “safe_cast({0} as numeric)” ,quantity_kg )
  • Shipped Value: sql_double_op ( “safe_cast({0} as numeric)” , shipvalue )

Since the date is also stored as varchar in ddmmYYYY format, and ThoughtSpot’s standard function to_date passes the parse datetime correctly to BigQuery, using a passthrough function is not required. However, if you wanted to use it, it would look like this:

to_date ( date_shipped , ‘%d/%m/%Y’ )

For details on all supported passthrough functions, see Embrace passthrough functions.

For details on BigQuery SQL functions, see Google’s Expressions, functions, and operators in Standard SQL.