Skip to content
Tutorials Center
My learning

Topics

Lesson 15 of 38 ยท Loading and Unloading Data

Unloading Data with COPY INTO a Location

Export Snowflake query results to files with COPY INTO a stage or bucket. Cover CSV, JSON and Parquet output, partitioning, file size, single files and GET.

Loading brings data in; unloading sends it out. You might export a table to Parquet for a data lake, hand a CSV to a partner, or back up a result set. In Snowflake the same COPY INTO command works in the other direction: instead of COPY INTO table, you write COPY INTO <location>.

Unload a table or a query

The target is a stage (internal or external). The source is a table or any SELECT:

SQL
-- Snowflake SQL (read-only)
COPY INTO @shop.raw.exports/orders_2025/
FROM shop.raw.orders
FILE_FORMAT = (TYPE = CSV COMPRESSION = GZIP FIELD_OPTIONALLY_ENCLOSED_BY = '"')
HEADER = TRUE;

COPY INTO @shop.raw.exports/big_customers_
FROM (
  SELECT c.id, c.name, SUM(o.total) AS lifetime_value
  FROM shop.raw.customers c JOIN shop.raw.orders o ON o.customer_id = c.id
  GROUP BY c.id, c.name
  HAVING SUM(o.total) > 10000
)
FILE_FORMAT = (TYPE = PARQUET)
OVERWRITE = TRUE;

Unlike loading, an unload SELECT can contain joins, filters and aggregates. The statement runs on a warehouse and writes files in parallel, so the output is usually many files rather than one, each named like orders_2025/data_0_0_0.csv.gz.

Options you will use

Option Meaning
FILE_FORMAT CSV, JSON or PARQUET (named format or inline)
HEADER = TRUE Write column names as the first line (CSV)
SINGLE = TRUE One output file instead of many (slower, size-limited)
MAX_FILE_SIZE Target size in bytes; the default is about 16 MB, the maximum 5 GB
OVERWRITE = TRUE Replace files with the same name
PARTITION BY (expr) Write into folders by a value (for example date)
INCLUDE_QUERY_ID = TRUE Add the query ID to file names so reruns do not collide
DETAILED_OUTPUT = TRUE List each file written with size and row count

SINGLE = TRUE is convenient for a small hand-off but turns off parallelism. A multi-gigabyte single file is slow and may fail on size limits, so prefer many files and tell the consumer to read the folder.

Partitioned unloads

Writing one folder per day or per region is the usual layout in a data lake, because readers then scan only what they need:

SQL
-- Snowflake SQL (read-only)
COPY INTO @shop.raw.exports/orders_by_day/
FROM (SELECT id, customer_id, total, order_date FROM shop.raw.orders)
PARTITION BY ('date=' || TO_VARCHAR(order_date, 'YYYY-MM-DD'))
FILE_FORMAT = (TYPE = PARQUET)
MAX_FILE_SIZE = 134217728;     -- about 128 MB

This creates paths such as orders_by_day/date=2025-06-01/data_..._0.snappy.parquet. The folder-per-value style is the Hive convention that Spark, Athena and many engines understand.

The grouping idea behind PARTITION BY is the same as GROUP BY. The stand-in shows which "folders" and counts an unload would produce, computed from real order dates:

SQL Stand-in: what would be written to each folder? ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Unloading to JSON and Parquet

To export JSON, build one object per row using OBJECT_CONSTRUCT, since a JSON unload expects a single VARIANT column:

SQL
-- Snowflake SQL (read-only)
COPY INTO @shop.raw.exports/orders_json/
FROM (SELECT OBJECT_CONSTRUCT('id', id, 'customer', customer_id, 'total', total)
      FROM shop.raw.orders)
FILE_FORMAT = (TYPE = JSON);

Parquet keeps types and compresses well (Snappy by default), which makes it the best format to hand to other analytics engines. CSV is the lowest common denominator for spreadsheets and partners.

You can imitate row-to-JSON building in the browser with SQLite's json_object, one JSON document per row:

SQL Stand-in: one JSON object per row ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

And a CSV-style export, with a header line and quoted text, done with string functions:

SQL Stand-in: CSV lines with header ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Getting files out

Files unloaded to an internal stage live in Snowflake until you fetch them:

SQL
-- Snowflake SQL (read-only), from SnowSQL or the CLI
LIST @shop.raw.exports/orders_2025/;
GET @shop.raw.exports/orders_2025/ file://C:\exports\;
REMOVE @shop.raw.exports/orders_2025/;

With an external stage the files are already in your bucket, so the destination system reads them from there. Remove old files regularly; stage storage is billed.

Security and cost notes

  • Restrict who can write to external stages. Unloading is a way to copy data out of the account, so grants on stages and storage integrations deserve review (the governance chapter covers access control).
  • Data going to an external stage is encrypted in transit. For internal stages, files are encrypted at rest automatically.
  • Unloading uses warehouse credits. A big export on an XS warehouse is slow; scale up for large tables because the work is parallel across files.
  • Use COPY INTO for bulk; for a small result a user can simply download from Snowsight.

Treat exported files like the data they contain. Do not unload personal or confidential data to a bucket that is broadly readable. Check masking policies: a role that sees masked values in a query will also get masked values in the unloaded file.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the target of COPY INTO when unloading?

    COPY INTO <location> writes files to an internal or external stage.
  2. 2. Why is the default unload output many files?

    Parallel writers each produce files. SINGLE = TRUE forces one file but removes parallelism.
  3. 3. Which clause writes files into folders by date?

    PARTITION BY expr creates a folder per distinct value.
  4. 4. What does a JSON unload expect as its source?

    Use OBJECT_CONSTRUCT to build one JSON object per row.

Summary

  • COPY INTO @stage FROM table_or_query unloads data as CSV, JSON or Parquet.
  • Output is multiple files by default; control size with MAX_FILE_SIZE, and use PARTITION BY for folder layouts.
  • HEADER, OVERWRITE, SINGLE and INCLUDE_QUERY_ID shape names and content.
  • Fetch internal-stage files with GET; external stages already sit in your bucket.
  • Guard exports: they leave the account and carry whatever masking your role sees.

Next: SELECT and Snowflake SQL Extras