DAA-C01 Question 86
Select 2Prepare external tablesA retail analytics team stores daily product inventory files as partitioned Parquet data in Amazon S3 at paths such as s3://retail-raw/inventory/dt=2024-11-01/region=us-east-1/part-000.parquet. Analysts need to query the files through a Snowflake external table and frequently filter on both date and region. New partitions are added throughout the day. The team wants partition pruning to work efficiently and wants newly arrived partitions to become queryable with minimal manual effort. Which TWO actions should the data analyst take when preparing the external table?
- A
Define partition columns on the external table using expressions based on METADATA$FILENAME so Snowflake can prune files by dt and region.
- B
Enable AUTO_REFRESH on the external table and configure the supported cloud event notification integration for the stage so newly added files are registered automatically.
- C
Create a materialized view directly on the S3 stage instead of an external table, because materialized views provide automatic partition discovery for staged files.
- D
Use COPY INTO to load the Parquet files into a temporary table first, because external tables cannot expose partition values derived from folder paths.
- E
Store dt and region only inside the Parquet file body and avoid folder-based partitioning, because external tables prune only on virtual columns extracted from file contents.
Show answer and explanation
Correct answers: A, B
Explanation
The best answer is to combine path-based partition columns with automated metadata refresh. In Snowflake, external tables can expose partition columns derived from the file path, commonly by parsing METADATA$FILENAME. This is especially useful when data is organized in Hive-style partitions such as dt=.../region=.... Queries filtering on those columns can benefit from partition pruning, which reduces the number of files Snowflake needs to inspect. To keep the external table current as new files arrive, enable AUTO_REFRESH and configure the appropriate cloud event notification integration for the external stage so Snowflake automatically registers new files. This aligns with Snowflake best practices for external tables over partitioned S3 data. Relevant Snowflake documentation includes external tables, partition columns, METADATA$FILENAME usage, and automated refresh using cloud messaging/event notifications.
- A. Correct.
Correct. When preparing an external table over partitioned files, defining partition columns from METADATA$FILENAME is a common best practice if partition values are embedded in the path. This allows queries that filter on dt or region to take advantage of partition pruning and avoid scanning unrelated files. In a layout like dt=2024-11-01/region=us-east-1/, the expressions can parse the path segments and expose them as partition columns in the external table definition.
- B. Correct.
Correct. For external tables, AUTO_REFRESH can be used with supported cloud-provider event notifications so Snowflake automatically updates metadata when new files arrive. This reduces manual execution of REFRESH operations and is the standard way to make newly added partitions queryable with minimal operational effort, assuming the stage and notification integration are configured correctly for the cloud platform.
- C. Incorrect.
Incorrect. Materialized views are created on tables, including external tables in supported scenarios, not directly on an S3 stage. A stage by itself is just a pointer to external files and does not provide the table metadata abstraction needed for this use case. Also, partition discovery for files is an external table concern, not something solved by creating a materialized view on a stage.
- D. Incorrect.
Incorrect. COPY INTO can load files into native Snowflake tables, but that changes the architecture from querying external data in place to ingesting data. The scenario explicitly requires an external table and efficient filtering on path-based partitions. External tables do support deriving partition columns from file paths, so loading into a temporary table is unnecessary and does not address the requirement for external querying.
- E. Incorrect.
Incorrect. External table partition pruning is commonly implemented using partition columns derived from folder paths via METADATA$FILENAME, not only from values inside the file body. In fact, folder-based partitioning is a standard and effective design for external tables because it helps limit file scans before reading file contents. Avoiding folder-based partitioning would usually reduce pruning effectiveness in this scenario.