Snowflake unload basics: export query results to a stage as CSV or Parquet with COPY INTO

#The goal

Someone needs data out of Snowflake: a CSV for a partner, a Parquet extract for a data lake, or a one-off dump for an investigation. The tool for this is COPY INTO <location>, the reverse of the load command. It runs a query on a warehouse and writes the result as files to a stage.

This guide covers the commands you use on a normal day and the options that catch people out: file size, single files, headers, partitioning and duplicate files on rerun.

#Before you start

You need:

• A role with SELECT on the source table and USAGE on its database and schema.
• A running warehouse. Unloading is a query and uses credits like one.
• A stage you can write to: your user stage (@~), a named internal stage, or an external stage backed by a storage integration. For a named stage, the role needs WRITE on it (and READ to list files).

USE ROLE analyst;
USE WAREHOUSE wh_export_xs;

CREATE STAGE IF NOT EXISTS ops.exports.unload_stage
  COMMENT = 'Ad hoc exports';

GRANT READ, WRITE ON STAGE ops.exports.unload_stage TO ROLE analyst;

#Unload to CSV

COPY INTO @ops.exports.unload_stage/orders/2026-11-08/orders_
FROM (
  SELECT order_id, customer_id, order_ts, status, amount
  FROM sales.core.orders
  WHERE order_ts >= '2026-11-08' AND order_ts < '2026-11-09'
)
FILE_FORMAT = (
  TYPE = CSV
  FIELD_OPTIONALLY_ENCLOSED_BY = '"'
  NULL_IF = ()
  COMPRESSION = GZIP
)
HEADER = TRUE;

What each part does:

• The path after the stage name is a prefix. orders_ becomes the start of each file name; Snowflake adds a suffix per thread such as orders_0_0_0.csv.gz.
• FIELD_OPTIONALLY_ENCLOSED_BY = '"' quotes fields so commas inside text don't break the file. The default is no quoting.
• NULL_IF = () writes NULLs as empty fields. The default writes \N, which surprises most spreadsheet users.
• HEADER = TRUE writes column names. With multiple files, every file gets the header row.
• CSV output is gzip-compressed by default. Use COMPRESSION = NONE if the receiver can't handle .gz.

The command returns a summary row with the number of rows unloaded and the data size before and after compression. Add DETAILED_OUTPUT = TRUE to get one row per file instead, which is useful for handing over a manifest.

#Unload to Parquet

COPY INTO @ops.exports.unload_stage/orders_parquet/2026-11-08/
FROM (
  SELECT order_id,
         customer_id,
         order_ts::TIMESTAMP_NTZ AS order_ts,
         status,
         amount
  FROM sales.core.orders
  WHERE order_ts >= '2026-11-08' AND order_ts < '2026-11-09'
)
FILE_FORMAT = (TYPE = PARQUET)
HEADER = TRUE;

Warning

Without HEADER = TRUE, Parquet files get generic column names (col1, col2, ...). Readers that map by name will load nulls or fail. Give every expression an alias too, so the column name is predictable.

Parquet type caveats from the Snowflake documentation:

Parquet output is Snappy-compressed by default.

#Control file size and count

By default each file is up to 16 MB (MAX_FILE_SIZE = 16777216) and Snowflake writes files in parallel, so a large export becomes many files.

-- Fewer, larger files (value in bytes; the documented maximum is 5 GB on S3, Azure and GCS)
COPY INTO @ops.exports.unload_stage/orders_big/
FROM sales.core.orders
FILE_FORMAT = (TYPE = PARQUET)
HEADER = TRUE
MAX_FILE_SIZE = 268435456;

When the receiver insists on exactly one file:

COPY INTO @ops.exports.unload_stage/partner/orders_2026-11-08.csv.gz
FROM (SELECT * FROM sales.core.orders WHERE order_ts::DATE = '2026-11-08')
FILE_FORMAT = (TYPE = CSV COMPRESSION = GZIP FIELD_OPTIONALLY_ENCLOSED_BY = '"')
HEADER = TRUE
SINGLE = TRUE
MAX_FILE_SIZE = 4900000000;

