Snowpipes loading more than 1,000 files a month averaging under 50 MB

How Snowpipe bills, and why file size still matters

Snowpipe is now billed at a fixed credit amount per GB loaded, on every edition. Text formats such as CSV and JSON are charged on uncompressed size, and binary formats such as Parquet on observed size. The former model charged for load compute plus a per-1,000-files fee, which made tiny files directly expensive.

Under per-GB billing the file count is no longer a line item, but Snowflake’s file sizing guidance still recommends files of roughly 100 to 250 MB or larger, compressed, because the number of parallel load operations cannot exceed the number of files. The same page notes that files of that size make the per-load overhead immaterial relative to the data loaded. A producer writing a file per event or per second is the usual cause.

Measuring file counts and sizes per pipe

PIPE_USAGE_HISTORY records files and bytes per pipe:

SELECT pipe_name,
       SUM(files_inserted::number)                     AS files_30d,
       ROUND(SUM(bytes_inserted)
             / NULLIF(SUM(files_inserted::number), 0)
             / 1048576, 2)                             AS avg_file_mb,
       SUM(credits_used)                               AS credits_30d
FROM SNOWFLAKE.ACCOUNT_USAGE.PIPE_USAGE_HISTORY
WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY pipe_name
HAVING SUM(files_inserted::number) > 1000
ORDER BY avg_file_mb;

For a per-file view, COPY_HISTORY carries FILE_SIZE and PIPE_NAME for each loaded file. Both views need the USAGE_VIEWER database role and lag by up to 3 and 2 hours respectively.

More than 1,000 files, each under 50 MB on average

ZopNight sums the files the pipe ingested over the last 30 days and requires more than 1,000. It then averages bytes per file over the same window and requires that average to be below 50 MB (52,428,800 bytes). A pipe loading 900 tiny files, or 50,000 files of 60 MB, does not qualify.

Pipes left alone

Without both series for the pipe, or with no datapoints in the window, nothing is raised. Pipes driven by the COPY command from a scheduled warehouse job are outside this check, which reads Snowpipe activity only.

What batching changes, and why no saving is shown

The finding is a governance advisory with no dollar figure. Because current Snowpipe billing is per GB, larger files do not by themselves shrink the credit charge for the same data, and ZopNight does not claim that they do. What improves is load efficiency: fewer, larger files match Snowflake’s sizing guidance, which describes that size as the point where load overhead stops mattering relative to the data loaded.

Batching files before they land

1. Find the producer writing to the pipe’s stage path and check its flush interval and size limit.
2. Buffer output toward 100 to 250 MB compressed. If data arrives slowly, Snowflake suggests creating a new file about once per minute rather than per record.
3. Re-run the query above after a week to confirm the average file size moved.