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 row can contain one or more columns.

Table functions are sometimes called “tabular functions”.

What are table functions?

Table functions are typically used when a function returns multiple rows for each individual input.

Each time that a table function is called, it can return a different number of rows. For example, a function record_high_temperatures_for_date(), which returns a list of record high temperatures for a specified date, might return 0 rows on April 10, 1 row on June 10, and 40 rows on August 20.

Simple examples of table functions

The following are appropriate as table functions:

  • 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.)

  • A function that accepts a user ID and returns the database roles assigned to that user. (A user might have multiple roles, including “sysadmin” and “useradmin”.)

Functions in which each output row depends upon multiple input rows

Table functions can be grouped into two categories based on the number of input rows that affect each output row:

  • 1-to-N

  • M-to-N

The functions described earlier are 1-to-N table functions: each output row depends upon only one input row. For example, a function record_high_temperatures_for_date() might produce multiple output rows (one for each city that hit a record on that date). Each output row for a specific input date depends only on that date; each output row is independent of the rows for every other date.

Snowflake also supports M-to-N table functions: each output row can depend upon multiple input rows. For example, if a function generates a moving average of stock prices, that function uses stock prices from multiple input rows (multiple dates) to generate each output row.

More generally, 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, in a 10-day moving average, M is 10. N is 1 because each group of 10 input rows produces one average price.

Built-in table functions vs user-defined table functions

Snowflake provides hundreds of built-in functions, many of which are table functions. Built-in table functions are listed in System-Defined Table Functions.

Users can also write their own functions, called user-defined functions or “UDFs”. Some UDFs are scalar; some are tabular. User-defined table functions are called “UDTFs”. For information about UDFs (including UDTFs), refer to User-defined functions overview.

Built-in table functions and user-defined table functions generally follow the same rules; for example, they are called the same way from SQL statements.

Using a table function

Using a table function in the FROM clause

A table contains a set of rows. Similarly, a table function returns a set of rows. Both tables and table functions are used in contexts that expect a set of rows. Specifically, table functions are used in the FROM clause of a SQL statement.

To help the SQL compiler recognize a table function as a source of rows, Snowflake requires that the table function call be wrapped by the TABLE() keyword.

For example, the following statement calls a table function named record_high_temperatures_for_date(), 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;
Copy

For more information about the syntax of TABLE(), refer to Table literals.

Table functions, like functions in general, can accept zero, one, or multiple input arguments in each invocation. Each argument must be a scalar expression.

For more details about the syntax of table function calls, refer to Syntax (in this topic).

Using a table as input to a table function

The argument to a table function can be a literal or an expression, such as a column of a table. For example, the SELECT statement below passes values from a table as arguments to a table function:

CREATE OR REPLACE table dates_of_interest (event_date DATE);
INSERT INTO dates_of_interest (event_date) VALUES
    ('2021-06-21'::DATE),
    ('2022-06-21'::DATE);

CREATE OR REPLACE FUNCTION record_high_temperatures_for_date(d DATE)
    RETURNS TABLE (event_date DATE, city VARCHAR, temperature NUMBER)
    as
    $$
    SELECT d, 'New York', 65.0
    UNION ALL
    SELECT d, 'Los Angeles', 69.0
    $$;
Copy
SELECT
        doi.event_date as "Date", 
        record_temperatures.city,
        record_temperatures.temperature
    FROM dates_of_interest AS doi,
         TABLE(record_high_temperatures_for_date(doi.event_date)) AS record_temperatures
      ORDER BY doi.event_date, city;
+------------+-------------+-------------+
| Date       | CITY        | TEMPERATURE |
|------------+-------------+-------------|
| 2021-06-21 | Los Angeles |          69 |
| 2021-06-21 | New York    |          65 |
| 2022-06-21 | Los Angeles |          69 |
| 2022-06-21 | New York    |          65 |
+------------+-------------+-------------+
Copy

The arguments to a table function can come from other table-like sources, including views and other table functions.

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, refer to Load Data into Snowflake.

VALIDATE

Data Generation

GENERATOR

Data Conversion

SPLIT_TO_TABLE

STRTOK_SPLIT_TO_TABLE

Differential Privacy

CUMULATIVE_PRIVACY_LOSSES

Object Modeling

GET_OBJECT_REFERENCES