With SINGLE = TRUE you name the file yourself, including the extension, because Snowflake doesn't add one. A single file is written by one thread, so it is slower. MAX_FILE_SIZE still caps the file, so set it above the expected compressed size. If the export can exceed the 5 GB limit, drop SINGLE and send a set of files.

#Partition the output

To write a lake-friendly folder layout, use PARTITION BY with an expression that builds the path:

COPY INTO @ops.exports.unload_stage/orders_by_day/
FROM (
  SELECT order_id, customer_id, order_ts::TIMESTAMP_NTZ AS order_ts, status, amount,
         order_ts::DATE AS order_date
  FROM sales.core.orders
  WHERE order_ts >= '2026-11-01'
)
PARTITION BY ('order_date=' || TO_VARCHAR(order_date, 'YYYY-MM-DD'))
FILE_FORMAT = (TYPE = PARQUET)
HEADER = TRUE;

Rules to know:

• Files are named data_<query id>_..., and the query ID is always included. INCLUDE_QUERY_ID = FALSE, OVERWRITE = TRUE and SINGLE = TRUE are not supported with PARTITION BY.
• Rows where the partition expression is NULL go to a _NULL_ folder.
• Partition values appear in file paths, so partition on dates or other low-cardinality, non-sensitive values. Never on email addresses or customer names.
• Partition columns stay in the data files as well.

#Reruns and duplicate files

This is the mistake that hurts most. OVERWRITE = TRUE only replaces files with the same name; it does not delete other files under the path. Because parallel threads can produce different file names on each run, a rerun can leave old and new files side by side, and the consumer reads the data twice.

Safer patterns:

-- 1. Clear the target path first (check it with LIST before you REMOVE)
LIST @ops.exports.unload_stage/orders/2026-11-08/;
REMOVE @ops.exports.unload_stage/orders/2026-11-08/;

-- 2. Or make every run write unique file names and point the consumer at one run
COPY INTO @ops.exports.unload_stage/orders/2026-11-08/
FROM sales.core.orders
FILE_FORMAT = (TYPE = PARQUET)
HEADER = TRUE
INCLUDE_QUERY_ID = TRUE;

Caution

REMOVE deletes files in the stage, and for an external stage that means objects in your S3 bucket or Azure container. Run LIST with the exact same path first and make sure the prefix ends where you think it does: REMOVE @stage/orders also matches orders_archive/.

#Preview, list and download

Check what the query will produce without writing files:

COPY INTO @ops.exports.unload_stage/preview/
FROM (SELECT * FROM sales.core.orders LIMIT 10)
VALIDATION_MODE = RETURN_ROWS;

List the written files and their sizes:

LIST @ops.exports.unload_stage/orders/2026-11-08/;

Download from an internal stage with SnowSQL (GET doesn't work in the web UI worksheets):

snowsql -a myorg-myaccount -u analyst -q \
  "GET @ops.exports.unload_stage/orders/2026-11-08/ file:///tmp/orders_2026-11-08/;"

For an external stage, the files are already in your bucket or container. Fetch them with aws s3 cp or az storage blob download-batch.

To find out later what an export did, look up the COPY statement in query history:

SELECT query_id, user_name, start_time, rows_produced, query_text
FROM snowflake.account_usage.query_history
WHERE query_text ILIKE 'COPY INTO @%'
  AND start_time > DATEADD(day, -7, CURRENT_TIMESTAMP())
ORDER BY start_time DESC;

ACCOUNT_USAGE views lag by up to a few hours; use INFORMATION_SCHEMA.QUERY_HISTORY() for the last few minutes.

#Prevent it next time

• Give each export its own dated path and clear it, or use INCLUDE_QUERY_ID = TRUE, so reruns never mix old and new files.
• Always set HEADER = TRUE and alias every expression for Parquet.
• Use a small, dedicated export warehouse with a statement timeout so a bad export query can't burn credits for hours.
• Restrict WRITE on external stages to the roles that need it. Unloading is also a way for data to leave the account. Review who can write to which external locations.
• Save the export SQL in version control with the recipient and the agreed format, so the next person can rerun it the same way.