How can the Snowpipe REST API be used to keep a log of data load history?

The Snowpipe REST API provides two endpoints for retrieving the data load history: insertReport and

loadHistoryScan. The insertReport endpoint returns the status of the files that were submitted to the

insertFiles endpoint, while the loadHistoryScan endpoint returns the history of the files that were

actually loaded into the table by Snowpipe. To keep a log of data load history, it is recommended to

use the loadHistoryScan endpoint, which provides more accurate and complete information about

the data ingestion process. The loadHistoryScan endpoint accepts a start time and an end time as

parameters, and returns the files that were loaded within that time range. The maximum time range

that can be specified is 15 minutes, and the maximum number of files that can be returned is 10,000.

Therefore, to keep a log of data load history, the best option is to call the loadHistoryScan endpoint

every 10 minutes for a 15-minute time range, and store the results in a log file or a table. This way,

the log will capture all the files that were loaded by Snowpipe, and avoid any gaps or overlaps in the

time range. The other options are incorrect because:

Calling insertReport every 20 minutes, fetching the last 10,000 entries, will not provide a complete

log of data load history, as some files may be missed or duplicated due to the asynchronous nature of

Snowpipe. Moreover, insertReport only returns the status of the files that were submitted, not the

files that were loaded.

Calling loadHistoryScan every minute for the maximum time range will result in too many API calls

and unnecessary overhead, as the same files will be returned multiple times. Moreover, the

maximum time range is 15 minutes, not 1 minute.

Calling insertReport every 8 minutes for a 10-minute time range will suffer from the same problems

as option A, and also create gaps or overlaps in the time range.

Reference:

Snowpipe REST API

Option 1: Loading Data Using the Snowpipe REST API

PIPE_USAGE_HISTORY