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
- Files land in an S3 bucket
- Create an IAM policy and role for Snowflake in AWS
- Create a storage integration in Snowflake for that role
- Create an external stage on the bucket path using the integration
- Does LIST @stage show the files? Yes: Run COPY INTO the target table. No: Fix the IAM trust policy or allowed locations.
- 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 policyKey points
- Storage integrations avoid embedding AWS keys in SQL
- Loads CSV, JSON, Avro, ORC, Parquet and XML directly from S3
- COPY INTO loads many files in parallel in one statement
- STORAGE_ALLOWED_LOCATIONS limits which paths stages can use
- Keep the bucket in the same region as the account to avoid transfer costs