External Table Partition
6 0
Read Time:1 Minute, 33 Second
Partition table

In our last post, we talked about the External tables, their properties and types of External tables. During this article will discuss about one Step ahead i.e. External tables partition. As Snowflake recommends , External table should be partitioned to get the maximum performance benefit while querying the data lies outside the Snowflake.

Therefore,Partitioning divides your external table data into multiple parts using partition columns. Partition columns must evaluate as expressions that parse the path and/or filename information stored in the METADATA$FILENAME pseudo column.

Advantages:
  • External tables can be created instead of loading data into Snowflake. This would be useful when only a portion of data is needed.
  • The External tables are commonly use to build the data lake where you access the raw data which store in the form of file and perform join with existing tables.

However, all external tables include the following columns:
VALUE : A VARIANT type column that represents a single row in the external file.
METADATA$FILENAME : A pseudo column that identifies the name of each staged data file included in the external table, including its path in the stage.

METADATA$FILENAME pseudo column helps to create/identify the partition columns on the External table with below query:
select metadata$filename from @<<stage name>>;

Scenario: Say, we are getting Demographic data on daily basis in the Parquet format. This is the raw data i.e. information about the Customers and we don’t need to ingest this data into Snowflake and require only few attributes of data.
So instead of ingesting such huge amount of Raw data into the Snowflake, External tables points to the S3 Location and will just query this data inside the snowflake.

Prerequisites:

  • Parquet files used in process.
  • File Structure
  • AWS File folder structure
  • AWS credentials

For Technical Steps, Please Click here.

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

Leave a Reply

Your email address will not be published. Required fields are marked *