TopolearnUDFs capture reusable logic in one place instead of copy-pasting expressions across queries.
BigQuery has several kinds of user-defined routines.
SQL UDFs wrap an expression: CREATE TEMP FUNCTION f(x INT64) AS (x * 2);. A temporary UDF lives for one query or script; CREATE FUNCTION mydataset.f(...) makes a persistent one others can call as mydataset.f(...). SQL UDFs are inlined into the query plan and are fast. They can take and return ARRAYs and STRUCTs, and they can contain subqueries over UNNEST. Use ANY TYPE parameters for templated functions that accept any compatible type.
JavaScript UDFs (LANGUAGE js AS r""" ... """) handle logic that's awkward in SQL, and can import libraries from Cloud Storage. They run in a sandbox and are much slower than SQL; GoogleSQL types map to JavaScript types, and INT64 is not supported as a JS UDF return type because JavaScript numbers can't represent all 64-bit integers. Prefer SQL when you can.
Table-valued functions (TVFs) are parameterized views: CREATE TABLE FUNCTION ds.orders_since(d DATE) AS SELECT ... WHERE order_date >= d, then SELECT * FROM ds.orders_since('2025-06-01').
Remote functions call an HTTP endpoint on Cloud Run or Cloud Functions for each batch of rows, for example to call an ML API. User-defined aggregate functions (UDAFs) define custom aggregates in SQL or JavaScript.
Persistent routines live in datasets and use IAM. Sharing a dataset of UDFs is a clean way to standardize business logic.
Example
CREATE TEMP FUNCTION net_price(gross NUMERIC, vat NUMERIC) AS (
ROUND(gross / (1 + vat), 2)
);
CREATE TEMP FUNCTION domain_of(email STRING) AS (
LOWER(SPLIT(email, '@')[SAFE_OFFSET(1)])
);
CREATE TEMP FUNCTION title_case(s STRING)
RETURNS STRING
LANGUAGE js AS r"""
return s.split(' ').map(w => w.charAt(0).toUpperCase() + w.slice(1)).join(' ');
""";
SELECT net_price(121, NUMERIC '0.21') AS net, -- 100
domain_of('Ana@Example.COM') AS domain, -- example.com
title_case('hello big query') AS title; -- Hello Big QueryRemember
- SQL UDFs are inlined and fast; temporary or persistent in a dataset
- JavaScript UDFs are flexible but much slower
- Table functions are parameterized views; remote functions call HTTP endpoints
Watch out for
- Reaching for a JavaScript UDF for logic that plain SQL functions can express.