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.
Tried Native Derived Tables, but sql_preamble doesn’t appear in the query, which prevents us from using the UDFs.
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.