Snowpipe ingesting files too small to be efficient

Snowpipe bills twice

Once for the data volume, and once per file. The per-file charge is small in isolation and becomes the dominant term when files are tiny: 100,000 files of 1 MB each cost dramatically more to ingest than 1,000 files of 100 MB carrying the same bytes.

This is the single most common Snowpipe cost surprise, and it is invisible in the credit total because Snowflake reports the combined figure rather than splitting overhead from throughput.

The thresholds

More than 1,000 files ingested and an average below 52,428,800 bytes (50 MB), both over 30 days.

The file-count floor matters: a pipe that has moved twelve small files is not worth changing. The finding is about sustained ingestion patterns, not one-off loads.

Where small files come from

Almost always a streaming producer flushing on a short time interval. A Kinesis Firehose or Kafka connector configured to flush every 60 seconds produces 1,440 files a day per partition, regardless of how much data arrived in each window.

The fix is upstream, not in Snowflake: raise the buffer interval or the size threshold on the producer so it flushes at 100–250 MB rather than on a timer. Most connectors let you set both and flush on whichever comes first.

Averaging file size per pipe in COPY_HISTORY

SELECT pipe_name,
       COUNT(*)                       AS files,
       SUM(file_size)/COUNT(*)/POW(1024,2) AS avg_mb,
       SUM(credits_used)              AS credits
FROM snowflake.account_usage.copy_history
WHERE last_load_time > DATEADD(day,-30,CURRENT_TIMESTAMP())
GROUP BY 1
HAVING files > 1000
ORDER BY credits DESC;

The latency trade

Larger files mean data lands less often. If your consumers genuinely need sub-minute freshness, the small-file cost is the price of that latency and the finding is not actionable. If they read hourly (which most reporting workloads do), you are paying for freshness nobody consumes.