How to Create External Table in Snowflake: Syntax and Example
To create an external table in Snowflake, use the
CREATE EXTERNAL TABLE statement specifying the table name, columns, and the external location (like an S3 bucket). This lets Snowflake query data stored outside its internal storage directly.Syntax
The CREATE EXTERNAL TABLE statement defines a table that references data stored outside Snowflake, such as in cloud storage. You specify the table name, columns with data types, the location of the external data, and the file format.
- table_name: Name of the external table to create.
- columns: List of columns and their data types.
- LOCATION: URL of the external storage location (e.g., S3 bucket path).
- FILE_FORMAT: Defines the format of the external files (e.g., CSV, Parquet).
- AUTO_REFRESH (optional): Enables automatic metadata refresh.
sql
CREATE EXTERNAL TABLE table_name ( column1 datatype, column2 datatype, ... ) WITH LOCATION = 'external_location_url' FILE_FORMAT = (format_name => 'file_format_name') [ AUTO_REFRESH = TRUE | FALSE ];
Example
This example creates an external table named sales_ext that reads CSV files stored in an S3 bucket. It defines three columns and uses a predefined CSV file format.
sql
CREATE FILE FORMAT my_csv_format TYPE = 'CSV' FIELD_DELIMITER = ',' SKIP_HEADER = 1 FIELD_OPTIONALLY_ENCLOSED_BY = '"'; CREATE EXTERNAL TABLE sales_ext ( order_id STRING, customer_id STRING, amount NUMBER ) WITH LOCATION = 's3://my-bucket/sales-data/' FILE_FORMAT = (format_name => 'my_csv_format') AUTO_REFRESH = TRUE;
Output
External table SALES_EXT created successfully.
Common Pitfalls
Common mistakes when creating external tables include:
- Incorrect
LOCATIONURL or missing permissions to access the external storage. - Not defining or using the correct
FILE_FORMATmatching the external data. - Forgetting to grant necessary privileges on the external stage or storage.
- Assuming external tables support all internal table features (they have some limitations).
Always verify the external data format and access rights before creating the table.
sql
/* Wrong: Missing FILE_FORMAT causes errors */ CREATE EXTERNAL TABLE wrong_table ( id STRING ) WITH LOCATION = 's3://my-bucket/data/'; /* Right: Specify FILE_FORMAT */ CREATE EXTERNAL TABLE correct_table ( id STRING ) WITH LOCATION = 's3://my-bucket/data/' FILE_FORMAT = (format_name => 'my_csv_format');
Quick Reference
| Clause | Description |
|---|---|
| CREATE EXTERNAL TABLE | Starts the external table creation. |
| (columns) | Defines columns and their data types. |
| WITH LOCATION | Specifies the external storage path. |
| FILE_FORMAT | Defines the format of external files. |
| AUTO_REFRESH | Optional; enables automatic metadata refresh. |
Key Takeaways
Use CREATE EXTERNAL TABLE with LOCATION and FILE_FORMAT to define external data.
Ensure the external storage path and permissions are correct.
Define a matching FILE_FORMAT for your external data files.
External tables query data outside Snowflake without loading it internally.
AUTO_REFRESH helps keep metadata in sync with external data changes.
