Snowflake Data Unloading & Parquet Type Mapping

Micro-Concept 1: COPY INTO <location>, the Unload Direction

The same COPY INTO command runs in two directions. Day 30 used the load form, stage to table. Today uses the unload form, table to stage. The stage path moves to the left of the FROM clause. That single change flips the direction.

The source can be a table. It can also be a SELECT query. A query source accepts the full Snowflake SQL syntax, including JOIN. This is the reverse of the load side. A load transformation could not join, but an unload query can.

-- Unload a whole table to a stage path
COPY INTO @my_stage/unload/
  FROM sales
  FILE_FORMAT = (TYPE = CSV);

-- Unload the result of a query, joins allowed
COPY INTO @my_stage/unload/
  FROM (
    SELECT s.order_id, c.region, s.amount
    FROM sales s
    JOIN customers c ON s.cust_id = c.cust_id
  )
  FILE_FORMAT = (TYPE = CSV);

Three destinations are valid: an internal stage, an external stage, and a direct external location. With no options set, the output is CSV. The files are GZIP-compressed and UTF-8 encoded. Those three defaults sit behind many “what does the file look like” stems.

Micro-Concept 2: One File or Many, SINGLE and MAX_FILE_SIZE

The first decision an unload makes is file count. The SINGLE option controls it. The default is SINGLE = FALSE. That setting writes multiple files. Each parallel thread writes its own file. The names follow a pattern like data_0_0_0.csv.gz.

The file count scales with the warehouse. One server writes up to eight files at once. A larger warehouse has more servers, so it produces more, smaller files. This is the opposite of intuition: scaling up makes files smaller, not bigger.

Set SINGLE = TRUE to force one output file. It runs without the parallel split, so it is slower on large tables. With SINGLE = TRUE and no filename in the path, the file lands with no extension. Supply a filename and extension in the path to fix that.

MAX_FILE_SIZE sets the upper target for each file in a multi-file unload. The default is 16 MB (16,777,216 bytes). The maximum supported value is 5 GB for an external stage. The target is not a promise. A file can finish smaller when memory runs short. A single set of rows can push a file slightly past the limit.

Micro-Concept 3: HEADER and OVERWRITE

Two options decide what the output looks like and what happens to old files. Both default to off. Both catch candidates who assume the friendlier behaviour.

HEADER writes the column names as the first row. The default is FALSE, so unloaded files carry no header by default. Set HEADER = TRUE when a downstream tool expects a name row. On a recent migration, an unload left HEADER at its default. The loader on the far side expected a header line and rejected every file.

OVERWRITE decides what happens when a file of the same name already sits in the path. The default is FALSE. A second unload to a path with matching filenames raises an error instead of replacing them. Set OVERWRITE = TRUE to replace the old files. This is the unload-side equivalent of a name collision, not the 64-day load metadata from Day 30.

-- One file, with a header row, replacing any old file
COPY INTO @my_stage/unload/orders.csv
  FROM sales
  FILE_FORMAT = (TYPE = CSV)
  SINGLE = TRUE
  HEADER = TRUE
  OVERWRITE = TRUE;

Micro-Concept 4: Parquet Type Mapping, the Fact With Its Own Question

Unloading to Parquet changes column types in a fixed, documented way. The exam tests one mapping directly. A fixed-point column unloads to a Parquet DECIMAL column. The precision and scale are preserved. A NUMBER(12,2) total stays exact in the file.

A floating-point column unloads to a Parquet DOUBLE column. That is the pair the exam plays on. Fixed-point goes to DECIMAL. Floating-point goes to DOUBLE. The trap swaps them and claims fixed-point becomes DOUBLE. That claim is false.

A few other mappings are worth a glance. VARIANT, GEOGRAPHY, and GEOMETRY unload as JSON-encoded strings. TIMESTAMP_NTZ(9) unloads as milliseconds, not nanoseconds. Some types must be cast before they unload. You can also cast a number explicitly to choose its Parquet type.

-- Fixed-point NUMBER(12,2) lands as Parquet DECIMAL, precision kept
COPY INTO @my_stage/unload/parquet/
  FROM (SELECT order_id, amount FROM sales)
  FILE_FORMAT = (TYPE = PARQUET);

-- Cast explicitly to pick the Parquet integer width
COPY INTO @my_stage/unload/parquet/
  FROM (SELECT CAST(order_id AS INT) FROM sales)
  FILE_FORMAT = (TYPE = PARQUET);

Micro-Concept 5: Partitioned Unload With PARTITION BY

The PARTITION BY option splits rows into separate files using an expression. Each distinct value of the expression gets its own path. The result is a folder layout in the stage. This is how Snowflake feeds a data lake that reads by directory.

The filenames carry the partition values and a data_ prefix. Snowflake also adds a UUID to each filename. The reason is INCLUDE_QUERY_ID. It defaults to TRUE the moment PARTITION BY is set. The UUID is the query ID of the unload statement.

Two cautions appear in the documentation. An ORDER BY paired with PARTITION BY does not guarantee row order in the files. Snowflake also advises partitioning on dates or timestamps rather than on sensitive string or numeric values. The partition values become visible in the file paths. In production projects I have worked on, PARTITION BY on a date column is the standard way to lay out lake data by day.

-- Split rows into one folder per order status
COPY INTO @my_stage/unload/parts/
  FROM (SELECT order_id, order_status, amount FROM sales)
  PARTITION BY ('status=' || order_status)
  FILE_FORMAT = (TYPE = CSV COMPRESSION = GZIP)
  HEADER = TRUE;
-- Produces paths like .../parts/status=O/data_..._.csv.gz

One account-level detail rounds this out. Two parameters can block unloading entirely. PREVENT_UNLOAD_TO_INLINE_URL stops ad hoc unloads to a cloud URL written into the statement. PREVENT_UNLOAD_TO_INTERNAL_STAGES stops unloads to any internal stage. Both are governance controls an admin sets, not copy options.

Authoritative references for every fact in today’s post. The COPY INTO <location> page and the considerations page are the two worth re-reading the day before the exam.

Two Snowflake guides that walk through the unload steps and the download path.