Table Functions¶

A table function returns a set of rows for each input row. The returned set can contain zero, one, or more rows, each of which can contain one or more columns.

For example, the following are appropriate as table functions:

A function that accepts a date as an argument and returns a set of rows listing cities that had record high temperatures on that date. (Multiple cities might hit record high temperatures on the same date.)
A function that accepts an account number and a date, and returns all charges billed to that account on that date. (More than one charge might have been billed on a particular date.)

In each of these examples, the function might return zero rows (e.g. if there are no record high temperatures on that date), one row (if one city had a record high temperature on that date), or many rows (if many cities hit record highs).

Each input row of a table function consists of zero, one, or more arguments. Each argument must be a scalar expression.

A table function appears in the FROM clause of a SQL statement. The call to the table function is usually wrapped in TABLE(). For example, the following statement calls a table function named record_high_temperatures(), which takes a DATE value as an argument:

SELECT city_name, temperature
    FROM TABLE(record_high_temperatures_for_date('2021-06-27'::DATE))
    ORDER BY city_name;

Table functions can be grouped into two sub-categories:

1-to-N functions.
M-to-N functions.

In a 1-to-N function, each input row row generates N output rows (where N can be zero, one, or more). The examples above are 1-to-N functions. For example, each time that the function RECORD_HIGH_TEMPERATURES_FOR_DATE() is called and passed a date, the function a set of rows. The rows for each date are independent of the rows for every other date. Most table functions are 1-to-N functions.

In an M-to-N function, a group of M input rows produces a group of N output rows. M can be one or more rows. N can be zero, one or more rows. For example, you can write a function that returns the moving average of a set of M rows. Suppose that you want to know the 10-day moving average of a stock price. Suppose also that you don’t want the 10-day moving average to generate output rows until the function has processed at least 10 rows, and you don’t want the average to use rows from different months. In this example, for January, the function would have 31 input rows (because January has 31 days), and 22 output rows (the first output row is on January 10th, and the last output row is on January 31st). In this example, both M and N vary, depending upon the length of the month.

Table functions can be built-in or user-defined. Built-in table functions are listed in List of System-Defined Table Functions. For information about user-defined functions, including user-defined table functions, see UDFs (User-Defined Functions).

Table functions are sometimes also called “tabular functions”.

In this Topic:

List of System-Defined Table Functions
Syntax
Usage Notes

List of System-Defined Table Functions¶

Snowflake provides the following system-defined (i.e. built-in) table functions:

Sub-category	Function	Notes
Data Loading	INFER_SCHEMA	For more information, see Loading Data into Snowflake.
Data Loading	VALIDATE
Data Generation	GENERATOR
Data Conversion	SPLIT_TO_TABLE
Data Conversion	STRTOK_SPLIT_TO_TABLE
Object Modeling	GET_OBJECT_REFERENCES
Semi-structured Queries	FLATTEN	For more information, see Querying Semi-structured Data.
Query Results	RESULT_SCAN	Can be used to perform SQL operations on the output from another SQL operation (e.g. SHOW).
Historical & Usage Information (Information Schema):
User Login	LOGIN_HISTORY , LOGIN_HISTORY_BY_USER
Queries	QUERY_HISTORY , QUERY_HISTORY_BY_*
Warehouse & Storage Usage	DATABASE_STORAGE_USAGE_HISTORY
	WAREHOUSE_LOAD_HISTORY
	WAREHOUSE_METERING_HISTORY
	STAGE_STORAGE_USAGE_HISTORY
Column-level Security	POLICY_REFERENCES
Database Replication	DATABASE_REFRESH_HISTORY DATABASE_REFRESH_PROGRESS , DATABASE_REFRESH_PROGRESS_BY_JOB REPLICATION_USAGE_HISTORY	For more information, see Replicating Databases Across Multiple Accounts
Data Loading & Transfer	COPY_HISTORY
	DATA_TRANSFER_HISTORY
	PIPE_USAGE_HISTORY
	VALIDATE_PIPE_LOAD
Data Clustering (within Tables)	AUTOMATIC_CLUSTERING_HISTORY	For more information, see Automatic Clustering
External Functions	EXTERNAL_FUNCTIONS_HISTORY	For more information, see External Functions
External Tables Maintenance	EXTERNAL_TABLE_FILES	For more information, see Working with External Tables
External Tables Maintenance	EXTERNAL_TABLE_FILE_REGISTRATION_HISTORY
Materialized Views Maintenance	MATERIALIZED_VIEW_REFRESH_HISTORY	For more information, see Working with Materialized Views
SCIM Maintenance	REST_EVENT_HISTORY	For more information, see Auditing with SCIM
Search Optimization Maintenance	SEARCH_OPTIMIZATION_HISTORY	For more information, see Using the Search Optimization Service
Tasks Maintenance	TASK_DEPENDENTS	For more information, see Executing SQL Statements on a Schedule Using Tasks
Tasks Maintenance	TASK_HISTORY

Syntax¶

SELECT ...
  FROM [ <input_table> [ [AS] <alias_1> ] ,
         [ LATERAL ]
       ]
       TABLE( <table_function>( [ <arg_1> [, ... ] ] ) ) [ [ AS ] <alias_2> ];

For function-specific syntax, see the documentation for the individual system-defined table functions.

Usage Notes¶

Table functions can also be applied to a set of rows using the LATERAL construct.
To enable using table expressions, Snowflake supports ANSI/ISO standard syntax for table expressions in the FROM clause of queries and subqueries. This syntax is used to indicate that an expression returns a collection of rows instead of a single row.
This ANSI/ISO syntax is valid only in the FROM clause of the SELECT list. You cannot omit these keywords and parentheses from a collection subquery specification in any other context.