Parameterized Queries

TO_QUERY

Semi-structured Queries

FLATTEN

For more information, refer to 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).

Query Profile

GET_QUERY_OPERATOR_STATS

Historical & Usage Information

Includes:

User Login

LOGIN_HISTORY , LOGIN_HISTORY_BY_USER

Queries

QUERY_HISTORY , QUERY_HISTORY_BY_*

QUERY_ACCELERATION_HISTORY

For more information, refer to Using the Query Acceleration Service.

Warehouse & Storage Usage

DATABASE_STORAGE_USAGE_HISTORY

WAREHOUSE_LOAD_HISTORY

WAREHOUSE_METERING_HISTORY

STAGE_STORAGE_USAGE_HISTORY

Column-level & Row-level Security

POLICY_REFERENCES

Object Tagging

TAG_REFERENCES

Information Schema table function.

TAG_REFERENCES_ALL_COLUMNS

Information Schema table function.

TAG_REFERENCES_WITH_LINEAGE

Account Usage table function.

Account Replication

REPLICATION_GROUP_REFRESH_HISTORY

For more information, refer to Introduction to replication and failover across multiple accounts

REPLICATION_GROUP_REFRESH_PROGRESS, REPLICATION_GROUP_REFRESH_PROGRESS_BY_JOB

REPLICATION_GROUP_USAGE_HISTORY

Alerts

ALERT_HISTORY

For more information, refer to Setting up alerts based on data in Snowflake.

SERVERLESS_ALERT_HISTORY

Database Replication

DATABASE_REFRESH_HISTORY

For more information, refer to Replicating databases across multiple accounts.

DATABASE_REFRESH_PROGRESS , DATABASE_REFRESH_PROGRESS_BY_JOB

DATABASE_REPLICATION_USAGE_HISTORY

Data Loading & Transfer

COPY_HISTORY

DATA_TRANSFER_HISTORY

PIPE_USAGE_HISTORY

STAGE_DIRECTORY_FILE_REGISTRATION_HISTORY

VALIDATE_PIPE_LOAD

Data Clustering (within Tables)

AUTOMATIC_CLUSTERING_HISTORY

For more information, refer to Automatic Clustering.

Dynamic Tables

DYNAMIC_TABLES

For more information, refer to Working with dynamic tables.

DYNAMIC_TABLE_GRAPH_HISTORY

DYNAMIC_TABLE_REFRESH_HISTORY

External Functions

EXTERNAL_FUNCTIONS_HISTORY

For more information, refer to Writing external functions.

External Tables

AUTO_REFRESH_REGISTRATION_HISTORY

For more information, refer to Working with external tables.

EXTERNAL_TABLE_FILES

EXTERNAL_TABLE_FILE_REGISTRATION_HISTORY

Materialized Views Maintenance

MATERIALIZED_VIEW_REFRESH_HISTORY

For more information, refer to Working with Materialized Views.

Notifications

NOTIFICATION_HISTORY

For more information, refer to Using SYSTEM$SEND_EMAIL to send email notifications.

SCIM Maintenance

REST_EVENT_HISTORY

For more information, refer to Auditing SCIM API requests

Search Optimization Maintenance

SEARCH_OPTIMIZATION_HISTORY

For more information, refer to Search Optimization Service.

Streams

SYSTEM$STREAM_BACKLOG

For more information, refer to Introduction to Streams.

Tasks

COMPLETE_TASK_GRAPHS

For more information, refer to Introduction to tasks.

CURRENT_TASK_GRAPHS

SERVERLESS_TASK_HISTORY

TASK_DEPENDENTS

TASK_HISTORY

Network rules

NETWORK_RULE_REFERENCES

Information Schema table function. For details, see Network rules.

Data Quality Monitoring

DATA_METRIC_FUNCTION_REFERENCES

For more information, see Introduction to Data Quality and data metric functions.

DATA_QUALITY_MONITORING_RESULTS

SYSTEM$DATA_METRIC_SCAN

Syntax

SELECT ...
  FROM [ <input_table> [ [AS] <alias_1> ] ,
         [ LATERAL ]
       ]
       TABLE( <table_function>( [ <arg_1> [, ... ] ] ) ) [ [ AS ] <alias_2> ];
Copy

For function-specific syntax, refer to 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.