Categories:

Information Schema , Table functions

VALIDATE_PIPE_LOAD¶

This table function can be used to validate data files processed by Snowpipe within a specified time range. The function returns details about any errors encountered during an attempted data load into Snowflake tables.

Note

This function returns pipe activity within the last 14 days.

Syntax¶

VALIDATE_PIPE_LOAD(
      PIPE_NAME => '<string>'
       , START_TIME => <constant_expr>
      [, END_TIME => <constant_expr> ] )
Copy

Arguments¶

PIPE_NAME => string

A string specifying a pipe. The function returns results for the specified pipe only.

START_TIME => constant_expr

Timestamp (in TIMESTAMP_LTZ format), within the last 14 days, marking the start of the time range for retrieving error events.

Optional:

END_TIME => constant_expr

Timestamp (in TIMESTAMP_LTZ format), within the last 14 days, marking the end of the time range for retrieving error events.

Usage notes¶

  • Returns results only for the pipe owner (i.e. the role with the OWNERSHIP privilege on the pipe) or a role with the following minimum permissions:

    Privilege

    Object

    Notes

    MONITOR

    Pipe

    Alternatively, the global MONITOR EXECUTION privilege is supported.

    USAGE

    Stage in the pipe definition

    External stages only

    READ

    Stage in the pipe definition

    Internal stages only

    SELECT

    Table in the pipe definition

    INSERT

    Table in the pipe definition

    SQL operations on schema objects also require the USAGE privilege on the database and schema that contain the object.

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

  • If Snowpipe encountered no errors while processing data files within the specified time range, the function returns no results.

  • If the COPY statement in the pipe description includes a query to further transform the data during the load (i.e. a COPY transformation), then the function currently returns a user error.

  • If the specified date range falls outside the last 15 days, an error is returned.

Output¶

The function returns the following columns:

Column Name

Data Type

Description

ERROR

TEXT

First error in the source file.

FILE

TEXT

Name of the source file where the error was encountered.

LINE

NUMBER

Number of the line in the source file where the error was encountered.

CHARACTER

NUMBER

Position of the character where the error was encountered.

BYTE_OFFSET

NUMBER

Byte offset to the character where the error was encountered.

CATEGORY

TEXT

Category of the operation when the error was produced.

CODE

NUMBER

ID for the error message displayed in the ERROR column.

SQL_STATE

NUMBER

SQL state code.

COLUMN_NAME

TEXT

Name and order of the column that contained the error.

ROW_NUMBER

NUMBER

Number of the row in the source file where the error was encountered.

ROW_START_LINE

NUMBER

Number of the first line of the row where the error was encountered.

REJECTED_RECORD

TEXT

Record that contained the error.

Examples¶

Validate any loads for the mypipe pipe within the previous hour:

select * from table(validate_pipe_load(
  pipe_name=>'MY_DB.PUBLIC.MYPIPE',
  start_time=>dateadd(hour, -1, current_timestamp())));
Copy