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

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  
STRTOK_SPLIT_TO_TABLE  
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 table functions):
Queries
QUERY_HISTORY , QUERY_HISTORY_BY_*  
Warehouse & Storage Usage
DATABASE_STORAGE_USAGE_HISTORY  
WAREHOUSE_LOAD_HISTORY  
WAREHOUSE_METERING_HISTORY  
STAGE_STORAGE_USAGE_HISTORY  
Data Loading & Transfer
COPY_HISTORY  
DATA_TRANSFER_HISTORY  
PIPE_USAGE_HISTORY  
Data Clustering (within Tables)
AUTOMATIC_CLUSTERING_HISTORY  
User Login
LOGIN_HISTORY , LOGIN_HISTORY_BY_USER  

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.