COF-C03 Question 303
Single answerUnstructuredA media company stores product manuals and warranty PDFs in Amazon S3 and wants analysts to query metadata about these files from Snowflake without copying the files into database tables. The files must remain in cloud object storage, but the company wants Snowflake to manage access to the files and support SQL-based discovery of the available documents. Which solution best meets these requirements?
- A
Create an external stage that points to the S3 bucket and use a directory table on the stage to expose file-level metadata for SQL queries
- B
Load the PDFs into a VARIANT column in a permanent table and query the table for file metadata
- C
Create a stream on the S3 bucket so Snowflake can automatically query file metadata without a stage
- D
Create a materialized view directly on the S3 bucket to expose the file names and paths
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use an external stage with a directory table. In Snowflake, unstructured data such as PDFs, images, and documents is accessed through stages, including external stages that reference cloud storage locations like Amazon S3. A directory table associated with the stage provides a Snowflake-managed metadata listing of the files, enabling SQL-based discovery without moving the files into relational tables. This is the practical pattern when an organization wants files to stay in object storage while still being governed and discoverable through Snowflake. By contrast, loading files into VARIANT would copy data into Snowflake tables and is intended for semi-structured formats rather than general unstructured documents. Streams and materialized views do not serve as substitutes for stages and directory tables in this use case. This aligns with Snowflake documentation on unstructured data, stages, and directory tables.
- A. Correct.
Correct. For unstructured data in Snowflake, files can remain in cloud object storage and be accessed through an external stage. Enabling and querying a directory table on the stage allows users to discover files and query file-level metadata such as relative path, size, and last modified information using SQL. This approach aligns with Snowflake support for unstructured data stored in stages.
- B. Incorrect.
Incorrect. Loading PDF files into a VARIANT column would require ingesting the file contents into table storage, which violates the requirement to keep the files in object storage. VARIANT is also intended for semi-structured data such as JSON, Avro, ORC, Parquet, and XML, not as the standard approach for managing unstructured files like PDFs in place.
- C. Incorrect.
Incorrect. Snowflake streams track change data on Snowflake objects such as tables and some external/table-related constructs, but they do not replace stages for accessing unstructured files in cloud storage. A stream on an S3 bucket is not how Snowflake exposes unstructured file metadata for SQL discovery.
- D. Incorrect.
Incorrect. Materialized views are created on query results over supported Snowflake objects, not directly on an S3 bucket. Snowflake requires a stage to reference files in object storage, and a directory table is the feature designed to surface file listings and metadata for unstructured data.