Categories:

Information Schema , Table functions

DYNAMIC_TABLE_REFRESH_HISTORY¶

This table function returns information about each refresh (completed and running) of dynamic tables.

This table function returns all refreshes that are in progress as well as all refreshes that have a DATA_TIMESTAMP within 7 days of the current time.

Syntax¶

DYNAMIC_TABLE_REFRESH_HISTORY(
  [ DATA_TIMESTAMP_START => <constant_expr> ]
  [ , DATA_TIMESTAMP_END => <constant_expr> ]
  [ , RESULT_LIMIT => <integer> ]
  [ , NAME => '<string>' ]
  [ , NAME_PREFIX => '<string>' ]
  [ , ERROR_ONLY => { TRUE | FALSE } ]
)
Copy

Arguments¶

All the arguments are optional. If no arguments are provided, 100 refreshes from all dynamic tables in the account will be returned.

DATA_TIMESTAMP_START => constant_expr , . DATA_TIMESTAMP_END => constant_expr

Time range (in TIMESTAMP_LTZ format) during which the refreshes occurred.

  • If neither a start version nor an end version is specified, the default range will be the past day.

  • If an end version is not specified, CURRENT_TIMESTAMP is used as the end of the range.

  • If a start version is not specified, the range starts 1 day prior to the start of DATE_TIMESTAMP_END.

RESULT_LIMIT => integer

A number specifying the maximum number of rows returned by the function.

If the number of matching rows is greater than this limit, the refreshes that finished most recently (and those that are still running) are returned, up to the specified limit.

Range: 1 to 10000

Default: 100.

NAME => string

The name of a dynamic table.

Names must be single-quoted and are case insensitive.

You can specify the unqualified name (dynamic_table_name), the partially qualified name (schema_name.dynamic_table_name), or the fully qualified name (database_name.schema_name.dynamic_table_name).

For more information on object name resolution, refer to Object name resolution.

The function returns the refreshes for this table.

NAME_PREFIX => string

A prefix for dynamic tables.

Name prefixes must be single-quoted and are case insensitive.

The function returns refreshes for tables with names that start with this prefix.

You can use this argument to return the refreshes for dynamic tables in a specific database or schema.

ERROR_ONLY => TRUE | FALSE

When set to TRUE, this function returns only refreshes that failed or were cancelled.

Output¶

The function returns the following columns.

To view these columns, you must use a role with the MONITOR privilege. For more information, see Privileges to view a dynamic table’s metadata.

Column Name

Data Type

Description

NAME

TEXT

Name of the dynamic table.

SCHEMA_NAME

TEXT

Name of the schema that contains the dynamic table.

DATABASE_NAME

TEXT

Name of the database that contains the dynamic table.

STATE

TEXT

Status of the refresh for the dynamic table. The status can be one of the following:

  • SCHEDULED: refresh scheduled, but not yet executed.

  • EXECUTING: refresh in progress.

  • SUCCEEDED: refresh completed successfully.

  • FAILED: refresh failed during execution.

  • CANCELLED: refresh was canceled before execution.

  • UPSTREAM_FAILED: refresh not performed due to an upstream failed refresh.

STATE_CODE

TEXT

Code representing the current state of the refresh.

STATE_MESSAGE

TEXT

Description of the current state of the refresh.

QUERY_ID

TEXT

ID of the SQL statement that produced the results for the dynamic table.

DATA_TIMESTAMP

TIMESTAMP_LTZ

Transactional timestamp when the refresh was evaluated. (This might be slightly before the actual time of the refresh.) All data, in base objects, that arrived before this timestamp is currently included in the dynamic table.

REFRESH_START_TIME

TIMESTAMP_LTZ

Time when the refresh job started.

REFRESH_END_TIME

TIMESTAMP_LTZ

Time when the refresh completed.

COMPLETION_TARGET

TIMESTAMP_LTZ

Time by which this refresh should complete to keep lag under the TARGET_LAG parameter for the dynamic table. This is equal to the DATA_TIMESTAMP of the last refresh + TARGET_LAG.

QUALIFIED_NAME

TEXT

Fully qualified name of the dynamic table as it appears in the graph of dynamic tables. You can use this to join the output with the output of the DYNAMIC_TABLE_GRAPH_HISTORY function.

LAST_COMPLETED_DEPENDENCY

OBJECT

Contains the following properties:

  • qualified_name: The qualified name of the latest dependency to become available.

  • data_timestamp: The refresh version of that dependency.

STATISTICS

OBJECT

Contains the following properties:

  • numInsertedRows: The number of inserted rows.

  • numDeletedRows: The number of rows that were deleted.

  • numCopiedRows: The number of rows that were copied unchanged.

  • numAddedPartitions: The number of added partitions.

  • numRemovedPartitions : The number of removed partitions.

    For example: If an UPDATE statement updates 1 row in a partition with 10 rows. Then the metrics above show 1 row inserted, 1 deleted, and 9 copied. Additionally, 1 partition is removed and 1 partition added.

REFRESH_ACTION

TEXT

One of:

  • NO_DATA - no new data in base tables. Doesn’t apply to the initial refresh of newly created dynamic tables regardless of whether or not the base tables have data.

  • REINITIALIZE - base table changed or source table of a cloned dynamic table was refreshed during clone.

  • FULL - Full refresh, because dynamic table contains query elements that are not incrementalizable (see SHOW DYNAMIC TABLE refresh_mode_reason) or because full refresh was cheaper than incremental refresh.

  • INCREMENTAL - normal incremental refresh.

REFRESH_TRIGGER

TEXT

One of:

  • SCHEDULED - normal background refresh to meet target lag or downstream target lag.

  • MANUAL - user/task used ALTER DYNAMIC TABLE <name> REFRESH

  • CREATION - refresh performed during the creation DDL statement, triggered by the creation of the dynamic table or any consumer dynamic tables.

TARGET_LAG_SEC

NUMBER

Describes the target lag value for the dynamic tables at the time the refresh occurred.

GRAPH_HISTORY_VALID_FROM

TIMESTAMP_NTZ

Encodes the VALID_FROM timestamp of the DYNAMIC_TABLE_GRAPH_HISTORY table function when the refresh occurred to clarify which version of a dynamic table a specific refresh corresponds to. This value can also be NULL if the corresponding dynamic table hasn’t been created.

Usage notes¶

  • When calling an Information Schema table function, the session must have an INFORMATION_SCHEMA schema in use or the function name must be fully-qualified. For more details, see Snowflake Information Schema.

Examples¶

Retrieve the refreshes that failed or were canceled:

SELECT
  name,
  state,
  state_code,
  state_message,
  query_id,
  data_timestamp,
  refresh_start_time,
  refresh_end_time
FROM
  TABLE (
    INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY (
      NAME_PREFIX => 'MYDB.MYSCHEMA.', ERROR_ONLY => TRUE
    )
  )
ORDER BY
  name,
  data_timestamp;
Copy
Language: English