SnowPro Associate: Platform Question 150
Single answer○ Use the INFORMATION_SCHEMAA data engineering team needs to audit which tables in the SALES database have not been modified in the last 30 days so they can identify stale objects for review. They want to use Snowflake metadata and avoid scanning table data. Which query is the most appropriate?
- 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, created FROM SALES.INFORMATION_SCHEMA.COLUMNS WHERE created < DATEADD(day, -30, CURRENT_TIMESTAMP());
- C
SELECT table_schema, table_name, last_altered FROM ACCOUNT_USAGE.TABLES WHERE table_type = 'BASE TABLE' AND last_altered < DATEADD(day, -30, CURRENT_TIMESTAMP());
- D
SELECT table_schema, table_name FROM SALES.PUBLIC.TABLES WHERE last_altered < DATEADD(day, -30, CURRENT_TIMESTAMP());
Show answer and explanation
Correct answer: A
Explanation
Snowflake provides INFORMATION_SCHEMA as a read-only, ANSI-style metadata schema within each database. For database-scoped object discovery, SALES.INFORMATION_SCHEMA.TABLES is the correct place to query table metadata such as LAST_ALTERED without scanning user data. This is a practical approach for governance and cleanup tasks. While SNOWFLAKE.ACCOUNT_USAGE also exposes metadata, it is account-level and commonly used for broader reporting, with some latency considerations. For immediate, database-focused metadata inspection, INFORMATION_SCHEMA is generally the best fit. Relevant Snowflake documentation includes INFORMATION_SCHEMA views, especially TABLES, and guidance on the distinction between INFORMATION_SCHEMA and ACCOUNT_USAGE.
- A. Correct.
Correct. The INFORMATION_SCHEMA.TABLES view contains metadata about tables and includes columns such as TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, and LAST_ALTERED. Querying SALES.INFORMATION_SCHEMA.TABLES limits the scope to the SALES database and uses metadata only, which avoids scanning table contents. Filtering on TABLE_TYPE = 'BASE TABLE' excludes views and other non-table objects, making this appropriate for an audit of stale tables.
- B. Incorrect.
Incorrect. INFORMATION_SCHEMA.COLUMNS contains column-level metadata, not table-level lifecycle metadata for identifying stale tables. Although it can be useful for schema discovery, using CREATED from COLUMNS would not correctly answer which tables have not been modified recently. It also introduces duplication because each table can have many rows in COLUMNS.
- C. Incorrect.
Incorrect. ACCOUNT_USAGE.TABLES can also provide table metadata, but this option is less appropriate for the stated requirement to use INFORMATION_SCHEMA. In addition, ACCOUNT_USAGE views can have latency before metadata appears, so they are not the best fit when the goal is a direct database-scoped INFORMATION_SCHEMA query. The question asks for the most appropriate query, and the database INFORMATION_SCHEMA view is the better match.
- D. Incorrect.
Incorrect. SALES.PUBLIC.TABLES is not a valid way to reference Snowflake metadata views. INFORMATION_SCHEMA is a special schema provided for metadata. TABLES is a view in INFORMATION_SCHEMA, not typically a user table in the PUBLIC schema. This option reflects a common misconception that metadata objects live in standard user schemas.