ADA-C01 Question 234
Single answerCreate and manage external tablesA data engineering team stores daily JSON files in an S3 bucket and exposes them to analysts through a Snowflake external table. New files arrive throughout the day, and analysts have reported that recently landed files are not visible in queries for several hours. The team wants metadata for newly added files to be reflected in the external table automatically, with the least ongoing operational effort. Which action should the Snowflake administrator take?
- A
Recreate the external table with AUTO_REFRESH = TRUE and configure the S3 bucket to send event notifications to the Snowflake notification integration associated with the stage.
- B
Enable CHANGE_TRACKING on the external table so Snowflake can detect new files automatically without any cloud messaging configuration.
- C
Create a stream on the external table and query the stream on a schedule to force Snowflake to discover newly added files.
- D
Schedule periodic ALTER EXTERNAL TABLE
REFRESH statements in a task because AUTO_REFRESH is only supported for internal stages.
Show answer and explanation
Correct answer: A
Explanation
External tables do not automatically query cloud storage for every SELECT; they rely on metadata about files registered with the table. When new files are added to the external location, Snowflake must refresh that metadata. Administrators can do this manually with ALTER EXTERNAL TABLE ... REFRESH, but the best practice for frequently arriving files is to enable AUTO_REFRESH and configure the cloud provider's event notifications so Snowflake is notified when files are added. On Amazon S3, this involves S3 event notifications and the Snowflake notification integration tied to the external stage/external table workflow. Streams and CHANGE_TRACKING do not discover new files; they operate on metadata or row changes Snowflake already knows about. This aligns with Snowflake documentation on creating and managing external tables, automatic refresh, and cloud messaging integration requirements.
- A. Correct.
Correct. For external tables on cloud storage such as Amazon S3, the preferred low-maintenance approach is to use automatic metadata refresh. This requires the external table to be created or altered with AUTO_REFRESH = TRUE and the cloud storage location to publish event notifications that Snowflake can consume through the appropriate notification integration. With this setup, newly added files are registered in external table metadata automatically instead of waiting for manual refresh operations.
- B. Incorrect.
Incorrect. CHANGE_TRACKING is used to support features such as streams and tracking row-level changes in tables and some views; it does not make Snowflake discover new files in external cloud storage for an external table. File discovery for external tables depends on refresh operations, either manual or automatic through event notifications.
- C. Incorrect.
Incorrect. A stream on an external table tracks changes to the external table metadata that Snowflake already knows about. It does not trigger discovery of new files in the stage. If the external table metadata has not been refreshed, the stream will not help surface files that Snowflake has not yet registered.
- D. Incorrect.
Incorrect. Manual refresh via ALTER EXTERNAL TABLE ... REFRESH is a valid fallback when auto-refresh is not configured, but it adds operational overhead and does not meet the requirement for the least ongoing effort. The statement that AUTO_REFRESH is only supported for internal stages is false; external tables commonly use AUTO_REFRESH with supported cloud event notifications for external stages.