COF-C03 Question 290
Single answerMetadata cacheA data engineering team stores raw CSV files in an external Amazon S3 stage and queries them through an external table in Snowflake. During testing, new files were added to the S3 location, but a query against the external table did not return rows from those new files. The files are present in S3 and match the table's location and file format. Which action should the engineer take to make Snowflake recognize the newly added files as quickly as possible?
- A
Run ALTER EXTERNAL TABLE ... REFRESH to update the external table metadata
- B
Resume the virtual warehouse used for the query so the metadata cache is rebuilt
- C
Run ALTER STAGE ... REFRESH to invalidate the result cache for the stage
- D
Execute SELECT ... with USE_CACHED_RESULT = FALSE so Snowflake re-reads the S3 location immediately
Show answer and explanation
Correct answer: A
Explanation
This scenario tests understanding of Snowflake metadata caching and file discovery for external tables. For external tables over data in external cloud storage, Snowflake maintains metadata about the files available for query. If new files are added after the table metadata was last synchronized, queries may not see them until the metadata is refreshed. The correct operational step is ALTER EXTERNAL TABLE ... REFRESH. Candidates often confuse this with warehouse cache, result cache, or stage directory refresh operations, but those address different layers of Snowflake's architecture. Snowflake documentation on external tables explains that file-level metadata must be refreshed so newly added files are available for querying.
- A. Correct.
Correct. External tables rely on Snowflake-maintained metadata about files in the external stage location. When new files are added, Snowflake does not automatically query the cloud storage path on every SELECT. Running ALTER EXTERNAL TABLE ... REFRESH updates the registered file metadata so the new files become visible to queries as soon as the refresh completes. This is the standard operational action when metadata is stale for an external table.
- B. Incorrect.
Incorrect. Resuming or changing a virtual warehouse affects compute resources and local disk cache behavior, not external table file metadata. Metadata for external tables is maintained separately from warehouse execution state. A warehouse restart will not cause Snowflake to discover newly added files in S3 for an existing external table.
- C. Incorrect.
Incorrect. ALTER STAGE ... REFRESH applies to directory tables associated with stages, not to refreshing external table metadata for queryable external table files. It also does not invalidate the result cache in the way described. This option reflects a common confusion between stage directory metadata and external table metadata.
- D. Incorrect.
Incorrect. USE_CACHED_RESULT = FALSE bypasses the persisted query result cache, forcing query re-execution, but it does not refresh the external table's file metadata. If Snowflake's external table metadata has not been refreshed, re-running the query still will not include files that Snowflake has not yet registered.