Working with Snowflake External Tables and S3 Examples

Snowflake External tables allow you to access files stored in external stage as a regular table. You can join the Snowflake external table with permanent or managed table to get required information or perform the complex transformations involving various tables. The External tables are commonly used to build the data lake where you access the raw data which is stored in the form of file and perform join with existing tables.

Snowflake External Tables

As mentioned earlier, external tables access the files stored in external stage area such as Amazon S3, GCP bucket, or Azure blob storage.

You can create a new external table in the current/specified schema. You can also replace an existing external table. When queried, an external table reads data from a set of one or more files in a specified external stage and outputs the data in a single VARIANT (JSON) column. The VARIANT column name would be VALUE. You write the expression to extract the values from VALUE fields.

DML on the Snowflake External Table

External tables are read-only, therefore no DML operations can be performed on them. However, you can use external tables for query and join operations. Views can be created against external tables.

Querying data stored external to the Snowflake database is likely to be slower than querying native database tables; however, materialized views based on external tables can improve query performance.. Note that, materialized views are available starting from Snowflake enterprise version.

Steps to Create Snowflake External Table

You can follow the below steps to create external tables on Cloud data warehouse.

Create File Format
Create External Stage for External Storage (S3, GCP bucket, Azure Blob)
Define or Create External Table using external stage location

Note that, for simplicity, we are going to use Amazon S3 as an external Stage.

Test CSV File

We have already uploaded following CSV file to S3 bucket.

ID,NAME,DEPT
1234567890,aaa,32114
0987654123,bbb,45789
1122445521,ccc,75896
7845951656,ddd,45795
5645798965,xyz,45564
4547858551,vvd,84654

Define or Create File format

The first step is to create file format.

For example, we have CSV file so we have to create a CSV file format. Note that, you can directly mention the file format on STAGE object, but, it is always best practice to create file format.

Following is the file format example.

create or replace file format mys3csv 
type = 'CSV' 
field_delimiter = ',' 
skip_header = 1;

Create External Stage Object (S3 Stage)

Secondly, create an external stage for Amazon S3 bucket.

For example, consider following example.

create or replace stage MYS3STAGE url='s3://snow-testing/testfiles/' CREDENTIALS=(aws_key_id='ABCDRHDKSNF' aws_secret_key='***********') file_format = mys3csv;

Create Snowflake External Table

The Third step would be to create an external table by providing external stage as a location.

There are two types of external tables that you can create.

External Table without Column Names
External Tables with Column Names

Snowflake External Table without Column Details

Following example allow you to create an external table without a column Name.

create or replace external table sample_ext 
with location = @mys3stage file_format = mys3csv;

Now, query the external table. Note that, we have derived the column names from the VALUE VARIANT column.

select 
value:c1::int as ID, 
value:c2::varchar as name , 
value:c3::int as dept from sample_ext;

+------------+------+-------+
|         ID | NAME |  DEPT |
|------------+------+-------|
| 1234567890 | aaa  | 32114 |
|  987654123 | bbb  | 45789 |
| 1122445521 | ccc  | 75896 |
| 7845951656 | ddd  | 45795 |
| 5645798965 | xyz  | 45564 |
| 4547858551 | vvd  | 84654 |
+------------+------+-------+

Snowflake External Tables with Column Names

Create a column expression on VALUE json object.

For example, consider following

create or replace external table sample_ext 
(ID INT as  (value:c1::int), 
Name varchar(20) as ( value:c2::varchar), 
dept int as (value:c3::int))
with location = @mys3stage
file_format = mys3csv;

Check the records

select id, name, dept from sample_ext;
+------------+------+-------+
|         ID | NAME |  DEPT |
|------------+------+-------|
| 1234567890 | aaa  | 32114 |
|  987654123 | bbb  | 45789 |
| 1122445521 | ccc  | 75896 |
| 7845951656 | ddd  | 45795 |
| 5645798965 | xyz  | 45564 |
| 4547858551 | vvd  | 84654 |
+------------+------+-------+