How to bulk load data into Snowflake with COPY INTO

What and why

Snowflake loads data efficiently from files using the COPY INTO command, which reads staged files in parallel into a table. Stages point at cloud storage or internal Snowflake storage, and file formats describe how to parse the data. This tutorial loads CSV data with error handling.

Prerequisites

• A Snowflake account with a usable warehouse and role.
• Data files, for example CSVs, available locally or in S3/GCS/Azure.
• Privileges to create tables, stages, and file formats.

Steps

1. Create a target table

CREATE TABLE orders (id NUMBER, customer_id NUMBER, amount NUMBER, created_at TIMESTAMP);

2. Define a file format

CREATE FILE FORMAT csv_fmt
  TYPE = CSV FIELD_OPTIONALLY_ENCLOSED_BY = '"' SKIP_HEADER = 1;

3. Create a stage

For an external S3 bucket:

CREATE STAGE orders_stage
  URL = 's3://my-bucket/orders/'
  STORAGE_INTEGRATION = s3_int
  FILE_FORMAT = csv_fmt;

For local files, use the internal user stage and PUT.

4. Stage the files

For local data, upload with SnowSQL:

PUT file:///data/orders/*.csv @orders_stage;

External stages already see files in the bucket; list them with LIST @orders_stage;.

5. Run COPY INTO

COPY INTO orders
  FROM @orders_stage
  FILE_FORMAT = (FORMAT_NAME = csv_fmt)
  ON_ERROR = 'CONTINUE';

ON_ERROR = 'CONTINUE' loads valid rows and skips bad ones; use ABORT_STATEMENT to fail the whole load instead.

6. Inspect load errors

SELECT * FROM TABLE(VALIDATE(orders, JOB_ID => '_last'));

This returns rejected rows and the reason, so you can fix the source data.

Verification

SELECT count(*) FROM orders should match the expected row count. The COPY INTO output reports files loaded, rows parsed, and errors. Re-running COPY INTO skips already-loaded files because Snowflake tracks load metadata.

Next Steps

Automate continuous loading with Snowpipe, load semi-structured JSON into a VARIANT column, and use a transformation COPY to reshape columns during load.