- External tables store file-level metadata about the data files, such as the filename, a version identifier and related properties.
- Can access data stored in any format supported by COPY INTO <table> statements.
- Being Read-only in nature therefore, no DML operations can be performed on them. However, used for query and join operations.
- We can create views for these tables, However ,Querying data stored external to the database is likely to be slower than querying native database tables;
- Materialized views based on external tables can improve query performance.
- Strongly recommend partitioning these tables..
- There are two types of external table that you can create.
- Table without Column Names.
- Table with Column Names
All external tables include the following columns:
- VALUE : A VARIANT type column that represents a single row in the external file.
- METADATA$FILENAME:A pseudocolumn that identifies the name of each staged data file included in the table, including its path in the stage.
If you are familiar with the schema of the source data files, you can create additional virtual columns as expressions using the VALUE column and/or the METADATA$FILENAME pseudo column.
Periodically executing an ALTER EXTERNAL TABLE … REFRESH statement to register any missed files.
Billing for Refreshing Table Metadata
There is an overhead i.e charges to manage event notifications (for the files added in cloud storage) . This overhead increases in relation to the number of files added in cloud storage for the external stages and paths specified for your tables.
Snowflake charges 0.06 credits per 1000 event notifications received.
Query tables just as you would standard tables. Files scanned partially and return the rows scanned before the error was encountered
To Create and Query External Table, Click here.