How to Create External Stage in Snowflake: Syntax and Example
To create an external stage in Snowflake, use the
CREATE STAGE command specifying the external storage location like an S3 bucket or Azure container. Include credentials and file format options to enable Snowflake to access and read data from the external source.Syntax
The CREATE STAGE command defines an external stage by specifying its name, URL to the external storage, credentials, and optional file format.
- stage_name: Name of the stage to create.
- URL: Location of the external storage (e.g., S3 bucket URL).
- CREDENTIALS: Access keys or IAM role for authentication.
- FILE_FORMAT: Defines how files are read (e.g., CSV, JSON).
sql
CREATE STAGE stage_name URL='external_storage_url' CREDENTIALS=(credential_type='value') FILE_FORMAT=(format_name='format_type');
Example
This example creates an external stage named my_s3_stage that points to an Amazon S3 bucket. It uses an AWS IAM role for authentication and specifies a CSV file format.
sql
CREATE STAGE my_s3_stage URL='s3://my-bucket/data/' CREDENTIALS=(AWS_ROLE='arn:aws:iam::123456789012:role/mySnowflakeRole') FILE_FORMAT=(TYPE='CSV' FIELD_DELIMITER=',' SKIP_HEADER=1);
Output
Stage MY_S3_STAGE successfully created.
Common Pitfalls
Common mistakes when creating external stages include:
- Using incorrect or missing credentials causing access failures.
- Specifying wrong URL format or missing trailing slash in the storage path.
- Not defining or mismatching the file format, leading to data read errors.
- Forgetting to grant Snowflake the necessary permissions on the external storage.
Always verify credentials and permissions before creating the stage.
sql
/* Wrong: Missing credentials */ CREATE STAGE bad_stage URL='s3://my-bucket/data/'; /* Right: Include credentials */ CREATE STAGE good_stage URL='s3://my-bucket/data/' CREDENTIALS=(AWS_KEY_ID='AKIA...' AWS_SECRET_KEY='secret');
Quick Reference
| Parameter | Description | Example |
|---|---|---|
| stage_name | Name of the external stage | my_s3_stage |
| URL | External storage location URL | s3://my-bucket/data/ |
| CREDENTIALS | Authentication details | AWS_ROLE='arn:aws:iam::123456789012:role/mySnowflakeRole' |
| FILE_FORMAT | File format options | TYPE='CSV' FIELD_DELIMITER=',' SKIP_HEADER=1 |
Key Takeaways
Use CREATE STAGE with URL and CREDENTIALS to define an external stage in Snowflake.
Always specify the correct file format to match your data files.
Ensure Snowflake has proper permissions to access the external storage.
Check URL syntax carefully, including trailing slashes for folders.
Test access with credentials before loading data from the stage.
