ADA-C01 Question 334
Single answerMetadata cacheA data engineering team stores clickstream data in an external stage on Amazon S3 and exposes it through an external table in Snowflake. New files are added to the S3 location every 5 minutes. Analysts report that queries against the external table sometimes do not return the newest files immediately, even though the files are already present in S3. The Snowflake administrator needs to reduce this delay while keeping query performance efficient. Which action should the administrator take?
- A
Refresh the external table metadata after new files arrive, either manually or by using automated refresh where supported.
- B
Suspend and resume the virtual warehouse before each query so Snowflake clears the metadata cache.
- C
Disable the result cache at the session level so queries force a fresh scan of the S3 location.
- D
Recluster the external table after each file load so Snowflake rebuilds the file-level metadata.
Show answer and explanation
Correct answer: A
Explanation
This scenario tests understanding of metadata cache behavior for external tables. Snowflake maintains metadata about files associated with an external table rather than listing cloud storage contents for every query. As a result, newly added files in S3 are not immediately queryable until the external table metadata is refreshed. The correct operational response is to refresh the external table, either explicitly with ALTER EXTERNAL TABLE ... REFRESH or by configuring automated refresh mechanisms where available and appropriate. This preserves performance because Snowflake can query based on maintained metadata instead of repeatedly scanning cloud storage. Result cache settings and warehouse restarts do not update external table file metadata, and reclustering is unrelated. This aligns with Snowflake documentation on external tables and metadata refresh behavior.
- A. Correct.
Correct. External tables rely on metadata about the files in the external location. If new files are added in S3, Snowflake does not query the cloud storage namespace on every SELECT; instead, the external table must be refreshed so Snowflake updates its file metadata. Using REFRESH manually or automated refresh/integration options where supported is the correct way to reduce lag between file arrival and query visibility.
- B. Incorrect.
Incorrect. Suspending or resuming a warehouse does not control external table metadata visibility. Metadata for external tables is maintained by Snowflake separately from warehouse state. This option reflects a common misconception that warehouse lifecycle operations clear all query-related caches or metadata.
- C. Incorrect.
Incorrect. The result cache affects reuse of prior query results, but it does not update the external table's file metadata. Even if result caching is disabled, Snowflake still depends on the external table metadata to know which files exist. Therefore, this would not make newly added S3 files visible sooner.
- D. Incorrect.
Incorrect. Reclustering applies to table micro-partition organization for Snowflake-managed tables and is not the mechanism for discovering new files in an external stage. External tables do not use reclustering to refresh file listings. This distractor targets confusion between table optimization features and external metadata management.