Extending BigQuery SQL: UDFs, Table Functions, and Remote Functions on Cloud Run

Sooner or later every BigQuery estate hits a piece of logic that SQL alone handles badly: a proprietary hashing scheme, an address normaliser, a call to an internal pricing service, a model hosted outside BigQuery. The reflex is to pull the data out into Python, transform it, and push it back. That reflex costs you egress, latency, a scheduler, and a second copy of your governance model.

BigQuery gives you four ways to keep that logic inside the warehouse, in ascending order of power and blast radius: SQL UDFs, JavaScript UDFs, remote functions backed by Cloud Run, and Vertex AI–backed functions such as AI.GENERATE. This tutorial covers when to use each, how to build them, and the operational details — quotas, batching, IAM, cost — that decide whether they survive contact with production.

1. Persistent SQL UDFs: the default you should reach for first

A persistent SQL UDF is a named, version-controlled expression stored in a dataset. It runs inside the query engine, costs nothing extra, and the optimiser can inline it.

CREATE SCHEMA IF NOT EXISTS `my-project.udf`
OPTIONS (location = 'US');

CREATE OR REPLACE FUNCTION `my-project.udf.net_revenue`(
  gross NUMERIC, discount NUMERIC, tax_rate NUMERIC
)
RETURNS NUMERIC
AS (
  ROUND((gross - IFNULL(discount, 0)) * (1 - IFNULL(tax_rate, 0)), 2)
);

Use it like any built-in:

SELECT order_id,
       `my-project.udf.net_revenue`(gross_amount, discount_amount, tax_rate) AS net_revenue
FROM `my-project.sales.orders`
WHERE order_date >= CURRENT_DATE() - 30;

Three habits make SQL UDFs pay off at scale:

• Put them in a dedicated udf dataset in the same region as the data, and grant roles/bigquery.dataViewer on that dataset broadly. A UDF nobody can call is a UDF everybody re-implements.
• Templated type parameters let one function serve many column types: CREATE FUNCTION udf.coalesce_blank(x ANY TYPE) AS (NULLIF(TRIM(CAST(x AS STRING)), '')).
• Table functions (TVFs) are the underrated cousin. When the reusable unit is a parameterised query rather than a scalar, use CREATE TABLE FUNCTION:

CREATE OR REPLACE TABLE FUNCTION `my-project.udf.orders_in_window`(
  start_date DATE, end_date DATE
)
AS (
  SELECT * FROM `my-project.sales.orders`
  WHERE order_date BETWEEN start_date AND end_date
);

SELECT * FROM `my-project.udf.orders_in_window`(DATE '2026-01-01', DATE '2026-01-31');

Because the TVF body is inlined, partition pruning still happens on order_date — you get reuse without giving up the cost controls from your partitioning and clustering design.

2. JavaScript UDFs: for logic SQL genuinely can't express

JavaScript UDFs run in a sandbox on the slots executing your query. They are the right tool for parsing oddities, legacy algorithms, and libraries you can ship as a single file to Cloud Storage.

CREATE OR REPLACE FUNCTION `my-project.udf.parse_semver`(v STRING)
RETURNS STRUCT<major INT64, minor INT64, patch INT64>
LANGUAGE js
AS r"""
  if (!v) return null;
  const m = /^v?(\d+)\.(\d+)\.(\d+)/.exec(v);
  if (!m) return null;
  return { major: parseInt(m[1]), minor: parseInt(m[2]), patch: parseInt(m[3]) };
""";

To use an external library, upload the bundled file and reference it:

CREATE OR REPLACE FUNCTION `my-project.udf.normalize_phone`(raw STRING)
RETURNS STRING
LANGUAGE js
OPTIONS (library = ['gs://my-bucket/js/phone-bundle.min.js'])
AS r"""
  return PhoneLib.normalize(raw, 'US');
""";

What to watch:

• Performance. A JavaScript UDF is typically an order of magnitude slower per row than equivalent SQL. On a billion-row scan, that difference is the whole query. Always benchmark the SQL version first.
• Types. INT64 values cross the SQL/JS boundary as floating point; anything above 2^53 must be passed as STRING and parsed, or you will silently corrupt IDs.
• Determinism. Avoid Math.random() and clock reads inside UDFs; retries will produce different answers and break incremental merges.
• Resource limits. Per-row memory and output-size limits apply, and a UDF returning large arrays will fail the whole query rather than one row.

3. Remote functions: BigQuery calling Cloud Run

When the logic lives in another language, needs a secret, or must call an internal service, a remote function lets BigQuery invoke an HTTP endpoint — usually Cloud Run or a Cloud Run function — as if it were a UDF.

Step 1 — the service

BigQuery posts a JSON body containing a calls array, one element per row batch, and expects a replies array of the same length in the same order. A minimal Python service:

import json
from flask import Flask, request

app = Flask(__name__)

@app.route("/", methods=["POST"])
def handler():
    payload = request.get_json(silent=True) or {}
    calls = payload.get("calls", [])
    replies = []
    for call in calls:
        sku, region = call[0], call[1]
        try:
            replies.append(price_lookup(sku, region))   # your logic
        except Exception:
            replies.append(None)                        # never raise per row
    return json.dumps({"replies": replies}), 200

