Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

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_ROLE instead of AWS keys.
  • Omitting FILE_FORMAT can 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

ParameterDescriptionExample
stage_nameName of the Snowflake stagemy_s3_stage
URLS3 bucket path URLs3://my-bucket/path/
CREDENTIALSAWS keys or IAM role for accessAWS_KEY_ID and AWS_SECRET_KEY or AWS_IAM_ROLE
FILE_FORMATDefines file type and optionsTYPE='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.