Question 24 - DEA-C01 discussion

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 multidimensional 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 expres-sions 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 updated 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 service for your cloud storage service.