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

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.

VALIDATE

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, Account Usage):

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 & Row-level Security

POLICY_REFERENCES

Tags

TAG_REFERENCES TAG_REFERENCES_WITH_LINEAGE

Information Schema table function . Account Usage table function

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

AUTO_REFRESH_REGISTRATION_HISTORY

For more information, see Working with External Tables

EXTERNAL_TABLE_FILES

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

SERVERLESS_TASK_HISTORY

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

TASK_DEPENDENTS

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.