DAA-C01 Question 85
Single answerPrepare external tablesA data analyst team needs to query daily JSON files stored in an S3 bucket without loading the data into native Snowflake tables. New files are added every hour under paths such as s3://sales-raw/region=us/date=2026-04-20/*.json. The team wants analysts to query both the JSON attributes and the region/date values embedded in the folder structure. They also want newly arrived files to become queryable with minimal manual effort. Which approach best meets these requirements?
- A
Create an external stage and file format, then create an external table that defines VALUE as a VARIANT column and includes partition columns derived with expressions from METADATA$FILENAME. Refresh the external table manually or enable automatic metadata refresh if supported for the stage.
- B
Create a standard table with a VARIANT column and use Snowpipe Streaming to automatically ingest the files from S3, because external tables cannot expose partition values from file paths.
- C
Create a view directly on top of the external stage using SELECT from @stage, because views on stages automatically track newly added files and expose folder names as relational columns.
- D
Create a materialized view on the S3 stage and parse the JSON in the materialized view definition, because materialized views support automatic file discovery and eliminate the need for external table metadata.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to use an external table over an external stage. This allows Snowflake to query data in external cloud storage without ingesting it into native storage. For JSON files, the external table commonly exposes a VALUE column of type VARIANT, and analysts can extract attributes with standard semi-structured querying syntax. To expose region and date from folder names, define partition columns using expressions that parse METADATA$FILENAME, a supported pattern for path-based partitioning. Because external tables rely on registered file metadata, new files become queryable only after metadata is refreshed. Depending on the cloud platform and configuration, this can be done manually with ALTER EXTERNAL TABLE ... REFRESH or automatically through auto-refresh using event notifications and integrations. This aligns with Snowflake best practices for preparing external tables and managing partitioned file layouts.
- A. Correct.
Correct. This is the intended pattern for querying files in external cloud storage without loading them into Snowflake tables. An external table is created over files in a stage and can expose file contents through a VALUE column, typically VARIANT for semi-structured formats such as JSON. It can also define partition columns using expressions that parse METADATA$FILENAME to extract values like region and date from the folder path. To make new files queryable, the external table metadata must be refreshed, either manually with ALTER EXTERNAL TABLE ... REFRESH or automatically when auto-refresh is supported and configured for the cloud provider and notification integration.
- B. Incorrect.
Incorrect. A standard table plus Snowpipe Streaming changes the architecture from external querying to ingestion into Snowflake-managed storage. That does not meet the requirement to query data without loading it into native tables. Also, the claim that external tables cannot expose partition values from file paths is false; partition columns can be defined using expressions based on file path metadata.
- C. Incorrect.
Incorrect. You can query staged files ad hoc with SELECT from @stage in some cases, but a view directly over a stage does not replace an external table for governed, repeatable querying with managed file metadata. Stage queries do not provide the same external table metadata management, and folder names are not automatically exposed as relational columns unless you explicitly derive them. Newly added files are not tracked through view semantics; external table metadata refresh is the mechanism designed for this.
- D. Incorrect.
Incorrect. Materialized views are created on tables or views, not directly on an external stage in place of external table metadata. Even when using materialized views with external tables in some architectures, they do not eliminate the need for the external table to register and manage file metadata. The option incorrectly assumes a materialized view can serve as the primary object over cloud storage files.