ADA-C01 Question 532
Select 2External tablesA data engineering team stores daily JSON files in an S3 bucket and exposes them through a Snowflake external table. New files arrive throughout the day, but analysts report that queries against the external table do not show the newest files until several hours later. The administrator must reduce the delay while minimizing manual operational effort. Which TWO actions should the administrator take?
- A
Configure the external stage used by the external table with AUTO_REFRESH = TRUE and set up the required cloud event notifications integration for S3.
- B
Run ALTER EXTERNAL TABLE <table_name> REFRESH manually after each file arrives.
- C
Create the external table with REFRESH_ON_CREATE = TRUE so new files are discovered automatically after the table is created.
- D
Verify that the S3 bucket event notifications are correctly configured to send events to Snowflake so metadata refresh can occur automatically.
- E
Increase the virtual warehouse size used by analysts querying the external table so Snowflake can detect new files faster.
Show answer and explanation
Correct answers: A, D
Explanation
External tables query data in external cloud storage, but Snowflake must first know which files exist through external table metadata. When new files are not appearing quickly, the issue is usually metadata refresh configuration rather than query compute. For AWS S3, the best practice to minimize latency and administrative effort is to use automatic refresh backed by S3 event notifications and the appropriate Snowflake notification integration. Manual ALTER EXTERNAL TABLE ... REFRESH works but creates operational overhead. REFRESH_ON_CREATE only affects the initial population of metadata when the table is created; it does not maintain freshness for future files. Warehouse scaling does not influence file discovery. This aligns with Snowflake guidance on external tables, AUTO_REFRESH behavior, and cloud event notification-based metadata refresh.
- A. Correct.
Correct. For S3-backed external tables, reducing discovery delay with minimal manual effort typically requires auto-refresh. That means the stage and external table setup must support automatic metadata refresh driven by cloud event notifications. In practice, AUTO_REFRESH must be enabled and the required notification integration and eventing path must be configured so Snowflake receives file-change events and updates external table metadata without repeated manual refresh commands.
- B. Incorrect.
Incorrect. Manually issuing ALTER EXTERNAL TABLE ... REFRESH does update metadata and can make new files visible, but it increases operational effort and does not meet the requirement to minimize manual administration. It is a valid workaround, not the best long-term solution for frequent file arrivals.
- C. Incorrect.
Incorrect. REFRESH_ON_CREATE = TRUE refreshes metadata when the external table is created so existing files are discovered at creation time. It does not cause newly arriving files to be discovered automatically after creation. A common misconception is to treat creation-time refresh as ongoing refresh behavior.
- D. Correct.
Correct. Even if auto-refresh is intended, it will not work unless the cloud provider event notifications are properly configured. For S3, Snowflake relies on event notifications to detect new or changed files for external table metadata refresh. Verifying the event configuration is essential when new files are not appearing promptly.
- E. Incorrect.
Incorrect. Warehouse size affects query execution performance, not external table metadata discovery. External table file registration and refresh behavior are controlled by metadata refresh operations and event notifications, not by the size of the warehouse used to query the table.