SnowPro Associate: Platform Question 292
Single answer● Directory tablesA data engineering team stores daily CSV exports in an internal named stage and wants analysts to query metadata such as file names, sizes, and last modified timestamps directly from Snowflake without loading the files into a table. New files are added throughout the day, and the team wants the metadata view to stay current with minimal manual effort. Which action should the team take to meet this requirement?
- A
Enable a directory table on the stage and configure AUTO_REFRESH so Snowflake automatically keeps the file metadata current
- B
Create a materialized view on the stage and schedule it to refresh every hour
- C
Create a stream on the stage so analysts can query file metadata changes directly
- D
Use the LIST command once to populate a persistent metadata table that updates automatically when files are added
Show answer and explanation
Correct answer: A
Explanation
The best solution is to use a directory table on the stage. Directory tables let users query metadata about files stored in a stage without loading the file data itself. This is useful for pipelines, validation, and discovery use cases where teams need file-level information such as names, paths, sizes, and modification times. When supported, AUTO_REFRESH helps keep the directory table synchronized as staged files change, minimizing manual maintenance. By contrast, LIST is a command for point-in-time inspection, not a persistent queryable object. Streams are for change tracking on supported Snowflake objects, not for replacing staged file metadata catalogs. Materialized views are also not the correct feature for this scenario. This aligns with Snowflake documentation on directory tables and staged file metadata management.
- A. Correct.
Correct. Directory tables provide a Snowflake-managed catalog of files in a stage, including metadata such as relative path, size, and last modified time. For supported stages, enabling a directory table and using AUTO_REFRESH allows Snowflake to keep that metadata up to date as files are added or removed, which matches the requirement for querying current file metadata without loading file contents into a table.
- B. Incorrect.
Incorrect. Materialized views are created on query results from tables or certain supported constructs, not directly on staged file listings as a substitute for directory tables. This option reflects a common misconception that any repeatedly queried metadata can be solved with a materialized view.
- C. Incorrect.
Incorrect. Streams track change data capture for tables, external tables, and some other Snowflake objects, but they are not used as the primary mechanism for exposing staged file metadata for analyst queries. A stream does not replace a directory table for browsing files in a stage.
- D. Incorrect.
Incorrect. The LIST command returns the current contents of a stage when executed, but it does not create a persistent object that auto-updates. Someone might choose this if they know LIST shows file metadata, but LIST is an ad hoc command, not a continuously maintained metadata structure.