skip to content

Why does one 50 GB gzipped CSV load slowly into Snowflake even on a large warehouse?

level: middleimportance: should knowfreq 58%

answer

  1. Ask what the unit of work is during a load
  2. Threads take files, not byte ranges
  3. gzip cannot be split into independent chunks
  4. More nodes help only if more files exist
  5. Aim near 100–250 MB compressed per file

basics

~20 s

Snowflake parallelises a bulk load across files, and a single compressed file is handled by one loading thread. Fifty gigabytes in one file leaves most of a large warehouse idle. Split it into many files of roughly 100–250 MB compressed.

solid answer

~40 s

`COPY INTO` distributes **files** across the threads of the warehouse's nodes; it cannot split one gzipped file across threads, because gzip must be decompressed sequentially. So a single 50 GB file is processed by one thread no matter how big the warehouse is — you are paying for an L or XL warehouse and using a sliver of it. Snowflake's guidance is to produce files of roughly **100–250 MB compressed** and to have at least as many files as the warehouse has loading threads. Resizing the warehouse only helps once the file count exceeds the current parallelism. The opposite error is just as bad: hundreds of thousands of 5 KB files pay a fixed per-file overhead (open, authenticate, read metadata) that dominates the actual parsing, and with Snowpipe they also attract a per-file charge.

code

bash · 5 lines
bash
# 50 GB single file: one loading thread does all the work
# split into ~200 MB compressed parts before staging
split -b 1G -d --additional-suffix=.csv orders_full.csv part_
gzip part_*.csv
snowsql -q "PUT file://part_*.csv.gz @raw/orders/dt=2026-08-20/ PARALLEL=16"

go deeper

for a junior

Remember that Snowflake loads files in parallel, one thread per file, and that many mid-sized files beat one giant file.

for a middle

Explain why gzip cannot be split, why warehouse size stops mattering once files run out, and why the guidance lands near 100–250 MB compressed.

for a senior

Diagnose from evidence — COPY_HISTORY per-file durations, Query Profile, warehouse utilisation — and fix upstream at the producer rather than by resizing compute.

for a principal

Set the file-sizing and micro-batching contract for the whole ingestion platform, trading latency against per-file overhead, and decide where loading compute is isolated from query compute.

## How bulk load parallelism actually works `COPY INTO <table> FROM @stage` builds a list of candidate files (from the stage path, the `FILES` list, or a `PATTERN` regex), removes those already recorded in load metadata, and hands the rest out to the warehouse's worker threads. Each thread takes a file, decompresses it, parses it, converts it to Snowflake's columnar micro-partition format and writes it out. The unit of parallelism is therefore **the file**. Two consequences follow, and both show up in interviews: 1. **The number of files caps concurrency.** With one file, one thread works and the rest idle. With eight files on a warehouse that can run dozens of loaders, you still only use eight. 2. **Warehouse size only helps if there is work to spread.** A bigger warehouse means more nodes and more threads. If the file count is smaller than the thread count, doubling the warehouse doubles the credit burn and changes the wall-clock time by nothing. Gzip is the aggravating factor for the single-large-file case: the DEFLATE stream must be read from the start, so the file cannot be chopped into independent ranges the way an uncompressed or block-compressed format could be. ## The recommended shape Snowflake's published loading guidance is to aim for data files of roughly **100–250 MB compressed**, and to generate enough of them that every loading thread has work. That range is a balance: - **Too large** and you serialise (the case in the question) and lose retry granularity — a failure late in a 50 GB file wastes all the work done so far. - **Too small** and per-file fixed costs dominate: object listing, an authenticated GET per object, metadata bookkeeping, and a load-metadata entry per file. Millions of tiny files also make the stage itself slow to list. The fix for the 50 GB file is upstream: have the producer emit `part-00000.csv.gz` … `part-00499.csv.gz`, or split before uploading (`split -b`, or a Spark/Glue writer configured for target file size). If you cannot change the producer, an intermediate step that loads once into a table and re-`UNLOAD`s at a sane size is sometimes cheaper than fighting it every night. ## Diagnosing it The Query Profile for the `COPY` shows the load step and how long it ran; a single-file load presents as one long-running operation with no parallel branches, and the warehouse shows low utilisation for the duration. `COPY_HISTORY` gives per-file row counts and durations, which is the quickest way to see that one file accounted for the whole runtime. If a load is slow *and* the file count is healthy, look elsewhere: a wide `PATTERN` over a bucket root that forces a huge object listing, heavy per-row work in a transforming `COPY`, or a target table with an expensive clustering key. ## Other things that slow bulk loads - **`PATTERN` against a root prefix.** Snowflake must enumerate the objects before it can filter them; a dated path prefix is far cheaper than a regex over everything. - **Transformations inside `COPY`.** The `COPY INTO ... FROM (SELECT $1::int, $2::string FROM @stage)` form supports casts, reordering, subsetting and simple expressions, but every expression is per-row work on the loading thread. - **Format choice.** Parquet arrives already typed and columnar; CSV needs full text parsing and type inference against the target. For very large recurring loads, Parquet usually wins. - **One warehouse doing loads and queries.** Loading competes for the same slots as analytics; a dedicated loading warehouse both isolates the contention and lets you size it for the file count. ## The Snowpipe angle The same sizing advice applies to continuous ingestion but for an additional reason: Snowpipe bills a compute charge **plus a per-file overhead**, so a stream of tiny files is expensive out of proportion to the bytes. Micro-batching at the producer — accumulate for about a minute, then write one file — is the standard remedy, and it is exactly the trade-off between latency and cost an interviewer wants you to articulate. ## The short version to say out loud "Loading parallelism is per file. One big compressed file pins the load to one thread, so a bigger warehouse just costs more. Target roughly 100–250 MB compressed per file, enough files to saturate the threads, and micro-batch rather than emitting thousands of tiny objects."

  • You split the load into 200,000 files of 300 KB each. What new problem have you created?
    Per-file fixed cost now dominates: object listing, an authenticated read and load-metadata bookkeeping per file, none of which scales with the bytes inside. The stage becomes slow to enumerate, and on Snowpipe the per-file charge makes the same volume markedly more expensive. Coalesce toward the 100–250 MB compressed range instead.
  • Does resizing the warehouse from M to 2XL ever speed up a bulk load?
    Yes — when the file count exceeds the current thread count. Extra nodes bring extra loading threads, so a load of thousands of mid-sized files finishes proportionally faster. It does nothing for a load of one or a handful of files, where the bottleneck is available work rather than available compute, and it doubles the credit rate either way.
  • Would loading Parquet instead of gzipped CSV change the picture?
    Partly. Parquet arrives already columnar and typed, so per-row parsing and type conversion are much cheaper, and file sizing is easier to control at the writer. But parallelism is still granted per file, so one enormous Parquet file still underuses the warehouse. Right format plus right file count, not one instead of the other.

It is a supermarket with twenty checkouts and one customer pushing a single enormous trolley. Opening more checkouts changes nothing; splitting the trolley into twenty normal ones does.

saying these in an interview costs you the question

  • Resizing the warehouse to fix a single-file load
  • Believing Snowflake splits a gzip file across threads
  • Thinking more, smaller files are always better
  • Assuming load time scales with warehouse size regardless of file count
  • Ignoring per-file overhead when choosing a micro-batch size

context

resolving links…