Course contents

SnowPro Core COF-C03 Study GuidePart 4: Data loading and storageUnloading Data: COPY INTO <location>

Unloading Data: COPY INTO <location>

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.

Data path · Unload
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

Unloading. COPY INTO @export_stage runs the SELECT on your warehouse, which writes several files in parallel, about 16 MB each by default. GET downloads them from an internal stage to a client. For an external stage the files are already in your bucket.SNOWFLAKE · YOUR WAREHOUSE@EXPORT_STAGECLIENTTABLEWEEKLY_SALEStable or queryYOUR WAREHOUSECOPY INTO @stageruns the SELECT,writes in parallelFILEdata_0_0_0.csv.gzabout 16 MBFILEdata_0_0_1.csv.gzabout 16 MBFILEdata_0_0_2.csv.gzabout 16 MBCLIENTGETdownloadsthe filesSINGLE = TRUE writes one file;MAX_FILE_SIZE raises the size limit

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_0.csv.gzabout 16 MB

FILEdata_0_0_1.csv.gzabout 16 MB

FILEdata_0_0_2.csv.gzabout 16 MB

CLIENT

CLIENTGETdownloads the files

Figure 4.8 Unloading writes in parallel by default: several files of about 16 MB each. GET downloads them from an internal stage; for an external stage, the files are already in your bucket.

Example: three exports

-- Parquet for the data science team
COPY 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 weekly
COPY 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 row
COPY 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 machine
GET @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.

OptionDefaultWhat it changes
SINGLEFALSE: several files, written in parallelTRUE writes one file and gives up parallelism
MAX_FILE_SIZE16 MBTarget size per file, up to 5 GB. SINGLE = TRUE doesn't raise it, so raise MAX_FILE_SIZE too when you want one big file
Compressiongzip for CSV and JSON, Snappy for ParquetSet COMPRESSION = NONE for plain files
OVERWRITEFALSETRUE replaces files with the same names, but leaves other files in the path alone
INCLUDE_QUERY_IDFALSETRUE gives each run unique file names
HEADERFALSETRUE writes column names (CSV) or keeps them in the Parquet schema
Watch out
  • 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.
Exam takeaway
  • 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.