ARA-C01 Question 281
Single answerExternal tablesA retail company stores daily clickstream data as compressed JSON files in an Amazon S3 bucket partitioned by folder path: s3://retail-raw/clickstream/dt=YYYY-MM-DD/region=XX/. Analysts need to query the data in Snowflake within minutes of new files arriving, but the company does not want to copy the raw files into Snowflake tables yet because data scientists also access the same files directly in S3. The architect creates an external stage and an external table over the bucket. During testing, analysts notice that newly landed files are not consistently visible in queries until someone manually refreshes metadata. Which design change will best meet the near-real-time requirement while keeping the data external?
- A
Enable AUTO_REFRESH on the external table and configure the S3 bucket to send event notifications to Snowflake through the required cloud messaging integration.
- B
Create a materialized view on top of the external table; materialized view maintenance will automatically discover and register new files in S3.
- C
Replace the external table with a standard Snowflake table loaded by Snowpipe; this keeps the source data external while making new files queryable immediately.
- D
Schedule a larger virtual warehouse to run SELECT queries against the external table every few minutes; query execution will force Snowflake to discover new files automatically.
Show answer and explanation
Correct answer: A
Explanation
External tables allow Snowflake to query data stored in external cloud storage without loading it into native tables. However, Snowflake must maintain metadata about the files and partitions available in the external location. New files are not guaranteed to appear automatically unless metadata is refreshed. For near-real-time use cases, best practice is to enable AUTO_REFRESH on the external table and configure the cloud provider's event notification mechanism so Snowflake receives object creation events and updates metadata automatically. On Amazon S3, this involves S3 event notifications and the required notification integration setup supported by Snowflake. Manual REFRESH is valid for batch-oriented scenarios, but it does not meet the stated operational requirement. Materialized views can be used on external tables for performance optimization, but they do not replace metadata refresh. Snowpipe is for loading files into Snowflake tables, not for preserving an external-table architecture. These behaviors are documented in Snowflake guidance for creating and maintaining external tables, refreshing external table metadata, and configuring auto-refresh using cloud storage event notifications.
- A. Correct.
Correct. External tables rely on file metadata registration. To avoid manual ALTER EXTERNAL TABLE ... REFRESH operations, Snowflake supports automatic metadata refresh for supported cloud providers by using AUTO_REFRESH together with cloud event notifications. For Amazon S3, this requires configuring the bucket notifications and the related integration so Snowflake is informed when new objects arrive. This is the standard design for near-real-time discovery of newly added files while leaving the data in external storage.
- B. Incorrect.
Incorrect. A materialized view on an external table can improve query performance for selected data, but it does not solve file discovery by itself. Snowflake still needs external table metadata to be refreshed before newly added files are visible. Materialized view maintenance is not a substitute for external table auto-refresh or manual refresh.
- C. Incorrect.
Incorrect. Snowpipe loads data from staged files into native Snowflake tables, which means the data is copied into Snowflake storage. That contradicts the requirement to keep the raw data external and query it in place. Snowpipe is appropriate when the goal is ingestion, not external querying.
- D. Incorrect.
Incorrect. Running queries or increasing warehouse size does not cause Snowflake to automatically register new files for an external table. External tables use metadata about files in the external location; without auto-refresh or explicit refresh commands, newly landed files may remain invisible regardless of warehouse size or query frequency.