Load Data from AWS S3 to Snowflake

Snowflake · Cloud Integrations

Theory

With an integration, a file format and an external stage in place, loading is one COPY INTO statement. You can point COPY at a folder (FROM @my_s3_stage/orders/), at named files (FILES = (...)) or at a regular expression (PATTERN = '.*sales_2024.*[.]csv'). COPY keeps load metadata for each table, so files it has already loaded successfully are skipped on the next run unless you use FORCE = TRUE.

For example, running the same COPY every hour picks up only the new files in the folder. When you want files loaded as soon as they arrive, wrap the same COPY in a Snowpipe with AUTO_INGEST = TRUE.

Why we use it

Bulk loading from S3 must be repeatable without loading the same file twice. Pattern matching plus load metadata gives you safe, incremental reruns without writing your own file-tracking logic.

When to use it

Use scheduled COPY for batch loads (hourly, daily). Use Snowpipe for continuous arrival. Use FORCE = TRUE only when you deliberately want to reload files, and consider PURGE = TRUE or lifecycle rules to clean up old files.

Putting the Pieces Together

With a stage and file format defined, COPY INTO bulk-loads matching files from S3 into a target table in one statement.

Pattern Matching and Incremental Loads

COPY INTO supports pattern matching to load only a subset of files, and automatically tracks which files were already loaded, so scheduled reruns only pick up new arrivals.

Going Fully Automated

Wiring the same stage to Snowpipe turns this manual COPY INTO into continuous, event-driven loading — new files load within seconds of landing, with no schedule to manage.

How it works

  1. New files arrive in S3
  2. Run COPY INTO table FROM @stage with PATTERN
  3. Was the file already loaded successfully? Yes: Skip it (unless FORCE = TRUE). No: Parse and load it.
  4. Apply ON_ERROR rules to bad rows
  5. Check COPY results or COPY_HISTORY
  6. Rerun on the next schedule or hand over to Snowpipe

Example

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

CREATE OR REPLACE STAGE my_s3_stage
  STORAGE_INTEGRATION = s3_integration
  URL = 's3://my-bucket/data/'
  FILE_FORMAT = my_csv_format;

LIST @my_s3_stage;

COPY INTO orders FROM @my_s3_stage/orders/
FILE_FORMAT = my_csv_format PATTERN = '.*[.]csv' ON_ERROR = 'CONTINUE';

SELECT * FROM TABLE(INFORMATION_SCHEMA.COPY_HISTORY(
  TABLE_NAME => 'ORDERS', START_TIME => DATEADD(HOUR, -24, CURRENT_TIMESTAMP())));

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