Categories:
System Functions (System Information)

SYSTEM$PIPE_STATUS

Retrieves a JSON representation of the current status of a pipe.

For more information, see Loading Continuously Using Snowpipe.

Syntax

SYSTEM$PIPE_STATUS( '<pipe_name>' )

Arguments

pipe_name
Pipe for which you want to retrieve the current status.

Usage Notes

  • pipe_name is a string so it must be enclosed in single quotes:
    • Note that the entire name must be enclosed in single quotes, including the database and schema (if the name is fully-qualified), i.e. '<db>.<schema>.<pipe_name>'.
    • If the pipe name is case-sensitive or includes any special characters or spaces, double quotes are required to process the case/characters. The double quotes must be enclosed within the single quotes, i.e. '"<pipe_name>"'.

Output

The function returns a JSON object containing the following name/value pairs (if applicable to the current pipe status):

{"executionState":<value>,"oldestFileTimestamp":<value>,"pendingFileCount":<value>,"error":<value>,"fault":<value>}

Where:

executionState

Current execution state of the pipe; could be any one of the following:

  • RUNNING (i.e. everything is normal; Snowflake may or may not be actively processing files for this pipe)
  • STOPPED_FEATURE_DISABLED
  • STOPPED_STAGE_DROPPED
  • STOPPED_FILE_FORMAT_DROPPED
  • STOPPED_MISSING_PIPE
  • STOPPED_MISSING_TABLE
  • STALLED_COMPILATION_ERROR
  • STALLED_INITIALIZATION_ERROR
  • STALLED_EXECUTION_ERROR
  • STALLED_INTERNAL_ERROR
  • PAUSED
  • PAUSED_BY_SNOWFLAKE_ADMIN
  • PAUSED_BY_ACCOUNT_ADMIN
oldestFileTimestamp
Earliest timestamp among data files currently queued (if applicable), where the timestamp is set when the file is added to the queue.
pendingFileCount
Number of files in line to be ingested. This number includes files that are queued and those that are currently being processed. If no files are in line to be ingested, a value of 0 is returned.
error
Error message produced when the pipe was last compiled for execution (if applicable); often caused by problems accessing the necessary objects (i.e. table, stage, file format) due to permission problems or dropped objects.
fault
Most recent internal Snowflake process error (if applicable). Used primarily by Snowflake for debugging purposes.

Examples

Retrieve the status for a pipe with a case-insensitive name:

SELECT SYSTEM$PIPE_STATUS('mydb.myschema.mypipe');

+---------------------------------------------------+
| SYSTEM$PIPE_STATUS('MYDB.MYSCHEMA.MYPIPE')        |
|---------------------------------------------------|
| {"executionState":"RUNNING","pendingFileCount":0} |
+---------------------------------------------------+

Retrieve the status for a pipe with a case-sensitive name:

SELECT SYSTEM$PIPE_STATUS('mydb.myschema."myPipe"');

+---------------------------------------------------+
| SYSTEM$PIPE_STATUS('MYDB.MYSCHEMA."MYPIPE"')      |
|---------------------------------------------------|
| {"executionState":"RUNNING","pendingFileCount":0} |
+---------------------------------------------------+