Skip to main content

⚠️ ❄️ Load Using External Stage with "Direct AWS Credentials" ( Legacy )

This guide walks through how to load files from an Amazon S3 bucket into Snowflake using access keys, which is considered insecure and not recommended for production. This approach bypasses IAM Roles / Storage Integrations, opting instead to directly use static AWS access credentials.


❓ Why Is It Called Unsafe?​

It is called unsafe because AWS access keys behave like permanent passwords.


1️⃣ Keys Do Not Expire Automatically​

  • The access keys remain valid indefinitely
  • If keys are forgotten or leaked, access continues without interruption
  • There is no built-in time limit or session expiry

2️⃣ Keys Can Be Leaked Easily​

  • Stored in Snowflake metadata
  • Often copied into:
    • SQL scripts
    • Emails
    • Tickets or documentation
  • Sometimes accidentally committed to Git repositories

3️⃣ No Session Control​

  • You cannot limit access duration
  • You cannot enforce short-lived credentials
  • Key rotation must be done manually

🔍 What Is Happening Under the Hood?​


Snowflake
└── uses AWS_ACCESS_KEY_ID + AWS_SECRET_ACCESS_KEY
└── directly accesses S3 as an IAM user

This approach involves:

  • Static credentials
  • No role assumption
  • No temporary tokens
  • No automatic rotation

📌 1. What is "Unsafe Load"?​

"Unsafe" here refers to the use of AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY directly in your Snowflake STAGE definition. These keys:

CREDENTIALS = (
AWS_KEY_ID = '<your-access-key-id>'
AWS_SECRET_KEY = '<your-secret-access-key>'
)
  • Never expire unless rotated manually
  • Can be misused if leaked
  • Often end up hard-coded or shared improperly

✅ Use Case​

You may choose this method when:

  • You're just testing or prototyping
  • You don't want to create IAM roles and policies right away
  • You are working in a sandbox or demo environment

🧩 Overall Setup Summary​

▶️ AWS Side​

  1. ✅ Create an S3 bucket
  2. ✅ Upload your CSV file
  3. ✅ Create an IAM user
  4. ✅ Attach a policy that gives access to the S3 bucket
  5. ✅ Get the Access Key and Secret Access Key

▶️ Snowflake Side -​

Since it dont required heavy security set up.. we can directly access from STAGE like below..​

  1. ✅ Use the access credentials in a CREATE STAGE statement
  2. ✅ Run COPY INTO to load data into Snowflake
  3. ✅ Query the table to verify data

🛠️ AWS Setup Steps (Unsafe)​

🔹 1. Create an S3 Bucket​

  • Go to S3 Console
  • Click Create bucket
  • Name: unsafe-load-demo
  • Region: (e.g., us-east-1)
  • 🔒 Keep "Block all public access" enabled

🔹 2. Upload a Sample CSV File​

Example file name: south_indian_employees.csv Example file content:

Employee_ID,Name,Department,Joining_Date,Salary
1,Arun,Finance,2021-06-12,55000
2,Saravanan,Engineering,2020-04-01,72000
3,Divya,HR,2022-03-10,63000
4,Karthik,Sales,2019-11-21,50000
5,Meena,Support,2018-07-15,47000
6,Manoj,Engineering,2021-01-09,69000
7,Anjali,Marketing,2020-09-30,52000
8,Ramesh,Operations,2022-01-11,61000
9,Swathi,Finance,2017-10-05,58000
10,Vignesh,IT,2023-02-20,75000

🔹 3. Create an IAM User for Programmatic Access​

  • Go to IAM > Users > Add user
  • Name: snowflake-loader
  • ✅ Enable Programmatic access
    • Attach a policy manually (custom policy):

***For any doubts refer Snowflake + AWS S3 Access FAQs ***

{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "AllowListBucket",
"Effect": "Allow",
"Action": "s3:ListBucket",
"Resource": "arn:aws:s3:::unsafe-load-demo"
},
{
"Sid": "AllowObjectAccess",
"Effect": "Allow",
"Action": "s3:GetObject",
"Resource": "arn:aws:s3:::unsafe-load-demo/*"
}
]
}

  • Save the Access Key ID and Secret Access Key securely
  • MAKE SURE the slash should not come at the end; Entry should be "arn:aws:s3:::unsafe-load-demo" not "arn:aws:s3:::unsafe-load-demo/"

🧊 Snowflake Setup​

🔹 4. Create a Table for the CSV Structure​

CREATE OR REPLACE TABLE south_indian_employees (
Employee_ID INT,
Name STRING,
Department STRING,
Joining_Date DATE,
Salary NUMBER(10, 2)
);

🔹 5. Create a Snowflake Stage Using Access Keys​

CREATE OR REPLACE STAGE aws_s3_read_only_bucket_stage
URL = 's3://aws-s3-read-only-bucket/data/'
CREDENTIALS = (
AWS_KEY_ID = 'AKIATT4LUD2W56APV5VL'
AWS_SECRET_KEY = 'RF/V9Lil1lgGbV4FMNXw4Rgbe8ROWuUOZ4w2CQ8q'
)
FILE_FORMAT = (TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY='"' SKIP_HEADER=1);

  • ✅ Replace the placeholders with your actual keys and bucket name.

🔹 6. List Files in the Stage​

LIST @aws_s3_read_only_bucket_stage;

You should see: employees.csv


🔹 7. Load the Data into the Table​

COPY INTO south_indian_employees
FROM @aws_s3_read_only_bucket_stage/employees.csv;

🔹 8. Verify the Loaded Data​

