Connect AWS S3 to Snowflake

Snowflake · Cloud Integrations

Theory

To read files in Amazon S3, Snowflake uses an external stage: a named pointer to an S3 URL such as s3://my-bucket/data/, plus a file format and a way to authenticate. The recommended way to authenticate is a storage integration, which lets Snowflake assume an IAM role in your AWS account instead of storing access keys in SQL. The data stays in your bucket until COPY INTO reads it, so there is no separate ETL tool in the middle.

COPY reads CSV, JSON, Avro, ORC, Parquet and XML, and spreads the files across the warehouse's nodes, so loading a folder of a thousand files is one statement. The same stage can also be used for unloading data back to S3.

Why we use it

Most companies already land data in S3 from apps, logs and other systems. Connecting Snowflake directly to the bucket avoids extra copies and tools, and keeps credentials out of scripts.

When to use it

Use an S3 external stage whenever your source files are in S3 or you need to export files there. Use a storage integration for anything beyond a quick test; embedded keys are hard to rotate and easy to leak.

Why Connect to S3

An external stage pointed at an S3 bucket lets Snowflake read or write files directly in your AWS storage, without copying data through a separate ETL tool first.

Supported File Formats

CSV, JSON, Parquet, and Avro all work out of the box, each configured through a file format object that tells COPY INTO how to interpret the files.

Loading at Scale

COPY INTO can load thousands of files from a single stage in one parallelized statement, tracking which files were already loaded so re-runs don't duplicate data.

How the Pieces Connect

AWS S3 Bucket Snowflake ┌───────────┐ ┌─────────────────┐ │ data/ │ ◄── Trust ──► │ Storage │ │ file1.csv│ │ Integration │ │ file2.csv│ │ (IAM Role ARN) │ └───────────┘ └────────┬────────┘ │ ┌────────▼────────┐ │ External Stage │ │ @my_s3_stage │ └────────┬────────┘ │ ┌────────▼────────┐ │ COPY INTO table │ └─────────────────┘

How it works

  1. Files land in an S3 bucket
  2. Create an IAM policy and role for Snowflake in AWS
  3. Create a storage integration in Snowflake for that role
  4. Create an external stage on the bucket path using the integration
  5. Does LIST @stage show the files? Yes: Run COPY INTO the target table. No: Fix the IAM trust policy or allowed locations.
  6. Data is loaded and queryable

Example

CREATE OR REPLACE STORAGE INTEGRATION s3_integration
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'S3'
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/snowflake-access-role'
  STORAGE_ALLOWED_LOCATIONS = ('s3://my-bucket/data/');

DESC INTEGRATION s3_integration;
-- Use STORAGE_AWS_IAM_USER_ARN and STORAGE_AWS_EXTERNAL_ID in your AWS IAM trust policy

Key points

Snowflake topics

Snowflake Fundamentals

Warehouses & Performance

Staging & SnowSQL

Tables & Data Handling

Cloud Integrations

Storage & Optimization

Security & Governance

Views, Pipelines & Automation

Data Types & SQL Fundamentals

Semi-Structured Data Deep Dive

Advanced Features

Data Sharing & Collaboration

Cost Management & Monitoring

Databases, Schemas & Objects

Performance Deep Dive

Advanced Security & Governance

Stored Procedures & UDFs

Ecosystem & Integrations

SnowPro Certification Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Mastering Snowflake

The modern data cloud, from architecture to SnowPro certification. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice Snowflake SQL Sharpen the SQL you use in Snowflake: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Snowflake  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Unlike the SQL and Python courses, Snowflake needs a real account — there's no in-browser engine for it. Click Copy above, then paste it into a free Snowflake trial (30 days, no card required) and run it in Snowsight.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic