1 0
Read Time:2 Minute, 48 Second

During this post we will discuss about AUTO_REFRESH property on External tables. We know in an external table, the data stores in files in an external stage. External tables are read-only, therefore no DML operations can be perform on them. As per our requirement we receive feed file holds 5M records. This data need not to be ingest in snowflake and will use it for querying purpose only. So instead of consuming this data into Snowflake we have created the External table. Later on map the location of External stage with External table.

However, Down the time we expects the next set of Delta records/file would be uploaded to the External stage. So as per the ask new file needs to be refresh automatically to the External table. Hence, there should not be manual ALTER REFRESH command to be perform. Moreover if we purge the file from  the bucket then should reflect on External table count as well. Therefore, To cater this scenario AUTO_REFRESH property  comes into the significance.

Firstly, We have bucket:

External Bucket

Secondly, Create the stage:

create or replace stage demo_db.public.ext_csv_stage
URL = 's3://autorefreshexternalbucket/'
STORAGE_INTEGRATION = s3_int
file_format = demo_db.public.csv_format;

Thirdly, Create External Table:

create or replace external table colors
(Color_Code varchar(10) as  (value:c1::varchar),
Color varchar(20) as ( value:c2::varchar),
color_Type varchar(10) as (value:c3::varchar))
with location = @demo_db.public.ext_csv_stage
auto_refresh = true
file_format = csv_format;

Query on External Table

show external tables;

External table notification

Finally, Configure this notification value on Bucket the same way we do while Snowpipe configure.

EXTERNAL_TABLE_FILES:
This table function can be used to query information about the staged data files included in the metadata for a specified external table.

External_Table_File

EXTERNAL_TABLE_FILE_REGISTRATION_HISTORY: This table function can be used to query information about the metadata history for an external table, including:
Files added or removed automatically as part of a metadata refresh.

External_table_File_registration

Now upload the new file, say color_file1.csv to the bucket.

Therefore, An SQS notification would trigger and count of the External Table would be updated. No manual intervention is require and changes reflect automatically.

External Table count

Metadata andRegistration:

Verify the metadata and registration of new file in External table.

External Tables functions

Now we will purge the file from the Bucket and will observe the behaviour on External table.

File Delete

We can verify the last perform action by the SQS on file delete.

External table pipe status

Now verify the metadata and registration of purge file in External table.

External Table File Delete

Verify the latest count.

External table latest count

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 *