This Information Schema view enables you to retrieve the history of data loaded into tables using the COPY INTO <table> command. The view displays one row for each file loaded.
The view only includes COPY INTO commands that completed successfully, with or without errors. The view omits failed and canceled commands.
|Column Name||Data Type||Description|
|SCHEMA_NAME||TEXT||Schema of target table|
|FILE_NAME||TEXT||Name of source file|
|TABLE_NAME||TEXT||Name of target table|
|LAST_LOAD_TIME||TIMESTAMP_LTZ||Timestamp of the load record|
|STATUS||TEXT||Status: loaded, load failed or partially loaded|
|ROW_COUNT||NUMBER||Number of rows loaded from the source file|
|ROW_PARSED||NUMBER||Number of rows parsed from the source file|
|FIRST_ERROR_MESSAGE||TEXT||First error of the source file|
|FIRST_ERROR_LINE_NUMBER||NUMBER||Line number of the first error|
|FIRST_ERROR_CHARACTER_POSITION||NUMBER||Position of the first error character|
|FIRST_ERROR_COL_NAME||TEXT||Column name of the first error|
|ERROR_COUNT||NUMBER||Number of error rows in the source file|
|ERROR_LIMIT||NUMBER||If the number of errors reaches this limit, then abort|
Snowflake retains historical data for COPY INTO commands executed within the previous 14 days only.
The historical data for COPY INTO commands is removed from the system when a table is dropped.
When including a WHERE clause that references the
LAST_LOAD_TIMEcolumn, you can specify any day of the week. For example, April 1, 2016 was a Friday; however, specifying Sunday instead does not affect the query results:
WHERE last_load_time > 'Sun, 01 Apr 2016 16:00:00 -0800'
Retrieve the history of data loaded into the
MYDB.PUBLIC.MYTABLE table since April 1, 2016, assuming that April 1 occurred within the previous 14 days:
USE DATABASE mydb; SELECT * FROM information_schema.load_history WHERE schema_name=current_schema() AND table_name='MYTABLE' AND last_load_time > 'Fri, 01 Apr 2016 16:00:00 -0800';
Retrieve records for the 10 most recent COPY INTO commands executed against the
USE DATABASE mydb; SELECT * FROM information_schema.load_history ORDER BY last_load_time DESC LIMIT 10;