DAA-C01 Question 79
Single answerUnstructured dataA retail analytics team stores product manuals, warranty PDFs, and product images in an internal stage in Snowflake. Analysts need to build a searchable inventory that records each file's relative path, size, last modified timestamp, and a file URL that downstream applications can use to retrieve the file. The team wants to avoid loading file contents into tables unless needed later for deeper processing. Which approach best meets these requirements with the least unnecessary data movement?
- A
Create a directory table on the stage and query its metadata columns to capture file-level attributes and generated file URLs.
- B
Create an external table over the stage so Snowflake automatically extracts PDF text and image metadata into relational columns for search.
- C
Use COPY INTO to load every file into a VARIANT column, then query METADATA$FILENAME and METADATA$FILE_ROW_NUMBER to build the inventory.
- D
Use a view over LIST @stage output because LIST returns persistent relational metadata that downstream applications can query directly.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use a directory table on the stage. In Snowflake, directory tables are the recommended mechanism for working with unstructured data stored in stages when you need file metadata without ingesting file content. They provide queryable metadata including relative path, file size, and last modified timestamp, and they support file URL patterns used by downstream applications. This aligns with the requirement to minimize unnecessary data movement and defer content processing until later. By contrast, external tables are intended for queryable structured or semi-structured datasets in external storage, not generic unstructured files like PDFs and images. COPY INTO would introduce avoidable ingestion overhead, and LIST is an operational command rather than a persistent metadata management approach. This follows Snowflake best practices for managing unstructured data via stages and directory tables.
- A. Correct.
Correct. For unstructured data in stages, Snowflake supports directory tables, which expose file-level metadata such as relative path, size, and last modified time. Directory tables are designed to help manage and query files in staged storage without ingesting the file contents into a table. They also support generating file URLs that applications can use to access staged files. This is the most appropriate approach when the goal is to inventory and reference unstructured files rather than parse their contents.
- B. Incorrect.
Incorrect. External tables are primarily used to query structured and semi-structured data files in external storage, such as CSV, JSON, Avro, or Parquet, by mapping file contents to rows and columns. They do not automatically extract the contents of PDFs or metadata from images into relational columns for search. This option reflects a common misconception that external tables are the general solution for all file types, including arbitrary unstructured documents and images.
- C. Incorrect.
Incorrect. COPY INTO is unnecessary here because the requirement explicitly says to avoid loading file contents into tables unless deeper processing is needed later. In addition, loading arbitrary unstructured files like PDFs and images into VARIANT is not the intended pattern for building a lightweight file inventory. METADATA$ columns are relevant during data loading/querying of supported structured or semi-structured data files, not as the best mechanism for maintaining an inventory of staged unstructured files.
- D. Incorrect.
Incorrect. LIST can show staged files and basic details at execution time, but it is not a persistent relational object that downstream applications should rely on as a managed inventory layer. A view cannot directly convert LIST command output into a durable, queryable metadata store in the same way a directory table provides. This distractor targets the misconception that command output is equivalent to a maintained metadata object.