SnowPro Associate: Platform Question 293
Single answer● Directory tablesA data engineering team stores inbound CSV files in a named internal stage and wants analysts to query file metadata such as file names, sizes, and last modified timestamps directly from Snowflake without loading the files into a table. The stage receives new files several times each day. Which action should the team take to make this metadata queryable in the most appropriate way?
- A
Create a directory table on the stage and refresh it so Snowflake can expose the staged file metadata for SQL queries.
- B
Create an external table on the internal stage because external tables are the supported way to query metadata for files in any stage.
- C
Use the LIST command against the stage in a view so analysts can query file metadata with standard SELECT statements.
- D
Load the staged files into a transient table first, because directory tables only work after the file contents are ingested into Snowflake.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to use a directory table on the stage and refresh it so the metadata stays current as new files arrive. Directory tables are intended to provide queryable metadata about files in a stage, which is useful for workflows involving staged data, unstructured data, and file management. This fits the requirement precisely: analysts need metadata such as file names, sizes, and last modified timestamps, not the file contents themselves. In Snowflake documentation, directory tables are associated with stages and expose file-level metadata that can be queried through SQL after the metadata is refreshed. By contrast, external tables are for querying structured or semi-structured file contents in external storage, LIST is an administrative command rather than a relational object, and loading files into a table is unnecessary when the goal is only to inspect staged file metadata.
- A. Correct.
Correct. Directory tables provide a Snowflake-managed metadata listing for files in a stage, including information such as relative path, size, and last modified timestamp. They are designed for querying staged file metadata directly from SQL without loading the file contents into a table. Because new files arrive regularly, the directory table metadata must be refreshed to reflect newly added files.
- B. Incorrect.
Incorrect. External tables are used to query file contents stored in external stages such as cloud object storage, and they are not the correct mechanism for querying file metadata for an internal stage in this scenario. The requirement is specifically to expose staged file metadata, not to define table columns over file contents.
- C. Incorrect.
Incorrect. LIST can display files in a stage, but it is a command result, not a persistent relational object intended for analysts to query with normal SELECT statements. A common misconception is that LIST can replace a directory table for ongoing SQL-based access, but directory tables are the feature built for this purpose.
- D. Incorrect.
Incorrect. Directory tables do not require data files to be loaded into Snowflake tables first. They track staged file metadata independently of whether the files have been ingested. This option reflects the misunderstanding that staged file metadata becomes available only after COPY INTO or similar loading operations.