- Categories:
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 REFRESH_VERSION within 1 day 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 } ]
)
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
to10000
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:
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:
|
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. Equal to the previous REFRESH_VERSION + 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:
|
STATISTICS |
OBJECT |
Contains the following properties:
|
REFRESH_ACTION |
TEXT |
One of:
|
REFRESH_TRIGGER |
TEXT |
One of:
|
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 cancelled:
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;