SnowPro Associate: Platform Question 151
Single answer○ Use the INFORMATION_SCHEMAA data engineer needs to identify all BASE TABLES in the SALES database that have not been modified in the last 30 days so they can review candidates for archival. They want to use metadata that is available without scanning the table data itself. Which query is the best choice?
- A
SELECT table_schema, table_name, last_altered FROM SALES.INFORMATION_SCHEMA.TABLES WHERE table_type = 'BASE TABLE' AND last_altered < DATEADD(day, -30, CURRENT_TIMESTAMP());
- B
SELECT table_schema, table_name FROM SALES.INFORMATION_SCHEMA.COLUMNS WHERE table_type = 'BASE TABLE' AND last_altered < DATEADD(day, -30, CURRENT_TIMESTAMP());
- C
SELECT table_schema, table_name, created_on FROM SNOWFLAKE.ACCOUNT_USAGE.TABLES WHERE table_type = 'BASE TABLE' AND created_on < DATEADD(day, -30, CURRENT_TIMESTAMP());
- D
SELECT table_schema, table_name, last_query_id FROM SALES.INFORMATION_SCHEMA.TABLE_STORAGE_METRICS WHERE last_query_id < DATEADD(day, -30, CURRENT_TIMESTAMP());
Show answer and explanation
Correct answer: A
Explanation
The best answer is the query against SALES.INFORMATION_SCHEMA.TABLES because INFORMATION_SCHEMA is intended for metadata inspection within a database and can be queried without reading table data. For this scenario, the engineer needs table-level metadata and a way to identify stale objects. The TABLES view is the correct INFORMATION_SCHEMA view because it exposes TABLE_TYPE and LAST_ALTERED for objects in the specified database. Filtering to BASE TABLE excludes views and other object types. By contrast, INFORMATION_SCHEMA.COLUMNS is column-level metadata and is not suitable for one-row-per-table analysis. ACCOUNT_USAGE can also be useful for broader account-level reporting, but the question specifically asks for an INFORMATION_SCHEMA-based approach, and CREATED_ON does not indicate recent modification. This aligns with Snowflake documentation on INFORMATION_SCHEMA views, especially TABLES, which is commonly used to inspect database objects and metadata such as creation and alteration timestamps.
- A. Correct.
Correct. SALES.INFORMATION_SCHEMA.TABLES is the appropriate metadata view to list tables in a specific database and includes columns such as TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, and LAST_ALTERED. Filtering on TABLE_TYPE = 'BASE TABLE' and LAST_ALTERED older than 30 days uses metadata only and does not scan table contents.
- B. Incorrect.
Incorrect. INFORMATION_SCHEMA.COLUMNS contains column-level metadata, not table-level lifecycle metadata for identifying stale tables. It does not provide TABLE_TYPE in the way this query assumes for filtering table objects, and using COLUMNS would return one row per column rather than one row per table.
- C. Incorrect.
Incorrect. SNOWFLAKE.ACCOUNT_USAGE.TABLES is an account-level usage view, but this option uses CREATED_ON rather than a last modification field. A table created more than 30 days ago may still be actively modified, so this does not solve the stated requirement. It also shifts away from the requested INFORMATION_SCHEMA-based solution.
- D. Incorrect.
Incorrect. INFORMATION_SCHEMA.TABLE_STORAGE_METRICS provides storage-related metadata, not a LAST_QUERY_ID field for determining table modification recency. This option references a column that is not appropriate for the requirement and confuses storage metrics with object metadata.