Yes, Snowflake functions can return a table, and the two main ways to do it are with a SQL user-defined function (UDF) that uses the RETURNS TABLE clause, or with a table function (often called a UDTF) written in JavaScript or Python. A table-returning function behaves like a table in a query: you call it in the FROM clause, optionally pass it arguments, and it returns a set of rows with defined columns. This is different from a scalar function, which returns a single value.
What Is a Table Function in Snowflake?
In Snowflake, a table function is a function that returns a set of rows, meaning the output is a table structure rather than a single value. You invoke it in the FROM clause of a SELECT statement, often with the TABLE() keyword. For example, SELECT * FROM TABLE(my_function(1)) works when my_function is defined to return a table. Snowflake supports two broad categories: SQL table functions (created with CREATE FUNCTION ... RETURNS TABLE) and external-language table functions (JavaScript, Python, Java, Scala) that return rows programmatically.
How to Create a SQL Table Function
The simplest way is a SQL UDF that returns a table. You define the output columns in the RETURNS TABLE clause and then provide a SELECT statement as the body. Here is a concrete example:
CREATE OR REPLACE FUNCTION get_high_sales(min_amount NUMBER)
RETURNS TABLE (order_id NUMBER, total NUMBER)
AS
$$
SELECT order_id, total FROM sales WHERE total > min_amount
$$;
To call it, you use the TABLE() syntax:
SELECT * FROM TABLE(get_high_sales(100));
This returns all rows from the sales table where total exceeds 100. The function’s body is a standard SQL query, so you can use joins, aggregations, and any other SQL features inside it. The key limitation is that a SQL table function must be a single SELECT statement; you cannot have procedural logic like loops or conditionals in a pure SQL UDF.
When to Use a JavaScript or Python Table Function
If you need procedural logic—like iterating over rows, calling an external API, or doing complex string processing—you should write a table function in JavaScript or Python. These are called UDTFs (User-Defined Table Functions). Here is a minimal JavaScript example that returns one row per number in a range:
CREATE OR REPLACE FUNCTION generate_series(start_num NUMBER, end_num NUMBER)
RETURNS TABLE (num NUMBER)
LANGUAGE JAVASCRIPT
AS
$$
var result = [];
for (var i = START_NUM; i <= END_NUM; i++) {
result.push({NUM: i});
}
return result;
$$;
Note that in JavaScript UDTFs, input arguments are passed as variables with uppercase names (e.g., START_NUM), and the output column names must match the RETURNS TABLE column names in uppercase. Python UDTFs work similarly but use a class-based structure with a process method. These are useful when you need to generate rows based on logic that SQL alone cannot express.
Calling a Table Function Correctly
There are two main syntaxes for calling a table function. The first uses the TABLE() wrapper:
SELECT * FROM TABLE(my_function(5));
The second, which works for table functions with no arguments, is to call it directly in the FROM clause:
SELECT * FROM my_function();
You can also join a table function with a regular table. For example, if you have a table of customer IDs and want to call a function for each one, you can use a lateral join:
SELECT c.customer_id, t.recommendation
FROM customers c,
TABLE(get_recommendations(c.customer_id)) t;
This is called a lateral join because the function argument references the customers table column. Snowflake supports this pattern, which is extremely useful for row-by-row processing without writing a stored procedure.
Common Mistakes and Limitations
- Forgetting the
TABLE()wrapper when the function has arguments—you will get a syntax error. - Mismatched column names in JavaScript/Python UDTFs—the returned object keys must match the
RETURNS TABLEcolumns exactly, and in JavaScript they must be uppercase. - Using a SQL UDF for procedural logic—SQL UDFs cannot contain loops or conditionals; use JavaScript or Python instead.
- Returning a table from a scalar function—a function declared with
RETURNS NUMBERcannot return rows. - Performance issues—calling a table function for each row in a large table can be slow because the function executes per row. Consider rewriting as a join or using a SQL function that processes the whole set.
How Table Functions Differ from Stored Procedures and Views
A stored procedure can also return a result set, but it is invoked with CALL, not in a FROM clause, and you typically need to capture the result into a variable or a temporary table. A view is a named query that you can select from, but it cannot take parameters. A table function is the only one of the three that accepts arguments and returns a table directly in a query. For example, a view cannot answer “give me orders above $100” without a WHERE clause, but a table function can take 100 as a parameter. This makes table functions more flexible for reusable, parameterized queries.
Performance and Cost Considerations
Table functions in Snowflake consume compute credits based on the warehouse used to run the query. The function itself does not add extra cost beyond the query’s warehouse usage, but inefficient functions can cause longer query times and higher credit consumption. To optimize:
- Use a SQL table function whenever possible, because the optimizer can push predicates into the query body.
- Avoid calling a JavaScript/Python UDTF for each row in a large table; instead, try to pass a set of values or redesign the logic as a join.
- Set the warehouse size appropriately—a small warehouse may time out on heavy table functions.
- Use
LIMITin your outer query to reduce the result set size, but note that Snowflake may still process all rows inside the function.
Frequently Asked Questions
Can a table function return multiple columns?
Yes, you define as many columns as you need in the RETURNS TABLE clause. For example, RETURNS TABLE (id NUMBER, name STRING, score FLOAT) is valid, and the function body must return rows with those exact column types.
Can I call a table function inside a subquery?
Yes, you can use a table function in a subquery as long as it appears in the FROM clause. For instance, SELECT * FROM (SELECT * FROM TABLE(my_func())) works, but you cannot call it directly in the SELECT list because it returns multiple rows.
What is the difference between a UDF and a UDTF?
A UDF (user-defined function) can be scalar (returns one value) or tabular (returns a table). A UDTF specifically refers to a table function written in a programming language like JavaScript or Python. In Snowflake documentation, UDTF is often used for the external-language table functions, while SQL table functions are just called SQL UDFs.
Can I use a table function in a join with a regular table?
Yes, that is a common pattern, especially with lateral joins. You write the table function in the FROM clause and reference columns from other tables as arguments, as shown in the earlier example.
Do table functions work in Snowflake’s free trial?
Yes, table functions are available in all Snowflake editions, including the free trial. You just need a running warehouse to execute the query. The trial provides credits, so you can test table functions without additional cost.
Can I create a table function that returns no rows?
Yes, that is allowed. If the underlying query returns no rows, or if your JavaScript/Python function returns an empty array, the function returns an empty table. This is useful for conditional logic where you want to suppress output.
Related
- Is Oracle Stock Going to Split? What Investors Should Know
- SQL String Split: Is It Built In Across All Databases?
- Databricks STRING Data Type: Uses and Examples
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Not Blank Function: Check Whether a Cell Has DataExcel does not have a function named NOTBLANK. To test whether A2 is not truly empty, use =NOT(ISBLANK(A2));…
- COUNTIF Function to Find Duplicates in Excel: A GuideYes. In Excel, COUNTIF can find duplicates by counting how often each value appears: with values in A2:A7,…
- Excel CONCATENATE With Commas: Formulas and ExamplesTo concatenate two Excel cells with a comma and a space between them, use =A2&", "&B2. For a…
- Easy Way to Sum in Excel: Formulas and ShortcutsThe easiest way to sum numbers in Excel is to select the blank cell beneath or beside them…
- Excel Function to Check If a Cell Contains TextTo check whether a cell contains a particular word or phrase, use =ISNUMBER(SEARCH("text",A2)), replacing “text” with the text…
- Excel Goal Seek Formula: How to Loop ItExcel does not have a worksheet function called LOOP or GOALSEEK: Goal Seek is a built-in What-If Analysis…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.