External tables enable querying data stored in ECS for analysis without first loading it into Snowflake. The CREATE EXTERNAL TABLE command references the external stage that we created in the previous section to allow connectivity to ECS.

This example uses the popular Iris parquet file. Here we are specifying the datatype for each column when creating the table. The location of the data is the named external stage that we created in the previous example.

create or replace external table ext_table_dellecs (

  sepal_length double  as  (value:sepal_length::double),

  sepal_width  double  as  (value:sepal_width::double),

  petal_length double  as  (value:petal_length::double),

  petal_width  double  as  (value:petal_length::double),

  class        varchar as  (value:class::varchar)

)

location = @ext_stage_dellecs

auto_refresh = false

refresh_on_create = true

file_format = (type = parquet);

Note that auto_refresh is set to false because it is not currently part of Snowflake’s support for S3-compatible storage. If new objects are ingested, modified, or removed from the ECS bucket, you can manually initiate a refresh with the following command:

alter external table ext_table_dellecs refresh;

For further simplification, you can create a Snowflake Task to periodically refresh the table.