LOAD_HISTORY View

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.

Note

The view only includes COPY INTO commands that completed successfully, with or without errors. The view omits failed and canceled commands.

Columns

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

Usage Notes

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

  • This view returns an upper limit of 10,000 rows. To avoid this limitation, use the COPY_HISTORY function or COPY_HISTORY View.

  • When including a WHERE clause that references the LAST_LOAD_TIME column, 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'
    

Examples

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 MYDB database:

USE DATABASE mydb;

SELECT * FROM information_schema.load_history
  ORDER BY last_load_time DESC
  LIMIT 10;