Categories:

System functions (System Information)

SYSTEM$AUTO_REFRESH_STATUS

Returns the automated refresh status for an externally managed Iceberg table.

Syntax

SYSTEM$AUTO_REFRESH_STATUS('<table_name>')
Copy

Arguments

'table_name'

The name of the Iceberg table for which you want to retrieve the current automated refresh status.

The entire name must be enclosed in single quotes, including the database and schema if using the fully-qualified name. If the table name is case-sensitive or includes any special characters or spaces, you must use double quotes. Enclose the double quotes within the single quotes, for example, '"Table_Name"'.

Returns

The function returns a JSON object containing the following name/value pairs:

{
  “executionState”:”<value>”,
  ”invalidExecutionStateReason”:”<value>”
  ”pendingSnapshotCount”:<value>,
  ”oldestSnapshotTime”:<value>,
  ”currentSnapshotId”:”<value>”,
  ”lastSnapshotTime”:”<value>”,
  ”lastUpdatedTime”:<value>
}
Copy

Where:

“executionState”

Current execution state of the pipe that Snowflake uses to automate metadata refreshes for the table.

Values:

  • RUNNING: Automated refresh is running as expected. This status doesn’t indicate whether Snowflake is actively processing event messages for the pipe.

  • STALLED: Automated refresh encountered an error and is attempting to recover.

  • STOPPED: Automated refresh encountered an unrecoverable error and is stopped unless you take further action. For more information, see Error recovery.

”invalidExecutionStateReason”

Error message associated with a STALLED or STOPPED execution state.

”pendingSnapshotCount”

Number of snapshots queued for automated refresh.

”oldestSnapshotTime”

Earliest timestamp among queued snapshots. Snowflake sets the timestamp for a snapshot when the snapshot is added to the queue.

”currentSnapshotId”

ID of the current snapshot that Snowflake is tracking. This represents the snapshot that the current table data corresponds to.

”lastSnapshotTime”

Creation timestamp for the current snapshot according to Iceberg metadata. This timestamp corresponds to when the current snapshot was generated in the external catalog.

”lastUpdatedTime”

Timestamp that indicates when Snowflake successfully processed the current snapshot. The difference between this value and the lastSnapshotTime indicates the latency between when snapshots are created in the external catalog and when Snowflake successfully refreshes the table metadata.

To decrease the latency, adjust the REFRESH_INTERVAL_SECONDS parameter for the catalog integration associated with the table.

Usage notes

Calling this function requires a role that has the OWNERSHIP privilege on the Iceberg table.

Examples

Retrieve the automated refresh status for the table my_iceberg_table in the schema db1.schema1:

SELECT SYSTEM$AUTO_REFRESH_STATUS('db1.schema1.my_iceberg_table');
Copy
Language: English