ADA-C01 Question 235
Single answerCreate and manage external tablesA data engineering team stores hourly JSON files in an Amazon S3 bucket and exposes them through a Snowflake external table. New files are being added throughout the day, but analysts report that queries against the external table do not return the newly landed data until the administrator manually refreshes metadata. The team wants the external table metadata to stay current automatically with minimal administrative effort. Which configuration change should the administrator implement?
- A
Enable AUTO_REFRESH on the external table and configure the S3 bucket to send event notifications to the Snowflake-provided SNS topic for the stage
- B
Create a stream on the external table so Snowflake can detect new files automatically without additional cloud configuration
- C
Convert the external table to a temporary table and schedule a task to run ALTER EXTERNAL TABLE ... REFRESH every hour
- D
Increase the virtual warehouse size used by analyst queries so metadata refresh occurs automatically during query compilation
Show answer and explanation
Correct answer: A
Explanation
The core issue is that external tables do not automatically become aware of new files unless metadata is refreshed. For S3-based external tables, Snowflake supports automated refresh by combining AUTO_REFRESH with event notifications from the cloud provider. This is the recommended operational pattern because it reduces latency and administrative overhead compared to repeatedly issuing ALTER EXTERNAL TABLE ... REFRESH. Streams can be used on external tables for change processing once metadata is current, but they do not replace refresh mechanics. Similarly, warehouse sizing has no role in file discovery. Snowflake documentation on external tables and auto-refresh describes the need for supported cloud messaging/event notification integration, including S3 event notifications routed through Snowflake-managed notification infrastructure.
- A. Correct.
Correct. For Amazon S3-backed external tables, automatic metadata refresh requires both the external table property AUTO_REFRESH = TRUE and cloud event notification integration. In practice, this means configuring the S3 bucket to publish object create events to the Snowflake-provided SNS topic associated with the stage. This allows Snowflake to update external table metadata as files arrive, avoiding manual ALTER EXTERNAL TABLE ... REFRESH operations.
- B. Incorrect.
Incorrect. A stream on an external table tracks changes to rows exposed by the external table after metadata is updated, but it does not discover newly added files in cloud storage by itself. File discovery for external tables still depends on manual refresh or auto-refresh via supported event notifications.
- C. Incorrect.
Incorrect. Temporary tables are internal Snowflake objects and do not replace the purpose of an external table. While a scheduled task that runs ALTER EXTERNAL TABLE ... REFRESH could work as a manual workaround, it does not meet the requirement for minimal administrative effort as effectively as supported auto-refresh using cloud notifications.
- D. Incorrect.
Incorrect. Warehouse size affects compute available for query execution, not how Snowflake discovers new files for an external table. External table metadata refresh is separate from query compilation and does not happen automatically because a larger warehouse is used.