Table Functions¶

Table functions return a set of rows instead of a single scalar value. Table functions appear in the FROM clause of a SQL statement and cannot be used as scalar functions. Table functions take scalar expressions as input.

Note

You can also define table UDFs that return rows from a SQL query or JavaScript. For more information, see UDFs (User-Defined 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 table functions:

Sub-category	Function	Notes
Data Loading	VALIDATE	For more information, see Loading Data into Snowflake.
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.