Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

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

ParameterDescriptionExample
stage_nameName of the external stagemy_s3_stage
URLExternal storage location URLs3://my-bucket/data/
CREDENTIALSAuthentication detailsAWS_ROLE='arn:aws:iam::123456789012:role/mySnowflakeRole'
FILE_FORMATFile format optionsTYPE='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.