Data Unloading using COPY INTO Command in Snowflake

1. What is COPY INTO Command in Snowflake?

The COPY INTO command in Snowflake enables both loading data into a table and unloading data from a table to a stage location. Snowflake Stages are locations that store files which helps in loading and unloading data into tables.

In our earlier article – Data Load using COPY INTO Command in Snowflake, we explained how to load data into a table. In this article, we focus on unloading data from Snowflake tables into stage locations.

2. How to Unload Data using COPY INTO Command in Snowflake?

The following is the basic syntax of the COPY INTO command in Snowflake for unloading data from a database table into the stage location.

Syntax:

COPY INTO @stage_name/file_path
FROM { table_name | (query) }
FILE_FORMAT = (FORMAT_NAME = 'my_file_format' | TYPE = filetype [formatTypeOptions] )
;

Key Components:

• @stage_name/file_path: The location of the file in a Snowflake stage (internal or external). 
• table_name: The name of the table from which data is unloaded.
• query: A SELECT statement that transforms or filters data before unloading. 
• FORMAT_NAME: Specifies an existing named file format for loading data into the table.
• TYPE: Specifies the type of files (CSV, JSON, PARQUET) to which data is unloaded into from the table. When file type is specified, additional format-specific options can be specified.

Examples:

The following example unloads data into an internal named stage my_int_stage from table my_table using a named file format.

COPY INTO @my_int_stage
FROM my_table
FILE_FORMAT = (FORMAT_NAME = 'my_csv_format');

The following example unloads data into an external stage location my_ext_stage/inbox from my_table using a file type with format-specific options.

COPY INTO @my_ext_stage/inbox/
FROM my_table
FILE_FORMAT = (TYPE = CSV FIELD_DELIMITER = '|');

3. How to Include Column Headers while Unloading Data using COPY INTO Command in Snowflake?

By default, Snowflake does not include column headers in unloaded files. To add column names as the first row, set the HEADER option to TRUE in the COPY INTO command.

The following example unloads data from a table into a stage and includes column headers in the resulting CSV files

COPY INTO @my_stage/
FROM my_table
FILE_FORMAT = (TYPE = CSV)
HEADER = TRUE;

4. How to Transform Data while Unloading Data using COPY INTO Command in Snowflake?

The COPY INTO command allows you to transform data during unloading by using a SELECT statement in the FROM clause. This enables you to:

• Filter rows or columns.
• Aggregate data (e.g., SUM, GROUP BY).
• Rename columns or apply expressions.

The following example aggregates the sales quantity by product and unloads the result into a stage using the COPY INTO command.

COPY INTO @my_stage/
FROM (
  SELECT 
    product_id, 
    SUM(quantity) AS total_qty 
  FROM sales 
  GROUP BY product_id
)
FILE_FORMAT = (TYPE = CSV)
HEADER = TRUE;

Output: Unloads only product_id and aggregated total_qty fields.

5. How to Partition the data while Unloading using COPY INTO Command in Snowflake?

The COPY INTO command allows partitioning the data into multiple files based on a column or a SQL expression that evaluates to a string using the PARTITION BY parameter.

The following example partitions the exported data by order_date, resulting in separate folders for each order_date value under the @my_stage/partitioned/ stage location. Each folder will contain CSV files with headers.

COPY INTO @my_stage/partitioned/
FROM ORDERS
PARTITION BY order_date
FILE_FORMAT = (TYPE = CSV)
HEADER = TRUE;

Output:

6. What is VALIDATION_MODE in Data Unloading using COPY INTO Command in Snowflake?

The COPY INTO command allows validating files without loading them into a table using the VALIDATION_MODE parameter.

• It returns the results of the statement instead of unloading the results to the specified location.
• The only supported validation option is RETURN_ROWS.

Syntax:

COPY INTO @my_stage
FROM my_table
FILE_FORMAT = (TYPE = CSV)
VALIDATION_MODE = RETURN_ROWS;

7. What are Copy Options to Unload data using COPY INTO Command in Snowflake?

The COPY INTO command supports several “Copy Options” that help in controlling the load behaviour and performance of the copy statement.

Example:

COPY INTO @my_stage/
FROM my_table
FILE_FORMAT = (TYPE = CSV)
OVERWRITE = TRUE
SINGLE = FALSE
MAX_FILE_SIZE = 10485760; -- 10MB per file

8. Summary

The COPY INTO command in Snowflake enables unloading data from tables into internal or external stage locations in formats like CSV, JSON, or Parquet.

• Data can be unloaded either directly from a table or from the result of a SELECT query with filters, aggregations, or transformations.
• Use HEADER = TRUE to include column names as the first row in output files.
• Partitioning is supported via the PARTITION BY clause, which creates folder-like structures in the stage based on column values or expressions.
• File generation can be controlled using copy options such as SINGLE, MAX_FILE_SIZE, OVERWRITE, and INCLUDE_QUERY_ID.
• Use VALIDATION_MODE = RETURN_ROWS to preview what data would be unloaded without writing files to the stage.
• The command also supports returning metadata such as file names, sizes, and row counts using DETAILED_OUTPUT.

Subscribe to our Newsletter !!

Related Articles:

• Snowflake Micro-partitions & Data Clustering
• Snowflake File Formats
• Types of Snowflake Tables
• Types of Views in Snowflake
• Types of Snowflake Stages: Data Loading and Unloading Features
• HOW TO: Create External Stages in Snowflake
• HOW TO: Create Snowflake External Tables?
• HOW TO: Load and Query JSON data in Snowflake?
• INSERT and Multi-Table Inserts in Snowflake
• HOW TO: Create Snowflake Iceberg Tables?
• Creating Snowflake Iceberg tables using AWS Glue as Catalog
• Data Load using COPY INTO Command in Snowflake
• Data Unloading using COPY INTO Command in Snowflake
• Snowflake Directory Tables: Query and Manage Staged Files
• Snowflake File URLs: Securely Access and Share Staged Files