SnowPro Associate: Platform Question 149
Single answer○ Use the INFORMATION_SCHEMAA Snowflake administrator needs to identify all tables in the SALES database that have not been modified in the last 30 days so the team can review them for possible archival. The administrator wants to use metadata available through INFORMATION_SCHEMA and avoid scanning table data. 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 last_altered < DATEADD(day, -30, CURRENT_TIMESTAMP());
- C
SELECT table_schema, table_name, created FROM SALES.INFORMATION_SCHEMA.TABLES WHERE created < DATEADD(day, -30, CURRENT_TIMESTAMP());
- D
SELECT table_schema, table_name FROM SNOWFLAKE.ACCOUNT_USAGE.TABLES WHERE bytes = 0 AND deleted IS NULL;
Show answer and explanation
Correct answer: A
Explanation
The best answer is to query the SALES.INFORMATION_SCHEMA.TABLES view and filter on LAST_ALTERED. INFORMATION_SCHEMA is designed for standards-based metadata access at the database level and is appropriate when the requirement is to inspect object metadata without scanning table data. TABLES contains object-level fields such as TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, CREATED, and LAST_ALTERED. For this scenario, LAST_ALTERED is the relevant field because the administrator wants tables that have not been modified recently, not simply old or empty tables. Option 4 is plausible because ACCOUNT_USAGE also exposes metadata, but the question specifically asks to use INFORMATION_SCHEMA, and ACCOUNT_USAGE views are not the same and may have data latency. This aligns with Snowflake documentation on INFORMATION_SCHEMA views and the TABLES metadata view.
- A. Correct.
Correct. SALES.INFORMATION_SCHEMA.TABLES is the appropriate view to query table-level metadata within the SALES database. The LAST_ALTERED column can be used to identify tables whose metadata indicates no recent modifications. Filtering for TABLE_TYPE = 'BASE TABLE' excludes views and focuses on physical tables. This approach uses INFORMATION_SCHEMA metadata and does not scan table contents.
- B. Incorrect.
Incorrect. INFORMATION_SCHEMA.COLUMNS provides column-level metadata, not table-level lifecycle information for identifying stale tables. Although it includes details about columns, it is not the right source for determining whether a table itself has been modified recently.
- C. Incorrect.
Incorrect. CREATED shows when the table was created, not when it was last changed. A table created years ago may still be actively updated, and a newer table may be inactive. Using CREATED would identify old tables, not necessarily unmodified tables.
- D. Incorrect.
Incorrect. SNOWFLAKE.ACCOUNT_USAGE.TABLES is an account-level shared metadata view, not an INFORMATION_SCHEMA view. It also uses a different access pattern and can have latency. In addition, filtering on BYTES = 0 identifies empty tables, which is not the same as tables that have not been modified in the last 30 days.