Snowflake COPY INTO Command for Data Loading and Unloading

The COPY INTO command in Snowflake is a versatile tool used for loading data from various stages (internal or external) into Snowflake tables, or for unloading data from Snowflake tables into stages. This tutorial will guide you through the key functionalities, best practices, and examples of using the COPY INTO command.

What is the Snowflake COPY INTO Command?

The COPY INTO command in Snowflake is designed to facilitate the bulk loading and unloading of data. It supports various file formats, offers options for data transformation, and includes mechanisms for error handling and performance optimization.


COPY INTO <table_name>
FROM { internalStage | externalStage | externalLocation }
[ FILES = ( '<file_name>' [ , '<file_name>' ... ] ) ]
[ FILE_FORMAT = ( { FORMAT_NAME = '<file_format_name>' | TYPE = { CSV | JSON | PARQUET } [ formatTypeOptions ] } ) ]
[ copyOptions ]
[ VALIDATION_MODE = RETURN_ROWS ]
[ HEADER ]


This basic syntax highlights how to load data into a Snowflake table from various stages. The command supports different file formats and includes options for validation and error handling.

How to Load Data Using the COPY INTO Command?

Loading data using the COPY INTO command involves specifying the source stage, file format, and any necessary options for data transformation and error handling. Here are some key aspects:

• File Formats: The command supports CSV, JSON, PARQUET, AVRO, ORC, and XML formats, with various options for each format.
• Pattern Matching: Use the PATTERN parameter to match file names and paths using regular expressions.
• Transformations: Supports column reordering, omission, and data type casting during data load.
• Error Handling: The ON_ERROR option specifies actions like CONTINUE, SKIP_FILE, or ABORT_STATEMENT if errors are encountered.

How to Unload Data Using the COPY INTO Command?

Unloading data involves specifying the target location and file format. The command supports partitioning, file size control, and performance optimization options.

• File Naming and Partitioning: Supports partitioning data and writing partition column values to file names.
• File Size Control: The MAX_FILE_SIZE option helps manage file sizes for optimal performance.
• Performance Optimization: Factors like virtual warehouse size and configuration, file size, and region considerations can affect performance.

How To Use the COPY INTO Command

1. Stage the Files

Upload data files to a Snowflake stage using the PUT command. You can use an internal stage, or a table/user stage. If you're loading data from an S3 bucket, you can use the AWS upload interfaces and utilities to stage the files. Snowflake also recommends creating an external stage that references the bucket.


PUT file:///path/to/data/file.csv @my_stage;


This command uploads a local file to a specified internal stage in Snowflake.

2. Load the Contents

Use the COPY INTO <table> command to load the staged files into a Snowflake database table.


COPY INTO mytable
FROM @my_stage
FILE_FORMAT = (TYPE = CSV FIELD_DELIMITER = '|' SKIP_HEADER = 1);


This command loads data from the specified stage into the target table, using the defined file format options.

3. Using Pattern Matching

Specify a pattern to match file names and paths using regular expressions.


COPY INTO mytable
FROM @my_ext_stage
PATTERN='.*sales.*.csv';


This command loads only the files matching the specified pattern from the external stage.

4. Transforming Data During Load

Apply transformations such as column reordering and data type casting during the data load process.


COPY INTO home_sales(city, zip, sale_date, price)
FROM (SELECT SUBSTR(t.$2,4), t.$1, t.$5, t.$4 FROM @mystage t)
FILE_FORMAT = (FORMAT_NAME = mycsvformat);


This command loads data into the target table with specified transformations applied to the columns.

What Are the Best Practices for Using the COPY INTO Command?

To ensure efficient data loading and unloading, follow these best practices:

• Optimize File Sizes: Use the MAX_FILE_SIZE option to manage file sizes for better performance.
• Use Appropriate File Formats: Choose the file format that best suits your data and use case (e.g., CSV for simple data, PARQUET for complex nested data).
• Leverage External Stages: Use external stages for large datasets to avoid local storage limitations.
• Monitor and Tune Performance: Regularly monitor performance metrics and adjust virtual warehouse size and configuration as needed.

How to Handle Errors When Using the COPY INTO Command?

Error handling is crucial when loading and unloading data. The COPY INTO command provides several options:

• CONTINUE: Skip the problematic rows and continue loading the rest of the data.
• SKIP_FILE: Skip the entire file if any errors are encountered.
• ABORT_STATEMENT: Stop the entire operation if any errors are encountered.

Choose the error handling option that best fits your data integrity requirements and operational needs.

How to Optimize Performance When Using the COPY INTO Command?

Performance optimization is key to efficient data loading and unloading. Consider the following tips:

• Virtual Warehouse Size: Use an appropriately sized virtual warehouse to handle the data load.
• File Partitioning: Partition large files to improve parallel processing and performance.
• Region Considerations: Ensure that Snowflake and your external storage (e.g., S3 bucket) are in the same region to minimize latency.

How to Use the COPY INTO Command with Different Snowflake File Formats?

The COPY INTO command supports various file formats. Here are examples for different formats:

• CSV: COPY INTO mytable FROM @my_stage FILE_FORMAT = (TYPE = CSV FIELD_DELIMITER = '|' SKIP_HEADER = 1);
• JSON: COPY INTO mytable FROM @my_stage FILE_FORMAT = (TYPE = JSON);
• PARQUET: COPY INTO mytable FROM @my_stage FILE_FORMAT = (TYPE = PARQUET);

Specify the appropriate file format options to ensure correct data loading.

Common Challenges and Solutions

While using the COPY INTO command, you might encounter some common challenges. Here are solutions to address them:

• Error Handling: Use the ON_ERROR option to specify actions like CONTINUE, SKIP_FILE, or ABORT_STATEMENT when errors are encountered.
• File Size Management: Use the MAX_FILE_SIZE option to control the size of the files being unloaded, optimizing performance and manageability.
• Performance Optimization: Ensure that Snowflake and the external storage (e.g., S3 bucket) are in the same region to reduce latency and costs.

Recap of the Snowflake COPY INTO Command

In this tutorial, we covered the key functionalities and best practices for using the COPY INTO command in Snowflake. Here are the key takeaways:

• Versatility: The COPY INTO command supports various file formats and offers options for data transformation and error handling.
• Performance: Factors like virtual warehouse size, file size, and region considerations can significantly impact performance.
• Best Practices: Following best practices for staging files, managing file sizes, and optimizing performance can help ensure efficient data loading and unloading.