ARA-C01 Question 282
Single answerExternal tablesA retail company stores daily clickstream files as partitioned Parquet objects in an Amazon S3 bucket using paths such as s3://retail-raw/clickstream/dt=2026-01-15/region=us/file1.parquet. Analysts need near-real-time visibility into newly landed files without copying the data into Snowflake. The architecture team creates an external stage and an external table over the S3 location. New files arrive continuously throughout the day, and the analysts want query results to reflect new files with minimal operational effort. Which approach should the architect recommend?
- A
Configure auto-refresh on the external table using S3 event notifications integrated with Snowflake so metadata updates occur as new files land
- B
Create a materialized view on top of the external table; Snowflake will automatically discover new files through the materialized view refresh process
- C
Schedule a task that runs ALTER EXTERNAL TABLE ... REFRESH every minute, because external tables cannot automatically detect new files in cloud storage
- D
Use Snowpipe on the same stage; once Snowpipe loads file metadata, the external table will automatically include new files without any refresh mechanism
Show answer and explanation
Correct answer: A
Explanation
The best answer is to enable external table AUTO_REFRESH with the required S3 event-notification integration. External tables store metadata about files in external storage, and Snowflake queries that metadata rather than scanning the bucket blindly each time. To make newly landed files visible quickly and with minimal administration, Snowflake best practice is to use event-driven auto-refresh where supported rather than repeatedly issuing manual refresh commands. ALTER EXTERNAL TABLE ... REFRESH is useful for initial registration, backfills, troubleshooting, or environments where event integration is not configured, but it is not the preferred low-ops design for continuous ingestion. Materialized views do not perform file discovery for external tables, and Snowpipe addresses loading data into native Snowflake tables rather than maintaining external table metadata. This aligns with Snowflake documentation on external tables, auto-refresh behavior, and cloud event notification integration for external stages and external tables.
- A. Correct.
Correct. External tables can use AUTO_REFRESH to automatically update file metadata when cloud-provider event notifications are configured and integrated with Snowflake. For Amazon S3, this typically involves event notifications (often via SNS/SQS, depending on setup) so Snowflake is informed when new files arrive. This is the lowest-operational-overhead design for near-real-time discovery of newly landed files when querying external data in place.
- B. Incorrect.
Incorrect. A materialized view on an external table can improve query performance for supported use cases, but it does not replace external table metadata refresh. Snowflake still needs the external table's file metadata to be updated before new files are visible. Materialized view maintenance is not the mechanism used to discover newly added objects in external storage.
- C. Incorrect.
Incorrect. Manually or programmatically running ALTER EXTERNAL TABLE ... REFRESH is a valid approach when auto-refresh is not available or not configured, but the statement that external tables cannot automatically detect new files is false. Auto-refresh is supported for external tables when the appropriate cloud event integration is set up. A frequent task-based refresh also adds unnecessary operational overhead compared to event-driven refresh.
- D. Incorrect.
Incorrect. Snowpipe is used to load data from staged files into Snowflake tables, not to maintain external table metadata. An external table queries files in place and relies on refresh operations or auto-refresh notifications to register new files. Using Snowpipe on the same stage does not cause the external table to automatically recognize new files unless the external table itself is refreshed through its supported mechanisms.