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
- New files arrive in S3
- Run COPY INTO table FROM @stage with PATTERN
- Was the file already loaded successfully? Yes: Skip it (unless FORCE = TRUE). No: Parse and load it.
- Apply ON_ERROR rules to bad rows
- Check COPY results or COPY_HISTORY
- 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
- PATTERN takes a regular expression matched against the file path
- Load metadata skips already-loaded files, preventing duplicates
- FORCE = TRUE reloads files and can create duplicate rows
- COPY_HISTORY shows what was loaded, when and with how many errors
- Snowpipe runs the same COPY automatically for continuous loading