Loading Best Practices & File Sizing
In shortCOPY loads files in parallel, one file per thread. The number and size of your files decide how much of the warehouse actually works.
How it works
The number of load operations that run in parallel can't exceed the number of files being loaded. A single huge file keeps one thread busy while the rest of the warehouse waits. Thousands of tiny files spend more time on per-file overhead than on data.
What to do in each situation
| Situation | What to do | Why |
|---|---|---|
| Files of 100–250 MB compressed (or somewhat larger) | Keep them | Snowflake's documented sweet spot for COPY and Snowpipe |
| One multi-GB export per day | Split it by line into many files before staging | A single file can't use the warehouse's parallelism; files around 100 GB or more aren't recommended at all |
| Thousands of tiny files | Batch them upstream | Per-file overhead dominates. Snowpipe's per-GB billing has no per-file charge, but tiny files still add latency |
| Snowpipe source that takes more than a minute to fill 100–250 MB | Write a new file about once per minute | Staging more often than once a minute doesn't reliably cut latency |
| Everything lands in one folder | Organize paths by source and date (orders/) | COPY and pipes can target a prefix instead of listing everything |
| Loaded files aren't needed afterward | Use PURGE = TRUE on bulk COPY, or a lifecycle rule on the bucket | Smaller stages, faster listing, lower storage |
| A load feels slow | Check the file count before resizing the warehouse | A larger warehouse helps only if there are enough files to keep its threads busy |
Common mistakes
- Expecting the file extension to set the format.
- Overriding one format option on COPY and losing the stage's others.
- Rerunning COPY on already-loaded files and wondering why zero rows loaded.
- Using an external table for heavy, repeated analytics instead of loading or materializing.
- Embedding cloud credentials in CREATE STAGE instead of using a storage integration.
Exam takeaway
- 100–250 MB compressed per file.
- Parallelism is per file; one giant file means one busy thread.

