ADA-C01 Question 342
Select 3Create external tablesA data engineering team stores daily Parquet files in an Amazon S3 bucket under paths such as s3://sales-raw/orders/region=us/date=2026-01-15/. They need to expose the files in Snowflake through an external table so analysts can query both the file contents and the partition values. The team also wants new files to be discovered automatically without manually running ALTER EXTERNAL TABLE ... REFRESH. Which set of actions should the Snowflake administrator take?
- A
Create an external stage pointing to the S3 location, define a FILE FORMAT for Parquet, and create the external table with partition columns derived from METADATA$FILENAME using expressions.
- B
Configure auto-refresh for the external table by integrating Snowflake with Amazon S3 event notifications through the appropriate notification integration and stage configuration.
- C
Create a regular internal table on top of the S3 bucket and enable CHANGE_TRACKING so Snowflake automatically detects new files and partition folders.
- D
Use COPY INTO to load the Parquet data into an external table first, because external tables require data to be registered through COPY commands before queries can return rows.
- E
Define the external table with inferred columns from the Parquet files and include partition columns explicitly in the external table definition if they need to be queried separately.
Show answer and explanation
Correct answers: A, B, E
Explanation
The correct approach is to create an external stage over the S3 location, define a Parquet file format, and create an external table that exposes both data columns and partition columns. Because the files are organized by path segments such as region=us/date=2026-01-15, partition columns are typically defined from path/filename metadata using expressions based on METADATA$FILENAME. To discover new files automatically, the administrator should enable AUTO_REFRESH for the external table using the required cloud event notification setup for Amazon S3. External tables query data in place; they do not require COPY INTO before use. This aligns with Snowflake best practices for external tables, partitioning, and auto-refresh configuration for S3-backed stages.
- A. Correct.
Correct. To create an external table over S3 data, Snowflake needs an external stage that references the cloud storage location and a file format appropriate for the files, such as Parquet. External table partition columns can be defined using expressions, commonly parsing values from METADATA$FILENAME when the partition information is encoded in folder paths like region=us/date=2026-01-15. This is a standard pattern for partitioned data lakes.
- B. Correct.
Correct. Automatic metadata refresh for S3-backed external tables is achieved by configuring event-based auto-refresh using Amazon S3 event notifications with the required Snowflake integration. Without this, newly added files are not discovered automatically and administrators would need to run manual refresh operations. This option directly addresses the requirement to avoid ALTER EXTERNAL TABLE ... REFRESH.
- C. Incorrect.
Incorrect. Internal tables store data inside Snowflake storage; they are not created 'on top of' an S3 bucket. CHANGE_TRACKING is unrelated to discovering external files in cloud storage and does not provide automatic registration of new S3 objects for external tables. This distractor reflects a common confusion between Snowflake table features and external storage metadata management.
- D. Incorrect.
Incorrect. COPY INTO loads data from stages into internal or temporary/transient tables; it is not required to populate an external table. External tables reference data in place and expose file metadata and virtual columns without loading the data into Snowflake storage first. Someone might choose this if they confuse external tables with standard data-loading workflows.
- E. Correct.
Correct. For Parquet and other semi-structured external data, Snowflake can define external tables using column expressions over the file contents, and partition columns can be added separately so they can be queried directly and used for pruning. This is practical when partition values are embedded in folder names and should appear as relational columns in analyst queries.