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

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

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 Tables Maintenance

EXTERNAL_TABLE_FILES

For more information, see Working with External Tables

EXTERNAL_TABLE_FILE_REGISTRATION_HISTORY

Materialized Views Maintenance

MATERIALIZED_VIEW_REFRESH_HISTORY

For more information, see Working with Materialized Views

Tasks Maintenance

TASK_DEPENDENTS

For more information, see Executing SQL Statements on a Schedule Using Tasks

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.