I have found 4 ways to see the load history in Snowflake:
SELECT * from "SNOWFLAKE"."ACCOUNT_USAGE"."COPY_HISTORY"
SELECT * FROM "SNOWFLAKE"."INFORMATION_SCHEMA"."LOAD_HISTORY"
SELECT * from "SNOWFLAKE"."ACCOUNT_USAGE"."LOAD_HISTORY"
SELECT * FROM "DATABASE_NAME"."INFORMATION_SCHEMA"."LOAD_HISTORY"
What are the differences between these and when should each be used (or not used) ?
The difference between account_usage and information_schema is well-documented here:
The key being the amount of history, the latency, and the access control.
The difference between COPY_HISTORY and LOAD_HISTORY is that COPY_HISTORY include SNOWPIPE ingested data, while the LOAD_HISTORY only includes data loaded via a COPY INTO statement.
The last one is the LOAD_HISTORY of the SNOWFLAKE.INFORMATION_SCHEMA. This will most likely be empty as it would reflect the load history of data into that database/schema and since that is a shared database to customers from Snowflake, you won't see anything in there.
> SELECT * from "SNOWFLAKE"."ACCOUNT_USAGE"."COPY_HISTORY"
This is an Account Usage view that is used to query data loading history for the last 365 days, for both batch loading (COPY INTO <table>) and continuous loading (with Snowpipe).
https://docs.snowflake.com/en/sql-reference/account-usage/copy_history.html#copy-history-view
> SELECT * from "SNOWFLAKE"."ACCOUNT_USAGE"."LOAD_HISTORY"
This is also an Account Usage view, but excludes files loaded with Snowpipe. This view may also be subject to latency of up to 90 mins.
> SELECT * FROM "DATABASE_NAME"."INFORMATION_SCHEMA"."LOAD_HISTORY"
This is an Information Schema view that is used to query the history of data loaded into tables using the COPY INTO <table> command within the last 14 days. The view displays one row for each file loaded, does not include data loaded with Snowpipe.
https://docs.snowflake.com/en/sql-reference/info-schema/load_history.html#load-history-view
> SELECT * FROM "SNOWFLAKE"."INFORMATION_SCHEMA"."LOAD_HISTORY"
This is just a specific example of the previous one, but since the Snowflake database is a system defined, shared, read-only database, querying this view probably won't return anything (useful).