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> ] )

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

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

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

  • 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())));