User-Defined Functions in BigQuery

The moment you find yourself pasting the same little calculation into query after query, you have outgrown copy and paste. BigQuery’s user-defined functions, or UDFs, let you wrap that logic up once, give it a name, and call it like any built-in function. The payoff is queries that are clearer, more modular, and far easier to maintain, because the logic lives in one place instead of scattered across a dozen scripts. BigQuery supports two flavours, one written in SQL and one in JavaScript, and knowing when to reach for each is most of the skill.

SQL UDFs

The simplest kind defines its logic directly in SQL. You name the function, declare its parameters and their types, state the return type, and give the expression it should evaluate.

CREATE TEMP FUNCTION double_value(x FLOAT64)
RETURNS FLOAT64 AS (
  x * 2
);
SELECT double_value(10) AS doubled;

That is the whole pattern. The body is a single SQL expression, the parameter and return types are explicit, and once defined the function is called exactly like a native one. SQL UDFs are the right default because they are simple, fast, and transparent, and they cover the large majority of reusable logic you will actually need.

JavaScript UDFs

Sometimes SQL alone is awkward, particularly for string manipulation or anything procedural. For those cases you can write the body in JavaScript by adding the language clause and enclosing the code in triple-quoted strings.

CREATE TEMP FUNCTION reverse_text(s STRING)
RETURNS STRING LANGUAGE js AS """
  return s.split('').reverse().join('');
""";
SELECT reverse_text('hello') AS reversed;

The structure mirrors the SQL version, with the same typed parameters and return type, but the body is now real JavaScript that returns a value. This unlocks logic that would be painful in pure SQL, but it comes at a cost in performance and complexity, so the guideline is to use JavaScript only when SQL genuinely cannot do the job cleanly.

Temporary versus persistent

UDFs come in two scopes, and choosing the right one matters. A temporary function, created with CREATE TEMP FUNCTION, exists only for the duration of the current query or script and vanishes afterward. A persistent function, created with CREATE FUNCTION, is stored in a dataset and stays available across queries and sessions for anyone with access. Temporary functions are perfect for logic specific to one script, while persistent functions are how you build a shared library of common calculations your whole team can reuse.

Creating a persistent one looks almost identical, except you qualify the name with a dataset and typically use CREATE OR REPLACE so you can update it in place.

CREATE OR REPLACE FUNCTION dataset.square_number(x FLOAT64)
RETURNS FLOAT64 AS (
  x * x
);

From then on it is callable from any query by its qualified name.

SELECT dataset.square_number(4) AS squared;

That persistence is what turns a handy one-off into genuine shared infrastructure, since the definition lives in one place and every query that calls it stays consistent.

More than one parameter

UDFs take as many parameters as you need, which is where they start to read like real domain logic. A discount calculation, for instance, becomes a named function rather than a scattered expression.

CREATE TEMP FUNCTION apply_discount(price FLOAT64, discount FLOAT64)
RETURNS FLOAT64 AS (
  price * (1 - discount)
);
SELECT apply_discount(100, 0.20) AS discounted_price;

Naming the operation apply_discount makes every query that uses it self-documenting, which is a real maintainability win over an inline multiplication that the next reader has to decode.

JavaScript for JSON parsing

A common place JavaScript earns its keep is reaching into nested JSON. Parsing a string and pulling a deeply nested value is natural in JavaScript and clumsy in SQL.

CREATE TEMP FUNCTION extract_name(json STRING)
RETURNS STRING LANGUAGE js AS """
  var obj = JSON.parse(json);
  return obj.user.profile.name;
""";
SELECT extract_name('{"user": {"profile": {"name": "Alice"}}}') AS user_name;

This is a good example of the right use of a JavaScript UDF: the task is genuinely procedural, the JavaScript is short and clear, and wrapping it in a function keeps the messy parsing out of your main query.

Conditional logic

UDFs are not limited to arithmetic. A SQL UDF can wrap a CASE expression to turn a raw value into a category, which is the kind of business rule you want defined once and reused everywhere.

CREATE TEMP FUNCTION categorize_order(amount FLOAT64)
RETURNS STRING AS (
  CASE
    WHEN amount < 50 THEN 'Small'
    WHEN amount < 200 THEN 'Medium'
    ELSE 'Large'
  END
);
SELECT
  order_id,
  amount,
  categorize_order(amount) AS order_size
FROM `dataset.orders`;

Now the definition of a small, medium, or large order lives in one function. If the thresholds ever change, you edit them in a single place rather than hunting through every report that bucketed orders by hand.

Using UDFs well

A few habits keep UDFs an asset rather than a liability. Keep each one simple and focused, since a UDF that tries to do too much is harder to test and reason about than the duplication it replaced. Promote genuinely common logic to persistent functions so the whole team shares one definition. Always declare parameter and return types explicitly, because vague typing is a frequent source of errors. And be disciplined about scope, reaching for a persistent function only when reuse across queries is real, and a temporary one otherwise.

The single most common mistake is overusing JavaScript. It is tempting because it feels familiar, but SQL UDFs are simpler, faster, and easier to maintain, so favour them and keep JavaScript for the cases SQL truly cannot handle efficiently, like the JSON parsing above. When you do write a JavaScript UDF, test the logic in isolation first, because debugging it buried inside a large query is far harder than proving it works on its own.

A practical way to adopt UDFs is to let them emerge from your real work. Notice a piece of logic you keep repeating across queries, decide whether SQL or JavaScript fits it best, give it a clear descriptive name, test it on its own, and then choose its scope based on whether others will need it. Done that way, your UDF library grows naturally out of the duplication it removes, and every query you write afterward becomes a little shorter and a little clearer.

See you soon.