ARA-C01 Question 164
Single answerStorage directory structureA data platform team stores raw partner files in an external Amazon S3 stage. The bucket contains millions of objects organized by date and source system, for example: s3://acme-raw/crm/2026/04/18/file1.json and s3://acme-raw/erp/2026/04/18/file2.json. Analysts frequently need to discover which files arrived for a given prefix before loading them, but repeated LIST operations on deep prefixes are becoming slow and expensive. The architect wants a Snowflake-native way to expose the stage contents as queryable metadata and to support filtering by subpath without loading the files into tables first. Which solution best meets these requirements?
- A
Create a directory table on the external stage and query it with relative path filtering for the required subdirectories.
- B
Create a materialized view directly on the external stage so Snowflake can automatically index all object paths by folder.
- C
Use a stream on the external stage and query the stream to list all files currently present under each prefix.
- D
Load the stage contents into a transient table using COPY INTO, then use the table as the authoritative directory listing.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use a directory table on the stage. In Snowflake, directory tables provide metadata for files in internal or external stages and are intended for scenarios where teams need to inspect or process files based on the storage directory structure without ingesting file contents first. Because object stores are flat and folder structures are represented by path prefixes, the practical design pattern is to query the directory table and filter on RELATIVE_PATH or similar metadata to target logical subdirectories such as crm/2026/04/18/. This avoids repeated ad hoc storage LIST operations and centralizes file discovery in Snowflake SQL. Snowflake documentation for directory tables and querying staged file metadata supports this approach as a best practice when working with structured stage paths.
- A. Correct.
Correct. A directory table provides Snowflake-maintained metadata about files in a stage, including relative paths and file URLs, making staged files queryable without loading file contents into a table. This is the Snowflake-native feature designed for exposing stage contents and supports filtering by relative path or subpath patterns. It is well suited when users need to inspect files under folder-like prefixes in an internal or external stage.
- B. Incorrect.
Incorrect. Snowflake does not support creating a materialized view directly on an external stage to index object paths. Materialized views are created on base tables, not on stages. This option reflects a common misconception that stage metadata can be materialized in the same way as table query results.
- C. Incorrect.
Incorrect. Streams track data manipulation changes on supported Snowflake objects such as tables and some external tables, but a stream is not used to enumerate the current contents of a stage. It does not replace directory metadata for listing files under a storage path. This distractor confuses change capture with staged file discovery.
- D. Incorrect.
Incorrect. Loading file names into a transient table could work as a custom workaround, but it does not meet the requirement for a Snowflake-native solution to expose current stage contents before loading the files. It also adds operational overhead, risks becoming stale, and defeats the purpose of querying staged file metadata directly.