COPY INTO command in Snowflake - Commands & Configuration

Introduction

When you have data files stored outside Snowflake, you need a way to load that data into your tables. The COPY INTO command helps you quickly and safely move data from files into Snowflake tables.

When you want to load CSV files from cloud storage into a Snowflake table.

When you receive daily data exports and need to import them automatically.

When you want to bulk load JSON data into a Snowflake table for analysis.

When you need to reload data after fixing errors in the source files.

When you want to load data from an external stage like Amazon S3 or Azure Blob Storage.

Commands

This command creates a table named my_table with three columns to hold the data you will load.

Terminal

CREATE OR REPLACE TABLE my_table (id INT, name STRING, age INT);

Expected Output

Table MY_TABLE successfully created.

This command creates a named external stage pointing to an S3 bucket where your data files are stored. Snowflake uses this stage to access the files.

Terminal

CREATE OR REPLACE STAGE my_stage URL='s3://my-bucket/data/' CREDENTIALS=(AWS_KEY_ID='AKIAEXAMPLE' AWS_SECRET_KEY='abc123secret');

Expected Output

Stage MY_STAGE successfully created.

This command loads data from the CSV file in the stage into the my_table table. It uses a file format that matches the CSV structure.

Terminal

COPY INTO my_table FROM @my_stage/file.csv FILE_FORMAT = (TYPE = 'CSV' FIELD_DELIMITER = ',' SKIP_HEADER = 1);

Expected Output

COPY INTO my_table executed successfully. Loaded 100 rows.

→

FILE_FORMAT - Defines how the input file is structured so Snowflake can parse it correctly.

→

SKIP_HEADER - Skips the first line if it contains column names instead of data.

This command checks the data loaded into the table to confirm the COPY INTO command worked as expected.

Terminal

SELECT * FROM my_table;

Expected Output

ID | NAME  | AGE

1  | Alice | 30

2  | Bob   | 25

3  | Carol | 27

Key Concept

If you remember nothing else from this pattern, remember: COPY INTO moves data from files into Snowflake tables using a defined file format and stage.

Common Mistakes

Not creating or specifying the correct stage before running COPY INTO.

Snowflake cannot find the data files without a proper stage, so the command fails.

Always create and reference a valid stage that points to your data location before loading.

Using the wrong file format settings, like incorrect delimiter or skipping header lines.

Data will be parsed incorrectly, causing load errors or wrong data in the table.

Match the FILE_FORMAT options exactly to your file's structure, including delimiter and header rows.

Running COPY INTO without checking the table schema matches the data columns.

Data type mismatches cause load failures or incorrect data storage.

Ensure your table columns and data file columns align in order and type before loading.

Summary

Create a table to hold the data you want to load.

Create a stage that points to your external data files.

Use COPY INTO with the correct file format to load data from the stage into the table.

Verify the data loaded correctly by querying the table.