How to Create an S3 Stage in Snowflake: Step-by-Step Guide
To create a stage for Amazon S3 in Snowflake, use the
CREATE STAGE command with the URL pointing to your S3 bucket and specify your AWS credentials or an IAM role. This stage acts as a pointer to your S3 location for loading or unloading data.Syntax
The CREATE STAGE command defines a named location in Snowflake that points to an external storage like Amazon S3. You specify the S3 bucket URL and provide authentication details.
- stage_name: The name you assign to the stage.
- URL: The S3 bucket path, e.g.,
s3://my-bucket/path/. - CREDENTIALS: AWS credentials or IAM role for access.
- FILE_FORMAT: Optional, defines the file format for data files.
sql
CREATE STAGE stage_name URL='s3://bucket-name/path/' CREDENTIALS=(AWS_KEY_ID='your_key_id' AWS_SECRET_KEY='your_secret_key') FILE_FORMAT=(TYPE='CSV' FIELD_DELIMITER=',' SKIP_HEADER=1);
Example
This example creates a stage named my_s3_stage that points to an S3 bucket my-example-bucket/data/. It uses AWS access keys for authentication and sets the file format to CSV with a header row skipped.
sql
CREATE STAGE my_s3_stage URL='s3://my-example-bucket/data/' CREDENTIALS=(AWS_KEY_ID='AKIAEXAMPLEKEY' AWS_SECRET_KEY='wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY') FILE_FORMAT=(TYPE='CSV' FIELD_DELIMITER=',' SKIP_HEADER=1);
Output
Stage MY_S3_STAGE successfully created.
Common Pitfalls
- Using incorrect or expired AWS credentials will cause authentication errors.
- Not specifying the correct S3 bucket URL or path leads to file not found errors.
- For better security, prefer using an
IAM_ROLEinstead of AWS keys. - Omitting
FILE_FORMATcan cause data load errors if the file format is not default.
sql
/* Wrong: Missing credentials */ CREATE STAGE bad_stage URL='s3://my-bucket/data/'; /* Right: Using IAM role for better security */ CREATE STAGE secure_stage URL='s3://my-bucket/data/' CREDENTIALS=(AWS_IAM_ROLE='arn:aws:iam::123456789012:role/MySnowflakeRole');
Quick Reference
| Parameter | Description | Example |
|---|---|---|
| stage_name | Name of the Snowflake stage | my_s3_stage |
| URL | S3 bucket path URL | s3://my-bucket/path/ |
| CREDENTIALS | AWS keys or IAM role for access | AWS_KEY_ID and AWS_SECRET_KEY or AWS_IAM_ROLE |
| FILE_FORMAT | Defines file type and options | TYPE='CSV' FIELD_DELIMITER=',' SKIP_HEADER=1 |
Key Takeaways
Use CREATE STAGE with URL and CREDENTIALS to connect Snowflake to S3.
Prefer IAM roles over AWS keys for better security.
Always specify FILE_FORMAT to match your data files.
Check your S3 path and credentials carefully to avoid errors.
