Course contents

SnowPro Core COF-C03 Study GuidePart 4: Data loading and storageLoading Best Practices & File Sizing

Loading Best Practices & File Sizing

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.

The same 10 GB loaded on a warehouse with 8 threads, on the same time scale, illustrative. Left: one 10 GB file keeps thread 1 busy for the whole load while threads 2 to 8 stay idle. Right: 80 files of about 125 MB are shared out, 10 files per thread, so all 8 threads work at once and the load ends much sooner.ONE 10 GB FILE80 FILES × ~125 MBT1T2T3T4T5T6T7T8starttime →thread 1 busy · 7 idleT110 filesT210 filesT310 filesT410 filesT510 filesT610 filesT710 filesT810 filesstarttime →all 8 busy · ends much sooner The same 10 GB loaded on a warehouse with 8 threads, on the same time scale, illustrative. Left: one 10 GB file keeps thread 1 busy for the whole load while threads 2 to 8 stay idle. Right: 80 files of about 125 MB are shared out, 10 files per thread, so all 8 threads work at once and the load ends much sooner.ONE 10 GB FILET1T2T3T4T5T6T7T8starttime →thread 1 busy · 7 idle80 FILES × ~125 MBT110 filesT210 filesT310 filesT410 filesT510 filesT610 filesT710 filesT810 filesstarttime →all 8 busy · ends much sooner
Figure 4.15 The same 10 GB on an 8-thread warehouse, on one time scale (illustrative). One big file keeps one thread busy while seven wait; eighty mid-sized files keep all eight busy, so the same load ends much sooner.

What to do in each situation

SituationWhat to doWhy
Files of 100–250 MB compressed (or somewhat larger)Keep themSnowflake's documented sweet spot for COPY and Snowpipe
One multi-GB export per daySplit it by line into many files before stagingA single file can't use the warehouse's parallelism; files around 100 GB or more aren't recommended at all
Thousands of tiny filesBatch them upstreamPer-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 MBWrite a new file about once per minuteStaging more often than once a minute doesn't reliably cut latency
Everything lands in one folderOrganize paths by source and date (orders/2026/10/26/)COPY and pipes can target a prefix instead of listing everything
Loaded files aren't needed afterwardUse PURGE = TRUE on bulk COPY, or a lifecycle rule on the bucketSmaller stages, faster listing, lower storage
A load feels slowCheck the file count before resizing the warehouseA 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.