Categories:
Table Functions

VALIDATE

Validates the files loaded in a past execution of the COPY INTO <table> command and returns all the errors encountered during the load, rather than just the first error.

Syntax

VALIDATE( [<namespace>.]<table_name> , JOB_ID => { '<query_id>' | _last } )

Arguments

[namespace.]table_name

Specifies the fully-qualified name of the table that was the target of the load.

Namespace is the database and/or schema in which the table resides, in the form of database_name.schema_name or schema_name. It is optional if a database and schema are currently in use within the user session; otherwise, it is required.

JOB_ID => query_id | _last

The ID for the COPY INTO <table> command to be validated:

  • The ID can be obtained from the Query ID column in the History History tab page in the Snowflake web interface. The specified query ID must have been for the specified target table.
  • If _last is specified instead of query_id, the function validates the last load executed during the current session, regardless of the specified target table.

Usage Notes

  • This function does not support COPY INTO <table> statements that transform data during a load.

  • The validation returns no results for COPY statements that specify ON_ERROR = ABORT_STATEMENT (default value).

  • Validation fails if:

    • The current user does not have access to table_name.
    • The current user is not the user who executed query_id and does not have access control privileges on this user.
  • If new files have been added to the stage used by query_id since the load was executed, the new files added are ignored during the validation.

  • If files have been removed from the stage used by query_id since the load was executed, the files removed are reported as missing.

Examples

COPY INTO t1 FROM @%t1
  PATTERN='.*tw01.*'
  FILE_FORMAT=(NULL_IF=('') FIELD_OPTIONALLY_ENCLOSED_BY='"')
  ON_ERROR = SKIP_FILE;

COPY INTO t1;
FAILURE: Error parsing JSON: unterminated string
  File 'tables/530049/bad.json.gz', line 120
  Row 0 starts at line 1, column V
  If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.

Return errors for the last executed COPY command:

SELECT * FROM TABLE(VALIDATE(t1, JOB_ID => '_last'));

-----------------------------------------------------------------+---------------------------+------+-----------+-------------+----------+--------+-----------+-------------+------------+----------------+
                              ERROR                              |           FILE            | LINE | CHARACTER | BYTE_OFFSET | CATEGORY |  CODE  | SQL_STATE | COLUMN_NAME | ROW_NUMBER | ROW_START_LINE |
-----------------------------------------------------------------+---------------------------+------+-----------+-------------+----------+--------+-----------+-------------+------------+----------------+
 Error parsing JSON: unterminated string                         | tables/530049/bad.json.gz | 120  | [NULL]    | 7504        | parsing  | 100069 | 22P02     | V           | 0          | 1              |
 Error parsing JSON: misplaced colon                             | tables/530049/bad.json.gz | 214  | 18        | 84465       | parsing  | 100069 | 22P02     | V           | 2          | 214            |
 Error parsing JSON: unknown keyword "tru"                       | tables/530049/bad.json.gz | 1467 | [NULL]    | 85337       | parsing  | 100069 | 22P02     | V           | 3          | 1445           |
 Error parsing JSON: unknown keyword "stat"                      | tables/530049/bad.json.gz | 1469 | 13        | 85353       | parsing  | 100069 | 22P02     | V           | 5          | 1469           |
 Error parsing JSON: unknown keyword "ok"                        | tables/530049/bad.json.gz | 1469 | 20        | 85360       | parsing  | 100069 | 22P02     | V           | 7          | 1469           |
 Error parsing JSON: invalid character outside of a string: '\\' | tables/530049/bad.json.gz | 1469 | 21        | 163414      | parsing  | 100069 | 22P02     | V           | 8          | 1469           |
 Error parsing JSON: misplaced }                                 | tables/530049/bad.json.gz | 1470 | 3         | 163418      | parsing  | 100069 | 22P02     | V           | 9          | 1469           |
-----------------------------------------------------------------+---------------------------+------+-----------+-------------+----------+--------+-----------+-------------+------------+----------------+

Return errors by specifying a query ID obtained from the web interface:

SELECT * FROM TABLE(VALIDATE(t1, JOB_ID=>'5415fa1e-59c9-4dda-b652-533de02fdcf1'));

Same query as above, but save the results to a table for future reference:

CREATE OR REPLACE TABLE save_copy_errors AS SELECT * FROM TABLE(VALIDATE(t1, JOB_ID=>'5415fa1e-59c9-4dda-b652-533de02fdcf1'));