ADA-C01 Question 400
Single answerViews available from the INFORMATION_SCHEMAA Snowflake administrator needs to identify tables in the SALES database that have likely become stale because they have not been modified for more than 90 days. The administrator wants to use only metadata exposed through the SALES database's INFORMATION_SCHEMA and avoid querying Account Usage views. Which INFORMATION_SCHEMA view should the administrator query to get the most appropriate table-level timestamp for this task?
- A
SALES.INFORMATION_SCHEMA.TABLES
- B
SALES.INFORMATION_SCHEMA.COLUMNS
- C
SALES.INFORMATION_SCHEMA.VIEWS
- D
SALES.INFORMATION_SCHEMA.QUERY_HISTORY
- E
SALES.INFORMATION_SCHEMA.LOAD_HISTORY
Show answer and explanation
Correct answer: A
Explanation
For database-scoped metadata available through INFORMATION_SCHEMA, the TABLES view is the correct choice when evaluating table objects. It exposes table metadata such as TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, CREATED, and LAST_ALTERED. For a practical stale-object review, an administrator would typically query SALES.INFORMATION_SCHEMA.TABLES and filter on LAST_ALTERED < DATEADD('day', -90, CURRENT_TIMESTAMP()) and possibly TABLE_TYPE = 'BASE TABLE'.
The key distinction is that INFORMATION_SCHEMA contains object metadata views like TABLES, COLUMNS, SCHEMATA, and VIEWS, while operational history such as query execution is generally exposed differently, often through table functions or ACCOUNT_USAGE views. LOAD_HISTORY is useful for troubleshooting ingestion activity but does not represent overall object modification history. This aligns with Snowflake documentation on INFORMATION_SCHEMA views and their intended use for metadata inspection at the database level.
- A. Correct.
Correct. The INFORMATION_SCHEMA.TABLES view provides table-level metadata, including LAST_ALTERED, which is the most appropriate timestamp available in INFORMATION_SCHEMA for identifying tables that have not changed recently. In practice, administrators commonly filter TABLE_TYPE and compare LAST_ALTERED against a date threshold to find stale objects.
- B. Incorrect.
Incorrect. INFORMATION_SCHEMA.COLUMNS contains column-level metadata such as column names, data types, and ordinal positions. Although useful for schema analysis, it does not provide the right table-level view for determining when a table itself was last modified.
- C. Incorrect.
Incorrect. INFORMATION_SCHEMA.VIEWS is limited to metadata about views, not tables. An administrator looking for stale base tables in the SALES database would not use this view unless the scope specifically included views.
- D. Incorrect.
Incorrect. QUERY_HISTORY is not an INFORMATION_SCHEMA view in the same sense as the standard object metadata views listed here. In Snowflake, query history is typically accessed through table functions in INFORMATION_SCHEMA or through ACCOUNT_USAGE, and it reports executed SQL statements rather than authoritative table metadata for object staleness.
- E. Incorrect.
Incorrect. LOAD_HISTORY is focused on data loading activity, such as COPY INTO operations, and is not a general-purpose source for determining whether a table object has been modified. A table could be altered or changed in ways that are not captured as load history.