Table functions in BigQuery

A table function is a parameterized view, a stored query you call in FROM with arguments. Constant arguments prune partitions and unused columns aren't read; an argument computed by a subquery reads the whole table. There are no temporary table functions.

A table function (also called a table-valued function, or TVF) is a stored SELECT with parameters. You call it in FROM like a table, and pass the values the query needs. Think of it as a view that takes arguments: one definition of “sales for a day” or “orders for a customer” that every report can reuse, instead of each copying the query and the filter.

Creating and calling one

CREATE OR REPLACE TABLE FUNCTION mydataset.sales_on(d DATE)
AS (
  SELECT id, store, amount
  FROM mydataset.sales
  WHERE day = d
);

SELECT store, SUM(amount) AS total
FROM mydataset.sales_on('2026-03-02')
GROUP BY store;

In Querylab.io, a dataset’s SQL Templates menu has a Table Function skeleton to start from (DDL operations).

The result can be joined, filtered and aggregated like any table. A string literal like '2026-03-02' is converted to the DATE parameter, the same way it would be in a comparison.

Parameters can be ANY TYPE, and the body can use any SELECT syntax, including CTEs, joins and window functions. RETURNS TABLE<id INT64, ...> after the parameters is optional; without it, BigQuery works out the output columns from the query.

No temporary table functions

Unlike scalar user-defined functions, a table function can’t be temporary. CREATE TEMP TABLE FUNCTION fails with “CREATE TEMPORARY TABLE FUNCTION statements are not currently supported”. Every table function lives in a dataset.

What a call costs

A call is billed like the query it expands to, and BigQuery optimizes the function body together with the calling query. We measured this with a function over a 2.25 MB table (day, id, note) partitioned by day into 10 partitions:

So:

• A constant argument used in the body’s WHERE on the partitioning column prunes partitions, the same as writing the filter by hand.
• Columns the caller doesn’t select aren’t read, even if the function returns them. Selecting only id read id plus the day column used by the filter.
• An argument computed by a subquery isn’t known when the query is planned, so nothing is pruned. If you need “the latest day”, compute it first into a script variable and pass the variable.

Table functions vs views

Use a view when callers filter in different ways, and a table function when every call needs the same filter with a different value and you want to make it impossible to forget. See Views and Authorized views.

Built-in table functions

BigQuery also has its own table functions. A few that are specific to it:

• APPENDS(TABLE t, start, end) and CHANGES(TABLE t, start, end) return the rows inserted, or inserted and changed, in a time range. See Change data capture.
• VECTOR_SEARCH(...) finds the nearest rows by embedding. See Vector search.
• EXTERNAL_QUERY(connection, sql) runs a query in Cloud SQL, Spanner or AlloyDB and returns its result. See Federated queries.
• ML.PREDICT, ML.EVALUATE, AI.GENERATE_TABLE and the other ML. and AI. functions return tables. See BigQuery ML.
• GAP_FILL fills missing points in a time series, and RANGE_SESSIONIZE groups overlapping RANGE values into sessions.

Permissions

Callers need bigquery.routines.get on the function and read access to the tables it queries, unless the function is an authorized routine, which reads the tables with its own access.