# Snowflake Pipe Ingesting Many Small Files

> Flags Snowpipes loading over 1,000 files in 30 days averaging below 50 MB each, as an unpriced file-batching advisory.

Source: https://zop.dev/integrations/snowflake/recommendations/snowflake-pipe-ingesting-many-small-files

---

## How Snowpipe bills, and why file size still matters

[Snowpipe is now billed](https://docs.snowflake.com/en/user-guide/data-load-snowpipe-billing) 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](https://docs.snowflake.com/en/user-guide/data-load-considerations-prepare) 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](https://docs.snowflake.com/en/sql-reference/account-usage/pipe_usage_history) records files and bytes
per pipe:

```sql
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](https://docs.snowflake.com/en/sql-reference/account-usage/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.