ADA-C01 Question 559
Single answerQuery historical dataA Snowflake administrator needs to investigate a performance incident that occurred 10 days ago, when several BI dashboards timed out. The team wants to identify which SQL statements were executed during that period, how long they ran, and which warehouse was used. However, the administrator discovers that the Information Schema QUERY_HISTORY view no longer returns records from that date. Which approach should the administrator use to retrieve the needed query details most effectively?
- A
Query the SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY view, filtering by the incident time window and warehouse name if needed
- B
Use SHOW QUERIES with a date filter, because SHOW commands retain full historical query data for the account
- C
Query TABLE(information_schema.query_history()) again, but increase the warehouse size to access older metadata
- D
Restore the affected database to a point in time using Time Travel, then inspect the historical query metadata from the restored copy
Show answer and explanation
Correct answer: A
Explanation
When an administrator needs to investigate older query activity, Snowflake provides multiple metadata sources with different retention windows and use cases. Information Schema query history functions are suited to recent operational analysis, but for longer-range account-level historical reporting, the correct source is SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY. This is a common exam distinction: use Information Schema for more immediate metadata access and ACCOUNT_USAGE for broader historical analysis, while remembering that ACCOUNT_USAGE data can have latency before records appear. Best practice is to filter by START_TIME/END_TIME, WAREHOUSE_NAME, USER_NAME, or QUERY_ID as appropriate. Time Travel applies to historical data in Snowflake objects, not account usage metadata, and warehouse sizing does not change metadata retention. Relevant Snowflake documentation includes the ACCOUNT_USAGE QUERY_HISTORY view and Information Schema query history table functions.
- A. Correct.
Correct. For historical query analysis beyond the shorter retention available through Information Schema table functions/views, the administrator should use the SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY view in the shared SNOWFLAKE database. This view is designed for account-level historical monitoring and includes fields such as QUERY_TEXT, START_TIME, TOTAL_ELAPSED_TIME, WAREHOUSE_NAME, USER_NAME, and more. It is the appropriate source for investigating queries that ran 10 days ago, subject to the documented latency for ACCOUNT_USAGE views.
- B. Incorrect.
Incorrect. SHOW commands are useful for inspecting current or recent object metadata, but they are not the correct mechanism for retrieving full historical query execution records over that time range. SHOW QUERIES is not the standard administrator approach for deep historical query analysis, and it does not replace the ACCOUNT_USAGE.QUERY_HISTORY view for account-wide reporting.
- C. Incorrect.
Incorrect. Increasing warehouse size has no effect on metadata retention or the availability window of query history in Information Schema. Compute size influences query execution performance, not how far back Snowflake retains metadata in Information Schema query history functions. This option reflects a common misconception that larger warehouses can expose additional historical metadata.
- D. Incorrect.
Incorrect. Time Travel is used to access or restore historical table, schema, and database data states, not to recover query execution metadata. Restoring a database does not restore or expose old query history records. Query history is accessed through Snowflake metadata views and functions, not through Time Travel on user objects.