BigQuery UDFs in Derived Tables (or sql_preamble in Native Derived Tables)

[axjia]

I’ve developed some BigQuery UDFs (to remove non-business time between two timestamps) that we’re currently leveraging through sql_preamble in our explores, but I can’t seem to find a way to use them in a PDT – or a derived table of any sort for that matter.

Use case is we need to pre-aggregate timestamp differences with non-business time removed so that we can join them back in.

1. Tried Native Derived Tables, but sql_preamble doesn’t appear in the query, which prevents us from using the UDFs.

2. Tried copy-pasting the UDFs directly into a Derived Table, but since derived tables are generated as subqueries, the UDFs end up inside of the subquery…triggering a “Syntax error: Expected “(” or keyword SELECT or keyword WITH but got keyword CREATE”

Is there a better solution? Not much documentation for sql_preamble out there, so not sure if there’s a workaround I’m unaware of.

Thanks!