Schema:: ACCOUNT_USAGE

COPY_HISTORY View¶

This Account Usage view can be used to query Snowflake data loading history for the last 365 days (1 year). The view displays load activity for both COPY INTO <table> statements and continuous data loading using Snowpipe. The view avoids the 10,000 row limitation of the LOAD_HISTORY View.

You can also view data loading details in Snowsight. See Monitor data loading activity by using Copy History.

Columns¶

Column Name	Data Type	Description
FILE_NAME	TEXT	Name of the source file and relative path to the file.
STAGE_LOCATION	TEXT	Name of the stage where the source file is located.
LAST_LOAD_TIME	TIMESTAMP_LTZ	Date and time of when the file finished loading.
ROW_COUNT	NUMBER	Number of rows loaded from the source file.
ROW_PARSED	NUMBER	Number of rows parsed from the source file; `NULL` if STATUS is `Load in progress`.
FILE_SIZE	NUMBER	Size of the source file loaded.
FIRST_ERROR_MESSAGE	TEXT	First error of the source file.
FIRST_ERROR_LINE_NUMBER	NUMBER	Line number of the first error.
FIRST_ERROR_CHARACTER_POS	NUMBER	Position of the first error character.
FIRST_ERROR_COLUMN_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.
STATUS	TEXT	Status: `Loaded`, `Load failed`, `Partially loaded`, or `Load skipped`.
TABLE_ID	NUMBER	Internal/system-generated identifier for the target table.
TABLE_NAME	TEXT	Name of the target table.TABLE_NAME
TABLE_SCHEMA_ID	NUMBER	Internal, Snowflake-generated identifier of the schema for the table.
TABLE_SCHEMA_NAME	TEXT	Name of the schema in which the target table resides.
TABLE_CATALOG_ID	NUMBER	Internal/system-generated identifier for the database of the table.
TABLE_CATALOG_NAME	TEXT	Name of the database in which the target table resides.
PIPE_CATALOG_NAME	TEXT	Name of the database in which the pipe resides.
PIPE_SCHEMA_NAME	TEXT	Name of the schema in which the pipe resides.
PIPE_NAME	TEXT	Name of the pipe defining the load parameters; `NULL` for COPY statement loads.
PIPE_RECEIVED_TIME	TIMESTAMP_LTZ	Date and time when the INSERT request for the file loaded through the pipe was received; `NULL` for COPY statement loads.
FIRST_COMMIT_TIME	TIMESTAMP_LTZ	Date and time when the first chunk of the file is committed. Snowpipe may load a file in multiple chunks that are separately committed.

Usage Notes¶

In most cases, latency for the view may be up to 120 minutes (2 hours). The latency for a given table’s copy history may be up to 2 days if both of the following conditions are true:
- Fewer than 32 DML statements have been added to the given table since it was last updated in COPY_HISTORY.
- Fewer than 100 rows have been added to the given table since it was last updated in COPY_HISTORY.

The view only includes COPY INTO commands that executed to completion, with or without errors.
Dropping or recreating a table object removes the historical data for bulk data loads (COPY INTO <table> statements) into the table.
Dropping or recreating a pipe object removes the historical data for Snowpipe data loads using the pipe.
The view only displays objects for which the current role for the session has been granted access privileges.

After the replication of copy history, the COPY_HISTORY Account Usage view shows the history only after the latest truncate operation on the target table. This is different from the view without replication, which shows a complete copy history.

Examples¶

Retrieve records for the 10 most recent COPY INTO commands executed:

select file_name, error_count, status, last_load_time from snowflake.account_usage.copy_history
  order by last_load_time desc
  limit 10;

Copy