Table Functions

Table functions return a set of rows instead of a single scalar value.

In this Topic:

List of Table Functions

Snowflake provides the following system-defined table functions:

Sub-category Function Notes
Data Loading VALIDATE For more information, see Data Loading.
Data Generation GENERATOR  
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 Information: These table functions are part of the Snowflake Information Schema. For more information, see Information Schema.
Queries
QUERY_HISTORY , QUERY_HISTORY_BY_*
Warehouse & Storage Usage
DATABASE_STORAGE_USAGE_HISTORY
WAREHOUSE_LOAD_HISTORY
WAREHOUSE_METERING_HISTORY
STAGE_STORAGE_USAGE_HISTORY
User Login
LOGIN_HISTORY , LOGIN_HISTORY_BY_USER

Note

You can also define table functions that return rows from a SQL query. For more information, see User-Defined Functions.

General Syntax

SELECT * FROM <input_table> T, LATERAL <table_function>(T.<col1>, ..., <argN>) F;

General Usage Notes

  • Table functions appear in the FROM clause of a SQL statement and cannot be used as a replacement for scalar functions. Table functions take scalar expressions as input. Table functions can also be applied to a set of rows using the LATERAL construct.
  • To enable using table expressions in the FROM clause, Snowflake supports ANSI/ISO standard syntax for table expressions in the FROM clause of SELECT queries and subqueries. It 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.