Google launched the TYPEOF Function for BigQuery

How and When to use the new utility Function

Photo by Aivars Vilks on Unsplash

Google announced the launch of the new function TYPEOF which is already general available right now.

The TYPEOF function in SQL is used to determine the data type of an expression. It is particularly useful when dealing with dynamic or unknown data types, such as in loosely typed databases or when debugging queries.

SELECT TYPEOF(expression);
expression: The value or column whose data type you want to determine.

The return type is a String. The following example produces the name of the data type for the expression passed into the TYPEOF function. When NULL is passed in, the supertype, INT64, is produced[2].

SELECT
  TYPEOF(NULL) AS A,
  TYPEOF('hello') AS B,
  TYPEOF(12+1) AS C,
  TYPEOF(4.7) AS D

The result will look like the following:

Key Use Cases could be:

• Debugging queries by checking unexpected data types
• Handling dynamic schema situations where data types are not fixed
• and Validating data transformations