SELECT * FROM south_indian_employees;

❓ Common Doubts​

Here is your cleaned-up, well-structured markdown-style content for the Common IAM Doubts in Unsafe Load Setup, including best practices and FAQ clarifications:


🧩 ❓ Common Doubts About IAM Policies in Unsafe Load Setup​


✅ 1. Did I Create an Inline or Managed Policy?​

If you went to:

IAM → Users → YourUser → Permissions tab → Add inline policy

...then you created an Inline Policy.

🔹 Inline Policy​

  • Attached only to one IAM user, group, or role
  • Cannot be reused elsewhere
  • Ideal for quick setups, like our "unsafe load" scenario

🔹 Managed Policy (AWS or Customer-Managed)​

  • Can be reused across users/roles
  • Can be versioned and centrally managed
  • Better for production and scale
  • ✅ In our case, Inline Policy is acceptable since it's a one-off, non-production setup.

✅ 2. Where Exactly Do I Paste the Custom S3 Policy?​

Here’s how to properly attach an inline policy:

  1. Go to IAM → Users → [your user]
  2. Click the Permissions tab
  3. Scroll to Permissions policies
  4. Click Add inline policy
  5. Select the JSON tab
  6. Paste the following:
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": ["s3:GetObject", "s3:ListBucket"],
"Resource": [
"arn:aws:s3:::unsafe-load-demo",
"arn:aws:s3:::unsafe-load-demo/*"
]
}
]
}
  1. Click Review policy, name it snowflakeS3ReadOnly
  2. Click Create policy

✅ 3. Is This Enough to Allow Snowflake to Access My S3 Bucket?​

Yes — this inline policy is sufficient, provided:

  • You use the correct bucket name/ARN
  • The correct access key and secret key are supplied in Snowflake
  • The bucket is not blocked by additional bucket policies

❗ No need to modify the bucket policy unless:

  • You're using Snowflake Storage Integrations with IAM roles
  • You require cross-account access
  • You want to enforce network/IP-based restrictions

✅ 4. Why Didn’t We Create a Bucket Policy?​

For unsafe loads via access keys, you don’t need a bucket policy.

✅ Access is controlled entirely via the IAM user policy.

Bucket policies are useful only when:

  • Using role-based authentication (Storage Integrations)
  • Granting resource-level permissions
  • Enforcing cross-account or public access controls

✅ 5. How Can I Confirm This Policy Works?​

After attaching the inline policy, test it with:

LIST @aws_s3_read_only_bucket_stage;

If the file appears, then:

  • The policy is correctly applied
  • Snowflake successfully connected to S3

If it fails:

  • ✅ Check region mismatch
  • ✅ Ensure no typos in keys
  • ✅ Confirm /* is present in the ARN for object-level access

❌ Important Risks of Using Inline Policies in Production​

🔺 IssueDescription
Locked to one userNot reusable or inheritable
No central visibilityCan't easily manage, audit, or rotate across users
Policy sprawlToo many ad-hoc policies become hard to track
Not best practiceAWS recommends Managed Policies for security and scale

✅ When Are Inline Policies Okay?​

Use Inline Policies only in:

  • ✅ Quick prototypes
  • ✅ Temporary developer accounts
  • ✅ Educational/training setups
  • ✅ Controlled, sandbox-only environments

🧠 Snowflake + AWS S3 Access FAQs​


❓ Where Do I Put the S3 Access Policy?​

➡️ In the IAM user's inline policy, as demonstrated in step 2 above.


❓ What Is the "S3 URL" Format for Snowflake?​

Snowflake expects the S3 path in URI format:

  • ✅ Correct: s3://unsafe-load-demo/ s3://unsafe-load-demo/data/employees.csv

❌ Incorrect: https://unsafe-load-demo.s3.amazonaws.com/data/employees.csv


❓ Should We Enable "S3 Bucket Key" for SSE-KMS?​

No — it's mainly used to reduce AWS KMS costs in encryption-heavy workloads.

➡️ For Snowflake access via access keys, disable it.


❓ Should We Enable "Block Public Access"?​

  • ✅ Yes — definitely keep it enabled.

Snowflake accesses the bucket privately using credentials, not via public URLs.


RiskDescription
❌ Static CredentialsAccess Keys don’t auto-rotate; high chance of leakage
❌ Hardcoding SecretsDevelopers may expose credentials in code repos
❌ No Fine-Grained ControlYou can't limit access duration like IAM roles
❌ Audit GapsDifficult to audit access compared to role assumption
❌ Not ScalableManaging many key-pairs across users/environments is tedious
❌ Vendor RecommendationsSnowflake strongly recommends Storage Integrations using IAM Roles

Instead of credentials, Snowflake assumes a secure IAM role via Storage Integration:

CREATE STORAGE INTEGRATION my_s3_int
TYPE = EXTERNAL_STAGE
STORAGE_PROVIDER = S3
ENABLED = TRUE
STORAGE_AWS_ROLE_ARN = '<your-iam-role-arn>'
STORAGE_ALLOWED_LOCATIONS = ('s3://your-bucket/');

This approach uses:

  • Short-lived tokens
  • Least privilege
  • Trust policy with External ID
  • No secrets stored in Snowflake

✅ Summary​

StepUnsafe Load Approach
S3 SetupCreate bucket, upload CSV
IAM SetupCreate user, attach read policy
SnowflakeCreate stage with AWS_KEY_ID and AWS_SECRET_KEY
Load DataCOPY INTO from the stage

🔐 For production, always prefer secure Storage Integrations using IAM roles.