How to Load Data Products from Amazon S3 into Snowflake

If you’ve worked with data products, you’ve probably encountered this scenario:

"I have my data sitting nicely in an Amazon S3 bucket…Now I need to get it into Snowflake so my team can create data products, query and analyze the data, and customize orders so that we can share AI-ready data products with consumers."

You’re not alone - this is one of the most common use cases of Snowflake.

‍

Why?

‍

Because S3 is the most common used storage service for raw and intermediate data, and Snowflake is where the magic happens: transforming that data into AI-ready data products which can be used to power dashboards, models, and apps.

But here’s the catch: while it seems like it should just be a matter of “point and click,” loading data from S3 into Snowflake is more complex and technical than most people expect.

You have to create stages, write COPY INTO commands, configure file formats, set up permissions, and troubleshoot cryptic errors when formats or keys don’t line up.

It’s powerful, but it’s also fiddly — and not exactly friendly to business users or even busy data engineers.

‍

✅ The Easier Way

If you want to skip all of those technical steps - no stages, no COPY INTO, no IAM headaches - and just load your S3 data products into Snowflake in a few clicks, there’s a much simpler solution: Amplify Data Platform, powered by Monda.

Amplify automates the entire workflow for you:

• Point it at your S3 bucket.
• Select your target Snowflake table or let it create one.
• Click to load - and done.

No SQL scripts, no Snowflake stages, no guesswork.

You can even schedule loads or set up pipelines with minimal effort.

If you’d rather save time and focus on insights instead of infrastructure, check out Amplify and let it handle the heavy lifting for you. Book a demo.

Whether you want to understand the nuts and bolts of how loading data products from Amazon S3 into Snowflake works, or you just want to get your data flowing today using Amplify, this guide will walk you through both options.

‍

🧭 Technical Step-by-Step Guide to Loading Data from Amazon S3 into Snowflake

1. Prepare your data in S3

• Upload your files (e.g., CSV, JSON, Parquet) to an S3 bucket.
• Ensure the files have proper naming and format.
• Note down:
  • Bucket name
  • Folder path (prefix, if any)
  • AWS region

Define the structure of your data files (e.g., CSV, JSON, Parquet) by creating a file format object in Snowflake. This helps Snowflake interpret the files correctly during the load process.

CREATE OR REPLACE FILE FORMAT my_csv_format
TYPE = 'CSV'
FIELD_DELIMITER = ','
SKIP_HEADER = 1
NULL_IF = ('');


2. Grant Snowflake access to your S3 bucket

Snowflake needs IAM credentials to access your S3 bucket.

Option A: Use an AWS IAM User + Keys

• Create an IAM user in AWS with permissions to s3:GetObject and (optionally) s3:ListBucket.
• Generate Access Key ID and Secret Access Key.

Option B: Use an IAM Role (preferred for security)

• Create an IAM Role with a trust relationship to Snowflake’s AWS account.
• Attach a policy allowing s3:GetObject on your bucket.
• Copy the Role ARN.

You can also use AWS PrivateLink or Snowflake-managed storage integration if available.

3. Create a Stage in Snowflake

In Snowflake, a stage is a pointer to external storage.

Example: Using AWS keys

SQL

CREATE STAGE my_s3_stage
URL='s3://your-bucket-name/path/'
STORAGE_INTEGRATION = my_integration
-- or alternatively
CREDENTIALS = (AWS_KEY_ID = 'xxx' AWS_SECRET_KEY = 'yyy')
FILE_FORMAT = (TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY = '"' SKIP_HEADER = 1);


Or if using a Storage Integration

First create the integration:

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



Then attach it to a stage:

sql
CopyEdit
CREATE STAGE my_s3_stage
 URL = 's3://your-bucket-name/path/'
 STORAGE_INTEGRATION = my_integration
 FILE_FORMAT = (TYPE = 'CSV');



4. Create the target table

Define a table in Snowflake that matches the data you want to load:

sql
CopyEdit
CREATE TABLE my_table (
 column1 STRING,
 column2 INT,
 column3 DATE
);



5. Load the data

Use the COPY INTO command:

sql
CopyEdit
COPY INTO my_table
FROM @my_s3_stage
FILE_FORMAT = (TYPE = 'CSV')
PATTERN = '.*\\.csv';  -- optional: regex pattern to filter only .csv files



You can also validate before loading:

VALIDATE @my_s3_stage FILE_FORMAT = (TYPE='CSV');

6. Monitor the load

Run:

sql
CopyEdit
SELECT *
FROM information_schema.load_history
WHERE table_name = 'MY_TABLE';



• Monitor the Load Process:
• Use Snowflake's QUERY_HISTORY function or the web interface to monitor the status of your data load operations.
• Handle Errors:
• If errors occur, review the error messages to identify issues such as data type mismatches or formatting problems. Adjust your file format or data files accordingly.

‍

To Re-cap: 🛠 The Manual Way, Using Snowflake Stages + COPY INTO

If you’re doing it directly in Snowflake, here’s what the process looks like:

1. Create a Stage to Point at Your S3 Bucket

sql
CopyEdit
CREATE STAGE my_s3_stage
 URL='s3://my-bucket/data-products/'
 CREDENTIALS = (AWS_KEY_ID = 'your_key' AWS_SECRET_KEY = 'your_secret');



Or, if you’ve already set up a Snowflake storage integration:

sql
CopyEdit
CREATE STAGE my_s3_stage
 URL='s3://my-bucket/data-products/'
 STORAGE_INTEGRATION = my_integration;



2. Verify Your Files

sql
CopyEdit
LIST @my_s3_stage;



3️⃣ Load Into a Table

Make sure your table exists:

sql
CopyEdit
CREATE TABLE my_table ( … columns … );



Then load the data:

sql
CopyEdit
COPY INTO my_table
FROM @my_s3_stage
FILE_FORMAT = (TYPE = 'PARQUET');



Or for CSV:

sql
CopyEdit
COPY INTO my_table
FROM @my_s3_stage
FILE_FORMAT = (TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY='"');



It works — but it requires Snowflake privileges and some SQL knowledge to get right. You’ll also need to debug any file format mismatches or permissions issues.

‍

Alternatively: 🚀 The Easy Way, Using Amplify

If you want to skip all of that and just get your data loaded quickly, Amplify Data Platform makes it dead simple:

How it works:

✅ Log in to Amplify

✅ Connect your S3 bucket (via browser — no SQL)

✅ Select your Snowflake account and table

✅ Hit Load Data

That’s it.

No stages, no credentials pasted into SQL, no file format headaches.

Amplify handles authentication, staging, file parsing, and loading for you — and even gives you a nice UI to track progress and schedule future loads.

📋 Example Workflow in Amplify:

1. Click New Load Job
2. Select your S3 bucket and navigate to your file(s)
3. Choose Snowflake as the destination and pick or create a table
4. Configure optional transformations or schedules
5. Click Run

…and you’re done.

Skip these technical steps and load data products from Amazon S3 into Snowflake easily using Amplify (powered by Monda). Find out more or get a demo.

‍