Two rules decide whether this survives production: never let one bad row raise, because a non-200 response fails the batch and BigQuery retries the whole thing; and always return replies in input order and equal length, or you will silently misalign results across rows.

Step 2 — the connection

bq mk --connection --location=US --project_id=my-project \
      --connection_type=CLOUD_RESOURCE remote-fn-conn

bq show --format=prettyjson --connection my-project.US.remote-fn-conn

The output includes a service account like bqcx-...@gcp-sa-bigquery-condel.iam.gserviceaccount.com. Grant it permission to invoke the service — and nothing else:

gcloud run services add-iam-policy-binding price-lookup \
  --region=us-central1 \
  --member='serviceAccount:bqcx-XXXX@gcp-sa-bigquery-condel.iam.gserviceaccount.com' \
  --role='roles/run.invoker'

Keep the Cloud Run service on --no-allow-unauthenticated. A remote function endpoint left open to the internet is an unauthenticated copy of your business logic.

Step 3 — the function

CREATE OR REPLACE FUNCTION `my-project.udf.lookup_price`(sku STRING, region STRING)
RETURNS NUMERIC
REMOTE WITH CONNECTION `my-project.US.remote-fn-conn`
OPTIONS (
  endpoint = 'https://price-lookup-xxxxx-uc.a.run.app',
  max_batching_rows = 500,
  user_defined_context = [('version', '2026-02')]
);

max_batching_rows is the knob that matters. Too low and you pay HTTP overhead per handful of rows; too high and you hit the request-size limit or the service timeout. Start at a few hundred, watch Cloud Run p99 latency and BigQuery slot time, and tune. user_defined_context is passed through on every request, which is how you version behaviour without recreating the function.

Step 4 — call it on the right number of rows

-- Good: distinct keys only, then join back
WITH keys AS (
  SELECT DISTINCT sku, region
  FROM `my-project.sales.order_lines`
  WHERE order_date >= CURRENT_DATE() - 7
),
priced AS (
  SELECT sku, region, `my-project.udf.lookup_price`(sku, region) AS price
  FROM keys
)
SELECT l.*, p.price
FROM `my-project.sales.order_lines` l
JOIN priced p USING (sku, region)
WHERE l.order_date >= CURRENT_DATE() - 7;

Calling a remote function on 400 million rows when there are 30,000 distinct keys is the single most common — and most expensive — mistake we see. Deduplicate, call, join back. Better still, materialise the result into a small lookup table refreshed on a schedule, and let the hot path be a plain join.

4. Vertex AI–backed functions: the managed special case

If the remote service you were about to build is really "call an LLM", you do not need Cloud Run at all. A CLOUD_RESOURCE connection with Vertex AI access plus the built-in AI functions covers it:

SELECT
  ticket_id,
  AI.GENERATE(
    ('Classify this support ticket as billing, bug, or feature request: ', body),
    connection_id => 'us.vertex-conn',
    endpoint => 'gemini-2.5-flash',
    output_schema => 'category STRING, confidence FLOAT64'
  ).category AS category
FROM `my-project.support.tickets`
WHERE created_at >= CURRENT_DATE() - 1;

Same discipline applies: run it over the smallest row set that answers the question, persist the output, and never put a generative call in the inner loop of a dashboard query. Model names and function signatures move quickly here — verify against current Google Cloud documentation before shipping.

5. Choosing between them

Need	Use
Reusable arithmetic, formatting, business rules	Persistent SQL UDF
Parameterised reusable query	Table function (TVF)
Parsing, legacy algorithms, JS libraries	JavaScript UDF
External API, secrets, non-JS runtime, internal service	Remote function on Cloud Run
LLM classification, extraction, summarisation	AI.GENERATE / Vertex AI-backed functions
Heavy per-row ML inference on a trained model	ML.PREDICT on an imported or remote model

6. Operating this in production

• Version control. UDF definitions belong in the same repo as your transformations. If you use Dataform, define them as operations so every environment gets the same functions; with Terraform, google_bigquery_routine does the same job.
• Environments. Reference functions through a variable project or dataset (${udf_dataset}.net_revenue) so dev, staging, and prod do not share one mutable definition.
• Test them. A UDF is code. Keep a small table of input/expected-output pairs and assert against it in CI — the same assertion pattern used for data quality checks.
• Observe them. Remote function latency shows up as slot time in INFORMATION_SCHEMA.JOBS; Cloud Run gives you request counts, errors, and p99. Alert on both, and review your most expensive queries after any new remote function goes live.
• Regions must match. The connection, the dataset, and ideally the Cloud Run service should live in the same region. Cross-region calls add latency and, for some setups, are not permitted at all.
• Quotas. Remote functions are subject to request size, response size, and timeout limits; concurrency is bounded by your Cloud Run scaling settings. Load test at the batch size you configured, not at one row.

Where this pays off

Teams that adopt UDFs well end up with a small, well-governed library of business definitions — net revenue, active customer, fiscal period — that every dashboard and model shares. Teams that adopt them badly end up with a JavaScript UDF running on every row of a fact table and a Cloud Run bill nobody can explain.

If you want help drawing that line — auditing existing routines, moving Python transforms back into the warehouse, or standing up a governed UDF library with CI — our BigQuery SQL query development and data engineering teams do exactly this work. Get in touch with a description of the logic you are trying to bring into BigQuery.