SnowPro Associate: Platform Question 152
Single answer○ Use the INFORMATION_SCHEMAA data engineer needs to identify all BASE TABLES in the ANALYTICS database that have not been modified in the last 30 days so they can review candidates for archival. They want to use metadata available in Snowflake without scanning table data. Which query best meets this requirement?
- A
SELECT table_schema, table_name, last_altered FROM ANALYTICS.INFORMATION_SCHEMA.TABLES WHERE table_type = 'BASE TABLE' AND last_altered < DATEADD(day, -30, CURRENT_TIMESTAMP());
- B
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());
- C
SELECT table_schema, table_name, last_query_id FROM ANALYTICS.INFORMATION_SCHEMA.TABLE_STORAGE_METRICS WHERE last_query_id IS NULL;
- D
SELECT table_schema, table_name, last_altered FROM ANALYTICS.PUBLIC.TABLES WHERE table_type = 'BASE TABLE' AND last_altered < DATEADD(day, -30, CURRENT_TIMESTAMP());
Show answer and explanation
Correct answer: A
Explanation
The best answer is the query against ANALYTICS.INFORMATION_SCHEMA.TABLES. In Snowflake, each database provides an INFORMATION_SCHEMA containing read-only views for metadata. The TABLES view includes columns such as TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, and LAST_ALTERED, which are commonly used to inventory objects and review recent changes. This approach is efficient because it relies on metadata rather than querying the table data itself. Option 2 is a common mistake because CREATED_ON does not indicate recent modification and ACCOUNT_USAGE can be delayed. Option 3 confuses storage metadata with activity/change metadata. Option 4 reflects a misunderstanding that INFORMATION_SCHEMA views live in a regular schema. Snowflake documentation for INFORMATION_SCHEMA and the TABLES view describes these metadata columns and their intended use.
- A. Correct.
Correct. INFORMATION_SCHEMA.TABLES is the appropriate metadata view to query within a database when you need table-level metadata such as TABLE_TYPE and LAST_ALTERED. Filtering on TABLE_TYPE = 'BASE TABLE' limits results to physical tables, and comparing LAST_ALTERED to a timestamp 30 days in the past satisfies the requirement without scanning table contents.
- B. Incorrect.
Incorrect. SNOWFLAKE.ACCOUNT_USAGE.TABLES is an account-level usage view, but this query checks CREATED_ON rather than when the table was last modified. A table created long ago may have been altered recently, so this does not answer the requirement. In addition, ACCOUNT_USAGE views can have latency, making them less suitable when current metadata is needed.
- C. Incorrect.
Incorrect. INFORMATION_SCHEMA.TABLE_STORAGE_METRICS contains storage-related metadata, not a LAST_QUERY_ID column for determining table modification recency. This option mixes unrelated concepts and would not identify tables based on recent DDL or DML changes.
- D. Incorrect.
Incorrect. TABLES is an INFORMATION_SCHEMA view, not a physical table in a user schema such as ANALYTICS.PUBLIC. Referencing ANALYTICS.PUBLIC.TABLES assumes an object that typically does not exist and misunderstands how INFORMATION_SCHEMA is exposed in Snowflake.