VALIDATE | Snowflake Documentation (original) (raw)

Categories:

Table functions

Validates the files loaded in a past execution of the COPY INTO

Syntax¶

VALIDATE( [.] , JOB_ID => { '' | '_last' } )

Arguments¶

[_namespace_.]_tablename_

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 _databasename_._schemaname_ or _schemaname_. It is optional if a database and schema are currently in use within the user session; otherwise, it is required.

JOB_ID => _queryid_ | _last

The ID for the COPY INTO

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

Usage notes¶

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

  • SELECT statements are used to transform data during a COPY INTO operation.
  • The current user does not have access to _tablename_.
  • The current user is not the user who executed _queryid_ and does not have access control privileges on this user.
  • The copy history metadata has expired. For more information, refer to Load metadata.

• If new files have been added to the stage used by _queryid_ since the load was executed, the new files added are ignored during the validation.
• If files have been removed from the stage used by _queryid_ since the load was executed, the files removed are reported as missing.

Examples¶

Return errors for the last executed COPY command:

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

Return errors by specifying a query ID obtained from the Query History page in Snowsight or the History page in the Classic Console:

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'));