ADA-C01 Question 533
Single answerExternal tablesA data engineering team stores hourly JSON files in an Amazon S3 bucket and exposes them through a Snowflake external table for near-real-time reporting. The external table was created successfully and initially returned rows. After a new batch of files was added to the same S3 prefix, analysts reported that queries against the external table did not show the new data. The storage integration, stage, and file format are unchanged, and the new files match the existing pattern. Which action should the Snowflake administrator take to make the newly added files queryable as quickly as possible?
- A
Run ALTER EXTERNAL TABLE <table_name> REFRESH to update the external table metadata with the newly discovered files.
- B
Run ALTER STAGE <stage_name> REFRESH so the external table automatically reloads all partitions from the stage metadata.
- C
Recreate the external table so Snowflake rescans the S3 location and rebuilds the file inventory.
- D
Run COPY INTO an internal table first; external tables cannot expose newly added files until they are loaded into Snowflake-managed storage.
Show answer and explanation
Correct answer: A
Explanation
External tables allow Snowflake to query files stored in external cloud storage, but they rely on maintained metadata about the files and partitions available under the associated stage path. When new files arrive, Snowflake must become aware of them before they appear in query results. The direct administrative action is ALTER EXTERNAL TABLE ... REFRESH, which updates that metadata. In some architectures, auto-refresh can be configured for supported cloud event notification integrations, but when data is missing and an immediate fix is required, a manual refresh is the practical response. This aligns with Snowflake best practices for managing external table metadata and understanding the distinction between stage metadata features, directory tables, and external table refresh behavior.
- A. Correct.
Correct. External tables store file-level metadata about objects in the referenced external stage. When new files are added to cloud storage, Snowflake does not necessarily query them until the external table metadata is refreshed. Running ALTER EXTERNAL TABLE ... REFRESH updates the metadata so the new files become visible to queries. This is the standard manual action when auto-refresh is not configured or has not yet processed the change.
- B. Incorrect.
Incorrect. ALTER STAGE ... REFRESH is associated with directory tables for stages, not with updating the metadata used by an external table. Although stages and external tables are related, refreshing the stage itself does not replace the need to refresh the external table metadata when new files need to be discovered for external-table queries.
- C. Incorrect.
Incorrect. Recreating the external table would be unnecessary and operationally disruptive. Snowflake provides a supported metadata refresh operation specifically for this use case. Recreating the object could also introduce risk around permissions, dependent objects, and query downtime.
- D. Incorrect.
Incorrect. External tables are designed to query data in external cloud storage without loading it into Snowflake-managed storage. COPY INTO is only needed if the requirement is to ingest data into a native Snowflake table, not to expose newly added files through an external table.