Loading data into a Snowflake table is one of the most important steps when working with data engineering projects. Whether you are handling customer records, sales information, or large business datasets, Snowflake provides simple ways to move data into its cloud data warehouse.

One of the most commonly used commands for this process is COPY INTO. It enables you to load data directly into database tables from files stored in cloud storage or internal Snowflake stages. If you are exploring cloud data platforms and planning to build a career in data engineering, understanding this command is a useful skill. Learning through practical exercises at Snowflake Training in Chennai can also help you understand how data loading works in real-world projects.

What Is COPY INTO in Snowflake?

COPY INTO is a SQL command used to load data from staged files into Snowflake tables. It supports different file formats, including CSV, JSON, Parquet, Avro, and ORC.

Instead of manually inserting thousands of records into a table, you can use COPY INTO to load data in bulk. This makes the process faster and more convenient, especially when working with large datasets.

For example, imagine a company receives daily sales data from different branches. Instead of inserting each record individually, the data engineering team can store the files in a Snowflake stage and use COPY INTO to load them into a target table.

How Does COPY INTO Work?

Before loading data, it is important to understand the basic workflow.

The process generally involves these steps:

1. Prepare the source data file.

2. Create a database and target table in Snowflake.

3. Upload the file to a Snowflake stage or establish a connection to external cloud storage. 

4. Configure the appropriate file format.

5. Execute the COPY INTO command.

6. Validate the loaded records.

This approach helps organize the data loading process and makes it easier to manage different source files.

Step 1: Create a Database and Table

First, create a database where you want to store the incoming data.

CREATE DATABASE SALES_DB;

USE DATABASE SALES_DB;

CREATE SCHEMA SALES_SCHEMA;

Then, create a table to store customer sales data. 

CREATE TABLE SALES_DATA (

    CUSTOMER_ID INT,

    CUSTOMER_NAME STRING,

    PRODUCT STRING,

    AMOUNT FLOAT

);

This table contains four columns: customer ID, customer name, product, and amount.

Make sure the target table structure matches the data you are planning to load.

Step 2: Create an Internal Stage

A stage is a location where data files are stored before loading them into Snowflake.

Snowflake supports two main types of stages:

• Internal Stage: Stores files within Snowflake.

• External Stage: Links Snowflake with cloud storage platforms such as Amazon S3, Azure Blob Storage, and Google Cloud Storage.

For this example, let's create an internal stage.

CREATE OR REPLACE STAGE SALES_STAGE;

Once the stage is created, you can upload your CSV file using the SnowSQL PUT command or an appropriate Snowflake client.

PUT file://sales_data.csv

@SALES_STAGE;

This command uploads the local CSV file into the internal stage. The PUT command is typically executed through SnowSQL or supported Snowflake client environments, not directly as a standard worksheet SQL statement.

Step 3: Create a File Format

Snowflake needs to understand the structure of the source file before loading it.

You can create a CSV file format using the following command:

CREATE OR REPLACE FILE FORMAT SALES_CSV_FORMAT

TYPE = 'CSV'

FIELD_DELIMITER = ','

SKIP_HEADER = 1

FIELD_OPTIONALLY_ENCLOSED_BY = '"';

Here is what these settings mean:

• TYPE: Specifies the file format.

• FIELD_DELIMITER: Identifies the character separating columns.

• SKIP_HEADER: Skips the first row containing column names.

• FIELD_OPTIONALLY_ENCLOSED_BY: Handles values enclosed in double quotes.

Creating a file format makes it easier to reuse the same configuration when loading multiple files.

Step 4: Load Data Using COPY INTO

Now comes the most important step. Once the file is uploaded and the target table is ready, you can execute the COPY INTO command.

COPY INTO SALES_DATA

FROM @SALES_STAGE

FILE_FORMAT = (FORMAT_NAME = 'SALES_CSV_FORMAT');

This command reads the staged CSV file and loads its records into the SALES_DATA table.

You can also specify a particular file when the stage contains multiple files.

COPY INTO SALES_DATA

FROM @SALES_STAGE

FILES = ('sales_data.csv')

FILE_FORMAT = (FORMAT_NAME = 'SALES_CSV_FORMAT');

Snowflake processes the file and provides a result showing the loading status, including the number of rows loaded and any errors encountered.

Step 5: Verify the Loaded Data

After executing the COPY INTO command, it is important to check whether the records have been loaded correctly.

Use the following SQL query:

SELECT * FROM SALES_DATA;

To check the total number of records, execute:

SELECT COUNT(*) FROM SALES_DATA;

You can also review the COPY INTO execution results to identify successfully loaded files and any rejected records.

For learners exploring advanced data engineering concepts, practicing these steps through real-time exercises is valuable. A structured learning approach at Qmatrix Technologies can help students understand Snowflake stages, file formats, SQL commands, and practical data loading scenarios.

Common COPY INTO Errors and How to Handle Them

Sometimes, data loading may fail because of incorrect file configurations or source data issues. Understanding common errors helps you troubleshoot problems more efficiently.

1. Incorrect File Format

If the CSV delimiter or header configuration is incorrect, Snowflake may not read the data properly. Check the file format settings before executing COPY INTO.

2. Column Mismatch

When source files contain a different number of columns than the target table, loading issues may occur. Verify the source structure and target table definition.

3. Duplicate File Loading

Snowflake maintains load metadata to help prevent previously loaded files from being loaded again. If you intentionally need to reload a file, understand the FORCE option and its impact before using it.

4. Data Type Errors

If a source file contains text values in numeric columns, Snowflake may reject those records. Validate the source data and use appropriate conversion or error-handling options.

Best Practices for Using COPY INTO

Use the following best practices to ensure a more reliable Snowflake data loading process. 

• Use appropriate file formats for different source files.

• Organize staged files using meaningful folder structures.

• Validate source data before loading.

• Monitor COPY INTO execution results.

• Use ON_ERROR settings carefully to manage loading failures.

• Avoid unnecessary repeated file loading.

• Maintain proper access permissions for stages and storage integrations.

These practices help improve data quality and make large-scale data loading easier to manage.

Conclusion

The COPY INTO command is an essential feature in Snowflake that simplifies bulk data loading from internal stages and external cloud storage. By understanding databases, stages, file formats, and SQL commands, you can build a reliable data ingestion process for different business requirements.

Practicing these concepts with real-time datasets will help you gain confidence in handling data engineering tasks. If you want to develop practical cloud data skills, Best Snowflake Training in Coimbatore can be a useful learning opportunity to explore Snowflake architecture, SQL, data loading, and real-world projects.