TopolearnCOPY INTO is the standard way data gets into Snowflake, and its load history prevents duplicate loads.
Bulk loading has three pieces.
Stages are locations of files. There are internal stages managed by Snowflake: the user stage @~, a table stage @%orders, and named stages CREATE STAGE my_stage. There are also external stages, which point at your own S3, GCS or Azure location, usually through a storage integration so no keys are embedded. Upload local files to an internal stage with PUT file://... @stage from SnowSQL, the Snowflake CLI or a driver, or with the upload dialog in Snowsight.
File formats describe the files: TYPE = CSV with options such as SKIP_HEADER, FIELD_OPTIONALLY_ENCLOSED_BY, NULL_IF, or TYPE = JSON | PARQUET | AVRO | ORC | XML. Save a format as a named object and reuse it.
COPY INTO table FROM @stage loads files in parallel on your warehouse. Important behaviour:
- Snowflake remembers which files it loaded into each table for 64 days and skips them on later runs, so rerunning a COPY is safe.
FORCE = TRUEoverrides this. ON_ERRORdefaults toABORT_STATEMENT;CONTINUEorSKIP_FILEtolerate bad rows or files.VALIDATION_MODE = RETURN_ERRORSdry-runs the load and reports problems.- For Parquet and other columnar files,
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVEmaps columns by name.
Files of roughly 100 to 250 MB compressed load most efficiently, because each file is processed by one thread.
Example
CREATE FILE FORMAT csv_fmt TYPE = CSV SKIP_HEADER = 1
FIELD_OPTIONALLY_ENCLOSED_BY = '"' NULL_IF = ('', 'NULL');
CREATE STAGE raw_stage FILE_FORMAT = csv_fmt;
-- From SnowSQL / Snowflake CLI: upload (and gzip) a local file
PUT file:///tmp/orders_2025_06.csv @raw_stage/orders/;
LIST @raw_stage/orders/;
-- Check for problems without loading anything
COPY INTO orders FROM @raw_stage/orders/ VALIDATION_MODE = RETURN_ERRORS;
COPY INTO orders FROM @raw_stage/orders/
PATTERN = '.*orders_.*[.]csv[.]gz'
ON_ERROR = 'SKIP_FILE';
-- file | status | rows_parsed | rows_loaded ...
-- raw_stage/orders/orders_2025_06.csv.gz | LOADED | 1200 | 1200
COPY INTO orders FROM @raw_stage/orders/; -- run again: 0 files loaded (already loaded)Remember
- Stage (where files are) + file format (what they are) + COPY INTO (load)
- COPY skips files already loaded into that table within 64 days
- Use VALIDATION_MODE to test and ON_ERROR to control failures
Watch out for
- Using FORCE = TRUE to 'retry' a load and ending up with duplicated rows.