How to Create UDFs in BigQuery

Galaxy Glossary

How do I create and use UDFs in BigQuery?

BigQuery UDFs let you encapsulate reusable SQL or JavaScript logic and invoke it like a built-in function in any query.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What is a BigQuery UDF?

A user-defined function (UDF) is custom logic written in SQL or JavaScript that you register in a dataset and then call from any query. UDFs help eliminate duplicated code, centralize business rules, and simplify complex calculations.

Why use UDFs instead of subqueries?

UDFs improve readability, maintainability, and testing.You define the logic once, reference it everywhere, and update it in a single place when requirements change—no more hunting through dozens of queries.

How do I create a SQL UDF?

Use CREATE FUNCTION with a return type and an expression that operates on input parameters. You can include OR REPLACE to update an existing UDF without dropping it first.

How do I create a JavaScript UDF?

Add LANGUAGE js and provide a JS function body in triple quotes.JavaScript UDFs are useful for string manipulation, complex loops, or third-party libraries not easily expressed in SQL.

Can UDFs access other tables?

UDFs can reference other tables only if you embed them in subqueries inside the function body. Pure SQL scalar UDFs should avoid external table references for best performance and reusability.

What permissions are required?

You need bigquery.routines.create to create UDFs and bigquery.routines.update to replace them.Consumers require bigquery.routines.get on the dataset that stores the UDF.

Best practices for production UDFs?

Version using semantic names (customer_ltv_v1), add comments with inputs/outputs, place in a shared routines dataset, enforce naming conventions, and monitor usage with INFORMATION_SCHEMA.ROUTINE_USAGE.

When should I avoid UDFs?

Avoid UDFs for one-off analytics or when built-in functions already satisfy the requirement. UDFs add maintenance overhead, so favor them only when logic is reusable.

Why How to Create UDFs in BigQuery is important

How to Create UDFs in BigQuery Example Usage


-- Calculate lifetime value for every customer in the last year
SELECT c.id,
       c.name,
       analytics.customer_lifetime_value(c.id) AS ltv
FROM `project.ecom.Customers` AS c
WHERE c.created_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
ORDER BY ltv DESC;

How to Create UDFs in BigQuery Syntax


-- SQL scalar UDF
CREATE OR REPLACE FUNCTION analytics.customer_lifetime_value(cust_id INT64)
RETURNS FLOAT64
AS (
  SELECT SUM(total_amount)
  FROM `project.ecom.Orders`
  WHERE customer_id = cust_id
);

-- JavaScript scalar UDF for SKU normalization
CREATE OR REPLACE FUNCTION analytics.normalize_sku(sku STRING)
RETURNS STRING
LANGUAGE js AS """
  return sku.replace(/-/g, '').toUpperCase();
""";

Common Mistakes

Using unqualified dataset references inside the UDF body. This breaks if the UDF is copied to another project. Always fully qualify tables, e.g., `project.dataset.table`.
Forgetting to grant `bigquery.routines.get` on the dataset. Consumers will see "function not found" errors. Grant dataset-level `roles/bigquery.dataViewer` or a custom role with `bigquery.routines.get`.

Frequently Asked Questions (FAQs)

Can a UDF return a table?

Yes. Use RETURNS TABLE<schema> and write a query that yields the desired columns.

Are UDFs cached?

The function definition is cached, but result caching depends on the calling query. If the query qualifies for result caching, BigQuery stores results as usual.