What are the Correct Statements to configure Partition metadata refresh in case of External Tables?

Snowflake computes and adds partitions based on the defined partition column expressions when an external table metadata is refreshed.

What are the Correct Statements to configure Partition metadata refresh in case of External Tables?
A . By default, the metadata is refreshed automatically when the object is created.
B . The object owner can configure the metadata to refresh automatically when new or updated data files are available in the external stage.
C . Metadata refresh is not required as its Managed implicitly by Snowflake.
D . Partitions of External tables is managed by External Stage Cloud provider.
E . There is nothing like adding partitions on External tables.

View Answer

Answer: A, B

Explanation:

Snowflake strongly recommend partitioning your external tables, which requires that your underlying data is organized using logical paths that include date, time, country, or similar dimensions in the path.

Partitioning divides your external table data into multiple parts using partition columns.

An external table definition can include multiple partition columns, which impose a multi-

dimensional structure on the external data.

Partitions are stored in the external table metadata.

Benefits of partitioning include improved query performance.

Because the external data is partitioned into separate slices/parts, query response time is faster when processing a small part of the data instead of scanning the entire data set. Based on your individual use cases, you can either:

・ Add new partitions automatically by refreshing an external table that defines an expression for each partition column.

・ Add new partitions manually.

Partition columns are defined when an external table is created, using the CREATE EXTERNAL TABLE … PARTITION BY syntax.

After an external table is created, the method by which partitions are added cannot be changed. Partitions Added Automatically

An external table creator defines partition columns in a new external table as expressions that parse the path and/or filename information stored in the METADATA$FILENAME pseudocolumn.

A partition consists of all data files that match the path and/or filename in the expression for the partition column.

The CREATE EXTERNAL TABLE syntax for adding partitions automatically based on expressions is as follows:

CREATE EXTERNAL TABLE

<table_name>

( <part_col_name> <col_type> AS <part_expr> )

[,…]

[ PARTITION BY ( <part_col_name> [, <part_col_name> … ] ) ] ..

Snowflake computes and adds partitions based on the defined partition column expressions when an external table metadata is refreshed.

By default, the metadata is refreshed automatically when the object is created.

In addition, the object owner can configure the metadata to refresh automatically when new or up-dated data files are available in the external stage.

The owner can alternatively refresh the metadata manually by executing the ALTER EXTERNAL TABLE … REFRESH command.

The metadata for an external table can be refreshed automatically using the event notification ser-vice for your cloud storage service.