Table Valued Function Databricks: How to Use

Formulas & functions · Updated November 22, 2026

A table-valued function (TVF) in Databricks is a user-defined function that returns a table (a set of rows and columns) instead of a single scalar value, and you call it directly in the FROM clause of a query. In Databricks SQL and PySpark, TVFs let you encapsulate complex logic, reuse it across queries, and treat the result like any other table or view.

Background: What Are Table-Valued Functions in Databricks?

Databricks is built on Apache Spark, and its SQL engine supports two main types of user-defined functions: scalar UDFs (return a single value) and table-valued functions (return a relation). The concept of TVFs is borrowed from traditional SQL databases like SQL Server and PostgreSQL, but Databricks implements them using Spark SQL’s CREATE FUNCTION syntax. In Databricks, a TVF can be written in SQL or Python (via PySpark UDFs that return DataFrames). The key advantage is that a TVF can be used in joins, subqueries, and even in the FROM clause with parameters, making it a powerful tool for parameterized reporting and data transformation.

How Table-Valued Functions Work in Databricks SQL

In Databricks SQL, you define a TVF using the CREATE FUNCTION statement with RETURNS TABLE. The function body is a SQL query that returns the result set. Here’s a basic example:

CREATE FUNCTION get_orders_by_status(status STRING)
RETURNS TABLE (order_id INT, customer_id INT, order_total DECIMAL(10,2))
RETURN SELECT order_id, customer_id, order_total FROM orders WHERE order_status = status;

To call it, you use it in the FROM clause:

SELECT * FROM get_orders_by_status('shipped');

You can also pass a column as a parameter, which enables set-based logic. For example, you could join a table of statuses to the TVF:

SELECT s.status, t.order_id FROM statuses s
LATERAL VIEW get_orders_by_status(s.status) t;

Note: Databricks SQL supports LATERAL VIEW for this purpose, though the exact syntax may vary by version. In PySpark, you can define a Python function that returns a DataFrame and register it as a temporary table function using spark.udf.register, but the SQL-based approach is more straightforward for most users.

Choosing Between a TVF, a View, or a Stored Procedure

When working in Databricks, you’ll often wonder whether to use a TVF, a regular view, or a stored procedure. Here’s a quick guide:

  • Use a TVF when you need to pass parameters that change the result set, and you want to reuse that logic in multiple queries. TVFs are ideal for reporting where date ranges or category filters vary.
  • Use a view when the logic is static and doesn’t require parameters. Views are simpler and can be optimized by the query optimizer.
  • Use a stored procedure when you need to perform multiple steps (like inserting into a table, then updating another) and don’t need to return a result set to the caller. Procedures are for side effects, not for querying.

In practice, TVFs are the best choice for parameterized read-only queries. They are also more secure than dynamic SQL in stored procedures because they reduce SQL injection risk.

Common Problems and How to Avoid Them

One common issue is that TVFs in Databricks SQL may not support all SQL constructs inside the function body. For example, you cannot use INSERT or UPDATE statements; the function must be purely a SELECT. Also, TVFs do not support dynamic SQL (building a query string and executing it) inside the function. If you need dynamic logic, consider using a stored procedure or a PySpark UDF.

Another problem is performance: if you call a TVF in a join with a large table, the function may be executed once per row, leading to slow performance. To mitigate this, try to filter the input before joining, or use a broadcast hint. Also, be aware that TVFs are not materialized; they are re-evaluated each time you call them. If the underlying data is huge, consider caching the result in a temporary view.

Comparison: Databricks TVFs vs. Scalar UDFs

Scalar UDFs return a single value per row and are used in the SELECT list or WHERE clause. Table-valued functions return multiple rows and columns and are used in the FROM clause. For example, a scalar UDF could compute a discount percentage, while a TVF could return all orders for a customer. Scalar UDFs are easier to write but can be slower if used in row-by-row operations. TVFs are more flexible and can be optimized better because they are treated as a relation. In Databricks, Python UDFs (both scalar and table) run in a separate process, which adds serialization overhead. For large datasets, prefer native SQL functions or TVFs written in SQL.

Cost and Performance Considerations

Databricks charges based on compute (DBUs) used by clusters or SQL warehouses. TVFs themselves don’t add cost beyond the underlying query execution. However, if you use a TVF in a loop or call it many times with different parameters, each call incurs compute cost. To optimize, consider using a single query with a LATERAL VIEW to avoid multiple invocations. Also, be mindful of data shuffling: if the TVF returns a large result set, it may cause network shuffles. Use partitioning and bucketing on the input tables to reduce shuffle. For SQL warehouses, you can scale up or down based on workload; TVFs are generally efficient if written with proper filters.

Safety, Permissions, and Best Practices

When creating a TVF, you need appropriate permissions on the underlying tables. In Databricks, you can use CREATE FUNCTION with USING to specify a location for the function’s code, but for SQL TVFs, the function is stored in the metastore. To prevent SQL injection, never concatenate user input into the function body; always use parameters. Also, follow these best practices:

  • Always specify the return schema explicitly to avoid ambiguity.
  • Use meaningful names for functions and parameters.
  • Test the function with sample data before deploying.
  • Document the function’s behavior and expected input types.
  • Limit the use of TVFs in recursive or iterative processes; they are not designed for that.

Alternatives to TVFs in Databricks

If TVFs don’t fit your needs, consider these alternatives:

  • Parameterized views (not natively supported in Databricks, but you can simulate with a temporary view and a variable).
  • PySpark DataFrames: Write a Python function that takes parameters and returns a DataFrame. You can call it in a notebook and use the result as a temp view.
  • Stored procedures with output parameters (but they don’t return a table directly).
  • Delta Live Tables (DLT) for streaming or incremental pipelines, where you define tables with SQL and can parameterize them using pipeline parameters.

For example, in a notebook, you can do:

def get_orders(status):
    return spark.sql("SELECT * FROM orders WHERE order_status = '{}'".format(status))
df = get_orders('shipped')
df.createOrReplaceTempView('shipped_orders')

This is not a true TVF but achieves similar results. However, it lacks the reusability across different notebooks unless you package it as a library.

Frequently Asked Questions

Can I use a table-valued function in a JOIN in Databricks?

Yes, you can use a TVF in a JOIN, but you need to use LATERAL VIEW or a subquery. For example: SELECT * FROM table1 t1 JOIN get_orders(t1.status) t2 ON t1.id = t2.id may not work directly; instead use LATERAL VIEW to expand the function per row.

What is the difference between a table-valued function and a view in Databricks?

A view is a saved query with no parameters. A TVF accepts parameters and returns a table based on those parameters. Views are static; TVFs are dynamic.

Can I write a table-valued function in Python (PySpark)?

Yes, you can write a Python function that returns a DataFrame and register it as a temporary table function using spark.udf.register with returnType as a StructType. However, the syntax is more complex than SQL TVFs, and performance may be slower due to serialization.

Are table-valued functions supported in Databricks SQL Serverless?

Yes, SQL Serverless warehouses support TVFs as long as you use the correct syntax. The function is stored in the metastore and can be called from any warehouse.

How do I drop a table-valued function in Databricks?

Use the DROP FUNCTION statement: DROP FUNCTION IF EXISTS function_name;. This removes the function from the metastore.

Can a table-valued function call another table-valued function?

Yes, you can nest TVFs. For example, a TVF can call another TVF in its SELECT statement, as long as the inner function is defined first. This allows for modular design.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.