What does a Redshift UNLOAD to S3 in Parquet format actually write out?

## What UNLOAD is for `UNLOAD` is the mirror image of `COPY`: it takes a query result and writes it to S3 in parallel. It is how a Redshift cluster hands data to the rest of the platform — a data lake, another engine, an archive, a partner feed — without dragging the result set through a client connection. ```sql UNLOAD ('SELECT sale_id, sale_ts, channel, net_amount FROM sales WHERE sale_ts >= ''2026-01-01''') TO 's3://exports/sales/2026-01-01/' IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftUnload' FORMAT AS PARQUET PARTITION BY (channel); ``` Note the doubled quotes: the query is a string literal, so single quotes inside it must be escaped. ## Parallel by default Every slice writes its own portion of the result directly to S3, producing a set of numbered part files under the prefix. This is why an `UNLOAD` of a large table finishes in a fraction of the time a client-side export would take, and why you get many files rather than one. The consumer is expected to treat the prefix as the unit, not any individual file. `PARALLEL OFF` turns that off: the result is written serially to a single object, which is split only when the maximum file size is exceeded. Two reasons to use it: the downstream consumer genuinely needs one file, or the result must preserve the query's `ORDER BY` across the whole output — with parallel writes, ordering only holds within a file. The cost is that the write becomes a single-threaded operation, so reserve it for small results. `MAXFILESIZE` sets the size at which a new file is started, letting you tune output granularity for whatever reads the data next. ## Why Parquet rather than text `FORMAT AS PARQUET` writes a columnar, self-describing, compressed file. Compared to delimited text it is smaller on S3, carries column types so the consumer does not have to infer or configure them, and lets a downstream query engine read only the columns it needs. It is also the format a lake table or an external table over the same S3 location will expect. Format-specific text options — delimiters, headers, quoting, escaping — do not apply to a Parquet unload; the format carries that information itself. ## Partitioning the output `PARTITION BY (col1, col2)` writes Hive-style directories: `channel=web/`, `channel=store/`, and so on. Anything reading the prefix afterwards can eliminate whole directories from a scan by looking at the path alone, which is the same pruning idea a partitioned table uses internally. By default the partition columns are represented only in the path; adding `INCLUDE` keeps them as columns inside the files too, which matters when the consumer reads a single file out of context and still needs the value. Partitioning on a high-cardinality column is the classic mistake here — it produces an enormous number of small directories and files, and the per-object overhead on the read side outweighs any pruning benefit. ## Knowing the write is complete `MANIFEST` writes an additional JSON file listing the objects the unload produced. A consumer that reads the manifest sees exactly the file set of this run, which protects it from picking up leftovers from a previous export or reading a prefix mid-write. Combined with `ALLOWOVERWRITE` — which permits the unload to replace objects already at the destination — and a per-run prefix, this is how repeatable exports are built. Without `ALLOWOVERWRITE`, an unload to a prefix that already contains matching files fails rather than silently mixing two runs' output. That default is deliberately conservative and worth keeping: write each run to its own dated prefix instead of overwriting in place, and the ambiguity disappears. ## Typical uses - **Offloading cold history.** Unload old partitions of a fact table to Parquet in S3, then delete them from the cluster, keeping them queryable through an external table over the same location. - **Feeding other engines.** Any tool that reads Parquet from object storage can consume the output directly. - **Cheap, portable backups.** Parquet on S3 is far cheaper than warehouse storage, self-describing, and restorable with a `COPY`. ## What interviewers listen for That the write is parallel across slices and produces many files; that `PARALLEL OFF` exists and why you would accept its cost; that partitioned output is about pruning on the read side; and that Parquet is chosen for typing and columnar reads, not just for size.