John, Data Engineer, do have technical requirements to refresh the External tables Metadata periodically or in auto mode, which approach John can take to meet this technical specification?

John, Data Engineer, do have technical requirements to refresh the External tables Metadata periodically or in auto mode, which approach John can take to meet this technical specification?

A. John can use AUTO_REFRESH parameter if the underlying External Cloud host sup-ports this for External tables.

B. He can create a task that executes an ALTER EXTERNAL TABLE … REFRESH statement every 5 minutes.

C. External table cannot be scheduled via Snowflake Tasks, 3rd party tools/scripts needs to be used provided by External cloud storage provider.

D. Snowflake implicitly take care this Infrastructure needs, as underlying warehouse layer internally manage the refresh. No action needed from John.

View Answer

Answer: B

Explanation:

External tables in Snowflake do not automatically reflect changes made to the underlying data in external cloud storage. To see these changes in Snowflake, you need to refresh the external table metadata.

Let’s go through the options:

A. As of my last update in January 2022, Snowflake does not have an AUTO_REFRESH parameter for external tables.

B. This is feasible. Creating a Snowflake task to periodically run an ALTER EXTERNAL TABLE … REFRESH statement can update the metadata of the external table, making it reflect any changes in the underlying data.

C. This statement is incorrect. Although external tables reference data outside of Snowflake, they can still be acted upon with Snowflake SQL commands, including being refreshed via a Snowflake task.

D. This is also incorrect. Snowflake does not automatically refresh the metadata for external tables. An explicit refresh action is needed.

The best approach for John to meet the technical specification, based on the given options, is:

B. He can create a task that executes an ALTER EXTERNAL TABLE … REFRESH statement every 5 minutes.