An external table makes files in a cloud-storage external stage queryable with standard SQL while keeping the data outside Snowflake. It is read-only: queries can read the data, but DML cannot modify it. This avoids Snowflake storage costs and preserves access for other systems, although performance is slower than querying regular Snowflake tables. section 3.4 section 3.5
Create and operate an external table by combining an external stage, file format, projected columns, and refresh behavior:
- Specify the external stage and known file format when defining the table. section 3.4.1
- Project fields from the VALUE VARIANT column with positional references such as c1, c2, and c3. section 3.4.1
- Use METADATA$FILENAME and METADATA$FILE_ROW_NUMBER to retain file and record context. section 3.4.1
- Refresh automatically through cloud-storage event notifications or manually with ALTER EXTERNAL TABLE when notifications are unavailable. section 3.4.1
Orders staged as CSV files
Define ORDERS_BISTRO_EXT over BISTRO_STAGE with ORDERS_CSV_FORMAT, parse customer and date fields from VALUE, and expose the source filename. Query it with SELECT * FROM ORDERS_BISTRO_EXT; run ALTER EXTERNAL TABLE ORDERS_BISTRO_EXT REFRESH when a manual refresh is needed. section 3.4.1
Figure 3.2 Data pipeline that takes CSV files from a cloud storage location, makes the files available to Snowflake in an external stage, presents the data as an external table, and persists the data in Snowflake as a materialized view
Trace the pipeline from CSV files in cloud storage through an external stage and external table into a materialized view stored in Snowflake. section 3.4
