The 55,000-file day: Snowpipe cost and latency under small-file telemetry
An IoT platform drops device telemetry into S3 as JSON: 55,000 objects per day, 12–20 KB compressed each (about 900 MB/day), arriving continuously. A Snowpipe with AUTO_INGEST lands them in RAW_TELEMETRY in roughly 40 seconds, and the team treats that latency as a hard requirement. After three months, PIPE_USAGE_HISTORY shows the pipe burning about 3.3 credits/day while the data volume itself is negligible. During fleet bursts the pending-file count reaches 20,000 and end-to-end latency stretches past 25 minutes. Two downstream Dynamic Tables with a 5-minute target lag starve as a result. The team's proposed fix is a larger pipe warehouse plus additional S3 event notifications on the same bucket.
1. The team wants to resize the pipe's warehouse and add more S3 event notifications. Evaluate both proposals.
Read the worked answer
Resizing the warehouse is not an available lever. Snowpipe runs on Snowflake-managed compute; there is no warehouse attribute on a pipe to change, so the proposal cannot be executed as stated and would not help if it could. The latency is per-file overhead multiplied by file count, not insufficient compute per file.
More event notifications will not help either. S3 notifications are deduplicated against load metadata, so a second notification path adds cost and noise without adding throughput.
The cost is arithmetic, not a mystery. Snowpipe overhead is billed per 1,000 files: 55,000 / 1,000 × 0.06 = 3.3 credits/day, roughly 100 credits/month for under a gigabyte a day. At a 15 KB average, you are paying for file count, not for data.
The real constraint is the 40-second expectation. Any answer that ignores it is incomplete - consolidating files helps cost but moves latency in the wrong direction.
2. Propose a redesign that fixes cost without breaking the latency requirement, and state the trade-off you are accepting.
Read the worked answer
Two viable paths, and the choice depends on which half of the requirement is negotiable.
Path A - buffer and enlarge files (cost-first). Aggregate upstream (Kinesis Firehose buffering, or the Kafka connector's flush interval/size thresholds) into 100–250 MB compressed objects. Once files land only on close, latency becomes buffered seconds-to-minutes, not 40 seconds. Deduplication by load metadata keeps the pipe safe if a file is retried.
Path B - Snowpipe Streaming (latency-first). Write rows through the streaming SDK/connector instead of staging files. This removes per-file charges entirely and gives sub-second visibility, at the cost of client-side complexity, offset/commit management, and a different operational model from stage-based loading.
Judgement call: if 40 seconds is a genuine product SLO for anomaly detection, pick B. If it is an internal habit, Path A is roughly two orders of magnitude cheaper and is the better engineering answer - but be explicit that you are renegotiating the SLO rather than pretending A meets it.
3. What would you monitor in the first two weeks after the change to prove it worked?
Read the worked answer
Monitor the three things the incident was actually about, not just credits.
- Cost: PIPE_USAGE_HISTORY credits broken out per day, plus file counts from COPY_HISTORY, so you can see whether savings came from fewer files rather than from reduced volume.
- Latency: p50 and p95 of (file arrival → row visible), which is not the same as pipe status. A drop in pending-file count with flat p95 means you moved the bottleneck.
- Downstream health: Dynamic Table refresh history and lag for the two dependent objects; their recovery is the proof that the ingest tier was the constraint.
Set an alert on pending-file count and on credits/day above a threshold, so a regression in the upstream producer's file sizing gets caught before it becomes a quarter of spend. Confirm in the same window that the burst scenario - the 20,000-file backlog - no longer reproduces.