Snowflake Data Unloading

Loading brings files into Snowflake. Unloading sends data back out. Partners ask for extracts, machine learning teams need Parquet files, and archives belong in cheap cloud storage. COPY INTO a location writes table data or query results into files on a stage, ready for download or for another tool.

The Moving Truck Comparison

A moving truck carries furniture into a house and carries it out too. Loading and unloading use the same truck, the stage, with the direction reversed. The load command moves files into a table. The unload command moves rows out into files.

UNLOAD FLOW
===========

 +--------------+    COPY INTO @stage    +-----------+    GET or   +------------+
 | TABLE or     |----------------------->|  STAGE    |------------>| Partner,   |
 | QUERY RESULT |    writes files        |  (files)  |   cloud     | tool, or   |
 +--------------+                        +-----------+   download  | archive    |
                                                                   +------------+

A Basic Unload

-- Create a stage and a format
CREATE STAGE export_stage;

-- Unload a full table to CSV files
COPY INTO @export_stage/orders/
FROM sales_db.public.orders
FILE_FORMAT = (TYPE = CSV COMPRESSION = GZIP)
HEADER = TRUE;

-- Check the files
LIST @export_stage/orders/;

Snowflake splits big tables into many files in parallel. Each file defaults to about 16 MB before compression.

Unloading Query Results

Wrap a query in parentheses to export a filtered or joined result.

COPY INTO @export_stage/west_orders/
FROM (
  SELECT order_id, customer_id, amount, order_date
  FROM sales_db.public.orders
  WHERE region = 'WEST' AND order_date >= '2026-01-01'
)
FILE_FORMAT = (TYPE = PARQUET)
HEADER = TRUE
OVERWRITE = TRUE;

Useful Options

OPTION                   PURPOSE
-----------------------  -----------------------------------------------
HEADER = TRUE            Write column names in the first row or metadata
OVERWRITE = TRUE         Replace files with the same names
SINGLE = TRUE            Create one file instead of many
MAX_FILE_SIZE = n        Set the target size in bytes for each file
PARTITION BY (expr)      Write files into folders based on an expression
INCLUDE_QUERY_ID = TRUE  Add the query ID to file names for traceability
DETAILED_OUTPUT = TRUE   Return one result row per file written

Partitioned Output

Folders split by date or region let downstream tools read only the slice they need.

COPY INTO @export_stage/by_month/
FROM sales_db.public.orders
PARTITION BY ('month=' || TO_VARCHAR(DATE_TRUNC('month', order_date), 'YYYY-MM'))
FILE_FORMAT = (TYPE = PARQUET)
HEADER = TRUE;
RESULTING FOLDERS
=================
by_month/month=2026-08/data_0_0_0.snappy.parquet
by_month/month=2026-09/data_0_0_0.snappy.parquet
by_month/month=2026-10/data_0_0_0.snappy.parquet

Choosing a Destination

  • Internal stage: files stay inside Snowflake until someone downloads them with GET from SnowSQL or a driver.
  • External stage: files land directly in your own cloud bucket, which suits large exports and data lakes.
  • Direct cloud path: a storage integration lets COPY INTO write to a bucket path without naming a stage.
COPY INTO 's3://my-company-data/exports/orders/'
FROM sales_db.public.orders
STORAGE_INTEGRATION = s3_int
FILE_FORMAT = (TYPE = PARQUET);

-- Download from an internal stage with SnowSQL
GET @export_stage/orders/ file:///home/user/downloads/;

Format Choices

FORMAT    GOOD FOR                                   NOTE
--------  -----------------------------------------  ---------------------
CSV       Spreadsheets and simple partner exchanges  Larger, no type info
JSON      Application and API consumers              Verbose
Parquet   Data lakes, analytics, machine learning    Compact, keeps types

Safety and Cost

  • Exports can expose sensitive data. Unload only approved columns, and protect output locations with proper access rules.
  • Masking policies apply to the role running the unload, so use a role with the right visibility.
  • Transfers to another cloud or region incur data transfer fees from the provider.
  • Delete stage files after download to avoid storage charges.

A Practical Scenario

A logistics company shares weekly delivery summaries with a partner who uses an older reporting tool. A scheduled task unloads a query result to CSV on an external stage every Monday morning. The partner's system picks up the file from the bucket. Nobody sends emails or builds screenshots, and the file always matches the week's closed data.

Frequently Asked Questions

Why do I receive several files from one unload?

Snowflake writes in parallel and splits output into many files. Use SINGLE = TRUE for one file, and remember that a single file caps the size and runs slower.

Can I unload to a local folder directly?

COPY INTO writes to stages and cloud locations. Download from an internal stage with GET in SnowSQL or a driver to reach a local folder.

Do masked columns appear in the output?

The unload runs under the current role, so masking policies shape what lands in the files. A role that sees masked values exports masked values.

Testing an Unload

Run the first unload with a small filter, list the files, and download one to inspect. Check headers, column order, date formats, and special characters before scheduling the full export. A short test saves partners from receiving files that need repairs.

Key Points

  • COPY INTO a location unloads tables or query results into files.
  • Stages hold the files until downloaded or picked up by another tool.
  • Options control headers, size, overwriting, and folder partitions.
  • Parquet suits analytics, and CSV suits simple exchanges.
  • Storage integrations write directly to cloud buckets.
  • Protect exports like any other copy of sensitive data.
Previous lesson
Back to course
Next lesson