Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

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 LOCATION URL or missing permissions to access the external storage.
  • Not defining or using the correct FILE_FORMAT matching 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

ClauseDescription
CREATE EXTERNAL TABLEStarts the external table creation.
(columns)Defines columns and their data types.
WITH LOCATIONSpecifies the external storage path.
FILE_FORMATDefines the format of external files.
AUTO_REFRESHOptional; 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.