COF-C03 Question 302
Single answerUnstructuredA media company stores product manuals and warranty PDFs in an Amazon S3 bucket and wants analysts to access those files through Snowflake without loading the file contents into table columns. The company also needs Snowflake to track file-level metadata and support SQL-based access to the files. Which approach should the architect recommend?
- A
Create an external stage pointing to the S3 bucket and define an external volume so Snowflake can automatically expose the files as rows in a standard table
- B
Create a directory table on an external stage that points to the S3 bucket, and query the staged files and their metadata through Snowflake
- C
Load the PDF files into a VARIANT column using COPY INTO so Snowflake can treat them as unstructured data without any staging objects
- D
Create a materialized view directly on the S3 bucket path so analysts can query file contents and metadata with standard SELECT statements
Show answer and explanation
Correct answer: B
Explanation
The best answer is to use an external stage that points to the S3 location and enable a directory table for that stage. Snowflake supports unstructured data by referencing files stored in cloud object storage rather than requiring those files to be loaded into table columns. Directory tables provide metadata about staged files and are a core mechanism for SQL-based interaction with unstructured data stored in stages. This aligns with Snowflake best practices for unstructured data access: keep files in object storage, expose them through stages, and use directory tables for file metadata and discovery. Relevant Snowflake documentation covers unstructured data, stages, and directory tables for external and internal stages.
- A. Incorrect.
Incorrect. An external stage is part of the solution, but an external volume is not used to expose unstructured files from S3 as rows in a standard table. External volumes are associated with Snowflake-managed features such as Iceberg table storage, not with basic access to unstructured files in stages. Also, Snowflake does not automatically turn staged unstructured files into rows in a normal table simply by defining these objects.
- B. Correct.
Correct. For unstructured data in Snowflake, a common pattern is to store files in cloud storage and reference them through an external stage. A directory table can then be associated with the stage to provide file-level metadata such as relative path, size, checksum information, and timestamps. This lets users access and manage unstructured files through SQL without loading the file contents into table columns.
- C. Incorrect.
Incorrect. COPY INTO is used to load structured or semi-structured data into tables, but it is not the recommended way to handle files such as PDFs when the requirement is to keep them as files and access them through Snowflake. Loading binary document contents into VARIANT is not how Snowflake supports unstructured data access for this scenario.
- D. Incorrect.
Incorrect. Snowflake does not support creating a materialized view directly on an S3 bucket path. Materialized views are created on query results from base tables. They are not used as a mechanism to expose external unstructured files and their metadata from cloud object storage.