Unloading Data: COPY INTO <location>
In shortThe same COPY command runs in reverse: if the target is a stage (it starts with @) or a cloud URL, Snowflake runs the query and writes the results as files.
- Source
- A table or any query
- Trigger
- You run COPY INTO @stage
- Processor
- Your virtual warehouse
- Destination
- Files in an internal stage, an external stage or a cloud URL
- Compute and billing
- Warehouse credits like any query; the files are then stored in the stage or your bucket
The problem
A data science team wants Parquet files, a partner wants one CSV a week, an app wants JSON. All of them want files, not a database connection. Read the target first: a table after COPY INTO means load, a stage or URL means unload.
How it works
SNOWFLAKE · YOUR WAREHOUSE
TABLEWEEKLY_SALEStable or query
YOUR WAREHOUSECOPY INTO @stageruns the SELECT, writes in parallel
SINGLE = TRUE writes one file; MAX_FILE_SIZE raises the size limit
@EXPORT_STAGE
FILEdata_0_0_
FILEdata_0_0_
FILEdata_0_0_
CLIENT
CLIENTGETdownloads the files
Example: three exports
-- Parquet for the data science teamCOPY INTO @sales_db. raw. export_ stage/ orders/ FROM sales_db. core. orders FILE_FORMAT = (TYPE = PARQUET)HEADER = TRUE; -- keep column names (otherwise col1, col2...)-- One CSV for a partner, replaced weeklyCOPY INTO @sales_db. raw. export_ stage/ weekly/ sales. csv FROM (SELECT * FROM sales_db. core. weekly_ sales) FILE_FORMAT = (TYPE = CSV COMPRESSION = NONE)HEADER = TRUE SINGLE = TRUE OVERWRITE = TRUE MAX_FILE_ SIZE = 1000000000; -- JSON needs one object per rowCOPY INTO @sales_db. raw. export_ stage/ json/ FROM (SELECT OBJECT_CONSTRUCT('order_id', order_id, 'amount', amount) FROM sales_ db. core. orders) FILE_FORMAT = (TYPE = JSON);-- From SnowSQL on Alice's machineGET @sales_db. raw. export_ stage/ weekly/ file: / / / Users/ alice/ exports/ ;
Expected resultThe first COPY returns the number of rows unloaded and writes several Parquet files under orders/. The second writes exactly one uncompressed sales.csv and replaces last week's file. GET copies the file to Alice's folder.
| Option | Default | What it changes |
|---|---|---|
SINGLE | FALSE: several files, written in parallel | TRUE writes one file and gives up parallelism |
MAX_ | 16 MB | Target size per file, up to 5 GB. SINGLE = TRUE doesn't raise it, so raise MAX_ |
| Compression | gzip for CSV and JSON, Snappy for Parquet | Set COMPRESSION = NONE for plain files |
OVERWRITE | FALSE | TRUE replaces files with the same names, but leaves other files in the path alone |
INCLUDE_ | FALSE | TRUE gives each run unique file names |
HEADER | FALSE | TRUE writes column names (CSV) or keeps them in the Parquet schema |
- Relational rows don't become JSON by themselves: wrap them in OBJECT_CONSTRUCT.
- Writing to an internal stage needs WRITE on it, and GET works only from internal stages.
- Default unload files are about 16 MB, well under the 100 to 250 MB that loads best. Raise MAX_FILE_SIZE if the files will be loaded again.
- COPY INTO @stage = unload; COPY INTO table = load.
- Default output is several files of about 16 MB; SINGLE = TRUE forces one.
- PUT goes up to an internal stage; GET comes down from one.

